[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p_document_id doc_id,
p_document_type doc_type,
p_draft_id draft_id,
'Y' select_flag,
hdr.vendor_id vendor_id,
vendor.vendor_name supplier_name,
hdr.vendor_site_id vendor_site_id,
vendor_sites.vendor_site_code vendor_site_code,
offer.bid_number,
vendor_sites.duns_number,
PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
pk1_value => hdr.po_header_id,
pk2_value => -1,
p_attr_grp_int_name => 'SUPPLIER_DTLS',
p_attr_int_name => 'TIN Number') tin_number,
NULL eligibility_status,
NULL received_date,
NULL performed_by,
NULL Error,
NULL approved_without_check,
NULL Reason
FROM PO_HEADERS_MERGE_V hdr,
PO_VENDORS vendor,
PO_VENDOR_SITES_ALL vendor_sites,
PON_BID_HEADERS offer
WHERE hdr.po_header_id = p_document_id AND draft_id = -1
AND hdr.vendor_id = vendor.vendor_id(+)
AND vendor.vendor_id = vendor_sites.vendor_id(+)
AND hdr.vendor_site_id = vendor_sites.vendor_site_id
AND hdr.po_header_id = offer.po_header_id(+)
AND (p_document_id,p_document_type,p_draft_id, hdr.vendor_id, hdr.vendor_site_id) NOT IN (SELECT doc_id, doc_type, draft_id, vdr_elgb.vendor_id, vdr_elgb.vendor_site_id FROM po_vendor_eligibility vdr_elgb);
SELECT p_document_id doc_id,
p_document_type doc_type,
NULL draft_id,
'N' select_flag,
bh.trading_partner_id vendor_id,
bh.trading_partner_name supplier_name,
bh.vendor_site_id vendor_site_id,
bh.vendor_site_code vendor_site_code,
bh.bid_number,
(SELECT vendor_sites.duns_number FROM PO_VENDOR_SITES_ALL vendor_sites WHERE vendor_sites.vendor_site_id = bh.vendor_site_id AND ROWNUM=1) duns_number,
NULL tin_number,
NULL eligibility_status,
NULL received_date,
NULL performed_by,
NULL Error,
NULL approved_without_check,
NULL Reason
FROM PON_AUCTION_HEADERS_ALL_V ah,
PON_BID_HEADERS bh,
PO_VENDORS vendor
WHERE ah.auction_header_id = p_document_id
AND ah.auction_header_id = bh.auction_header_id
AND bh.trading_partner_id = vendor.vendor_id(+)
AND (p_document_id,p_document_type, bh.trading_partner_id, Nvl(bh.vendor_site_id,-99)) NOT IN (SELECT doc_id, doc_type, vdr_elgb.vendor_id, Nvl(vdr_elgb.vendor_site_id,-99) FROM po_vendor_eligibility vdr_elgb);
SELECT p_document_id doc_id,
p_document_type doc_type,
NULL draft_id,
'N' select_flag,
trading_partner_id vendor_id,
trading_partner_name supplier_name,
suppliers.vendor_site_id vendor_site_id,
suppliers.vendor_site_code vendor_site_code,
NULL bid_number,
(SELECT vendor_sites.duns_number FROM PO_VENDOR_SITES_ALL vendor_sites WHERE vendor_sites.vendor_site_id = suppliers.vendor_site_id AND ROWNUM=1) duns_number,
NULL tin_number,
NULL eligibility_status,
NULL received_date,
NULL performed_by,
NULL Error,
NULL approved_without_check,
NULL Reason
FROM PON_BIDDING_PARTIES suppliers, PO_VENDORS vendor
WHERE suppliers.auction_header_id = p_document_id
AND suppliers.trading_partner_id = vendor.vendor_id(+)
AND (p_document_id, p_document_type, suppliers.trading_partner_id, Nvl(suppliers.vendor_site_id, -99)) NOT IN (SELECT doc_id, doc_type, vdr_elgb.vendor_id, Nvl(vdr_elgb.vendor_site_id,-99) FROM po_vendor_eligibility vdr_elgb);
SELECT doc_id,
doc_type,
draft_id,
'N' select_flag,
vendor_id,
supplier_name,
vendor_site_id,
vendor_site_code,
duns_number,
tin_number,
bid_number,
eligibility_status,
received_date,
performed_by,
error,
approved_without_check,
reason,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
FROM po_vendor_eligibility vdr
WHERE vdr.doc_id = p_document_id
AND vdr.doc_type = p_document_type
AND (vdr.vendor_id , Nvl(vdr.vendor_site_id, -99)) NOT IN (SELECT bh.trading_partner_id , Nvl(bh.vendor_site_id,-99)
FROM PON_AUCTION_HEADERS_ALL_V ah,
PON_BID_HEADERS bh
WHERE ah.auction_header_id = p_document_id
AND ah.auction_header_id = bh.auction_header_id(+)
)
AND (vdr.vendor_id , Nvl(vdr.vendor_site_id, -99)) NOT IN (SELECT suppliers.trading_partner_id , Nvl(suppliers.vendor_site_id,-99)
FROM PON_BIDDING_PARTIES suppliers
WHERE suppliers.auction_header_id = p_document_id
);
SELECT p_document_id doc_id,
p_document_type doc_type,
p_draft_id draft_id,
'N' select_flag,
vendor_id,
supplier_name,
vendor_site_id,
vendor_site_code,
duns_number,
tin_number,
bid_number,
eligibility_status,
received_date,
performed_by,
error,
approved_without_check,
reason,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
FROM po_vendor_eligibility_h vdr
WHERE vdr.doc_id IN (SELECT auction_header_id FROM pon_bid_item_prices WHERE po_header_id = p_document_id)
AND vdr.doc_type = 'SOLICITATION'
AND vdr.vendor_id = (SELECT vendor_id FROM po_vendor_eligibility WHERE doc_id = p_document_id AND draft_id = p_draft_id)
AND Nvl(vdr.vendor_site_id,-99) = (SELECT Nvl(vendor_site_id,-99) FROM po_vendor_eligibility WHERE doc_id = p_document_id AND draft_id = p_draft_id);
INSERT INTO po_vendor_eligibility
(
id ,
doc_id ,
doc_type ,
draft_id ,
select_flag ,
vendor_id ,
supplier_name ,
vendor_site_id ,
vendor_site_code ,
duns_number ,
tin_number ,
bid_number ,
eligibility_status ,
received_date ,
performed_by ,
error ,
approved_without_check ,
reason ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
VALUES(po_vendor_eligibility_s.NEXTVAL,
c.doc_id,
c.doc_type,
c.draft_id,
c.select_flag,
c.vendor_id,
c.supplier_name,
c.vendor_site_id,
c.vendor_site_code,
c.duns_number,
c.tin_number,
c.bid_number,
c.eligibility_status,
c.received_date,
c.performed_by,
c.error,
c.approved_without_check,
c.Reason,
SYSDATE,
Fnd_Global.User_Id,
SYSDATE,
Fnd_Global.User_Id,
Fnd_Global.Login_Id
);
INSERT INTO po_vendor_eligibility_h
(
id ,
doc_id ,
doc_type ,
draft_id ,
select_flag ,
vendor_id ,
supplier_name ,
vendor_site_id ,
vendor_site_code ,
duns_number ,
tin_number ,
bid_number ,
eligibility_status ,
received_date ,
performed_by ,
error ,
approved_without_check ,
reason ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
VALUES (po_vendor_eligibility_s.NEXTVAL,
c.doc_id,
c.doc_type,
c.draft_id,
c.select_flag,
c.vendor_id,
c.supplier_name,
c.vendor_site_id,
c.vendor_site_code,
c.duns_number,
c.tin_number,
c.bid_number,
c.eligibility_status,
c.received_date,
c.performed_by,
c.error,
c.approved_without_check,
c.reason,
c.last_update_date,
c.last_updated_by,
c.creation_date,
c.created_by,
c.last_update_login);
INSERT INTO po_vendor_eligibility
(
id ,
doc_id ,
doc_type ,
draft_id ,
select_flag ,
vendor_id ,
supplier_name ,
vendor_site_id ,
vendor_site_code ,
duns_number ,
tin_number ,
bid_number ,
eligibility_status ,
received_date ,
performed_by ,
error ,
approved_without_check ,
reason ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
VALUES(po_vendor_eligibility_s.NEXTVAL,
c.doc_id,
c.doc_type,
c.draft_id,
c.select_flag,
c.vendor_id,
c.supplier_name,
c.vendor_site_id,
c.vendor_site_code,
c.duns_number,
c.tin_number,
c.bid_number,
c.eligibility_status,
c.received_date,
c.performed_by,
c.error,
c.approved_without_check,
c.Reason,
SYSDATE,
Fnd_Global.User_Id,
SYSDATE,
Fnd_Global.User_Id,
Fnd_Global.Login_Id
);
INSERT INTO po_vendor_eligibility
(
id ,
doc_id ,
doc_type ,
draft_id ,
select_flag ,
vendor_id ,
supplier_name ,
vendor_site_id ,
vendor_site_code ,
duns_number ,
tin_number ,
bid_number ,
eligibility_status ,
received_date ,
performed_by ,
error ,
approved_without_check ,
reason ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
VALUES(po_vendor_eligibility_s.NEXTVAL,
c.doc_id,
c.doc_type,
c.draft_id,
c.select_flag,
c.vendor_id,
c.supplier_name,
c.vendor_site_id,
c.vendor_site_code,
c.duns_number,
c.tin_number,
c.bid_number,
c.eligibility_status,
c.received_date,
c.performed_by,
c.error,
c.approved_without_check,
c.Reason,
SYSDATE,
Fnd_Global.User_Id,
SYSDATE,
Fnd_Global.User_Id,
Fnd_Global.Login_Id
);
INSERT INTO po_vendor_eligibility_h
(
id ,
doc_id ,
doc_type ,
draft_id ,
select_flag ,
vendor_id ,
supplier_name ,
vendor_site_id ,
vendor_site_code ,
duns_number ,
tin_number ,
bid_number ,
eligibility_status ,
received_date ,
performed_by ,
error ,
approved_without_check ,
reason ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
VALUES (po_vendor_eligibility_s.NEXTVAL,
c.doc_id,
c.doc_type,
c.draft_id,
c.select_flag,
c.vendor_id,
c.supplier_name,
c.vendor_site_id,
c.vendor_site_code,
c.duns_number,
c.tin_number,
c.bid_number,
c.eligibility_status,
c.received_date,
c.performed_by,
c.error,
c.approved_without_check,
c.reason,
c.last_update_date,
c.last_updated_by,
c.creation_date,
c.created_by,
c.last_update_login);
DELETE FROM po_vendor_eligibility
WHERE doc_id = c.doc_id
AND doc_type = c.doc_type
AND vendor_id = c.vendor_id
AND Nvl(vendor_site_code,'aa') = Nvl(c.vendor_site_code,'aa');
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'PO_VENDOR_CHECK_STATUS'
AND (SYSDATE BETWEEN start_date_active AND Nvl(end_date_active,SYSDATE+1))
AND lookup_code = p_lookup_code ;
CURSOR c_get_selected_suppliers IS
SELECT id , eligibility_status , supplier_name , vendor_site_code , duns_number , tin_number, vendor_site_id, vendor_id
FROM po_vendor_eligibility
WHERE doc_id = p_document_id
AND doc_type = p_document_type
AND Nvl(draft_id,-1) = Nvl(p_draft_id, -1)
AND select_flag = 'Y'
AND p_doc_type_class = 'SOURCING'
UNION
SELECT id , eligibility_status , supplier_name , vendor_site_code , duns_number , tin_number, vendor_site_id, vendor_id
FROM po_vendor_eligibility
WHERE doc_id = p_document_id
AND doc_type = p_document_type
AND Nvl(draft_id,-1) = Nvl(p_draft_id, -1)
AND p_doc_type_class = 'PO';
SELECT error
FROM po_Vendor_eligibility
WHERE id = p_id;
FOR c IN c_get_selected_suppliers LOOP
IF c.eligibility_status IS NOT NULL OR l_error IS NOT NULL THEN
INSERT INTO po_vendor_eligibility_h
(
id ,
doc_id ,
doc_type ,
draft_id ,
select_flag ,
vendor_id ,
supplier_name ,
vendor_site_id ,
vendor_site_code ,
duns_number ,
tin_number ,
bid_number ,
eligibility_status ,
received_date ,
performed_by ,
error ,
approved_without_check ,
reason ,
file_id_request ,
file_id_response ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
SELECT po_vendor_eligibility_h_s.NEXTVAL,
doc_id,
doc_type,
draft_id,
select_flag,
vendor_id,
supplier_name,
vendor_site_id,
vendor_site_code,
duns_number,
tin_number,
bid_number,
eligibility_status,
received_date,
performed_by,
error,
approved_without_check,
reason,
file_id_request,
file_id_response,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
FROM po_vendor_eligibility
WHERE id = c.id;
SELECT po_epls_evnt_s.NEXTVAL INTO l_event_key FROM dual;
ParamList.DELETE;
INSERT INTO fnd_lobs
(
file_id,
file_name,
file_content_type,
file_data,
upload_date,
expiration_date,
program_name,
program_tag,
language,
oracle_charset,
file_format
)
VALUES
(l_fnd_lob_seq,
'Verify Vendor Eligibility Check Request XML',
'text/xml',
l_blob,
SYSDATE,
NULL,
NULL,
NULL,
null,
null,
'text');
UPDATE po_vendor_eligibility
SET eligibility_status = l_lookup_code,
received_date = SYSDATE,
performed_by = Fnd_Global.User_Id,
file_id_request = l_fnd_lob_seq,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.User_Id,
last_update_login = Fnd_Global.Login_Id,
select_flag = 'N'
WHERE id = c.id;
SELECT org_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT ENABLE_EPLS , Trunc(EPLS_TIMEFRAME) FROM po_system_parameters_all WHERE org_id = l_org_id;
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'PO_VENDOR_CHECK_STATUS'
AND lookup_code = cp_lookup_code
AND (SYSDATE BETWEEN start_date_active AND Nvl(end_date_active,SYSDATE+1));
SELECT supplier_name, (CASE
WHEN eligibility_status = l_eligible AND Trunc((SYSDATE - received_Date)*24) <= l_epls_timeframe THEN 'ELIGIBLE'
WHEN approved_without_check = 'Y' THEN 'APPROVED'
WHEN eligibility_status = l_ineligible AND Trunc((SYSDATE - received_Date)*24) <= l_epls_timeframe THEN 'INELIGIBLE'
ELSE 'NOTRUN' END) supplierstatus
FROM po_vendor_eligibility
WHERE doc_id = p_auction_header_id
AND doc_type = 'SOLICITATION'
--AND vendor_id IN (SELECT trading_partner_id FROM pon_bid_headers WHERE auction_header_id = p_auction_header_id AND award_status IN ('AWARDED','PARTIAL'));
AND ((vendor_id IN (SELECT trading_partner_id FROM pon_bid_headers WHERE auction_header_id = p_auction_header_id))
OR
(vendor_id IN (SELECT trading_partner_id FROM PON_BIDDING_PARTIES WHERE auction_header_id = p_auction_header_id)));
SELECT EXTRACTVALUE (VALUE (a), '*//city') city,
EXTRACTVALUE (VALUE (a), '*//country') country,
EXTRACTVALUE (VALUE (a), '*//duns') duns_number,
EXTRACTVALUE (VALUE (a), '*//province') province,
EXTRACTVALUE (VALUE (a), '*//state') state,
EXTRACTVALUE (VALUE (a), '*//street1') address_line1,
EXTRACTVALUE (VALUE (a), '*//street2') address_line2,
EXTRACTVALUE (VALUE (a), '*//zip') zip ,
EXTRACTVALUE (VALUE (a), '*//classification') classification,
EXTRACTVALUE (VALUE (a), '*//exclusionType') exclusionType,
EXTRACTVALUE (VALUE (a), '*/first') first,
EXTRACTVALUE (VALUE (a), '*/last') last,
EXTRACTVALUE (VALUE (a), '*/middle') middle,
EXTRACTVALUE (VALUE (a), '*/name') name
FROM TABLE (XMLSEQUENCE (EXTRACT (l_result_xml,'*//results/results'))) a ;
SELECT CITY,COUNTRY,vdr.DUNS_NUMBER,PROVINCE,STATE,ADDRESS_LINE1,ADDRESS_LINE2,ZIP,SUPPLIER_NAME
FROM po_vendor_eligibility vdr, po_vendor_sites_all site
WHERE id = to_number(p_id)
AND vdr.vendor_site_id = site.vendor_site_id;
INSERT INTO fnd_lobs
(
file_id,
file_name,
file_content_type,
file_data,
upload_date,
expiration_date,
program_name,
program_tag,
language,
oracle_charset,
file_format
)
VALUES
(l_fnd_lob_seq,
'Verify Vendor Eligibility Check Response XML',
'text/xml',
l_blob,
SYSDATE,
NULL,
NULL,
NULL,
null,
null,
'text');
UPDATE po_vendor_eligibility
SET file_id_response = l_fnd_lob_seq
WHERE id = l_id;
UPDATE po_vendor_eligibility
SET eligibility_status = l_lookup_code,
received_date = SYSDATE,
-- performed_by = Fnd_Global.User_Id,
last_update_date = SYSDATE,
-- last_updated_by = Fnd_Global.User_Id,
-- last_update_login = Fnd_Global.Login_Id,
select_flag = 'N',
error = l_error_msg
WHERE id = l_id;
SELECT 'Y'
FROM po_lines_merge_v modif
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id
AND CLM_EXERCISED_FLAG = 'Y'
AND EXISTS (SELECT 1
FROM po_lines_merge_v base
WHERE modif.po_line_id = base.po_line_id
AND po_header_id = p_po_header_id
AND draft_id = -1
AND Nvl(CLM_EXERCISED_FLAG,'N')='N');
SELECT 'Y'
FROM po_lines_merge_v
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id
AND CHANGE_STATUS = 'NEW';
SELECT 'N' FROM dual;
SELECT 'Y'
FROM po_drafts
WHERE draft_id = p_draft_id
AND draft_type = 'MOD';
/*SELECT 'Y'
FROM po_line_locations_merge_v modif
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id
AND CLM_POP_DURATION > (SELECT CLM_POP_DURATION
FROM po_line_locations_merge_v base
WHERE modif.po_line_location_id = base.po_line_location_id
AND po_header_id = p_po_header_id
AND draft_id = -1
);*/
SELECT 'Y'
FROM po_vendor_eligibility
WHERE eligibility_status = 'Submitted'
AND RECEIVED_DATE < (SYSDATE - (1/24))
AND doc_id = p_po_header_id
AND doc_type = p_document_type
AND draft_id = p_draft_id;