[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
object_data := allowed_value_column_object('SECURITY_LEVEL_CODE',allowed_values_table_data); --SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'PON_SECURITY_LEVEL_CODE' AND LANGUAGE=UserEnv('LANG'))
OPEN curvar FOR 'select ' || cols(i) || ' from pon_auction_headers_interface where batch_id = ' || p_batch_id
|| ' and rownum < 2';
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE,
ERROR_MESSAGE_NAME,
column_name,
table_name,
batch_id
)VALUES
(
g_interface_type,
'PON_FIELD_MUST_BE_ENTERED',
cols(i),
'PON_AUCTION_HEADERS_INTERFACE',
p_batch_id
);
SELECT doctype_id,uda_template_id
INTO l_doctype_id, l_uda_template_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
select attr_group_id into l_solnum_group_id
from ego_attr_groups_v
where attr_group_name = 'DOD_SOL'
and attr_group_type = 'PON_AUC_HDRS_EXT_ATTRS';
SELECT 'Y' INTO l_sol_num_given
FROM pon_auction_headers_ext_b
WHERE auction_header_id = p_auction_header_id
AND attr_group_id = l_solnum_group_id;
SELECT DATABASE_COLUMN BULK COLLECT INTO solnum_attr
FROM EGO_ATTRS_V
WHERE APPLICATION_ID=201
AND ATTR_GROUP_NAME = 'DOD_SOL'
AND ATTR_GROUP_TYPE = 'PON_AUC_HDRS_EXT_ATTRS'
AND ATTR_NAME IN ('DODAAC','FISCAL_YEAR','INSTR_TYPE','SERIAL_NUMBER','DELIMITER')
ORDER BY attr_name;
l_query:='SELECT '||l_delimiter||','
|| l_dodaac||',' || l_fiscal_year||','|| l_instrument_type||','|| l_serial_number
|| ' FROM pon_auction_headers_ext_b WHERE auction_header_id = '
|| l_auction_header_id || ' AND attr_group_id = ' || l_solnum_group_id;
UPDATE pon_auction_headers_ext_b
SET C_EXT_ATTR40 = l_document_number
WHERE auction_header_id = p_auction_header_id
AND attr_group_id = l_solnum_group_id;
print_log('Concatenated Document number ' || l_document_number || ' updated rows ' || SQL%ROWCOUNT );
UPDATE pon_auction_headers_all
SET DOCUMENT_NUMBER = l_document_number
WHERE AUCTION_HEADER_ID = p_auction_header_id;
SELECT DISTINCT GROUP_NAME,
BATCH_ID ,
INTERFACE_LINE_ID ,
AUCTION_HEADER_ID ,
AUCTION_LINE_NUMBER ,
ATTR_GROUP_SEQ_NUMBER ,
ACTION
FROM PON_AUC_ATTRIBUTES_INTERFACE
WHERE BATCH_ID =batchId
AND AUCTION_HEADER_ID IN
(SELECT DISTINCT AUCTION_HEADER_ID FROM PON_AUC_ATTRIBUTES_INTERFACE
)
AND AUCTION_LINE_NUMBER = -1;
SELECT BATCH_ID ,
INTERFACE_LINE_ID ,
AUCTION_HEADER_ID ,
AUCTION_LINE_NUMBER,
SEQUENCE_NUMBER ,
ATTRIBUTE_NAME ,
DATATYPE ,
RESPONSE_TYPE ,
RESPONSE_TYPE_NAME ,
MANDATORY_FLAG ,
DISPLAY_ONLY_FLAG ,
DISPLAY_TARGET_FLAG,
VALUE ,
GROUP_CODE ,
GROUP_NAME ,
SCORING_TYPE ,
ATTR_MAX_SCORE ,
WEIGHT ,
INTERNAL_ATTR_FLAG ,
SCORING_METHOD ,
KNOCKOUT_SCORE ,
ACTION
FROM PON_AUC_ATTRIBUTES_INTERFACE
WHERE BATCH_ID =batchId
AND AUCTION_LINE_NUMBER = -1;
l_LAST_AMENDMENT_UPDATE PON_AUCTION_ATTRIBUTES.LAST_AMENDMENT_UPDATE%TYPE;
SELECT 'N' status
INTO l_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID=batchId
);
print_log('BEGIN of section insertion');
/* Insertion of section values from PON_AUC_ATTRIBUTES_INTERFACE interface table to PON_AUCTION_SECTIONS table */
OPEN c_interface_section;
IF(NVL(c_interface_section_rec.action,'INSERT')='INSERT') THEN
-- Check whether the section name already exists are not
BEGIN
SELECT DISTINCT SECTION_NAME
INTO l_section_name_check
FROM PON_AUCTION_SECTIONS
WHERE AUCTION_HEADER_ID = c_interface_section_rec.AUCTION_HEADER_ID
AND SECTION_NAME = c_interface_section_rec.GROUP_NAME;
SELECT MAX(ATTR_GROUP_SEQ_NUMBER)
INTO l_sequence_number
FROM PON_AUCTION_SECTIONS
WHERE AUCTION_HEADER_ID = c_interface_section_rec.AUCTION_HEADER_ID;
INSERT
INTO PON_AUCTION_SECTIONS
(
ATTRIBUTE_LIST_ID ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
ATTR_GROUP_SEQ_NUMBER,
SECTION_NAME ,
section_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by
)
VALUES
(
-1 ,
c_interface_section_rec.AUCTION_HEADER_ID,
-1 ,
l_sequence_number ,
c_interface_section_rec.GROUP_NAME ,
PON_AUCTION_SECTIONS_S.NEXTVAL ,
SYSDATE ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id
);
print_log('END of section insertion');
/* End of section insertion */
print_log('BEGIN of requirement insertion');
IF(NVL(c_interface_attr_rec.action,'INSERT')='INSERT') THEN
-- For requirements
/* Setting the mandatory flag, display only flag, internal attr flag depending on the response value*/
IF(c_interface_attr_rec.RESPONSE_TYPE ='REQUIRED')THEN
c_interface_attr_rec.MANDATORY_FLAG := 'Y';
SELECT MAX(SEQUENCE_NUMBER)
INTO l_sequence_number_attr
FROM pon_auction_attributes
WHERE AUCTION_HEADER_ID = c_interface_attr_rec.AUCTION_HEADER_ID;
SELECT DISTINCT ATTR_GROUP_SEQ_NUMBER
INTO l_ATTR_GROUP_SEQ_NUMBER
FROM PON_AUCTION_SECTIONS
WHERE auction_header_id= c_interface_attr_rec.AUCTION_HEADER_ID
AND SECTION_NAME = c_interface_attr_rec.GROUP_NAME;
SELECT MAX(ATTR_DISP_SEQ_NUMBER)
INTO l_ATTR_DISP_SEQ_NUMBER
FROM PON_AUCTION_ATTRIBUTES
WHERE auction_header_id= c_interface_attr_rec.AUCTION_HEADER_ID
AND SECTION_NAME = c_interface_attr_rec.GROUP_NAME;
SELECT MAX(amendment_number)
INTO l_LAST_AMENDMENT_UPDATE
FROM pon_auction_headers_all
WHERE AUCTION_HEADER_ID = c_interface_attr_rec.AUCTION_HEADER_ID;
l_LAST_AMENDMENT_UPDATE:= NULL;
l_LAST_AMENDMENT_UPDATE:= NVL(l_LAST_AMENDMENT_UPDATE,0);
SELECT COUNT(*)
INTO l_count_acc
FROM pon_attribute_scores_interface
WHERE batch_id = c_interface_attr_rec.batch_id
AND auction_header_id = c_interface_attr_rec.auction_header_id
AND ATTRIBUTE_SEQUENCE_NUMBER = l_sequence_number_attr;
print_log(' Begin validating AND INSERTING acceptable VALUES');
SELECT COUNT(*)
INTO l_count_acc
FROM pon_attribute_scores_interface
WHERE batch_id = c_interface_attr_rec.batch_id
AND auction_header_id = c_interface_attr_rec.auction_header_id
AND ATTRIBUTE_SEQUENCE_NUMBER= l_sequence_number_attr;
acceptance_values_insert(c_interface_attr_rec,l_sequence_number_attr ,l_status);
print_log('End validating AND INSERTING acceptable VALUES');
SELECT MAX(score)
INTO c_interface_attr_rec.attr_max_score
FROM pon_attribute_scores
WHERE ATTRIBUTE_SEQUENCE_NUMBER=l_sequence_number_attr
AND auction_header_id =c_interface_attr_rec.auction_header_id;
/* Validating the requirement before inserting */
l_status :=NULL;
print_log('BEGIN Validating the requirement before inserting');
print_log('END Validating the requirement before inserting');
INSERT
INTO pon_auction_attributes
(
ATTRIBUTE_LIST_ID ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
ATTRIBUTE_NAME ,
SECTION_NAME ,
DATATYPE ,
MANDATORY_FLAG ,
INTERNAL_ATTR_FLAG ,
DISPLAY_ONLY_FLAG ,
DISPLAY_TARGET_FLAG ,
VALUE ,
SCORING_TYPE ,
ATTR_MAX_SCORE ,
WEIGHT ,
SCORING_METHOD ,
SEQUENCE_NUMBER ,
ATTR_LEVEL ,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
MODIFIED_DATE ,
LAST_AMENDMENT_UPDATE,
KNOCKOUT_SCORE
)
VALUES
(
-1 ,
c_interface_attr_rec.AUCTION_HEADER_ID ,
-1 ,
c_interface_attr_rec.ATTRIBUTE_NAME ,
c_interface_attr_rec.GROUP_NAME ,
c_interface_attr_rec.DATATYPE ,
c_interface_attr_rec.MANDATORY_FLAG ,
c_interface_attr_rec.INTERNAL_ATTR_FLAG ,
c_interface_attr_rec.DISPLAY_ONLY_FLAG ,
c_interface_attr_rec.DISPLAY_TARGET_FLAG ,
c_interface_attr_rec.VALUE ,
NVL(c_interface_attr_rec.SCORING_TYPE,'NONE') ,
c_interface_attr_rec.ATTR_MAX_SCORE ,
c_interface_attr_rec.WEIGHT ,
NVL(c_interface_attr_rec.SCORING_METHOD,'NONE') ,
l_sequence_number_attr ,
'HEADER' ,
l_ATTR_GROUP_SEQ_NUMBER ,
l_ATTR_DISP_SEQ_NUMBER ,
SYSDATE ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id ,
SYSDATE ,
l_LAST_AMENDMENT_UPDATE ,
c_interface_attr_rec.KNOCKOUT_SCORE
);
print_log('END of requirement insertion');
IF(c_interface_attr_rec.action='DELETE') THEN
print_log('Begin of requirement deletion');
SELECT sequence_number
INTO c_interface_attr_rec.sequence_number
FROM pon_auction_attributes
WHERE ATTRIBUTE_NAME= c_interface_attr_rec.attribute_name;
print_log('No record to delete');
DELETE
FROM pon_auction_attributes
WHERE sequence_number = c_interface_attr_rec.sequence_number
AND auction_header_id = c_interface_attr_rec.auction_header_id;
DELETE
FROM pon_attribute_scores
WHERE attribute_sequence_number= c_interface_attr_rec.sequence_number
AND auction_header_id = c_interface_attr_rec.auction_header_id;
SELECT Decode(Sum(Nvl(weight,0)),100,'Y','N') INTO l_sum_wt_err
FROM pon_auction_attributes paa
WHERE paa.auction_Header_id = l_auction_header_id
AND Nvl(paa.scoring_METHOD,'NONE') <>'NONE'
AND EXISTS(SELECT 'Y' FROM PON_AUCtion_ATTRIBUTES paa,pon_auction_Headers_all pah WHERE paa.auction_Header_id = l_auction_header_id
AND paa.auction_Header_id = pah.auction_Header_id
AND Nvl(pah.HDR_ATTR_ENABLE_WEIGHTS,'N') = 'Y'
AND Nvl(paa.scoring_METHOD,'NONE') <>'NONE');
PROCEDURE acceptance_values_insert
(
p_interface_attr_rec1 IN ATTRIBUTES_VALUES_VALIDATION,
p_sequence_number_attr IN pon_attribute_scores_interface.ATTRIBUTE_SEQUENCE_NUMBER%TYPE,
l_status IN OUT NOCOPY VARCHAR2
) AS
CURSOR c_attr_score
IS
SELECT *
FROM pon_attribute_scores_interface
WHERE batch_id = p_interface_attr_rec1.l_batch_id
AND auction_header_id = p_interface_attr_rec1.l_auction_header_id
AND ATTRIBUTE_SEQUENCE_NUMBER= p_sequence_number_attr;
SELECT MAX(SEQUENCE_NUMBER)
INTO l_sequence_number_scr
FROM pon_attribute_scores
WHERE auction_header_id =p_interface_attr_rec1.l_auction_header_id
AND attribute_sequence_number=p_sequence_number_attr;
pon_auc_interface_table_pkg.insert_error_interface ( c_attr_score_rec.BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_REQUIREMENT_ERR_11',p_interface_attr_rec1.l_SCORING_METHOD );
SELECT DISTINCT Value
INTO l_value
FROM pon_attribute_scores
WHERE auction_header_id =p_interface_attr_rec1.l_auction_header_id
AND ATTRIBUTE_SEQUENCE_NUMBER= p_sequence_number_attr
AND Upper(Value) =upper(c_attr_score_rec.Value);
pon_auc_interface_table_pkg.insert_error_interface ( c_attr_score_rec.BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_DUP_ACC_BID_VALUES',c_attr_score_rec.value );
/* insert the score record for the text datatype*/
INSERT
INTO pon_attribute_scores
(
AUCTION_HEADER_ID ,
LINE_NUMBER ,
ATTRIBUTE_SEQUENCE_NUMBER,
VALUE ,
FROM_RANGE ,
TO_RANGE ,
SCORE ,
ATTRIBUTE_LIST_ID ,
SEQUENCE_NUMBER ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
c_attr_score_rec.auction_header_id,
-1 ,
p_sequence_number_attr ,
c_attr_score_rec.value ,
NULL ,
NULL ,
c_attr_score_rec.score ,
-1 ,
l_sequence_number_scr ,
SYSDATE ,
-1 ,
SYSDATE ,
-1
);
pon_auc_interface_table_pkg.insert_error_interface(c_attr_score_rec.BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_REQUIREMENT_ERR_11',p_interface_attr_rec1.l_SCORING_METHOD);
/* insert score value for NUM or DAT datatype*/
INSERT
INTO pon_attribute_scores
(
AUCTION_HEADER_ID ,
LINE_NUMBER ,
ATTRIBUTE_SEQUENCE_NUMBER,
VALUE ,
FROM_RANGE ,
TO_RANGE ,
SCORE ,
ATTRIBUTE_LIST_ID ,
SEQUENCE_NUMBER ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
c_attr_score_rec.auction_header_id,
-1 ,
p_sequence_number_attr ,
NULL ,
c_attr_score_rec.from_range ,
c_attr_score_rec.to_range ,
c_attr_score_rec.score ,
-1 ,
l_sequence_number_scr ,
SYSDATE ,
-1 ,
SYSDATE ,
-1
);
SELECT from_range,
to_range
FROM pon_attribute_scores
WHERE auction_header_id =p_attr_score_rec.l_auction_header_id
AND attribute_sequence_number=p_attr_score_rec.l_attribute_sequence_number;
SELECT To_Number(NVL(p_attr_score_rec.l_from_range,0)),
To_Number(NVL(p_attr_score_rec.l_to_range,0))
INTO l_from_range_num,
l_to_range_num
FROM dual;
pon_auc_interface_table_pkg.insert_error_interface ( p_attr_score_rec.l_BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_FROM_TO_ERR_NUM_R',l_from_range_num );
SELECT COUNT(*)
INTO l_count
FROM pon_attribute_scores
WHERE auction_header_id =p_attr_score_rec.l_auction_header_id
AND attribute_sequence_number=p_attr_score_rec.l_attribute_sequence_number;
pon_auc_interface_table_pkg.insert_error_interface ( p_attr_score_rec.l_BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_FROM_TO_ERR_NUM_R',l_from_range_num );
pon_auc_interface_table_pkg.insert_error_interface ( p_attr_score_rec.l_BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_FROM_TO_ERR_NUM_R',l_from_range_num );
SELECT 'N'
INTO l_status
FROM dual
WHERE (l_from_range_num BETWEEN t_from_range_num AND t_to_range_num)
OR (l_to_range_num BETWEEN t_from_range_num AND t_to_range_num);
pon_auc_interface_table_pkg.insert_error_interface ( p_attr_score_rec.l_BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_OVERLAP_RANGES',p_attr_score_rec.l_from_range );
SELECT To_date(NVL(p_attr_score_rec.l_from_range,sysdate),'dd-mm-yyyy'),
To_date(NVL(p_attr_score_rec.l_to_range,sysdate),'dd-mm-yyyy')
INTO l_from_range_dat,
l_to_range_dat
FROM dual;
pon_auc_interface_table_pkg.insert_error_interface ( p_attr_score_rec.l_BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_FROM_TO_ERR_NUM_R',l_from_range_dat );
SELECT COUNT(*)
INTO l_count
FROM pon_attribute_scores
WHERE auction_header_id =p_attr_score_rec.l_auction_header_id
AND attribute_sequence_number=p_attr_score_rec.l_attribute_sequence_number;
pon_auc_interface_table_pkg.insert_error_interface ( p_attr_score_rec.l_BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_FROM_TO_ERR_NUM_R',l_from_range_dat );
pon_auc_interface_table_pkg.insert_error_interface ( p_attr_score_rec.l_BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_FROM_TO_ERR_NUM_R',l_from_range_dat );
SELECT 'N'
INTO l_status
FROM dual
WHERE (l_from_range_dat BETWEEN t_from_range_dat AND t_to_range_dat)
OR (l_to_range_dat BETWEEN t_from_range_dat AND t_to_range_dat);
pon_auc_interface_table_pkg.insert_error_interface ( p_attr_score_rec.l_BATCH_ID,NULL,'pon_attribute_scores_interface','PON','PON_AUC_OVERLAP_RANGES',p_attr_score_rec.l_from_range );
SELECT * FROM PON_NEG_TEAM_INTERFACE WHERE BATCH_ID =batchId
AND user_name <> neg_header_record_data.trading_partner_contact_name;
l_LAST_AMENDMENT_UPDATE PON_AUCTION_ATTRIBUTES.LAST_AMENDMENT_UPDATE%TYPE;
/* If the mode is insert */
IF(NVL(c_interface_neg_team_rec.ACTION,'INSERT')='INSERT') THEN
print_log('c_interface_neg_team_rec.user_id' || c_interface_neg_team_rec.user_id);
IF(c_interface_neg_team_rec.ACTION='DELETE') THEN
print_log('In delete condition');
DELETE
FROM PON_NEG_TEAM_MEMBERS
WHERE AUCTION_HEADER_ID = c_interface_neg_team_rec.auction_header_id
AND USER_ID =
(SELECT USER_ID
FROM fnd_user
WHERE user_name LIKE Upper(c_interface_neg_team_rec.user_name)
)
AND MEMBER_TYPE='N';
print_log('Record has been deleted');
SELECT DISTINCT PER.FULL_NAME ,
EMP.USER_NAME ,
EMP.USER_ID ,
EMP.EMPLOYEE_ID
INTO l_full_name ,
l_user_name ,
l_user_id ,
l_employee_id
FROM PER_ALL_ASSIGNMENTS_F ASS,
FND_USER EMP ,
PER_ALL_PEOPLE_F PER ,
PER_ALL_POSITIONS POS ,
PON_AUCTION_HEADERS_ALL PAH ,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE ASS.PERSON_ID = EMP.EMPLOYEE_ID
AND ASS.POSITION_ID = POS.POSITION_ID(+)
AND ASS.PRIMARY_FLAG = 'Y'
AND ((ASS.ASSIGNMENT_TYPE = 'E'
AND PER.CURRENT_EMPLOYEE_FLAG = 'Y')
OR (ASS.ASSIGNMENT_TYPE = 'C'
AND PER.CURRENT_NPW_FLAG = 'Y'))
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE AND ASS.EFFECTIVE_END_DATE
AND PER.PERSON_ID = EMP.EMPLOYEE_ID
AND EMP.USER_NAME = username
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND PAH.AUCTION_HEADER_ID = l_auction_header_id
AND PAH.ORG_ID = FSP.ORG_ID
AND FSP.BUSINESS_GROUP_ID = PER.BUSINESS_GROUP_ID;
insert_collabteam_member(auction_header_id, -- auction_header_id
l_user_id, -- user_id from fnd
l_user_name, -- user_name from fnd
l_menu_name, -- MenuName
l_member_type, -- Member Type
NVL(l_approver_flag,'N'), -- Approver_flag
task_name, -- Task Name
target_date, -- Target_date
SYSDATE, -- Creation_date
fnd_global.user_id, -- Created_by
SYSDATE, -- last_update_date
fnd_global.user_id); -- last_updated_by
SELECT DISTINCT PER.FULL_NAME ,
SUP.USER_NAME ,
SUP.USER_ID ,
SUP.EMPLOYEE_ID
INTO lm_full_name ,
lm_user_name ,
lm_user_id ,
lm_employee_id
FROM PER_ALL_ASSIGNMENTS_F ASS,
PER_ALL_ASSIGNMENTS_F SUPASS ,
FND_USER SUP ,
FND_USER EMP ,
PER_ALL_PEOPLE_F PER ,
PER_ALL_POSITIONS POS
WHERE ASS.PERSON_ID = EMP.EMPLOYEE_ID
AND ASS.SUPERVISOR_ID = SUP.EMPLOYEE_ID
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.ASSIGNMENT_TYPE IN ('E', 'C')
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE AND ASS.EFFECTIVE_END_DATE
AND SUPASS.PERSON_ID = ASS.SUPERVISOR_ID
AND SUPASS.POSITION_ID = POS.POSITION_ID(+)
AND SUPASS.PRIMARY_FLAG = 'Y'
AND ((SUPASS.ASSIGNMENT_TYPE = 'E'
AND PER.CURRENT_EMPLOYEE_FLAG = 'Y')
OR (SUPASS.ASSIGNMENT_TYPE = 'C'
AND PER.CURRENT_NPW_FLAG = 'Y'))
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN SUPASS.EFFECTIVE_START_DATE AND SUPASS.EFFECTIVE_END_DATE
AND SUP.START_DATE <= SYSDATE
AND NVL(SUP.END_DATE, SYSDATE) >= SYSDATE
AND PER.PERSON_ID = SUP.EMPLOYEE_ID
AND EMP.USER_NAME = username;
insert_collabteam_member(auction_header_id, -- auction_header_id
lm_user_id, -- user_id from fnd
lm_user_name, -- user_name from fnd
'PON_SOURCING_EDITNEG', -- MenuName
'M', -- Member Type
NVL(manager_approver_flag,'Y'), -- Approver_flag
task_name, -- Task Name
target_date, -- Target_date
SYSDATE, -- Creation_date
fnd_global.user_id, -- Created_by
SYSDATE, -- last_update_date
fnd_global.user_id); -- last_updated_by
SELECT DISTINCT NEG_TEAM_ENABLED_FLAG
INTO l_neg_team_enabled_flag
FROM pon_auction_headers_all
WHERE auction_header_id = l_auction_header_id;
SELECT user_id
INTO l_user_id
FROM
(SELECT users.user_id
FROM pon_employees_current_v emp,
fnd_user users ,
hr_all_organization_units_tl orgs
WHERE emp.person_id = users.employee_id
AND users.start_date <= SYSDATE
AND NVL(users.end_date, SYSDATE) >= SYSDATE
AND emp.organization_id = orgs.organization_id
AND orgs.language = USERENV('LANG')
AND users.user_name LIKE Upper(username)
);
print_log('Inserting the record');
insert_collabteam_member(auction_header_id, -- auction_header_id
l_user_id, -- user_id from fnd
username, -- user_name from fnd
NVL(menu_name,'PON_SOURCING_SCORENEG'), -- MenuName
'N', -- Member Type
NVL(approver_flag,'N'), -- Approver_flag
task_name, -- Task Name
target_date, -- Target_date
SYSDATE, -- Creation_date
fnd_global.user_id, -- Created_by
SYSDATE, -- last_update_date
fnd_global.user_id); -- last_updated_by
SELECT COUNT(*)
INTO l_count
FROM PON_NEG_TEAM_MEMBERS pnt
WHERE pnt.auction_header_id = p_auction_header_id
AND pnt.list_id = -1
AND pnt.user_id = p_user_id;
SELECT COUNT(*)
INTO l_count1
FROM PON_NEG_TEAM_MEMBERS pnt
WHERE pnt.auction_header_id = p_auction_header_id
AND PNT.LIST_ID = -1
AND member_type = 'C';
PROCEDURE insert_collabteam_member
(
auction_header_id IN NUMBER,
user_id IN NUMBER,
user_name IN VARCHAR2,
menu_name IN VARCHAR2,
member_type IN VARCHAR2,
approver_flag IN VARCHAR2,
task_name IN VARCHAR2,
target_date IN DATE,
creation_date IN DATE,
created_by IN NUMBER,
last_update_date IN DATE,
last_updated_by IN NUMBER)
IS
l_user_id NUMBER;
INSERT
INTO PON_NEG_TEAM_MEMBERS
(
auction_header_id,
list_id ,
user_name ,
menu_name ,
member_type ,
approver_flag ,
task_name ,
target_date ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
user_id
)
VALUES
(
auction_header_id,
-1 ,
user_name ,
menu_name ,
member_type ,
approver_flag ,
task_name ,
target_date ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
user_id
);
END insert_collabteam_member;
SELECT * FROM pon_bid_parties_interface WHERE batch_id=p_batch_id;
l_LAST_UPDATE_DATE PON_BID_PARTIES_INTERFACE.LAST_UPDATE_DATE%TYPE;
l_LAST_UPDATED_BY PON_BID_PARTIES_INTERFACE.LAST_UPDATED_BY%TYPE;
l_LAST_AMENDMENT_UPDATE PON_BIDDING_PARTIES.LAST_AMENDMENT_UPDATE%TYPE;
SELECT 'E' status
INTO x_return_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
);
l_LAST_UPDATE_DATE :=SYSDATE;
l_LAST_UPDATED_BY :=FND_GLOBAL.USER_ID;
/*** If the action is insert ***/
IF (NVL(L_ACTION,'INSERT')='INSERT') THEN
-- If the vendor_name and vendor_id is not null
IF (L_VENDOR_NAME IS NOT NULL) THEN
SELECT VENDOR_ID, PARTY_ID
INTO L_VENDOR_ID, l_TRADING_PARTNER_ID
FROM AP_SUPPLIERS
WHERE VENDOR_NAME=L_VENDOR_NAME;
SELECT VENDOR_NAME, PARTY_ID
INTO L_VENDOR_NAME, l_TRADING_PARTNER_ID
FROM AP_SUPPLIERS
WHERE VENDOR_ID=L_VENDOR_ID;
SELECT ORG_ID INTO L_ORG_ID
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = l_AUCTION_HEADER_ID;
SELECT VENDOR_SITE_CODE
INTO L_VENDOR_SITE_CODE
FROM AP_SUPPLIER_SITES_ALL
WHERE VENDOR_SITE_ID=L_VENDOR_SITE_ID
AND ORG_ID =L_ORG_ID;
SELECT VENDOR_SITE_ID
INTO L_VENDOR_SITE_ID
FROM AP_SUPPLIER_SITES_ALL
WHERE VENDOR_SITE_CODE=L_VENDOR_SITE_CODE
AND ORG_ID =L_ORG_ID;
SELECT PERSON_LAST_NAME
||','
||PERSON_FIRST_NAME
INTO L_TRADING_PARTNER_CONTACT_NAME
FROM HZ_PARTIES
WHERE PARTY_ID=L_TRADING_PARTNER_CONTACT_ID;
SELECT 'Y' INTO l_status
FROM PON_BIDDING_PARTIES
WHERE TRADING_PARTNER_NAME=L_VENDOR_NAME
AND VENDOR_SITE_ID =NVL(L_VENDOR_SITE_ID,VENDOR_SITE_ID)
AND auction_header_id =l_auction_header_id;
print_log('Supplier cannot be invited multiple times for the same Supplier Site or without a Supplier Site selection. Please select distinct Supplier Sites when inviting a supplier multiple times.');
SELECT MAX(amendment_number)
INTO l_LAST_AMENDMENT_UPDATE
FROM pon_auction_headers_all
WHERE AUCTION_HEADER_ID = l_AUCTION_HEADER_ID;
l_LAST_AMENDMENT_UPDATE:= NULL;
l_LAST_AMENDMENT_UPDATE:= NVL(l_LAST_AMENDMENT_UPDATE,0);
/* Insert Into PON_BIDDING_PARTIES */
INSERT
INTO PON_BIDDING_PARTIES
(
AUCTION_HEADER_ID ,
LIST_ID ,
SEQUENCE ,
TRADING_PARTNER_NAME ,
TRADING_PARTNER_ID ,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID ,
VENDOR_SITE_ID ,
VENDOR_SITE_CODE ,
ADDITIONAL_CONTACT_EMAIL ,
ACCESS_TYPE ,
ROUND_NUMBER ,
LAST_AMENDMENT_UPDATE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
L_AUCTION_HEADER_ID ,
-1 ,
L_SEQUENCE ,
L_VENDOR_NAME ,
L_TRADING_PARTNER_ID ,
L_TRADING_PARTNER_CONTACT_NAME,
L_TRADING_PARTNER_CONTACT_ID ,
NVL(L_VENDOR_SITE_ID, -1) ,
NVL(L_VENDOR_SITE_CODE,-1) ,
L_ADDITIONAL_CONTACT_EMAIL ,
'FULL' ,
1 ,
L_LAST_AMENDMENT_UPDATE ,
L_CREATION_DATE ,
L_CREATED_BY ,
L_LAST_UPDATE_DATE ,
L_LAST_UPDATED_BY
);
INSERT ALL
WHEN (sel_vendor_name IS NULL AND sel_vendor_id IS NULL )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
COLUMN_NAME,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_FIELD_MUST_BE_ENTERED',
'VENDOR_NAME',
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (sel_vendor_name IS NOT NULL AND sel_vendor_id IS NOT NULL
AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIERS
WHERE VENDOR_NAME=sel_vendor_name AND VENDOR_ID=sel_vendor_id))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
TOKEN1_NAME,
TOKEN1_VALUE,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_IMPORT_INV_VENDOR',
'VENDOR_NAME',
sel_vendor_name,
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (sel_vendor_id IS NOT NULL
AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIERS
WHERE VENDOR_ID=sel_vendor_id))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
TOKEN1_NAME,
TOKEN1_VALUE,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_IMPORT_INV_VENDORID',
'VENDOR_ID',
sel_vendor_id,
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (sel_vendor_NAME IS NOT NULL
AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIERS
WHERE VENDOR_NAME=sel_vendor_NAME))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
TOKEN1_NAME,
TOKEN1_VALUE,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_IMPORT_INV_VENDORNAME',
'VENDOR_NAME',
sel_vendor_NAME,
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (sel_vendor_site_code IS NOT NULL AND sel_vendor_site_id IS NOT NULL
AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIER_SITES_ALL
WHERE vendor_site_code=sel_vendor_site_code AND vendor_site_id=sel_vendor_site_id))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
TOKEN1_NAME,
TOKEN1_VALUE,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_IMPORT_INV_VENDORSITE',
'VENDOR_SITE',
sel_vendor_site_code,
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (sel_vendor_site_id IS NOT NULL
AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIER_SITES_ALL
WHERE vendor_site_id=sel_vendor_site_id))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
TOKEN1_NAME,
TOKEN1_VALUE,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_IMPORT_INV_VENDORSITEID',
'SITE_ID',
sel_vendor_site_id,
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (sel_vendor_site_code IS NOT NULL
AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIER_SITES_ALL
WHERE vendor_site_code=sel_vendor_site_code))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
TOKEN1_NAME,
TOKEN1_VALUE,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_IMPORT_INV_VENDORSITECODE',
'SITE_CODE',
sel_vendor_site_code,
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (sel_tpc_id IS NOT NULL
AND NOT EXISTS(SELECT 1 FROM hz_parties
WHERE party_id=sel_tpc_id))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
TOKEN1_NAME,
TOKEN1_VALUE,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_IMPORT_INV_VENDORTPC',
'TPC',
sel_tpc_id,
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (sel_tpc_id IS NULL AND sel_add_mail IS null)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
COLUMN_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_FIELD_MUST_BE_ENTERED',
'trading_partner_contact_name',
p_batch_id,
'PON_BID_PARTIES_INTERFACE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
SELECT
vendor_name sel_vendor_name,
vendor_id sel_vendor_id,
vendor_site_code sel_vendor_site_code,
vendor_site_id sel_vendor_site_id,
trading_partner_contact_id sel_tpc_id,
trading_partner_contact_name sel_tpc_name,
additional_contact_email sel_add_mail
FROM pon_bid_parties_interface
WHERE batch_id = p_batch_id
AND action='INSERT';
SELECT batch_id ,
AUCTION_HEADER_ID ,
auction_LINE_NUMBER,
SEQUENCE_NUMBER ,
SCORING_TYPE ,
datatype ,
ATTR_MAX_SCORE ,
WEIGHT
FROM PON_AUC_ATTRIBUTES_INTERFACE
WHERE batch_id = p_batch_id
AND NVL(SCORING_TYPE,'NONE') <> 'NONE'
AND auction_LINE_NUMBER <> -1;
SELECT paa_int.attribute_name att_name,
paa_int.auction_line_number line_num ,
paa_int.sequence_number
FROM pon_auc_attributes_interface paa_int,
pon_item_prices_interface p1
WHERE paa_int.batch_id = p_batch_id
AND p1.batch_id = paa_int.batch_id
AND p1.action = g_update_action
AND paa_int.auction_line_number = p1.auction_line_number
AND paa_int.auction_LINE_NUMBER <> -1
AND NOT EXISTS
(SELECT 'x'
FROM pon_auction_attributes auction_attributes
WHERE paa_int.auction_header_id = auction_attributes.auction_header_id
AND paa_int.auction_line_number = auction_attributes.line_number
AND paa_int.attribute_name = auction_attributes.attribute_name
)
ORDER BY paa_int.auction_line_number,
paa_int.sequence_number FOR UPDATE OF sequence_number ;
SELECT interface_line_id,group_type
FROM pon_item_prices_interface
WHERE batch_id = p_batch_id
FOR UPDATE OF disp_line_number,document_disp_line_number,
sub_line_sequence_number,parent_line_number
ORDER BY interface_line_id;
SELECT doctype_id ,
trading_partner_id ,
trading_partner_contact_id ,
contract_type ,
NVL(GLOBAL_AGREEMENT_FLAG,'N') ,
DECODE(NVL(progress_payment_type,'NONE'),'NONE','N','Y'),
org_id ,
currency_code ,
NVL(auction_round_number,0) ,
NVL(amendment_number,0),
close_bidding_date,
po_start_date,
po_end_date,
price_break_response,
price_tiers_indicator
INTO l_doctype_id ,
l_trading_partner_id ,
l_trading_partner_contact_id,
l_contract_type ,
l_global_flag ,
L_is_complex ,
l_org_id ,
l_currency_code ,
l_auction_round_number ,
l_amendment_number ,
l_close_bidding_date ,
l_po_start_date ,
l_po_end_date ,
l_price_break_response ,
l_price_tiers_indicator
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT fnd_global.login_id,
fnd_global.CURRENT_LANGUAGE
INTO g_login_id,
g_curr_lang
FROM dual;
/*select fnd_currency.GET_INFO(l_currency_code,l_precision,l_ext_precision,l_minAcctUnit)
INTO l_price_precision FROM dual;*/
SELECT 'N' status
INTO l_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
);
UPDATE pon_item_prices_interface
SET disp_line_number = rec.interface_line_id,
sub_line_sequence_number = rec.interface_line_id,
document_disp_line_number = rec.interface_line_id
WHERE CURRENT OF line_cur;
UPDATE pon_item_prices_interface t1
SET disp_line_number = rec.interface_line_id,
sub_line_sequence_number = Decode (is_child,'N',parent_seq,child_seq),
document_disp_line_number = Decode (is_child,'N',parent_seq,parent_seq || '.' || child_seq),
parent_line_number=Decode(is_child,'Y',parent_line)
WHERE CURRENT OF line_cur;
attributes, cost factors etc. While updating any line, it is expected to insert all children data
in respective interface tables. The children that are missing from previous amendments
are assumed as to-be-deleted children and will be deleted from main txn tables.
*/
PON_CP_INTRFAC_TO_TRANSACTION.SYNCH_FROM_INTERFACE(p_batch_id,
p_auction_header_id,
g_user_id,
l_trading_partner_id,
'N',
x_number_of_lines,
x_max_disp_line,
x_last_line_close_date,
x_result,
x_error_code,
x_error_message
);
SELECT Max(document_disp_line_number) INTO l_last_line_num
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND parent_line_number IS NULL;
UPDATE pon_auction_headers_all pah
SET (MAX_INTERNAL_LINE_NUM,
NUMBER_OF_LINES,
last_line_number) = (SELECT Max(line_number), Count(line_number),l_last_line_num
FROM pon_auction_item_prices_all paip
WHERE paip.auction_header_id = p_auction_header_id)
WHERE auction_header_id = p_auction_header_id;
so we will have to update interface tables with new sequence number
*/
IF (l_auction_round_number > 0 OR l_amendment_number > 0) THEN
LOOP
EXIT WHEN c_new_attributes%NOTFOUND;
SELECT sequence_number
INTO c_seq_num2
FROM pon_auction_attributes
WHERE auction_header_id = p_auction_HEAder_id
AND line_number = c_line_num
AND attribute_name = c_att_name;
UPDATE pon_auc_attributes_interface
SET sequence_number = c_seq_num2
WHERE CURRENT OF c_new_attributes;
UPDATE pon_attribute_scores_interface
SET attribute_sequence_number = c_seq_num2
WHERE batch_id = p_batch_id
AND auction_header_id = p_auction_header_id
AND line_number = c_line_num
AND attribute_sequence_number = c_seq_num;
/* PON_CP_INTRFAC_TO_TRANSACTION.SYNCH_FROM_INTERFACE api would have already inserted attributes
But scores are not yet inserted. Also the above api inserts zero for weight and scores
in attributes table. This needs to be corrected
*/
VAL_ATTR_SCORES(
p_auction_header_id,
NULL,
NULL,
g_user_id,
g_login_id,
p_batch_id
);
SELECT 'N' status
INTO l_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID=p_batch_Id
);
UPDATE pon_auction_attributes paa
SET paa.SCORING_TYPE = c_scored_atts_rec.scoring_type,
paa.WEIGHT = c_scored_atts_rec.WEIGHT ,
paa.ATTR_MAX_SCORE = c_scored_atts_rec.ATTR_MAX_SCORE
WHERE paa.auction_header_id = c_scored_atts_rec.auction_header_id
AND paa.line_number = c_scored_atts_rec.auction_line_number
AND paa.sequence_number = c_scored_atts_rec.sequence_number;
INSERT
INTO pon_attribute_scores
(
AUCTION_HEADER_ID ,
LINE_NUMBER ,
ATTRIBUTE_SEQUENCE_NUMBER,
VALUE ,
FROM_RANGE ,
TO_RANGE ,
SCORE ,
ATTRIBUTE_LIST_ID ,
SEQUENCE_NUMBER ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
SELECT p_auction_Header_id ,
pasi.line_number ,
pasi.attribute_sequence_number ,
DECODE(c_scored_atts_rec.datatype,'TXT',pasi.Value,NULL) ,
DECODE(c_scored_atts_rec.datatype,'DAT',pasi.FROM_RANGE,'NUM',pasi.FROM_range,NULL),
DECODE(c_scored_atts_rec.datatype,'DAT',pasi.TO_RANGE,'NUM',pasi.TO_RANGE,NULL) ,
pasi.score ,
-1 ,
pasi.sequence_number ,
SYSDATE ,
g_user_id ,
SYSDATE ,
g_user_id
FROM pon_attribute_scores_interface pasi
WHERE pasi.batch_id = p_batch_id
AND pasi.auction_header_id = p_auction_Header_id
AND NVL(pasi.line_number,-1) <> -1
AND pasi.line_number = c_scored_atts_rec.auction_line_number
AND pasi.attribute_sequence_number = c_scored_atts_rec.sequence_number;
UPDATE pon_auc_payments_interface papi
SET papi.auction_Header_id = p_auction_header_id
WHERE papi.batcH_id = p_batch_id;
SELECT 'N' status
INTO l_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID=p_batch_Id
);
the price breaks for those lines will be picked and inserted into pon_auction_shipments_all table.
For amendments, all the existing price breaks will be deleted from pon_auction_shipments_all table
for those lines marked with '#' action in lines interface table.
Then only those price breaks given in interface tables will be added.
Also this procedure inserts data from price differentials interface table
to main txn table for price breaks with price differentials.
*/
PROCEDURE add_price_breaks
(
p_batch_id IN NUMBER ,
p_auction_header_id IN NUMBER,
x_result IN OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2 )
AS
TYPE numbers IS TABLE OF NUMBER;
SELECT auction_header_id, auction_line_number, shipment_number
FROM pon_auc_price_breaks_interface ppbi
WHERE ppbi.batch_id = p_batch_id
AND Nvl(HAS_PRICE_DIFFERENTIALS_FLAG,'N') = 'Y'
*/
BEGIN
-- for amendments
/*
we will delete price break information in txn tables for lines which are
being updated in this amendment.
later we will insert only price breaks given in interface tables.
*/
print_log(' Entering add_price_breaks ');
SELECT price_tiers_indicator INTO l_price_tiers_indicator
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT shipment_number,
line_number bulk collect
INTO l_shipment_number,
l_line_number
FROM pon_auction_shipments_all auction_shipments,
pon_item_prices_interface line_interface
WHERE line_interface.action = g_update_action
AND line_interface.batch_id = p_batch_id
AND line_interface.auction_header_id = p_auction_header_id
AND auction_shipments.auction_header_id = line_interface.auction_header_id
AND auction_shipments.line_number = line_interface.auction_line_number;
DELETE
FROM PON_PRICE_DIFFERENTIALS price_diffs
WHERE price_diffs.auction_header_id = p_auction_header_id
AND price_diffs.line_number = l_line_number(x)
AND price_diffs.shipment_number = l_shipment_number(x);
DELETE
FROM pon_auction_shipments_all auction_shipments
WHERE auction_shipments.auction_header_id = p_auction_header_id
AND auction_shipments.line_number = l_line_number(x)
AND auction_shipments.shipment_number = l_shipment_number(x);
INSERT
INTO pon_auction_shipments_all
(
auction_header_id ,
line_number ,
shipment_number ,
shipment_type ,
ship_to_organization_id ,
ship_to_location_id ,
quantity ,
price ,
effective_start_date ,
effective_end_date ,
org_id ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
has_price_differentials_flag,
DIFFERENTIAL_RESPONSE_TYPE ,
MAX_QUANTITY
)
SELECT ppbi.auction_header_id ,
ppbi.auction_line_number ,
ppbi.shipment_number ,
decode(l_price_tiers_indicator,'QUANTITY_BASED', 'QUANTITY BASED','PRICE_BREAK','PRICE BREAK'),
ppbi.ship_to_organization_id ,
ppbi.ship_to_location_id ,
ppbi.quantity ,
ppbi.price ,
ppbi.effective_start_date ,
ppbi.effective_end_date ,
ppbi.org_id ,
sysdate ,
fnd_global.USER_id ,
sysdate ,
fnd_global.USER_id ,
fnd_global.login_id ,
Nvl(ppbi.HAS_PRICE_DIFFERENTIALS_FLAG,'N'),
ppbi.DIFFERENTIAL_RESPONSE_TYPE ,
ppbi.MAX_QUANTITY
FROM pon_auc_price_breaks_interface ppbi,
pon_item_prices_interface pipi
WHERE ppbi.batch_id = p_batch_id
AND ppbi.batch_id = pipi.batch_id
AND ppbi.auction_line_number = pipi.auction_line_number
AND Nvl(pipi.action,g_add_action) IN (g_add_action,g_update_action);
print_Log(' inserted price breaks ');
INSERT
INTO PON_PRICE_DIFFERENTIALS fields
(
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
PRICE_DIFFERENTIAL_NUMBER,
PRICE_TYPE ,
MULTIPLIER ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT pdf_int.AUCTION_HEADER_ID,
pdf_int.AUCTION_LINE_NUMBER ,
pdf_int.auction_shipment_number,
pdf_int.SEQUENCE_NUMBER ,
pdf_int.PRICE_TYPE ,
pdf_int.MULTIPLIER ,
sysdate ,
fnd_global.USER_id ,
sysdate ,
fnd_global.USER_id ,
fnd_global.login_id
FROM pon_auc_price_differ_int pdf_int,
pon_auc_price_breaks_interface ppb_int
WHERE pdf_int.batch_id = p_batch_id
AND NVL(ppb_int.HAS_PRICE_DIFFERENTIALS_FLAG,'N') = 'Y'
AND pdf_int.batch_id = ppb_int.batch_id
AND pdf_int.auction_line_number = ppb_int.auction_line_number
AND pdf_int.auction_shipment_number = ppb_int.shipment_number
AND pdf_int.auction_shipment_Number <> -1
AND pdf_int.auction_shipment_number IS NOT null;
UPDATE pon_auc_price_breaks_interface pb_int
SET org_id =
(SELECT ORG_ID
FROM pon_auction_headers_all
WHERE auction_header_id = pb_int.auction_header_id
)
WHERE batch_id = p_batch_id;
UPDATE pon_auc_price_breaks_interface pb_int
SET SHIP_TO_ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM hr_all_organization_units
WHERE name = 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 po_v.location_code = 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 ) 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_auc_price_differ_int PPD
WHERE ppd.batch_id = p_batch_id
AND PPD.AUCTION_HEADER_ID = p_auction_header_id
AND PPD.auction_LINE_NUMBER = sel_line_number
AND PPD.auction_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
)
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,
papbi.SHIPMENT_NUMBER sel_shipment_number ,
papbi.PRICE sel_price ,
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 ,
papbi.DIFFERENTIAL_RESPONSE_TYPE sel_differential_response_type
FROM PON_AUCTION_ITEM_PRICES_ALL PAIP,
pon_auc_price_breaks_interface papbi
WHERE PAIP.AUCTION_HEADER_ID = p_auction_header_id
AND papbi.AUCTION_HEADER_ID = p_auction_header_id
AND PAIP.LINE_NUMBER = papbi.auction_LINE_NUMBER;
UPDATE pon_auc_price_differ_int diff
SET price_type = (SELECT price_type FROM po_price_diff_lookups_v WHERE price_type_name = diff.price_type )
WHERE diff.auction_header_id = p_auction_header_id
AND diff.price_type_name IS NOT null;
INSERT ALL
WHEN (sel_seq_num IS NULL )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
COLUMN_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type , --INTERFACE_TYPE
'PON_FIELD_MUST_BE_ENTERED' , -- ERROR_MESSAGE_NAME
'SEQUENCE_NUMBER',
p_request_id , -- REQUEST_ID
p_batch_id , -- BATCH_ID
'PON_AUC_PRICE_DIFFER_INT', -- ENTITY_TYPE
p_auction_header_id , -- AUCTION_HEADER_ID
sel_line_num , -- LINE_NUMBER
sel_ship_num , -- SHIPMENT_NUMBER
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 (sel_price_type IS NULL
OR NOT EXISTS (SELECT 1 FROM po_price_diff_lookups_v WHERE price_differential_type = sel_price_type))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type , --INTERFACE_TYPE
'PON_IMPORT_PRICEDIFF_TYPE' , -- ERROR_MESSAGE_NAME
p_request_id , -- REQUEST_ID
p_batch_id , -- BATCH_ID
'PON_AUC_PRICE_DIFFER_INT' , -- ENTITY_TYPE
p_auction_header_id , -- AUCTION_HEADER_ID
sel_line_num , -- LINE_NUMBER
sel_ship_num , -- SHIPMENT_NUMBER
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 (sel_multiplier IS NULL )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
COLUMN_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type , --INTERFACE_TYPE
'PON_FIELD_MUST_BE_ENTERED' , -- ERROR_MESSAGE_NAME
'MULTIPLIER',
p_request_id , -- REQUEST_ID
p_batch_id , -- BATCH_ID
'PON_AUC_PRICE_DIFFER_INT' , -- ENTITY_TYPE
p_auction_header_id , -- AUCTION_HEADER_ID
sel_line_num , -- LINE_NUMBER
sel_ship_num , -- SHIPMENT_NUMBER
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 (sel_line_num IS NULL
or NOT EXISTS(SELECT 1 FROM pon_auction_item_prices_all paip
WHERE paip.auction_header_id = p_auction_header_id
AND paip.line_number = sel_line_num ))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type , --INTERFACE_TYPE
'PON_IMPORT_PRICEDIFF_LINE' , -- ERROR_MESSAGE_NAME
p_request_id , -- REQUEST_ID
p_batch_id , -- BATCH_ID
'PON_AUC_PRICE_DIFFER_INT' , -- ENTITY_TYPE
p_auction_header_id , -- AUCTION_HEADER_ID
sel_line_num , -- LINE_NUMBER
sel_ship_num , -- SHIPMENT_NUMBER
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 (sel_ship_num IS NOT NULL AND sel_ship_num <> -1
AND NOT EXISTS(SELECT 1 FROM pon_auc_price_breaks_interface pas
WHERE pas.auction_header_id = p_auction_header_id
AND pas.auction_line_number = sel_line_num
AND pas.shipment_number = sel_ship_num ))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type , --INTERFACE_TYPE
'PON_IMPORT_PRICEDIFF_SHIP' , -- ERROR_MESSAGE_NAME
p_request_id , -- REQUEST_ID
p_batch_id , -- BATCH_ID
'PON_AUC_PRICE_DIFFER_INT' , -- ENTITY_TYPE
p_auction_header_id , -- AUCTION_HEADER_ID
sel_line_num , -- LINE_NUMBER
sel_ship_num , -- SHIPMENT_NUMBER
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.price_type sel_price_type,
ppd.sequence_number sel_seq_num,
ppd.auction_line_number sel_line_num,
ppd.multiplier sel_multiplier,
ppd.auction_shipment_number sel_ship_num
FROM PON_AUC_PRICE_DIFFER_INT ppd
WHERE ppd.auction_header_id = p_auction_header_id;
INSERT ALL
WHEN(NOT EXISTS
(SELECT 'Y'
FROM pon_attribute_scores_interface pasi
WHERE pasi.batch_id = p_batch_id
AND pasi.line_number = line_num
AND pasi.attribute_sequence_number = seq_num
)
-- it is possible that scores can exist from previous round/amendment
-- we need not expect attribute scores in such cases.
AND NOT EXISTS
(SELECT 'Y'
FROM pon_attribute_scores pas
WHERE pas.auction_header_id = p_auction_header_id
AND pas.line_number = line_num
AND pas.attribute_sequence_number = seq_num
)) 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 ,
'PON_IMPORT_ATTR_SCORES_NO',
P_request_id ,
p_batch_id ,
g_auction_attrs_type ,
p_auction_header_id ,
line_num ,
NULL ,
p_user_id ,
SYSDATE ,
p_user_id ,
SYSDATE ,
p_user_id
)
SELECT g_interface_type ,
'PON_ATTR_SCORES_MISSING' ,
P_request_id ,
p_batch_id ,
g_auction_attrs_type ,
p_auction_header_id ,
paai.auction_line_number line_num,
paai.sequence_number seq_num ,
NULL ,
p_user_id
FROM PON_AUC_ATTRIBUTES_INTERFACE PAAI
WHERE paai.batch_id = p_batch_id
AND paai.auction_header_id = p_auction_header_id
AND NVL(paai.SCORING_TYPE,'NONE') <> 'NONE' ;
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_IMPORT_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 pasi.SCORE,
pasi.LINE_NUMBER,
TRUNC (pasi.SCORE) sel_trunc_score
FROM pon_attribute_scores_interface pasi,
PON_AUC_ATTRIBUTES_INTERFACE paai
WHERE pasi.batch_id = p_batch_id
AND pasi.auction_header_id = p_auction_Header_id
AND NVL(pasi.line_number,-1) <> -1
AND pasi.batch_id = paai.batch_id
AND NVL(paai.SCORING_TYPE,'NONE') <> 'NONE'
AND pasi.line_number = paai.auction_line_number
AND pasi.attribute_sequence_number = paai.sequence_number;
SELECT interface_header_id FROM pon_auction_Headers_interface
WHERE interface_group_id = p_group_batch_id
AND interface_header_id IS NOT NULL
AND PROCESSING_STATUS_CODE = 'PENDING'
AND Nvl(amendment_flag,'N') = 'N';
UPDATE pon_auction_Headers_interface
SET PROCESSING_STATUS_CODE = 'PROCESSING'
WHERE interface_header_id = l_batch_id;
DELETE FROM pon_interface_errors WHERE batch_id = l_batch_id;
UPDATE pon_auction_headers_interface
SET PROCESSING_STATUS_CODE = 'PROCESSED'
WHERE batch_id = l_batch_id;
DELETE FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_item_prices_all WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_price_elements WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_shipments_all WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auc_payments_shipments WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_neg_team_members WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_attributes WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_bidding_parties WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_attribute_scores WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_sections WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_headers_ext_b WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_headers_ext_tl WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_item_prices_ext_b WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_item_prices_ext_tl WHERE auction_header_id = l_auction_header_id;
UPDATE pon_auction_headers_interface
SET PROCESSING_STATUS_CODE = 'FAILED'
WHERE batch_id = l_batch_id;
update pon_auction_headers_interface SET batch_id = p_batch_id WHERE interface_header_id = p_batch_id;
update pon_item_prices_interface SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update PON_AUC_ATTRIBUTES_INTERFACE SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_attribute_scores_interface SET batch_id = p_batch_id, LINE_NUMBER = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_bid_parties_interface SET batch_id = p_batch_id WHERE interface_header_id = p_batch_id;
update PON_NEG_TEAM_INTERFACE SET batch_id = p_batch_id WHERE interface_header_id = p_batch_id;
update PON_AUC_PRICE_ELEMENTS_INT SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_auc_payments_interface SET batch_id = p_batch_id, document_disp_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_auc_price_breaks_interface SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_auc_price_differ_int SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
SELECT 1
INTO dummy1
FROM pon_auction_headers_Interface WHERE batch_id = p_batch_id;
SELECT uda_template_id INTO l_uda_template_id
FROM pon_auction_Headers_all
WHERE auction_header_id = x_auction_header_id;
print_log('Header UDA information inserted' );
SELECT auction_header_id INTO dummy2 FROM pon_item_prices_interface
WHERE batch_id = p_batch_id
AND ROWNUM=1;
print_log('Lines information inserted for batch_id ' || p_batch_id || ' auction_header_id ' || x_auction_header_id
|| ' x_return_status ' || x_return_status);
SELECT uda_template_id INTO l_uda_template_id
FROM pon_auction_item_prices_all
WHERE auction_header_id = x_auction_header_id
AND ROWNUM=1;
FOR complex_pricing_rec IN (SELECT clm_idc_type, clm_contract_type, line_number FROM pon_auction_item_prices_all
WHERE auction_Header_id = x_auction_header_id
AND clm_cost_constraint NOT IN ('NS','NSP')
AND purchase_basis <> 'GOODS') LOOP
BEGIN
SELECT
usages.attribute_group_id INTO l_attr_group_id
FROM po_uda_ag_template_usages usages
WHERE usages.attribute_category = 'PRICING'
AND usages.template_id = l_uda_template_id
AND usages.attribute1 = (SELECT clm_idc_type FROM pon_auction_item_prices_all WHERE auction_Header_id = x_auction_header_id AND line_number=complex_pricing_rec.line_number)
AND usages.attribute2 = (SELECT clm_contract_type FROM pon_auction_item_prices_all WHERE auction_Header_id = x_auction_header_id AND line_number=complex_pricing_rec.line_number);
insert into PON_INTERFACE_ERRORS
(interface_type,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
SELECT g_interface_type,
ATTR_GROUP_ID,
'PON_CLM_COMP_PRIC_ERR',
'PO_UDA_INTERFACE',
p_batch_id,
interface_line_id
from po_uda_interface
where INTERFACE_REFERENCE_ID = p_batch_id
AND INTERFACE_line_id = complex_pricing_rec.line_number
AND attr_group_name = 'PRICING'
AND attr_group_id <> l_attr_group_id;
SELECT 'E' status
INTO x_return_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
);
FOR rec IN (SELECT line_number FROM pon_auction_item_prices_all WHERE auction_Header_id = x_auction_header_id ) LOOP
process_uda_attributes(p_batch_id, x_auction_header_id, rec.line_number, l_uda_template_id, 'PON_AUC_PRICES_EXT_ATTRS', x_return_status);
print_log('failure in Line UDA insertion for line ' || rec.line_number);
print_Log('Line level UDA insertion completed successfully');
print_Log('Supplier information inserted for batch_id ' || p_batch_id || ' auction_header_id ' || x_auction_header_id
|| ' x_return_status ' || x_return_status);
SELECT DISTINCT attr_group_id
FROM po_Uda_interface
WHERE interface_reference_id = p_batch_id
AND ATTR_GROUP_TYPE = p_level;
SELECT DISTINCT attr_group_id
FROM po_Uda_interface
WHERE interface_reference_id = p_batch_id
AND ATTR_GROUP_TYPE = p_level
AND interface_line_id = p_line_number;
UPDATE PO_UDA_INTERFACE
SET pk1_value = p_auction_Header_id
WHERE INTERFACE_REFERENCE_ID = p_batch_Id
AND ATTR_GROUP_TYPE=p_level
AND ATTR_GROUP_ID = l_attr_group_id;
SELECT Count(*) INTO l_count
FROM po_uda_interface
WHERE INTERFACE_REFERENCE_ID = p_batch_Id AND
ATTR_GROUP_TYPE = p_level AND
PROCESS_STATUS = 1 AND
ATTR_GROUP_ID = l_attr_group_id;
UPDATE PO_UDA_INTERFACE
SET pk1_value = p_auction_Header_id,
pk2_value = p_line_number
WHERE INTERFACE_REFERENCE_ID = p_batch_Id
AND interface_line_id = p_line_number
AND ATTR_GROUP_TYPE=p_level
AND ATTR_GROUP_ID = l_attr_group_id;
SELECT Count(*) INTO l_count
FROM po_uda_interface
WHERE pk1_value = p_auction_Header_id AND
pk2_value = p_line_number AND
INTERFACE_REFERENCE_ID = p_batch_Id AND
interface_line_id = p_line_number AND
ATTR_GROUP_TYPE=p_level AND
PROCESS_STATUS = 1 AND
ATTR_GROUP_ID = l_attr_group_id;
SELECT internal_name INTO l_document_type
FROM pon_auc_doctypes
WHERE doctype_id = neg_header_record_data.doctype_id;
INSERT INTO PON_AUCTION_HEADERS_ALL (
AUCTION_HEADER_ID,
DOCUMENT_NUMBER,
AUCTION_HEADER_ID_ORIG_AMEND,
AUCTION_HEADER_ID_ORIG_ROUND,
AMENDMENT_NUMBER,
AUCTION_TITLE,
description,
AUCTION_STATUS,
AWARD_STATUS,
AUCTION_TYPE,
CONTRACT_TYPE,
TRADING_PARTNER_NAME,
TRADING_PARTNER_NAME_UPPER,
TRADING_PARTNER_ID,
trading_partner_contact_id,
LANGUAGE_CODE,
BID_VISIBILITY_CODE,
ATTACHMENT_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
DOCTYPE_ID,
ORG_ID,
BUYER_ID,
MANUAL_EDIT_FLAG,
SHARE_AWARD_DECISION,
APPROVAL_STATUS,
GLOBAL_AGREEMENT_FLAG,
ATTRIBUTE_LINE_NUMBER,
HAS_HDR_ATTR_FLAG,
HAS_ITEMS_FLAG,
STYLE_ID,
PO_STYLE_ID,
PRICE_BREAK_RESPONSE,
NUMBER_OF_LINES,
ADVANCE_NEGOTIABLE_FLAG,
RECOUPMENT_NEGOTIABLE_FLAG,
PROGRESS_PYMT_NEGOTIABLE_FLAG,
RETAINAGE_NEGOTIABLE_FLAG,
MAX_RETAINAGE_NEGOTIABLE_FLAG,
SUPPLIER_ENTERABLE_PYMT_FLAG,
PROGRESS_PAYMENT_TYPE,
LINE_ATTRIBUTE_ENABLED_FLAG,
LINE_MAS_ENABLED_FLAG,
PRICE_ELEMENT_ENABLED_FLAG,
RFI_LINE_ENABLED_FLAG,
LOT_ENABLED_FLAG,
GROUP_ENABLED_FLAG,
LARGE_NEG_ENABLED_FLAG,
HDR_ATTRIBUTE_ENABLED_FLAG,
NEG_TEAM_ENABLED_FLAG,
PROXY_BIDDING_ENABLED_FLAG,
POWER_BIDDING_ENABLED_FLAG,
AUTO_EXTEND_ENABLED_FLAG,
TEAM_SCORING_ENABLED_FLAG,
PRICE_TIERS_INDICATOR,
QTY_PRICE_TIERS_ENABLED_FLAG,
ship_to_location_id,
bill_to_location_id,
payment_terms_id,
fob_code,
freight_terms_code,
rate_type,
currency_code,
security_level_code,
PO_START_DATE,
PO_END_DATE,
open_auction_now_flag,
open_bidding_date,
CLOSE_bidding_date,
publish_auction_now_flag,
view_by_date,
note_to_bidders,
SHOW_BIDDER_NOTES,
BID_SCOPE_CODE,
BID_LIST_TYPE,
BID_FREQUENCY_CODE,
bid_ranking,
rank_indicator,
full_quantity_bid_code,
multiple_rounds_flag,
manual_close_flag,
manual_extend_flag,
award_approval_flag,
auction_origination_code,
pf_type_allowed,
HDR_ATTR_ENABLE_WEIGHTS,
AUCTION_STATUS_NAME,
AWARD_STATUS_NAME,
TRADING_PARTNER_CONTACT_NAME,
ORIGINAL_CLOSE_BIDDING_DATE,
AWARD_BY_DATE,
PUBLISH_DATE,
CLOSE_DATE,
CANCEL_DATE,
TIME_ZONE,
AUTO_EXTEND_FLAG,
AUTO_EXTEND_NUMBER,
NUMBER_OF_EXTENSIONS,
NUMBER_OF_BIDS,
MIN_BID_DECREMENT,
PRICE_DRIVEN_AUCTION_FLAG,
CARRIER_CODE,
RATE_DATE,
RATE,
WF_ITEM_KEY,
WF_ROLE_NAME,
AUTO_EXTEND_ALL_LINES_FLAG,
ALLOW_OTHER_BID_CURRENCY_FLAG,
SHIPPING_TERMS_CODE,
SHIPPING_TERMS,
AUTO_EXTEND_DURATION,
PROXY_BID_ALLOWED_FLAG,
PUBLISH_RATES_TO_BIDDERS_FLAG,
ATTRIBUTES_EXIST,
ORDER_NUMBER,
DOCUMENT_TRACKING_ID,
PO_TXN_FLAG,
EVENT_ID,
EVENT_TITLE,
SEALED_AUCTION_STATUS,
SEALED_ACTUAL_UNLOCK_DATE,
SEALED_ACTUAL_UNSEAL_DATE,
SEALED_UNLOCK_TP_CONTACT_ID,
SEALED_UNSEAL_TP_CONTACT_ID,
MODE_OF_TRANSPORT,
MODE_OF_TRANSPORT_CODE,
PO_AGREED_AMOUNT ,
MIN_BID_CHANGE_TYPE,
NUMBER_PRICE_DECIMALS,
AUTO_EXTEND_TYPE_FLAG,
AUCTION_HEADER_ID_PREV_ROUND,
AUCTION_ROUND_NUMBER,
AUTOEXTEND_CHANGED_FLAG,
OFFER_TYPE,
APPROVAL_REQUIRED_FLAG,
MAX_RESPONSES,
RESPONSE_ALLOWED_FLAG,
FOB_NEG_FLAG,
CARRIER_NEG_FLAG,
FREIGHT_TERMS_NEG_FLAG,
MAX_RESPONSE_ITERATIONS,
PAYMENT_TERMS_NEG_FLAG,
MODE_OF_TRANSPORT_NEG_FLAG,
CONTRACT_ID,
CONTRACT_VERSION_NUM,
SHIPPING_TERMS_NEG_FLAG,
SHIPPING_METHOD_NEG_FLAG,
USE_REGIONAL_PRICING_FLAG,
DERIVE_TYPE,
PRE_DELETE_AUCTION_STATUS,
DRAFT_LOCKED,
DRAFT_LOCKED_BY,
DRAFT_LOCKED_BY_CONTACT_ID ,
DRAFT_LOCKED_DATE,
DRAFT_UNLOCKED_BY,
DRAFT_UNLOCKED_BY_CONTACT_ID,
DRAFT_UNLOCKED_DATE,
MAX_LINE_NUMBER,
SHOW_BIDDER_SCORES,
TEMPLATE_ID,
REMINDER_DATE,
WF_PONCOMPL_ITEM_KEY,
HAS_PE_FOR_ALL_ITEMS,
HAS_PRICE_ELEMENTS ,
PO_MIN_REL_AMOUNT,
OUTCOME_STATUS,
SOURCE_REQS_FLAG,
AWARD_COMPLETE_DATE,
WF_PONCOMPL_CURRENT_ROUND,
WF_APPROVAL_ITEM_KEY,
SOURCE_DOC_ID,
SOURCE_DOC_NUMBER,
SOURCE_DOC_MSG,
SOURCE_DOC_LINE_MSG,
SOURCE_DOC_MSG_APP,
TEMPLATE_SCOPE,
TEMPLATE_STATUS,
IS_TEMPLATE_FLAG,
AWARD_APPROVAL_STATUS,
AWARD_APPR_AME_TRANS_ID ,
AWARD_APPR_AME_TRANS_PREV_ID,
WF_AWARD_APPROVAL_ITEM_KEY,
AMENDMENT_DESCRIPTION,
AUCTION_HEADER_ID_PREV_AMEND,
AWARD_APPR_AME_TXN_DATE,
HDR_ATTR_DISPLAY_SCORE,
HDR_ATTR_MAXIMUM_SCORE,
CONTERMS_EXIST_FLAG,
CONTERMS_ARTICLES_UPD_DATE,
CONTERMS_DELIV_UPD_DATE,
AWARD_MODE,
AWARD_DATE,
MAX_INTERNAL_LINE_NUM,
MAX_BID_COLOR_SEQUENCE_ID,
INT_ATTRIBUTE_CATEGORY,
INT_ATTRIBUTE1,
INT_ATTRIBUTE2,
INT_ATTRIBUTE3,
INT_ATTRIBUTE4,
INT_ATTRIBUTE5,
INT_ATTRIBUTE6,
INT_ATTRIBUTE7,
INT_ATTRIBUTE8,
INT_ATTRIBUTE9,
INT_ATTRIBUTE10,
INT_ATTRIBUTE11,
INT_ATTRIBUTE12 ,
INT_ATTRIBUTE13 ,
INT_ATTRIBUTE14 ,
INT_ATTRIBUTE15,
EXT_ATTRIBUTE_CATEGORY,
EXT_ATTRIBUTE1,
EXT_ATTRIBUTE2,
EXT_ATTRIBUTE3,
EXT_ATTRIBUTE4,
EXT_ATTRIBUTE5 ,
EXT_ATTRIBUTE6 ,
EXT_ATTRIBUTE7 ,
EXT_ATTRIBUTE8,
EXT_ATTRIBUTE9,
EXT_ATTRIBUTE10,
EXT_ATTRIBUTE11,
EXT_ATTRIBUTE12,
EXT_ATTRIBUTE13,
EXT_ATTRIBUTE14 ,
EXT_ATTRIBUTE15,
INCLUDE_PDF_IN_EXTERNAL_PAGE,
ABSTRACT_DETAILS,
ABSTRACT_STATUS,
SUPPLIER_VIEW_TYPE,
IS_PAUSED ,
PAUSE_REMARKS,
LAST_PAUSE_DATE,
MAX_DOCUMENT_LINE_NUM,
PROJECT_ID ,
HAS_SCORING_TEAMS_FLAG ,
SCORING_LOCK_DATE,
SCORING_LOCK_TP_CONTACT_ID ,
REQUEST_ID ,
REQUEST_DATE ,
REQUESTED_BY ,
IMPORT_FILE_NAME ,
LAST_LINE_NUMBER ,
GLOBAL_TEMPLATE_FLAG,
CONTRACT_TEMPLATE_ID,
COMPLETE_FLAG ,
BID_DECREMENT_METHOD,
DISPLAY_BEST_PRICE_BLIND_FLAG,
FIRST_LINE_CLOSE_DATE ,
STAGGERED_CLOSING_INTERVAL ,
ENFORCE_PREVRND_BID_PRICE_FLAG,
AUTO_EXTEND_MIN_TRIGGER_RANK ,
TWO_PART_FLAG ,
TECHNICAL_LOCK_STATUS,
TECHNICAL_EVALUATION_STATUS,
TECHNICAL_ACTUAL_UNLOCK_DATE,
TECHNICAL_ACTUAL_UNSEAL_DATE ,
TECHNICAL_UNLOCK_TP_CONTACT_ID,
TECHNICAL_UNSEAL_TP_CONTACT_ID,
EMD_ENABLE_FLAG,
EMD_AMOUNT,
EMD_DUE_DATE,
EMD_TYPE,
EMD_GUARANTEE_EXPIRY_DATE,
EMD_ADDITIONAL_INFORMATION,
POST_EMD_TO_FINANCE,
NO_OF_NOTIFICATIONS_SENT,
NEGOTIATION_REQUESTER_ID,
SUPP_REG_QUAL_FLAG,
SUPP_EVAL_FLAG,
HIDE_TERMS_FLAG,
HIDE_ABSTRACT_FORMS_FLAG,
HIDE_ATTACHMENTS_FLAG,
INTERNAL_EVAL_FLAG,
HDR_SUPP_ATTR_ENABLED_FLAG,
INTGR_HDR_ATTR_FLAG,
INTGR_HDR_ATTACH_FLAG,
LINE_SUPP_ATTR_ENABLED_FLAG,
ITEM_SUPP_ATTR_ENABLED_FLAG,
INTGR_CAT_LINE_ATTR_FLAG,
INTGR_ITEM_LINE_ATTR_FLAG,
INTGR_CAT_LINE_ASL_FLAG,
INTERNAL_ONLY_FLAG,
UDA_TEMPLATE_ID,
UDA_TEMPLATE_DATE,
standard_form,
document_format
,ALLOW_WITHDRAW_FLAG,ALLOW_UNSOL_OFFER_LINES,ALLOW_MULTIPLE_ACTIVE_OFFERS,
--EFFECTIVE_DATE,
FAIR_OPP_NOTICE_FLAG , FEDERAL_PUBLICATION_EXCEPTION
,NO_OF_COPIES, REVISION, SIGNED_DATE, SOLICITATION_TYPE, UMBRELLA_PROGRAM_ID
,CD_EDITBYUSER_FLAG, CD_GENERATED_DATE, CD_GENERATED_FLAG, AMENDUPDATED_AFTERCDGENERATED
) VALUES(
neg_header_record_data.auction_header_id, -- AUCTION_HEADER_ID
neg_header_record_data.auction_header_id, -- DOCUMENT_NUMBER
neg_header_record_data.auction_header_id, -- AUCTION_HEADER_ID_ORIG_AMEND,
neg_header_record_data.auction_header_id, -- AUCTION_HEADER_ID_ORIG_ROUND,
0, -- AMENDMENT_NUMBER
neg_header_record_data.auction_title, -- AUCTION_TITLE
neg_header_record_data.description,
'DRAFT', -- AUCTION_STATUS
'NO', -- AWARD_STATUS
neg_header_record_data.auction_type, -- AUCTION_TYPE
neg_header_record_data.CONTRACT_TYPE, -- CONTRACT_TYPE
neg_header_record_data.trading_partner_name, -- TRADING_PARTNER_NAME
upper(neg_header_record_data.trading_partner_name), -- TRADING_PARTNER_NAME_UPPER
neg_header_record_data.trading_partner_id, -- TRADING_PARTNER_ID
neg_header_record_data.trading_partner_contact_id,
userenv('LANG'), -- LANGUAGE_CODE
neg_header_record_data.bid_visibility_code, -- BID_VISIBILITY_CODE
'N', -- ATTACHMENT_FLAG
Nvl(neg_header_record_data.creation_date, SYSDATE), -- CREATION_DATE
Nvl(neg_header_record_data.created_by,fnd_global.user_id), -- CREATED_BY
Nvl(neg_header_record_data.last_update_date,SYSDATE), -- LAST_UPDATE_DATE
Nvl(neg_header_record_data.last_updated_by,fnd_global.user_id), -- LAST_UPDATED_BY
neg_header_record_data.doctype_id, -- DOCTYPE_ID
neg_header_record_data.ORG_ID, -- ORG_ID
null, -- BUYER_ID
'N', -- MANUAL_EDIT_FLAG
'N', -- SHARE_AWARD_DECISION
neg_header_record_data.approval_status, -- APPROVAL_STATUS
Nvl(neg_header_record_data.GLOBAL_AGREEMENT_FLAG,'Y'), -- GLOBAL_AGREEMENT_FLAG
-1, -- ATTRIBUTE_LINE_NUMBER
null, -- HAS_HDR_ATTR_FLAG
null, -- HAS_ITEMS_FLAG
neg_header_record_data.STYLE_ID, -- STYLE_ID
neg_header_record_data.PO_STYLE_ID, -- PO_STYLE_ID
neg_header_record_data.price_break_response, -- PRICE_BREAK_RESPONSE,
0, -- NUMBER_OF_LINES
Nvl(neg_header_record_data.ADVANCE_NEGOTIABLE_FLAG,'N'), --ADVANCE_NEGOTIABLE_FLAG
Nvl(neg_header_record_data.RECOUPMENT_NEGOTIABLE_FLAG,'N'), --RECOUPMENT_NEGOTIABLE_FLAG
Nvl(neg_header_record_data.PROGRESS_PYMT_NEGOTIABLE_FLAG,'N'), --PROGRESS_PYMT_NEGOTIABLE_FLAG
Nvl(neg_header_record_data.RETAINAGE_NEGOTIABLE_FLAG,'N'), --RETAINAGE_NEGOTIABLE_FLAG
Nvl(neg_header_record_data.MAX_RETAINAGE_NEGOTIABLE_FLAG,'N'), --MAX_RETAINAGE_NEGOTIABLE_FLAG
neg_header_record_data.SUPPLIER_ENTERABLE_PYMT_FLAG, --SUPPLIER_ENTERABLE_PYMT_FLAG
neg_header_record_data.progress_payment_type, --PROGRESS_PAYMENT_TYPE
neg_header_record_data.line_attribute_enabled_flag,
neg_header_record_data.line_mas_enabled_flag,
neg_header_record_data.price_element_enabled_flag,
neg_header_record_data.rfi_line_enabled_flag,
neg_header_record_data.lot_enabled_flag,
neg_header_record_data.group_enabled_flag,
neg_header_record_data.large_neg_enabled_flag,
neg_header_record_data.hdr_attribute_enabled_flag,
neg_header_record_data.neg_team_enabled_flag,
neg_header_record_data.proxy_bidding_enabled_flag,
neg_header_record_data.power_bidding_enabled_flag,
neg_header_record_data.auto_extend_enabled_flag,
neg_header_record_data.team_scoring_enabled_flag,
neg_header_record_data.price_tiers_indicator,
neg_header_record_data.qty_price_tiers_enabled_flag,
neg_header_record_data.ship_to_location_id,
neg_header_record_data.bill_to_location_id,
neg_header_record_data.payment_terms_id,
neg_header_record_data.fob_code,
neg_header_record_data.freight_terms_code,
neg_header_record_data.rate_type,
neg_header_record_data.currency_code,
neg_header_record_data.security_level_code,
neg_header_record_data.PO_START_DATE,
neg_header_record_data.PO_END_DATE,
Nvl(neg_header_record_data.open_auction_now_flag,'N'),
Decode(Nvl(neg_header_record_data.open_auction_now_flag,'N'),'Y',null,neg_header_record_data.open_bidding_date),
neg_header_record_data.close_bidding_date,
Nvl(neg_header_record_data.publish_auction_now_flag,'N'),
Decode(Nvl(neg_header_record_data.publish_auction_now_flag,'N'),'Y',null,neg_header_record_data.view_by_date),
neg_header_record_data.note_to_bidders,
Nvl(neg_header_record_data.SHOW_BIDDER_NOTES,'N'),
Nvl(neg_header_record_data.BID_SCOPE_CODE,'MUST_BID_ALL_ITEMS'),
Nvl(neg_header_record_data.BID_LIST_TYPE,'PUBLIC_BID_LIST'),
Nvl(neg_header_record_data.BID_FREQUENCY_CODE,'SINGLE_BID_ONLY'),
Nvl(neg_header_record_data.bid_ranking,'PRICE_ONLY'),
Nvl(neg_header_record_data.rank_indicator,'NONE'),
Nvl(neg_header_record_data.full_quantity_bid_code,'FULL_QTY_BIDS_REQD'),
Decode(l_document_type,'REQUEST_FOR_QUOTE','Y','SOLICITATION','Y',Nvl(neg_header_record_data.multiple_rounds_flag,'N')),
Nvl(neg_header_record_data.manual_close_flag,'N'),
Decode(l_document_type,'SOLICITATION','N',Nvl(neg_header_record_data.manual_extend_flag,'N')),
Nvl(neg_header_record_data.award_approval_flag,'N'),
neg_header_record_data.auction_origination_code,
Decode(l_document_type,'REQUEST_FOR_INFORMATION','N', Nvl(neg_header_record_data.pf_type_allowed,'NONE')),
neg_header_record_data.HDR_ATTR_ENABLE_WEIGHTS,
null,
null,
neg_header_record_data.trading_partner_contact_name,
null,
null,
null,
null,
null,
null,
neg_header_record_data.AUTO_EXTEND_FLAG,
neg_header_record_data.AUTO_EXTEND_NUMBER,
NULL,--neg_header_record_data.NUMBER_OF_EXTENSIONS,
0,
neg_header_record_data.MIN_BID_DECREMENT,
neg_header_record_data.PRICE_DRIVEN_AUCTION_FLAG,
neg_header_record_data.CARRIER_CODE,
neg_header_record_data.RATE_DATE,
NULL,--neg_header_record_data.RATE,
null,
null,
neg_header_record_data.AUTO_EXTEND_ALL_LINES_FLAG,
neg_header_record_data.ALLOW_OTHER_BID_CURRENCY_FLAG,
null,
null,
neg_header_record_data.AUTO_EXTEND_DURATION,
neg_header_record_data.PROXY_BID_ALLOWED_FLAG,
neg_header_record_data.PUBLISH_RATES_TO_BIDDERS_FLAG,
null,
null,
null,
null,
neg_header_record_data.EVENT_ID,
neg_header_record_data.EVENT_TITLE,
neg_header_record_data.SEALED_AUCTION_STATUS,
null,
null,
null,
null,
null,
null,
null,
neg_header_record_data.MIN_BID_CHANGE_TYPE,
neg_header_record_data.NUMBER_PRICE_DECIMALS,
neg_header_record_data.AUTO_EXTEND_TYPE_FLAG,
NULL, --neg_header_record_data.auction_header_id,
null,
null,
null,
'N',
null,
'Y',
'N',
'N',
'N',
null,
'N',
'N',
null,
null,
'N',
'N',
null,
null,
null,
'Y',
neg_header_record_data.trading_partner_id,
neg_header_record_data.trading_partner_contact_id,
sysdate,
null,
NULL,
null,
null,
neg_header_record_data.SHOW_BIDDER_SCORES,
null,
null,
null,
null,
null ,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
'NOT_REQUIRED',
null ,
null,
null,
neg_header_record_data.amendment_description,
NULL,--neg_header_record_data.auction_header_id,
null,
'N',
5,
null,
null,
null,
null,
null,
null,
null,
neg_header_record_data.INT_ATTRIBUTE_CATEGORY,
neg_header_record_data.INT_ATTRIBUTE1,
neg_header_record_data.INT_ATTRIBUTE2,
neg_header_record_data.INT_ATTRIBUTE3,
neg_header_record_data.INT_ATTRIBUTE4,
neg_header_record_data.INT_ATTRIBUTE5,
neg_header_record_data.INT_ATTRIBUTE6,
neg_header_record_data.INT_ATTRIBUTE7,
neg_header_record_data.INT_ATTRIBUTE8,
neg_header_record_data.INT_ATTRIBUTE9,
neg_header_record_data.INT_ATTRIBUTE10,
neg_header_record_data.INT_ATTRIBUTE11,
neg_header_record_data.INT_ATTRIBUTE12 ,
neg_header_record_data.INT_ATTRIBUTE13 ,
neg_header_record_data.INT_ATTRIBUTE14 ,
neg_header_record_data.INT_ATTRIBUTE15,
neg_header_record_data.EXT_ATTRIBUTE_CATEGORY,
neg_header_record_data.EXT_ATTRIBUTE1,
neg_header_record_data.EXT_ATTRIBUTE2,
neg_header_record_data.EXT_ATTRIBUTE3,
neg_header_record_data.EXT_ATTRIBUTE4,
neg_header_record_data.EXT_ATTRIBUTE5 ,
neg_header_record_data.EXT_ATTRIBUTE6 ,
neg_header_record_data.EXT_ATTRIBUTE7 ,
neg_header_record_data.EXT_ATTRIBUTE8,
neg_header_record_data.EXT_ATTRIBUTE9,
neg_header_record_data.EXT_ATTRIBUTE10,
neg_header_record_data.EXT_ATTRIBUTE11,
neg_header_record_data.EXT_ATTRIBUTE12,
neg_header_record_data.EXT_ATTRIBUTE13,
neg_header_record_data.EXT_ATTRIBUTE14 ,
neg_header_record_data.EXT_ATTRIBUTE15,
null,
null,
null,
neg_header_record_data.SUPPLIER_VIEW_TYPE,
null ,
null,
null,
0,
neg_header_record_data.PROJECT_ID,
null,
null,
null ,
null ,
null ,
null ,
null ,
null ,
null,
null,
null ,
null,
neg_header_record_data.DISPLAY_BEST_PRICE_BLIND_FLAG,
neg_header_record_data.FIRST_LINE_CLOSE_DATE ,
neg_header_record_data.STAGGERED_CLOSING_INTERVAL ,
neg_header_record_data.ENFORCE_PREVRND_BID_PRICE_FLAG,
neg_header_record_data.AUTO_EXTEND_MIN_TRIGGER_RANK ,
neg_header_record_data.TWO_PART_FLAG ,
null,
null,
null,
null ,
null,
null,
'N',
null,
null,
null,
null,
null,
null,
null,
neg_header_record_data.trading_partner_contact_id,
neg_header_record_data.SUPP_REG_QUAL_FLAG,
neg_header_record_data.SUPP_EVAL_FLAG,
neg_header_record_data.HIDE_TERMS_FLAG,
neg_header_record_data.HIDE_ABSTRACT_FORMS_FLAG,
neg_header_record_data.HIDE_ATTACHMENTS_FLAG,
neg_header_record_data.INTERNAL_EVAL_FLAG,
neg_header_record_data.HDR_SUPP_ATTR_ENABLED_FLAG,
neg_header_record_data.INTGR_HDR_ATTR_FLAG,
neg_header_record_data.INTGR_HDR_ATTACH_FLAG,
neg_header_record_data.LINE_SUPP_ATTR_ENABLED_FLAG,
neg_header_record_data.ITEM_SUPP_ATTR_ENABLED_FLAG,
neg_header_record_data.INTGR_CAT_LINE_ATTR_FLAG,
neg_header_record_data.INTGR_ITEM_LINE_ATTR_FLAG,
neg_header_record_data.INTGR_CAT_LINE_ASL_FLAG,
'N',
neg_header_record_data.UDA_TEMPLATE_ID,
neg_header_record_data.UDA_TEMPLATE_DATE,
neg_header_record_data.standard_form,
neg_header_record_data.document_format
,neg_header_record_data.ALLOW_WITHDRAW_FLAG,neg_header_record_data.ALLOW_UNSOL_OFFER_LINES,neg_header_record_data.ALLOW_MULTIPLE_ACTIVE_OFFERS
--,neg_header_record_data.EFFECTIVE_DATE
,neg_header_record_data.FAIR_OPP_NOTICE_FLAG
,neg_header_record_data.FEDERAL_PUBLICATION_EXCEPTION
,neg_header_record_data.NO_OF_COPIES
,neg_header_record_data.REVISION
,neg_header_record_data.SIGNED_DATE
,neg_header_record_data.SOLICITATION_TYPE
,neg_header_record_data.UMBRELLA_PROGRAM_ID
,neg_header_record_data.CD_EDITBYUSER_FLAG
,neg_header_record_data.CD_GENERATED_DATE
,neg_header_record_data.CD_GENERATED_FLAG
,neg_header_record_data.AMENDUPDATED_AFTERCDGENERATED
);
update pon_item_prices_interface SET auction_header_id = neg_header_record_data.auction_header_id WHERE batch_id = p_batch_id;
update PON_AUC_ATTRIBUTES_INTERFACE SET auction_header_id = neg_header_record_data.auction_header_id WHERE batch_id = p_batch_id;
update pon_attribute_scores_interface SET auction_header_id = neg_header_record_data.auction_header_id WHERE batch_id = p_batch_id;
update pon_bid_parties_interface SET auction_header_id = neg_header_record_data.auction_header_id WHERE batch_id = p_batch_id;
update PON_NEG_TEAM_INTERFACE SET auction_header_id = neg_header_record_data.auction_header_id WHERE batch_id = p_batch_id;
update PON_AUC_PRICE_ELEMENTS_INT SET auction_header_id = neg_header_record_data.auction_header_id WHERE batch_id = p_batch_id;
update pon_auc_price_breaks_interface SET auction_header_id = neg_header_record_data.auction_header_id WHERE batch_id = p_batch_id;
update pon_auc_price_differ_int SET auction_header_id = neg_header_record_data.auction_header_id WHERE batch_id = p_batch_id;
SELECT doctype_id, org_id, style_id, po_style_name, trading_partner_contact_name
INTO l_doctype_id, l_org_id, l_style_id, l_po_style_name, l_tpc_name
FROM pon_auction_headers_Interface WHERE batch_id = p_batch_id;
SELECT DOCTYPE_ID INTO rfi_doctype_id FROM pon_auc_doctypes WHERE INTERNAL_NAME='REQUEST_FOR_INFORMATION';
SELECT user_id INTO l_tpc_id
FROM fnd_user
WHERE user_Name = l_tpc_name
AND Nvl(end_date,SYSDATE + 1) > sysdate;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_IMPORT_INVALID_TPC',
p_batch_id,
'HEADER',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
);
SELECT pdl.DOCUMENT_SUBTYPE, pdh.style_id INTO l_contract_type, l_po_style_id
FROM po_doc_style_headers pdh, po_all_doc_style_Lines pdl
WHERE pdh.style_id = pdl.style_id
AND pdh.status= 'ACTIVE'
AND pdl.enabled_flag= 'Y'
and Nvl(pdh.clm_flag,'N') = PON_CLM_UTIL_PKG.ISCLMRESP
AND pdl.LANGUAGE=UserEnv('LANG')
AND pdl.display_name = l_po_style_name;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_IMPORT_INVALID_POSTYLE',
p_batch_id,
'HEADER',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
);
SELECT doctype_group_name INTO dummy1 FROM pon_auc_doctypes WHERE doctype_id = l_doctype_id;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_IMPORT_INVALID_DOCTYPE',
p_batch_id,
'HEADER',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id);
INSERT ALL
WHEN (l_org_id IS NULL OR
NOT EXISTS(SELECT 'Y' FROM hr_operating_units WHERE organization_id = l_org_id))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_IMPORT_INVALID_ORG',
p_batch_id,
'HEADER',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
)
WHEN (l_style_id IS NULL
OR NOT EXISTS(select 1 from pon_negotiation_styles_vl ds, pon_doctype_styles ts
WHERE ds.style_id = l_style_id
AND ds.style_id = ts.style_id
and ds.status = 'ACTIVE'
and ts.doctype_id = l_doctype_id
and ts.enabled_flag = 'Y'))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_IMPORT_INVALID_NEGSTYLE',
p_batch_id,
'HEADER',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
)
--Bug 16801033
WHEN ((l_doctype_id<>rfi_doctype_id AND (l_po_style_id IS NULL
OR NOT EXISTS(SELECT 'Y' FROM po_doc_style_headers WHERE STYLE_ID = l_po_style_id
and Nvl(clm_flag,'N') = PON_CLM_UTIL_PKG.ISCLMRESP)))
OR (l_doctype_id=rfi_doctype_id AND (l_po_style_id IS not NULL OR l_po_style_name IS NOT null))
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_IMPORT_INVALID_POSTYLE',
p_batch_id,
'HEADER',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
)
SELECT 1 FROM dual;
SELECT 'E' status
INTO x_return_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
);
SELECT 'E' status
INTO x_return_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
);
SELECT 'Y' INTO approval_required_flag
FROM dual
WHERE
EXISTS(SELECT 1 FROM pon_neg_team_members
WHERE auctioN_header_id = neg_header_record_data.auction_header_id
and approver_flag = 'Y');
UPDATE pon_auction_headers_all
SET approval_status = 'REQUIRED'
WHERE auction_header_id = neg_header_record_data.auction_header_id;
UPDATE pon_auction_headers_all
SET approval_status = 'NOT_REQUIRED'
WHERE auction_header_id = neg_header_record_data.auction_header_id;
print_log('Header requirements inserted for for batch id ' || p_batch_id);
select DECODE(NVL(neg_header_record_data.progress_payment_type,'NONE'),'NONE','N','Y')
INTO l_is_complex FROM dual;
validate_sql := 'select ' || validate_col || ' from pon_auction_headers_interface '
|| ' where batch_id = ' || p_batch_id;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'test_col',
p_batch_id,
'test_col',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id);
SELECT doctype_group_name INTO doctype_name FROM pon_auc_doctypes WHERE doctype_id = neg_header_record_data.doctype_id;
INSERT ALL
WHEN ( neg_header_record_data.auction_title IS NULL )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_FIELD_MUST_BE_ENTERED',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
)
WHEN ( neg_header_record_data.ship_to_location_id IS NOT NULL AND neg_header_record_data.ship_to_location_id <> -1
AND NOT EXISTS(SELECT 1 FROM po_ship_to_loc_org_v WHERE location_id = neg_header_record_data.ship_to_location_id))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_INV_SHIP_TO',
p_batch_id,
'SHIP_TO_LOCATION_ID',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
)
WHEN ((neg_header_record_data.bill_to_location_id IS NOT NULL AND neg_header_record_data.bill_to_location_id <> -1
AND NOT EXISTS(SELECT 'Y' from HR_LOCATIONS_ALL L
where L.LOCATION_ID = neg_header_record_data.bill_to_location_id
AND NVL(L.BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99)
AND SYSDATE < NVL(L.INACTIVE_DATE, SYSDATE + 1) AND NVL(L.BILL_TO_SITE_FLAG,'N') = 'Y'))
OR (p_is_amendment<>'Y' AND neg_header_record_data.bill_to_location_id IS NULL))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_INV_BILL_TO',
p_batch_id,
'BILL_TO_LOCATION_ID',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
)
-- in case of amendment, event information wont be updated
-- so no need to validate for amendments
WHEN ( p_is_amendment <> 'Y' AND
neg_header_record_data.event_id IS NOT NULL AND neg_header_record_data.event_id <> -1
AND NOT EXISTS(select 'Y' from pon_auction_events
where event_id = neg_header_record_data.event_id and
trading_partner_id = p_tp_id
and event_status <> 'CANCELLED'
and NVL(open_date, SYSDATE - 100) >= SYSDATE)) THEN
INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_INV_EVENTID',
p_batch_id,
'EVENT_ID',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
)
WHEN (neg_header_record_data.bid_ranking='PRICE_ONLY' AND neg_header_record_data.show_bidder_scores='SCORE_WEIGHT') THEN
INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_INV_RANKING',
p_batch_id,
'SHOW_BIDDER_SCORES',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
)
/* ALLOW_MULTIPLE_ACTIVE_OFFERS */
WHEN ( neg_header_record_data.ALLOW_MULTIPLE_ACTIVE_OFFERS = 'Y'
AND neg_header_record_data.BID_FREQUENCY_CODE <> 'MULTIPLE_BIDS_ALLOWED')
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_MULTIPLE_ACTIVE_OFFER_VAL',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
--SIGNED DATE
WHEN ( neg_header_record_data.SIGNED_DATE < neg_header_record_data.CREATION_DATE
AND isFederal = 1)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_SIGNED_DATE_INVALID',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
--DOCUMENT_FORMAT
WHEN ( neg_header_record_data.DOCUMENT_FORMAT IS NULL AND isFederal = 1)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_DOC_FORMAT_NULL',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (isFederal = 1 AND neg_header_record_data.DOCUMENT_FORMAT NOT IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'PO_CLM_DOC_FORMAT'
AND LANGUAGE = USERENV('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
AND lookup_code IN (SELECT DOCUMENT_FORMAT FROM PO_PRINT_FORM_FORMATS WHERE DOCUMENT_TYPE = 'PO_SOL_STD_FORM'
and STANDARD_FORM = neg_header_record_data.STANDARD_FORM
AND SYSDATE < Nvl(inactive_date,SYSDATE + 1))))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_DOC_FORMAT_INVALID',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
--FEDERAL_PUBLICATION_EXCEPTION
WHEN ( isFederal = 1 AND neg_header_record_data.FEDERAL_PUBLICATION_EXCEPTION NOT IN
( SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'PON_FEDERAL_PUBLICATION'
AND LANGUAGE = USERENV('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1)
AND NVL(END_DATE_ACTIVE, SYSDATE + 1)))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_FED_PUB_INVALID',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
/* SOLICITATION_TYPE */
WHEN (neg_header_record_data.SOLICITATION_TYPE IS NULL AND isFederal = 1)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_SOLICITATION_TYPE_NULL',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN ( isFederal = 1 AND neg_header_record_data.SOLICITATION_TYPE NOT IN (SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'PON_SOLICITATION_TYPE'
AND LANGUAGE = USERENV('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_SOLICITATION_TYPE_INVALID',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
/* STANDARD_FORM */
WHEN ( neg_header_record_data.STANDARD_FORM IS NULL AND isFederal = 1)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_STANDARD_FORM_NULL',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN (isFederal = 1 AND neg_header_record_data.STANDARD_FORM NOT IN (SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'PO_SOL_STD_FORM'
AND LANGUAGE = USERENV('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN
NVL(START_DATE_ACTIVE, SYSDATE - 1)
AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
AND LOOKUP_CODE IN(SELECT STANDARD_FORM
FROM PO_PRINT_FORM_FORMATS
WHERE DOCUMENT_TYPE = 'PO_SOL_STD_FORM')
))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_STANDARD_FORM_INVALID',
p_batch_id,
'AUCTION_TITLE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
SELECT 1 from dual;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
'PON_FIELD_MUST_BE_ENTERED',
p_batch_id,
'STANDARD_FORM',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id
);
INSERT ALL
WHEN(neg_header_record_data.progress_payment_flag = 'Y'
AND (doctype_name NOT in ('REQUEST_FOR_QUOTE','SOLICITATION') OR neg_header_record_data.contract_type <> 'STANDARD'))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_INV_PROGRESS_PYMT',
p_batch_id,
'PROGRESS_PAYMENT_FLAG',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN(neg_header_record_data.bid_visibility_code = 'OPEN_BIDDING'
AND doctype_name IN ('REQUEST_FOR_QUOTE','SOLICITATION'))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_INV_BID_VISIBILITY',
p_batch_id,
'BID_VISIBILITY_CODE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN(neg_header_record_data.project_id IS NOT NULL
AND NOT EXISTS(SELECT 'Y' FROM pa_projects_expend_v WHERE project_Id = neg_header_record_data.project_Id))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_PROJID_INV',
p_batch_id,
'PROJECT_ID',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN(neg_header_record_data.fob_code IS NOT NULL
AND neg_header_record_data.fob_code NOT IN (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'FOB' AND LANGUAGE=UserEnv('LANG')))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_NEG_FOB_INV',
p_batch_id,
'AUCTION_OUTCOME',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN(neg_header_record_data.freight_terms_code IS NOT NULL
AND neg_header_record_data.freight_terms_code NOT IN (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'FREIGHT TERMS' AND LANGUAGE=UserEnv('LANG')))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_NEG_FREIGHT_INV',
p_batch_id,
'AUCTION_OUTCOME',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN(neg_header_record_data.payment_terms_id IS NOT NULL
AND NOT EXISTS (select 'Y' from ap_terms where term_id = neg_header_record_data.payment_terms_id and SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE-1) AND NVL(END_DATE_ACTIVE,SYSDATE+1)))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_NEG_PYMTTERMS_INV',
p_batch_id,
'PAYMENT_TERMS_ID',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN( (p_is_amendment <> 'Y' AND
neg_header_record_data.currency_code IS NULL)
or (neg_header_record_data.currency_code IS NOT NULL
AND NOT EXISTS (SELECT 'Y' FROM hr_operating_units org,gl_sets_of_books sob
WHERE org.organization_id = neg_header_record_data.org_id
AND org.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID
AND sob.currency_code = neg_header_record_data.currency_code
AND neg_header_record_data.currency_code IS NOT NULL)))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_NEG_CURR_INV',
p_batch_id,
'AUCTION_OUTCOME',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
-- for amendment open_bidding_date is not updatable.
-- so no need to validate
WHEN( p_is_amendment <> 'Y'
AND ((neg_header_record_data.open_bidding_date IS NULL
AND Nvl(neg_header_record_data.open_auction_now_flag,'N') = 'N')
OR neg_header_record_data.open_bidding_date < sysdate))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_NEG_OPENDT_INV',
p_batch_id,
'AUCTION_OPEN_DATE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN( p_is_amendment <> 'Y'
AND (neg_header_record_data.close_bidding_date IS NULL or
neg_header_record_data.open_bidding_date > neg_header_record_data.close_bidding_date ))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_NEG_CLOSEDT_INV',
p_batch_id,
'AUCTION_OPEN_DATE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN(neg_header_record_data.CONTRACT_TYPE = 'BLANKET' and
neg_header_record_data.po_start_date > neg_header_record_data.po_end_date)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_NEG_AGREEMENTDT_INV',
p_batch_id,
'AUCTION_OPEN_DATE',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
WHEN(neg_header_record_data.CONTRACT_TYPE = 'BLANKET' and
neg_header_record_data.po_min_rel_amount < 0 )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
g_interface_type,
'PON_NEG_MINREL_INV',
p_batch_id,
'PO_MIN_REL_AMOUNT',
NULL,
l_cp_user_id,
SYSDATE,
l_cp_user_id,
SYSDATE,
l_cp_login_id)
SELECT neg_header_record_data.org_id FROM dual ;
SELECT pon_auction_headers_all_s.NEXTVAL INTO neg_header_record_data.auction_header_id FROM dual;
SELECT person_party_id,user_name
INTO neg_header_record_data.trading_partner_contact_id,neg_header_record_data.trading_partner_contact_name
FROM fnd_user usr,pon_auction_headers_interface pahi
WHERE batch_id = p_batch_id
AND usr.user_name = pahi.trading_partner_contact_name
AND Nvl(usr.end_date,SYSDATE + 1) > SYSDATE ;
SELECT auction_title,
description,
contract_type,
bid_visibility_code,
doctype_id,
org_id,
buyer_id,
global_agreement_flag,
style_id,
po_style_id,
po_style_name,
price_break_response,
advance_negotiable_flag,
recoupment_negotiable_flag,
progress_pymt_negotiable_flag,
retainage_negotiable_flag,
max_retainage_negotiable_flag,
supplier_enterable_pymt_flag,
progress_payment_type,
line_mas_enabled_flag,
price_tiers_indicator,
ship_to_location_id,
ship_to_location_code,
bill_to_location_id,
bill_to_location_code,
payment_terms_id,
fob_code,
freight_terms_code,
rate_type,
currency_code,
rate_date,
security_level_code,
po_start_date,
po_end_date,
open_auction_now_flag,
open_bidding_date,
close_bidding_date,
publish_auction_now_flag,
--publish_date,
--auction_published_flag,
view_by_date,
note_to_bidders,
show_bidder_notes,
bid_scope_code,
bid_list_type,
bid_frequency_code,
bid_ranking,
rank_indicator,
full_quantity_bid_code,
multiple_rounds_flag,
manual_close_flag,
manual_extend_flag,
award_approval_flag,
auction_origination_code,
pf_type_allowed,
hdr_attr_enable_weights,
award_by_date,
auto_extend_flag,
auto_extend_number,
min_bid_decrement,
min_bid_change_type,
price_driven_auction_flag,
carrier_code,
auto_extend_all_lines_flag,
allow_other_bid_currency_flag,
auto_extend_duration,
publish_rates_to_bidders_flag,
event_id,
event_title,
sealed_auction_status,
auto_extend_type_flag,
show_bidder_scores,
po_min_rel_amount,
hdr_attr_display_score,
int_attribute_category,
int_attribute1,
int_attribute2,
int_attribute3,
int_attribute4,
int_attribute5,
int_attribute6,
int_attribute7,
int_attribute8 ,
int_attribute9,
int_attribute10,
int_attribute11,
int_attribute12,
int_attribute13,
int_attribute14,
int_attribute15,
ext_attribute_category,
ext_attribute1,
ext_attribute2,
ext_attribute3,
ext_attribute4,
ext_attribute5,
ext_attribute6,
ext_attribute7 ,
ext_attribute8,
ext_attribute9,
ext_attribute10,
ext_attribute11,
ext_attribute12,
ext_attribute13,
ext_attribute14,
ext_attribute15,
abstract_details,
supplier_view_type,
project_id,
bid_decrement_method ,
display_best_price_blind_flag,
first_line_close_date,
staggered_closing_interval,
enforce_prevrnd_bid_price_flag,
auto_extend_min_trigger_rank,
two_part_flag,
standard_form,
document_format,
amendment_description,
creation_date,
created_by,
last_update_date,
last_updated_by
,ALLOW_WITHDRAW_FLAG,ALLOW_UNSOL_OFFER_LINES, ALLOW_MULTIPLE_ACTIVE_OFFERS
--,EFFECTIVE_DATE
,FAIR_OPP_NOTICE_FLAG
,FEDERAL_PUBLICATION_EXCEPTION
,NO_OF_COPIES
,REVISION
,SIGNED_DATE
,SOLICITATION_TYPE
,UMBRELLA_PROGRAM_ID
,CD_EDITBYUSER_FLAG
,CD_GENERATED_DATE
,CD_GENERATED_FLAG
,AMENDUPDATED_AFTERCDGENERATED
INTO neg_header_record_data.auction_title,
neg_header_record_data.description,
neg_header_record_data.contract_type,
neg_header_record_data.bid_visibility_code,
neg_header_record_data.doctype_id,
neg_header_record_data.org_id,
neg_header_record_data.buyer_id,
neg_header_record_data.global_agreement_flag,
neg_header_record_data.style_id,
neg_header_record_data.po_style_id,
neg_header_record_data.po_style_name,
neg_header_record_data.price_break_response,
neg_header_record_data.advance_negotiable_flag,
neg_header_record_data.recoupment_negotiable_flag,
neg_header_record_data.progress_pymt_negotiable_flag,
neg_header_record_data.retainage_negotiable_flag,
neg_header_record_data.max_retainage_negotiable_flag,
neg_header_record_data.supplier_enterable_pymt_flag,
neg_header_record_data.progress_payment_type,
neg_header_record_data.line_mas_enabled_flag,
neg_header_record_data.price_tiers_indicator,
neg_header_record_data.ship_to_location_id,
neg_header_record_data.ship_to_location_code,
neg_header_record_data.bill_to_location_id,
neg_header_record_data.bill_to_location_code,
neg_header_record_data.payment_terms_id,
neg_header_record_data.fob_code,
neg_header_record_data.freight_terms_code,
neg_header_record_data.rate_type,
neg_header_record_data.currency_code,
neg_header_record_data.rate_date,
neg_header_record_data.security_level_code,
neg_header_record_data.po_start_date,
neg_header_record_data.po_end_date,
neg_header_record_data.open_auction_now_flag,
neg_header_record_data.open_bidding_date,
neg_header_record_data.close_bidding_date,
neg_header_record_data.publish_auction_now_flag,
--neg_header_record_data.publish_date,
--neg_header_record_data.auction_published_flag,
neg_header_record_data.view_by_date,
neg_header_record_data.note_to_bidders,
neg_header_record_data.show_bidder_notes,
neg_header_record_data.bid_scope_code,
neg_header_record_data.bid_list_type,
neg_header_record_data.bid_frequency_code,
neg_header_record_data.bid_ranking,
neg_header_record_data.rank_indicator,
neg_header_record_data.full_quantity_bid_code,
neg_header_record_data.multiple_rounds_flag,
neg_header_record_data.manual_close_flag,
neg_header_record_data.manual_extend_flag,
neg_header_record_data.award_approval_flag,
neg_header_record_data.auction_origination_code,
neg_header_record_data.pf_type_allowed,
neg_header_record_data.hdr_attr_enable_weights,
neg_header_record_data.award_by_date,
neg_header_record_data.auto_extend_flag,
neg_header_record_data.auto_extend_number,
neg_header_record_data.min_bid_decrement,
neg_header_record_data.min_bid_change_type,
neg_header_record_data.price_driven_auction_flag,
neg_header_record_data.carrier_code,
neg_header_record_data.auto_extend_all_lines_flag,
neg_header_record_data.allow_other_bid_currency_flag,
neg_header_record_data.auto_extend_duration,
neg_header_record_data.publish_rates_to_bidders_flag,
neg_header_record_data.event_id,
neg_header_record_data.event_title,
neg_header_record_data.sealed_auction_status,
neg_header_record_data.auto_extend_type_flag,
neg_header_record_data.show_bidder_scores,
neg_header_record_data.po_min_rel_amount,
neg_header_record_data.hdr_attr_display_score,
neg_header_record_data.int_attribute_category,
neg_header_record_data.int_attribute1,
neg_header_record_data.int_attribute2,
neg_header_record_data.int_attribute3,
neg_header_record_data.int_attribute4 ,
neg_header_record_data.int_attribute5,
neg_header_record_data.int_attribute6,
neg_header_record_data.int_attribute7,
neg_header_record_data.int_attribute8,
neg_header_record_data.int_attribute9,
neg_header_record_data.int_attribute10,
neg_header_record_data.int_attribute11,
neg_header_record_data.int_attribute12,
neg_header_record_data.int_attribute13,
neg_header_record_data.int_attribute14,
neg_header_record_data.int_attribute15,
neg_header_record_data.ext_attribute_category,
neg_header_record_data.ext_attribute1,
neg_header_record_data.ext_attribute2,
neg_header_record_data.ext_attribute3,
neg_header_record_data.ext_attribute4,
neg_header_record_data.ext_attribute5,
neg_header_record_data.ext_attribute6,
neg_header_record_data.ext_attribute7,
neg_header_record_data.ext_attribute8,
neg_header_record_data.ext_attribute9,
neg_header_record_data.ext_attribute10,
neg_header_record_data.ext_attribute11,
neg_header_record_data.ext_attribute12,
neg_header_record_data.ext_attribute13,
neg_header_record_data.ext_attribute14,
neg_header_record_data.ext_attribute15,
neg_header_record_data.abstract_details,
neg_header_record_data.supplier_view_type,
neg_header_record_data.project_id,
neg_header_record_data.bid_decrement_method,
neg_header_record_data.display_best_price_blind_flag,
neg_header_record_data.first_line_close_date,
neg_header_record_data.staggered_closing_interval,
neg_header_record_data.enforce_prevrnd_bid_price_flag,
neg_header_record_data.auto_extend_min_trigger_rank,
neg_header_record_data.two_part_flag,
neg_header_record_data.standard_form,
neg_header_record_data.document_format,
neg_header_record_data.amendment_description,
neg_header_record_data.creation_date,
neg_header_record_data.created_by,
neg_header_record_data.last_update_date,
neg_header_record_data.last_updated_by
,neg_header_record_data.ALLOW_WITHDRAW_FLAG,neg_header_record_data.ALLOW_UNSOL_OFFER_LINES, neg_header_record_data.ALLOW_MULTIPLE_ACTIVE_OFFERS
--,neg_header_record_data.EFFECTIVE_DATE
,neg_header_record_data.FAIR_OPP_NOTICE_FLAG
,neg_header_record_data.FEDERAL_PUBLICATION_EXCEPTION
,neg_header_record_data.NO_OF_COPIES
,neg_header_record_data.REVISION
,neg_header_record_data.SIGNED_DATE
,neg_header_record_data.SOLICITATION_TYPE
,neg_header_record_data.UMBRELLA_PROGRAM_ID
,neg_header_record_data.CD_EDITBYUSER_FLAG
,neg_header_record_data.CD_GENERATED_DATE
,neg_header_record_data.CD_GENERATED_FLAG
,neg_header_record_data.AMENDUPDATED_AFTERCDGENERATED
FROM pon_auction_headers_interface
WHERE batch_id = p_batch_id;
SELECT internal_name, transaction_type
INTO l_document_type, neg_header_record_data.auction_type
FROM pon_auc_doctypes
WHERE doctype_id = neg_header_record_data.doctype_id;
SELECT org_id, doctype_id, style_id, po_style_Id, contract_type
INTO neg_header_record_data.org_id, neg_header_record_data.doctype_id,
neg_header_record_data.style_id, neg_header_record_data.po_style_Id,
neg_header_record_data.contract_type
FROM pon_auction_headers_all
WHERE auction_header_id = p_src_auction_header_id;
insert_error ('PON_IMPORT_UDA_TEMP_ERR',p_batch_id,'UDA_TEMPLATE_ID',
NULL,fnd_global.user_id, fnd_global.login_id);
SELECT internal_name, transaction_type
INTO l_document_type, neg_header_record_data.auction_type
FROM pon_auc_doctypes
WHERE doctype_id = neg_header_record_data.doctype_id;
SELECT DOCTYPE_ID INTO rfi_doctype_id FROM pon_auc_doctypes WHERE INTERNAL_NAME='REQUEST_FOR_INFORMATION';
SELECT pdl.DOCUMENT_SUBTYPE, pdh.style_id
INTO neg_header_record_data.contract_type, neg_header_record_data.po_style_id
FROM po_doc_style_headers pdh, po_all_doc_style_Lines pdl
WHERE pdh.style_id = pdl.style_id
AND pdh.status= 'ACTIVE'
AND pdl.enabled_flag= 'Y'
and Nvl(pdh.clm_flag,'N') = PON_CLM_UTIL_PKG.ISCLMRESP
AND pdl.LANGUAGE=UserEnv('LANG')
AND pdl.display_name = neg_header_record_data.po_style_name;
select (nvl(max(location_id), -1))
INTO neg_header_record_data.ship_to_location_id
from po_ship_to_loc_org_v po_v
where po_v.location_code = neg_header_record_data.ship_to_location_code
AND neg_header_record_data.ship_to_location_code IS NOT NULL;
SELECT (nvl(max(location_id), -1))
INTO neg_header_record_data.bill_to_location_id
from HR_LOCATIONS_ALL L
where L.LOCATION_CODE = neg_header_record_data.bill_to_location_code
AND neg_header_record_data.bill_to_location_code IS NOT NULL
AND NVL(L.BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99)
AND SYSDATE < NVL(L.INACTIVE_DATE, SYSDATE + 1) AND NVL(L.BILL_TO_SITE_FLAG,'N') = 'Y';
select distinct
psp.org_id
, fsp.bill_to_location_id
, fsp.ship_to_location_id
, fsp.terms_id
, fsp.fob_lookup_code
, fsp.freight_terms_lookup_code
, psp.default_rate_type
, sob.currency_code
, posl.security_level_code
INTO
l_org_default_data
from
po_system_parameters_all psp
, hr_all_organization_units_tl haou
, financials_system_params_all fsp
, gl_sets_of_books sob
, hr_locations_all_tl bill_to
, hr_locations_all_tl ship_to
, ( SELECT po.org_id, po.security_level_code, pon.doctype_id
FROM po_document_types_all po, pon_auc_doctypes pon
WHERE po.document_type_code = pon.document_type_code
AND po.document_subtype = pon.document_subtype
AND po.security_level_code is not null
) posl
where psp.org_id = haou.organization_id
and haou.language = USERENV('LANG')
and psp.org_id = fsp.org_id (+)
and fsp.set_of_books_id = sob.set_of_books_id (+)
and fsp.bill_to_location_id = bill_to.location_id (+)
and fsp.ship_to_location_id = ship_to.location_id (+)
and psp.org_id = posl.org_id(+)
and neg_header_record_data.doctype_id = posl.doctype_id (+)
and neg_header_record_data.org_id = psp.org_id
AND bill_to.LANGUAGE(+) = USERENV('LANG')
AND ship_to.LANGUAGE(+) = USERENV('LANG');
SELECT
LINE_ATTRIBUTE_ENABLED_FLAG, LINE_MAS_ENABLED_FLAG, PRICE_ELEMENT_ENABLED_FLAG,
RFI_LINE_ENABLED_FLAG, LOT_ENABLED_FLAG, GROUP_ENABLED_FLAG, LARGE_NEG_ENABLED_FLAG,
HDR_ATTRIBUTE_ENABLED_FLAG, NEG_TEAM_ENABLED_FLAG, PROXY_BIDDING_ENABLED_FLAG,
POWER_BIDDING_ENABLED_FLAG, AUTO_EXTEND_ENABLED_FLAG, TEAM_SCORING_ENABLED_FLAG , QTY_PRICE_TIERS_ENABLED_FLAG,
SUPP_REG_QUAL_FLAG, SUPP_EVAL_FLAG, HIDE_TERMS_FLAG, HIDE_ABSTRACT_FORMS_FLAG, HIDE_ATTACHMENTS_FLAG,
INTERNAL_EVAL_FLAG, HDR_SUPP_ATTR_ENABLED_FLAG, INTGR_HDR_ATTR_FLAG, INTGR_HDR_ATTACH_FLAG, LINE_SUPP_ATTR_ENABLED_FLAG,
ITEM_SUPP_ATTR_ENABLED_FLAG, INTGR_CAT_LINE_ATTR_FLAG, INTGR_ITEM_LINE_ATTR_FLAG, INTGR_CAT_LINE_ASL_FLAG
INTO
neg_header_record_data.LINE_ATTRIBUTE_ENABLED_FLAG,
neg_header_record_data.LINE_MAS_ENABLED_FLAG,
neg_header_record_data.PRICE_ELEMENT_ENABLED_FLAG,
neg_header_record_data.RFI_LINE_ENABLED_FLAG,
neg_header_record_data.LOT_ENABLED_FLAG,
neg_header_record_data.GROUP_ENABLED_FLAG,
neg_header_record_data.LARGE_NEG_ENABLED_FLAG,
neg_header_record_data.HDR_ATTRIBUTE_ENABLED_FLAG,
neg_header_record_data.NEG_TEAM_ENABLED_FLAG,
neg_header_record_data.PROXY_BIDDING_ENABLED_FLAG,
neg_header_record_data.POWER_BIDDING_ENABLED_FLAG,
neg_header_record_data.AUTO_EXTEND_ENABLED_FLAG,
neg_header_record_data.TEAM_SCORING_ENABLED_FLAG ,
neg_header_record_data.QTY_PRICE_TIERS_ENABLED_FLAG,
neg_header_record_data.SUPP_REG_QUAL_FLAG,
neg_header_record_data.SUPP_EVAL_FLAG,
neg_header_record_data.HIDE_TERMS_FLAG,
neg_header_record_data.HIDE_ABSTRACT_FORMS_FLAG,
neg_header_record_data.HIDE_ATTACHMENTS_FLAG,
neg_header_record_data.INTERNAL_EVAL_FLAG,
neg_header_record_data.HDR_SUPP_ATTR_ENABLED_FLAG,
neg_header_record_data.INTGR_HDR_ATTR_FLAG,
neg_header_record_data.INTGR_HDR_ATTACH_FLAG,
neg_header_record_data.LINE_SUPP_ATTR_ENABLED_FLAG,
neg_header_record_data.ITEM_SUPP_ATTR_ENABLED_FLAG,
neg_header_record_data.INTGR_CAT_LINE_ATTR_FLAG,
neg_header_record_data.INTGR_ITEM_LINE_ATTR_FLAG,
neg_header_record_data.INTGR_CAT_LINE_ASL_FLAG
FROM
PON_NEGOTIATION_STYLES WHERE STYLE_ID = neg_header_record_data.style_id;
select event_id
INTO neg_header_record_data.event_id
from pon_auction_events
where event_title = neg_header_record_data.event_title
AND neg_header_record_data.event_title IS NOT NULL
and trading_partner_id = neg_header_record_data.trading_partner_id
and event_status <> 'CANCELLED' and NVL(open_date, SYSDATE - 100) >= SYSDATE;
SELECT STANDARD_FORM,DOCUMENT_FORMAT
INTO neg_header_record_data.standard_form,neg_header_record_data.document_format
FROM PO_PRINT_FORM_FORMATS
WHERE FORM_SOURCE='PON'
AND DEFAULT_FLAG='Y'
AND DOCUMENT_TYPE='PO_SOL_STD_FORM'
AND SYSDATE < NVL(INACTIVE_DATE,SYSDATE + 1);
SELECT close_bidding_date
INTO l_close_bidding_date
FROM PON_AUCTION_HEADERS_ALL
WHERE auction_header_id = p_auction_header_id;
pon_negotiation_publish_pvt.update_before_publish (
x_result,
x_error_code,
x_error_message,
p_auction_header_id,
l_close_bidding_date,
fnd_global.user_Id,
fnd_global.login_id);
UPDATE pon_auction_headers_all SET
OPEN_BIDDING_DATE=neg_header_record_data.open_bidding_date,
CLOSE_BIDDING_DATE=neg_header_record_data.CLOSE_BIDDING_DATE,
PUBLISH_DATE=neg_header_record_data.publish_date,
AUTO_EXTEND_FLAG='N'
WHERE auction_header_id = p_auction_header_id;
SELECT trading_partner_contact_id,auction_title
INTO l_trading_partner_contact_id,l_auction_title
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
l_disscussion_id:=PON_THREAD_DISC_PKG.INSERT_PON_DISCUSSIONS('PON_AUCTION_HEADERS_ALL',p_auction_header_id,NULL,NULL,
NULL,NULL,l_auction_title,USERENV('LANG'),l_trading_partner_contact_id,
'oracle.apps.pon.auctions.discussions.NegDiscussionValidation');
SELECT interface_header_id FROM pon_auction_Headers_interface
WHERE interface_group_id = p_group_batch_id
AND PROCESSING_STATUS_CODE = 'PENDING'
AND amendment_flag = 'Y';
UPDATE pon_auction_headers_interface
SET PROCESSING_STATUS_CODE = 'SUCCESS'
WHERE batch_id = l_batch_id;
DELETE FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_item_prices_all WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_price_elements WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_shipments_all WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_neg_team_members WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_attributes WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_bidding_parties WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_attribute_scores WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_sections WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_headers_ext_b WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_headers_ext_tl WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_item_prices_ext_b WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_auction_item_prices_ext_tl WHERE auction_header_id = l_auction_header_id;
UPDATE pon_auction_headers_interface
SET PROCESSING_STATUS_CODE = 'FAILED'
WHERE batch_id = l_batch_id;
l_update_clause pon_auction_headers_interface.update_clause_flag%TYPE;
update pon_auction_headers_interface SET batch_id = p_batch_id WHERE interface_header_id = p_batch_id;
update pon_item_prices_interface SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update PON_AUC_ATTRIBUTES_INTERFACE SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_attribute_scores_interface SET batch_id = p_batch_id, LINE_NUMBER = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_bid_parties_interface SET batch_id = p_batch_id WHERE interface_header_id = p_batch_id;
update PON_NEG_TEAM_INTERFACE SET batch_id = p_batch_id WHERE interface_header_id = p_batch_id;
update PON_AUC_PRICE_ELEMENTS_INT SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_auc_price_breaks_interface SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
update pon_auc_price_differ_int SET batch_id = p_batch_id, auction_line_number = interface_line_id WHERE interface_header_id = p_batch_id;
SELECT auction_header_id, award_approval_flag,
retain_clause_flag,update_clause_flag, retain_attachments_flag,
trading_partner_contact_name
INTO l_auction_header_id, l_award_approval,
l_retain_clause, l_update_clause, l_retain_attachments,
l_tpc_name
FROM pon_auction_headers_interface
WHERE batch_id = p_batch_id;
SELECT DISTINCT EMP.USER_NAME, emp.person_party_id, SUP.USER_ID
INTO l_username, l_tpc_id, l_mgr_id
FROM PER_ALL_ASSIGNMENTS_F ASS,
PER_ALL_ASSIGNMENTS_F SUPASS ,
FND_USER SUP ,
FND_USER EMP ,
PER_ALL_PEOPLE_F PER ,
PER_ALL_POSITIONS POS
WHERE ASS.PERSON_ID = EMP.EMPLOYEE_ID
AND ASS.SUPERVISOR_ID = SUP.EMPLOYEE_ID
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.ASSIGNMENT_TYPE IN ('E', 'C')
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE AND ASS.EFFECTIVE_END_DATE
AND SUPASS.PERSON_ID = ASS.SUPERVISOR_ID
AND SUPASS.POSITION_ID = POS.POSITION_ID(+)
AND SUPASS.PRIMARY_FLAG = 'Y'
AND ((SUPASS.ASSIGNMENT_TYPE = 'E'
AND PER.CURRENT_EMPLOYEE_FLAG = 'Y')
OR (SUPASS.ASSIGNMENT_TYPE = 'C'
AND PER.CURRENT_NPW_FLAG = 'Y'))
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN SUPASS.EFFECTIVE_START_DATE AND SUPASS.EFFECTIVE_END_DATE
AND SUP.START_DATE <= SYSDATE
AND NVL(SUP.END_DATE, SYSDATE) >= SYSDATE
AND PER.PERSON_ID = SUP.EMPLOYEE_ID
AND EMP.user_name = l_tpc_name;
insert_error ('PON_INT_TPC_INV',p_batch_id,'TRADING_PARTNER_CONTACT_NAME',
NULL,fnd_global.user_id, fnd_global.login_id);
SELECT doctype_id, style_id
INTO l_doctype_id, l_style_id
FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
update pon_item_prices_interface SET auction_header_id = x_auction_header_id WHERE batch_id = p_batch_id;
update PON_AUC_ATTRIBUTES_INTERFACE SET auction_header_id = x_auction_header_id WHERE batch_id = p_batch_id;
update pon_attribute_scores_interface SET auction_header_id = x_auction_header_id WHERE batch_id = p_batch_id;
update pon_bid_parties_interface SET auction_header_id = x_auction_header_id WHERE batch_id = p_batch_id;
update PON_NEG_TEAM_INTERFACE SET auction_header_id = x_auction_header_id WHERE batch_id = p_batch_id;
update PON_AUC_PRICE_ELEMENTS_INT SET auction_header_id = x_auction_header_id WHERE batch_id = p_batch_id;
update pon_auc_price_breaks_interface SET auction_header_id = x_auction_header_id WHERE batch_id = p_batch_id;
update pon_auc_price_differ_int SET auction_header_id = x_auction_header_id WHERE batch_id = p_batch_id;
SELECT 'E' status
INTO x_return_status
FROM dual
WHERE EXISTS
(SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
);
update_amendment_header(x_auction_header_id);
print_log('Header information updated for amendment ' || x_auction_header_id );
SELECT uda_template_id INTO l_uda_template_id
FROM pon_auction_Headers_all
WHERE auction_header_id = x_auction_header_id;
insert_error ('PON_INT_UDA_ERR',p_batch_id,'UDA',
NULL,fnd_global.user_id, fnd_global.login_id);
print_log('Header UDA information inserted' );
insert_error ('PON_INT_COLL_FAIL',p_batch_id,'COLLABORATION_TEAM',
NULL,fnd_global.user_id, fnd_global.login_id);
print_log('Negotiation team members insertion completed');
insert_error ('PON_INT_ATT_FAIL',p_batch_id,'HEADER_ATTRIBUTES',
NULL,fnd_global.user_id, fnd_global.login_id);
print_log('Header requirements insertion completed');
print_log('Lines information inserted for batch_id ' || p_batch_id || ' auction_header_id ' || x_auction_header_id
|| ' x_return_status ' || x_return_status);
insert_error ('PON_INT_LINE_FAIL',p_batch_id,'LINE',
NULL,fnd_global.user_id, fnd_global.login_id);
SELECT uda_template_id INTO l_uda_template_id
FROM pon_auction_item_prices_all
WHERE auction_header_id = x_auction_header_id
AND ROWNUM=1;
FOR rec IN (SELECT line_number FROM pon_auction_item_prices_all WHERE auction_Header_id = x_auction_header_id ) LOOP
process_uda_attributes(p_batch_id, x_auction_header_id, rec.line_number, l_uda_template_id, 'PON_AUC_PRICES_EXT_ATTRS', x_return_status);
print_log('failure in Line UDA insertion for line ' || rec.line_number);
insert_error ('PON_INT_LINEUDA_FAIL',p_batch_id,'LINE_UDA',
NULL,fnd_global.user_id, fnd_global.login_id);
print_Log('Line level UDA insertion completed successfully');
insert_error ('PON_INT_AMENDCHECK_FAIL',p_batch_id,'AMENDMENT',
NULL,fnd_global.user_id, fnd_global.login_id);
SELECT CLOSE_BIDDING_DATE, view_by_DATE, OPEN_BIDDING_DATE,auction_status,trading_partner_contact_id,trading_partner_id,security_level_code
INTO l_CLOSE_BIDDING_DATE,l_view_by_DATE,l_OPEN_BIDDING_DATE,l_auction_status,l_auc_tpc_id,l_auc_tp_id,l_security_level_code
FROM PON_AUCTION_HEADERS_ALL
WHERE auction_header_id=p_auction_header_id
AND auction_status = 'ACTIVE';
SELECT COUNT(AUCTION_HEADER_ID) INTO DRAFT_AMENDMENT_COUNT
FROM PON_AUCTION_HEADERS_ALL AH
WHERE AH.AUCTION_HEADER_ID_ORIG_AMEND <> AH.AUCTION_HEADER_ID
AND AH.AUCTION_HEADER_ID_ORIG_AMEND = (
SELECT AUCTION_HEADER_ID_ORIG_AMEND
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id)
AND AH.AUCTION_STATUS = 'DRAFT';
SELECT user_name INTO l_username FROM fnd_user WHERE user_id = p_tpc_id;
SELECT 'Y' INTO l_check FROM pon_neg_team_members WHERE MENU_NAME='PON_SOURCING_EDITNEG' AND user_name = l_username AND auction_header_id=p_auction_header_id ;
SELECT 'Y' INTO l_check FROM pon_neg_team_members WHERE user_name = l_username AND auction_header_id=p_auction_header_id ;
PROCEDURE update_amendment_header(p_auction_header_id IN NUMBER)
AS
BEGIN
UPDATE PON_AUCTION_HEADERS_ALL paha
SET
AUCTION_TITLE = Nvl(neg_header_record_data.auction_title,paha.auction_title) ,
description = Nvl(neg_header_record_data.description,paha.description),
GLOBAL_AGREEMENT_FLAG=Nvl(neg_header_record_data.GLOBAL_AGREEMENT_FLAG,paha.GLOBAL_AGREEMENT_FLAG),
PRICE_BREAK_RESPONSE = Nvl(neg_header_record_data.PRICE_BREAK_RESPONSE,paha.PRICE_BREAK_RESPONSE),
ADVANCE_NEGOTIABLE_FLAG = Nvl(neg_header_record_data.ADVANCE_NEGOTIABLE_FLAG,paha.ADVANCE_NEGOTIABLE_FLAG),
RECOUPMENT_NEGOTIABLE_FLAG= Nvl(neg_header_record_data.RECOUPMENT_NEGOTIABLE_FLAG,paha.RECOUPMENT_NEGOTIABLE_FLAG),
PROGRESS_PYMT_NEGOTIABLE_FLAG= Nvl(neg_header_record_data.PROGRESS_PYMT_NEGOTIABLE_FLAG,paha.PROGRESS_PYMT_NEGOTIABLE_FLAG),
RETAINAGE_NEGOTIABLE_FLAG= Nvl(neg_header_record_data.RETAINAGE_NEGOTIABLE_FLAG,paha.RETAINAGE_NEGOTIABLE_FLAG),
MAX_RETAINAGE_NEGOTIABLE_FLAG= Nvl(neg_header_record_data.MAX_RETAINAGE_NEGOTIABLE_FLAG,paha.MAX_RETAINAGE_NEGOTIABLE_FLAG),
SUPPLIER_ENTERABLE_PYMT_FLAG= Nvl(neg_header_record_data.SUPPLIER_ENTERABLE_PYMT_FLAG,paha.SUPPLIER_ENTERABLE_PYMT_FLAG),
PROGRESS_PAYMENT_TYPE= Nvl(neg_header_record_data.PROGRESS_PAYMENT_TYPE,paha.PROGRESS_PAYMENT_TYPE),
PRICE_TIERS_INDICATOR= Nvl(neg_header_record_data.PRICE_TIERS_INDICATOR, paha.PRICE_TIERS_INDICATOR),
-- QTY_PRICE_TIERS_ENABLED_FLAG= Nvl(neg_header_record_data.
ship_to_location_id= Nvl(neg_header_record_data.ship_to_location_id, paha.ship_to_location_id),
bill_to_location_id= Nvl(neg_header_record_data.bill_to_location_id, paha.bill_to_location_id),
payment_terms_id= Nvl(neg_header_record_data.payment_terms_id, paha.payment_terms_id),
fob_code= Nvl(neg_header_record_data.fob_code, paha.fob_code),
freight_terms_code= Nvl(neg_header_record_data.freight_terms_code, paha.freight_terms_code),
rate_type= Nvl(neg_header_record_data.rate_type, paha.rate_type),
currency_code= Nvl(neg_header_record_data.currency_code, paha.currency_code),
PO_START_DATE= Nvl(neg_header_record_data.PO_START_DATE, paha.PO_START_DATE),
PO_END_DATE= Nvl(neg_header_record_data.PO_END_DATE, paha.PO_END_DATE),
CLOSE_bidding_date= Nvl(neg_header_record_data.CLOSE_bidding_date, paha.CLOSE_bidding_date),
note_to_bidders= Nvl(neg_header_record_data.note_to_bidders, paha.note_to_bidders),
SHOW_BIDDER_NOTES= paha.SHOW_BIDDER_NOTES,
BID_SCOPE_CODE= Nvl(neg_header_record_data.BID_SCOPE_CODE, paha.BID_SCOPE_CODE),
-- BID_LIST_TYPE= Nvl(neg_header_record_data.
-- BID_FREQUENCY_CODE= Nvl(neg_header_record_data.
-- full_quantity_bid_code= Nvl(neg_header_record_data.
multiple_rounds_flag= Nvl(neg_header_record_data.multiple_rounds_flag, paha.multiple_rounds_flag),
manual_close_flag= Nvl(neg_header_record_data.manual_close_flag, paha.manual_close_flag),
manual_extend_flag= Nvl(neg_header_record_data.manual_extend_flag, paha.manual_extend_flag),
award_approval_flag= Nvl(neg_header_record_data.award_approval_flag, paha.award_approval_flag),
-- CLOSE_DATE= Nvl(neg_header_record_data.
AUTO_EXTEND_FLAG= Nvl(neg_header_record_data.AUTO_EXTEND_FLAG, paha.AUTO_EXTEND_FLAG),
AUTO_EXTEND_NUMBER= Nvl(neg_header_record_data.AUTO_EXTEND_NUMBER, paha.AUTO_EXTEND_NUMBER),
MIN_BID_DECREMENT= Nvl(neg_header_record_data.MIN_BID_DECREMENT, paha.MIN_BID_DECREMENT),
CARRIER_CODE= Nvl(neg_header_record_data.CARRIER_CODE, paha.CARRIER_CODE),
RATE_DATE= Nvl(neg_header_record_data.RATE_DATE, paha.RATE_DATE),
-- RATE= Nvl(neg_header_record_data.RATE, paha.RATE),
AUTO_EXTEND_ALL_LINES_FLAG= Nvl(neg_header_record_data.AUTO_EXTEND_ALL_LINES_FLAG, paha.AUTO_EXTEND_ALL_LINES_FLAG),
ALLOW_OTHER_BID_CURRENCY_FLAG= Nvl(neg_header_record_data.ALLOW_OTHER_BID_CURRENCY_FLAG, paha.ALLOW_OTHER_BID_CURRENCY_FLAG),
AUTO_EXTEND_DURATION= Nvl(neg_header_record_data.AUTO_EXTEND_DURATION, paha.AUTO_EXTEND_DURATION),
MIN_BID_CHANGE_TYPE= Nvl(neg_header_record_data.MIN_BID_CHANGE_TYPE, paha.MIN_BID_CHANGE_TYPE),
NUMBER_PRICE_DECIMALS= Nvl(neg_header_record_data.NUMBER_PRICE_DECIMALS, paha.NUMBER_PRICE_DECIMALS),
AUTO_EXTEND_TYPE_FLAG= Nvl(neg_header_record_data.AUTO_EXTEND_TYPE_FLAG, paha.AUTO_EXTEND_TYPE_FLAG),
-- APPROVAL_REQUIRED_FLAG= Nvl(neg_header_record_data.
MAX_RESPONSES= Nvl(neg_header_record_data.MAX_RESPONSES, paha.MAX_RESPONSES),
PO_MIN_REL_AMOUNT= Nvl(neg_header_record_data.PO_MIN_REL_AMOUNT, paha.PO_MIN_REL_AMOUNT),
AMENDMENT_DESCRIPTION= Nvl(neg_header_record_data.AMENDMENT_DESCRIPTION, paha.AMENDMENT_DESCRIPTION),
HDR_ATTR_MAXIMUM_SCORE= Nvl(neg_header_record_data.HDR_ATTR_MAXIMUM_SCORE, paha.HDR_ATTR_MAXIMUM_SCORE),
INT_ATTRIBUTE_CATEGORY= Nvl(neg_header_record_data.INT_ATTRIBUTE_CATEGORY, paha.INT_ATTRIBUTE_CATEGORY),
INT_ATTRIBUTE1= Nvl(neg_header_record_data.INT_ATTRIBUTE1, paha.INT_ATTRIBUTE1),
INT_ATTRIBUTE2= Nvl(neg_header_record_data.INT_ATTRIBUTE2, paha.INT_ATTRIBUTE2),
INT_ATTRIBUTE3= Nvl(neg_header_record_data.INT_ATTRIBUTE3, paha.INT_ATTRIBUTE3),
INT_ATTRIBUTE4= Nvl(neg_header_record_data.INT_ATTRIBUTE4, paha.INT_ATTRIBUTE4),
INT_ATTRIBUTE5= Nvl(neg_header_record_data.INT_ATTRIBUTE5, paha.INT_ATTRIBUTE5),
INT_ATTRIBUTE6= Nvl(neg_header_record_data.INT_ATTRIBUTE6, paha.INT_ATTRIBUTE6),
INT_ATTRIBUTE7= Nvl(neg_header_record_data.INT_ATTRIBUTE7, paha.INT_ATTRIBUTE7),
INT_ATTRIBUTE8= Nvl(neg_header_record_data.INT_ATTRIBUTE8, paha.INT_ATTRIBUTE8),
INT_ATTRIBUTE9= Nvl(neg_header_record_data.INT_ATTRIBUTE9, paha.INT_ATTRIBUTE9),
INT_ATTRIBUTE10= Nvl(neg_header_record_data.INT_ATTRIBUTE10, paha.INT_ATTRIBUTE10),
INT_ATTRIBUTE11= Nvl(neg_header_record_data.INT_ATTRIBUTE11, paha.INT_ATTRIBUTE11),
INT_ATTRIBUTE12 = Nvl(neg_header_record_data.INT_ATTRIBUTE12 , paha.INT_ATTRIBUTE12 ),
INT_ATTRIBUTE13 = Nvl(neg_header_record_data.INT_ATTRIBUTE13 , paha.INT_ATTRIBUTE13 ),
INT_ATTRIBUTE14 = Nvl(neg_header_record_data.INT_ATTRIBUTE14 , paha.INT_ATTRIBUTE14 ),
INT_ATTRIBUTE15= Nvl(neg_header_record_data.INT_ATTRIBUTE15, paha.INT_ATTRIBUTE15),
EXT_ATTRIBUTE_CATEGORY= Nvl(neg_header_record_data.EXT_ATTRIBUTE_CATEGORY, paha.EXT_ATTRIBUTE_CATEGORY),
EXT_ATTRIBUTE1= Nvl(neg_header_record_data.EXT_ATTRIBUTE1, paha.EXT_ATTRIBUTE1),
EXT_ATTRIBUTE2= Nvl(neg_header_record_data.EXT_ATTRIBUTE2, paha.EXT_ATTRIBUTE2),
EXT_ATTRIBUTE3= Nvl(neg_header_record_data.EXT_ATTRIBUTE3, paha.EXT_ATTRIBUTE3),
EXT_ATTRIBUTE4= Nvl(neg_header_record_data.EXT_ATTRIBUTE4, paha.EXT_ATTRIBUTE4),
EXT_ATTRIBUTE5 = Nvl(neg_header_record_data.EXT_ATTRIBUTE5 , paha.EXT_ATTRIBUTE5 ),
EXT_ATTRIBUTE6 = Nvl(neg_header_record_data.EXT_ATTRIBUTE6 , paha.EXT_ATTRIBUTE6 ),
EXT_ATTRIBUTE7 = Nvl(neg_header_record_data.EXT_ATTRIBUTE7 , paha.EXT_ATTRIBUTE7 ),
EXT_ATTRIBUTE8= Nvl(neg_header_record_data.EXT_ATTRIBUTE8, paha.EXT_ATTRIBUTE8),
EXT_ATTRIBUTE9= Nvl(neg_header_record_data.EXT_ATTRIBUTE9, paha.EXT_ATTRIBUTE9),
EXT_ATTRIBUTE10= Nvl(neg_header_record_data.EXT_ATTRIBUTE10, paha.EXT_ATTRIBUTE10),
EXT_ATTRIBUTE11= Nvl(neg_header_record_data.EXT_ATTRIBUTE11, paha.EXT_ATTRIBUTE11),
EXT_ATTRIBUTE12= Nvl(neg_header_record_data.EXT_ATTRIBUTE12, paha.EXT_ATTRIBUTE12),
EXT_ATTRIBUTE13= Nvl(neg_header_record_data.EXT_ATTRIBUTE13, paha.EXT_ATTRIBUTE13),
EXT_ATTRIBUTE14 = Nvl(neg_header_record_data.EXT_ATTRIBUTE14 , paha.EXT_ATTRIBUTE14 ),
EXT_ATTRIBUTE15= Nvl(neg_header_record_data.EXT_ATTRIBUTE15, paha.EXT_ATTRIBUTE15),
PROJECT_ID = Nvl(neg_header_record_data.PROJECT_ID , paha.PROJECT_ID ),
BID_DECREMENT_METHOD= Nvl(neg_header_record_data.BID_DECREMENT_METHOD, paha.BID_DECREMENT_METHOD),
DISPLAY_BEST_PRICE_BLIND_FLAG= Nvl(neg_header_record_data.DISPLAY_BEST_PRICE_BLIND_FLAG, paha.DISPLAY_BEST_PRICE_BLIND_FLAG),
FIRST_LINE_CLOSE_DATE = Nvl(neg_header_record_data.FIRST_LINE_CLOSE_DATE , paha.FIRST_LINE_CLOSE_DATE ),
STAGGERED_CLOSING_INTERVAL = Nvl(neg_header_record_data.STAGGERED_CLOSING_INTERVAL , paha.STAGGERED_CLOSING_INTERVAL ),
ENFORCE_PREVRND_BID_PRICE_FLAG= Nvl(neg_header_record_data.ENFORCE_PREVRND_BID_PRICE_FLAG, paha.ENFORCE_PREVRND_BID_PRICE_FLAG),
AUTO_EXTEND_MIN_TRIGGER_RANK = Nvl(neg_header_record_data.AUTO_EXTEND_MIN_TRIGGER_RANK , paha.AUTO_EXTEND_MIN_TRIGGER_RANK ),
standard_form= Nvl(neg_header_record_data.standard_form, paha.standard_form),
document_format= Nvl(neg_header_record_data.document_format, paha.document_format)
WHERE auction_header_id = p_auction_Header_id;
END update_amendment_header;
PROCEDURE insert_error (p_error_msg IN VARCHAR2 ,
p_batch_id IN NUMBER,
p_entity_type IN VARCHAR2,
p_auction_header_id IN NUMBER,
p_user_id IN NUMBER,
p_user_login IN number)
AS
BEGIN
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type,
p_error_msg,
p_batch_id,
p_entity_type,
p_auction_header_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_login
);
END insert_error;
INSERT ALL
WHEN (sel_line_id > 1
AND NOT EXISTS (SELECT interface_line_id FROM pon_item_prices_interface t2
WHERE t2.batch_id = p_batch_id
AND t2.interface_line_id = (sel_line_id - 1)) )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
line_number,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
'LINES',
'PON_IMPORT_LINE_NUM_ERR',
p_batch_id,
sel_line_id,
'PON_ITEM_PRICES_INTERFACE',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id)
WHEN (sel_line_id < 1 OR
( sel_line_id = 1 AND sel_group_type IN ('LOT_LINE','GROUP_LINE')))
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
line_number,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
'LINES',
'PON_IMPORT_LINE_NUM_ERR',
p_batch_id,
sel_line_id,
'PON_ITEM_PRICES_INTERFACE',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id)
WHEN (sel_line_id > 1
AND EXISTS (SELECT interface_line_id FROM pon_item_prices_interface t2
WHERE t2.batch_id = p_batch_id
AND t2.interface_line_id = (sel_line_id - 1)
AND
(
(sel_group_type = 'LOT_LINE'
AND t2.group_type NOT IN ('LOT','LOT_LINE'))
OR
(sel_group_type = 'GROUP_LINE'
AND t2.group_type NOT IN ('GROUP','GROUP_LINE'))
)) )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
line_number,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
'LINES',
'PON_IMPORT_LINE_ORDER_ERR',
p_batch_id,
sel_line_id,
'PON_ITEM_PRICES_INTERFACE',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id)
SELECT
interface_line_id sel_line_id,
group_type sel_group_type
FROM pon_item_prices_interface
WHERE batch_id = p_batch_id;
SELECT 1 INTO dummy FROM dual
WHERE NOT EXISTS (SELECT interface_line_id FROM pon_item_prices_interface
WHERE batch_id = p_batch_id AND interface_line_id = 1);
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
'LINES',
'PON_IMPORT_LINE_NUM_ERR',
p_batch_id,
'PON_ITEM_PRICES_INTERFACE',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id);
INSERT ALL
WHEN (sel_group_type <> 'LINE')
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
line_number,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
'LINES',
'PON_IMPORT_LINE_GRP_ERR',
p_batch_id,
sel_line_id,
'PON_ITEM_PRICES_INTERFACE',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id)
WHEN (sel_info_flag IS NULL )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
column_name,
BATCH_ID ,
line_number,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
'LINES',
'PON_FIELD_MUST_BE_ENTERED',
'CLM_INFO_FLAG',
p_batch_id,
sel_line_id,
'PON_ITEM_PRICES_INTERFACE',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id)
WHEN (sel_info_flag = 'Y'
AND (sel_price IS NOT NULL
OR sel_qty IS NOT NULL
OR sel_category IS NOT NULL
OR sel_line_type IS NOT NULL ) )
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
line_number,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
'LINES',
'PON_IMPORT_INFOLINE_ERR',
p_batch_id,
sel_line_id,
'PON_ITEM_PRICES_INTERFACE',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id)
WHEN (sel_group_Line_id IS NOT NULL
AND (EXISTS (SELECT interface_line_id FROM pon_item_prices_interface t2
WHERE t2.interface_line_id = sel_group_line_id
AND Nvl(t2.clm_info_flag,'N') = 'N'
AND Nvl(sel_info_flag,'N') = 'N' )
OR sel_line_id = 1)) -- first line can't be slin
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
BATCH_ID ,
line_number,
ENTITY_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(
'LINES',
'PON_IMPORT_CLINSLIN_ERR',
p_batch_id,
sel_line_id,
'PON_ITEM_PRICES_INTERFACE',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id)
SELECT
interface_line_id sel_line_id,
group_type sel_group_type,
clm_info_flag sel_info_flag,
group_line_id sel_group_Line_id,
price sel_price,
quantity sel_qty,
category_name sel_category,
line_type sel_line_type
FROM pon_item_prices_interface
WHERE batch_id = p_batch_id;