[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT blanket_po_header_id,
blanket_po_line_num,
document_type_code --
INTO x_source_header_id,
x_source_line_num,
l_doc_type_code --
FROM po_requisition_lines
WHERE requisition_line_id = x_requisition_line_id;
SELECT PH.po_header_id,
PH.type_lookup_code
INTO x_source_header_id,
x_src_document_type
FROM po_headers_all PH
WHERE PH.po_header_id = x_source_header_id
AND PH.type_lookup_code = 'CONTRACT'
AND PH.vendor_id = x_vendor_id
AND ((PH.global_agreement_flag = 'Y'
AND PH.currency_code = x_currency
AND (ph.vendor_site_id = p_vendor_site_id --
OR EXISTS (SELECT 1
FROM po_ga_org_assignments PGOA
WHERE PGOA.po_header_id = PH.po_header_id
AND PGOA.enabled_flag = 'Y'
AND PGOA.vendor_site_id = decode(Nvl(PH.Enable_All_Sites,'N'),'N',p_vendor_site_id, PGOA.vendor_site_id ))))
OR
(NVL(PH.global_agreement_flag, 'N') = 'N')
AND NVL(PH.org_id, -1) = NVL(p_purchasing_org_id, -1));
SELECT pl.po_line_id,
ph.type_lookup_code --
INTO x_source_line_id,
x_src_document_type --
FROM po_headers_all ph,
po_lines_all pl
WHERE ph.po_header_id = pl.po_header_id
AND ph.vendor_id = x_vendor_id
AND ph.currency_code = x_currency --
AND ((ph.type_lookup_code = 'BLANKET'
and nvl(ph.global_agreement_flag,'N') = 'Y'
--
--Need to ensure the site is header site or one of the
--purchasing site on Global Agreement
AND (ph.vendor_site_id = p_vendor_site_id
OR EXISTS (SELECT 1
FROM po_ga_org_assignments PGOA
WHERE PGOA.po_header_id = x_source_header_id
AND PGOA.enabled_flag = 'Y'
AND PGOA.vendor_site_id = p_vendor_site_id)))
OR (ph.type_lookup_code = 'QUOTATION'
AND ph.vendor_site_id = p_vendor_site_id))
--
AND ph.po_header_id = x_source_header_id
AND pl.line_num = x_source_line_num;
Select type_lookup_code,
vendor_id,
vendor_site_id,
currency_code,
terms_id,
ship_via_lookup_code,
fob_lookup_code,
freight_terms_lookup_code,
shipping_control --
into p_add_to_type,
p_add_to_vendor_id ,
p_add_to_vendor_site_id ,
p_add_to_currency_code ,
p_add_to_terms_id ,
p_add_to_ship_via_lookup_code ,
p_add_to_fob_lookup_code ,
p_add_to_freight_lookup_code,
x_add_to_shipping_control --
From po_headers_all
Where po_header_id = p_add_to_doc_id;
SELECT reqs_in_pool_flag
INTO l_reqs_in_pool_flag
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id;
PROCEDURE update_terms(p_new_po_id IN number) IS
l_source_doc_id number;
l_api_name CONSTANT VARCHAR2(30) := 'update_terms ';
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || '.begin','update terms');
SELECT COUNT(DISTINCT (DECODE(POSRC.type_lookup_code,
'BLANKET',
POSRC.po_header_id,
NULL))),
COUNT(DISTINCT (DECODE(POSRC.type_lookup_code,
'QUOTATION',
--NULL, --Bug# 5873206,
POSRC.po_header_id,
NULL))),
COUNT(DISTINCT (DECODE(POC.global_agreement_flag,
'Y',
NULL,
POC.po_header_id))),
COUNT(DISTINCT (DECODE(POC.global_agreement_flag,
'Y',
POC.po_header_id,
NULL)))
INTO l_ga_count,
l_quotation_count,
l_lc_count,
l_gc_count
FROM po_lines POL,
po_headers_all POSRC,
po_headers_all POC
WHERE POL.po_header_id = p_new_po_id
AND POL.from_header_id = POSRC.po_header_id (+)
AND POL.contract_id = POC.po_header_id (+);
Select terms_id,
fob_lookup_code,
freight_terms_lookup_code,
note_to_vendor,
note_to_receiver,
ship_via_lookup_code, -- <2748409>
pay_on_code, -- <2748409>
bill_to_location_id, -- <2748409>
ship_to_location_id, -- <2748409>
shipping_control --
into l_terms_id ,
l_fob_lookup_code ,
l_freight_lookup_code ,
l_supplier_note,
l_receiver_note,
l_ship_via_lookup_code, -- <2748409>
l_pay_on_code, -- <2748409>
l_bill_to_location_id, -- <2748409>
l_ship_to_location_id, -- <2748409>
l_shipping_control --
From po_headers_all
Where po_header_id = l_source_doc_id;
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || 'After SELECTing Terms and Conditions.' , 0);
update po_headers_all
set terms_id = nvl(l_terms_id ,terms_id),
fob_lookup_code = nvl(l_fob_lookup_code,fob_lookup_code),
freight_terms_lookup_code = nvl(l_freight_lookup_code,freight_terms_lookup_code),
note_to_vendor = l_supplier_note,
note_to_receiver = l_receiver_note,
shipping_control = nvl(l_shipping_control,shipping_control) --
where po_header_id = p_new_po_id;
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || 'After UPDATEing Global Terms and Conditions.' , 0);
UPDATE po_headers_all
SET ship_via_lookup_code = nvl(l_ship_via_lookup_code ,ship_via_lookup_code),
pay_on_code = nvl(l_pay_on_code ,pay_on_code ),
bill_to_location_id = l_bill_to_location_id ,
ship_to_location_id = l_ship_to_location_id
WHERE po_header_id = p_new_po_id;
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || 'After UPDATEing Local Terms and Conditions.' , 0);
FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,c_log_head || l_api_name ||'.EXCEPTION', 'update terms: Inside exception :'|| '000' ||sqlcode);
END update_terms;
SELECT POH.po_header_id
INTO x_src_doc_id
FROM po_lines POL,
po_headers_all POH
WHERE POL.po_header_id = p_po_header_id
AND POH.po_header_id = POL.from_header_id
AND POH.type_lookup_code = p_src_doc_type
AND NVL(POH.global_agreement_flag, 'N') = p_global_flag
AND ROWNUM = 1;
SELECT POH.po_header_id
INTO x_src_doc_id
FROM po_lines POL,
po_headers_all POH
WHERE POL.po_header_id = p_po_header_id
AND POH.po_header_id = POL.contract_id
AND NVL(POH.global_agreement_flag, 'N') = p_global_flag
AND ROWNUM = 1;