[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO l_clm_document
FROM po_requisition_headers_all
WHERE requisition_header_id = p_doc_level_id
AND Nvl(federal_flag,'N') = 'Y';
SELECT 'Y'
INTO l_clm_document
FROM po_headers_all h,
po_doc_style_headers pdsh
WHERE h.po_header_id = p_doc_level_id
AND h.style_id = pdsh.style_id
AND Nvl(pdsh.clm_flag,'N') = 'Y';
SELECT NVL(style_id,-1)
INTO l_style_id
FROM po_headers_all
WHERE po_header_id = p_doc_header_id;
SELECT NVL(purch_encumbrance_flag,'N')
INTO l_po_enc_flag
FROM financials_system_params_all fsp,
po_headers_all poh
WHERE poh.po_header_id = p_doc_header_id
AND fsp.org_id = poh.org_id;
This function should be able to calculate and update Qty Funded and amt funded to
*/
PROCEDURE Updt_funding_info
(x_return_status OUT NOCOPY VARCHAR2,
p_document_type IN VARCHAR2,
p_doc_header_id IN NUMBER,
p_draft_id IN NUMBER DEFAULT -1)
IS
d_api_name CONSTANT VARCHAR2(30) := 'Updt_Funding_Info';
SELECT d.distribution_id,
Nvl(l.unit_price,0),
Nvl(l.matching_basis,'QUANTITY'),
d.req_line_quantity,
d.req_line_amount,
Nvl(d.partial_funded_flag,'N'),
Nvl(d.funded_value,0),
Nvl(d.nonrecoverable_tax,0),
1,
d.quantity_funded,
d.amount_funded,
GLSOB.CURRENCY_CODE
BULK COLLECT INTO l_dist_id_tbl,l_price_tbl,l_match_basis_tbl,l_qty_ordered_tbl,
l_amt_ordered_tbl,l_partial_flag_tbl,l_fund_value_tbl,l_nr_tax_tbl,
l_rate_tbl,l_qty_funded_tbl,l_amt_funded_tbl,l_func_currency_tbl
FROM po_req_distributions_all d,
po_requisition_lines_all l,
GL_SETS_OF_BOOKS GLSOB
WHERE l.requisition_header_id = p_doc_header_id
AND l.requisition_line_id = d.requisition_line_id
AND Nvl(l.cancel_flag,'N') <> 'Y'
AND Nvl(l.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND d.prevent_encumbrance_flag <> 'Y'
AND d.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID;
SELECT d.po_distribution_id,
Nvl(s.price_override,0),
Nvl(s.matching_basis,'QUANTITY'),
d.quantity_ordered,
d.amount_ordered,
Nvl(d.partial_funded_flag,'N'),
Nvl(d.funded_value,0),
Nvl(d.nonrecoverable_tax,0),
Nvl(d.rate,1),
d.quantity_funded,
d.amount_funded,
GLSOB.CURRENCY_CODE
BULK COLLECT INTO l_dist_id_tbl,l_price_tbl,l_match_basis_tbl,l_qty_ordered_tbl,
l_amt_ordered_tbl,l_partial_flag_tbl,l_fund_value_tbl,l_nr_tax_tbl,
l_rate_tbl,l_qty_funded_tbl,l_amt_funded_tbl,l_func_currency_tbl
FROM po_distributions_merge_v d,
po_line_locations_merge_v s,
GL_SETS_OF_BOOKS GLSOB
WHERE d.po_header_id = p_doc_header_id
AND d.draft_id = p_draft_id
AND s.draft_id = p_draft_id
AND s.line_location_id = d.line_location_id
AND Nvl(s.cancel_flag,'N') <> 'Y'
AND Nvl(s.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND d.prevent_encumbrance_flag <> 'Y'
AND d.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID;
UPDATE po_req_distributions_all d
SET d.funded_value = L_fund_value_tbl(i),
d.quantity_funded = L_qty_funded_tbl(i),
d.amount_funded = L_amt_funded_tbl(i)
WHERE d.distribution_id = L_dist_id_tbl(i);
UPDATE po_distributions_draft_all d
SET d.funded_value = L_fund_value_tbl(i),
d.quantity_funded = L_qty_funded_tbl(i),
d.amount_funded = L_amt_funded_tbl(i)
WHERE d.po_distribution_id = L_dist_id_tbl(i)
AND d.draft_id = p_draft_id;
UPDATE po_distributions_all d
SET d.funded_value = L_fund_value_tbl(i),
d.quantity_funded = L_qty_funded_tbl(i),
d.amount_funded = L_amt_funded_tbl(i),
d.change_in_funded_value = L_fund_value_tbl(i) -- <>
WHERE d.po_distribution_id = L_dist_id_tbl(i)
AND Nvl(d.encumbered_flag,'N') <> 'Y'; -- <>
/* Bug#10206681: The change_in_funded_value of the Award is getting updated
as part of recalculation of PF attributes of Modification,
immediately after tax calculation. This need to be prevented,
using the constraint Nvl(d.encumbered_flag,'N') <> 'Y'. */
END IF;
SELECT Nvl(d.funded_value,0) - p_funded_value
INTO x_chng_in_funded_value
FROM po_req_distributions_all d
WHERE d.distribution_id = p_conf_dist_id;
SELECT Nvl(d.funded_value,0) - p_funded_value
INTO x_chng_in_funded_value
FROM po_distributions_all d
WHERE d.po_distribution_id = p_distribution_id;
select
funded_value,
Req_Line_Quantity
from po_Req_Distributions_all
Where Distribution_Id = p_Distribution_Id;
SELECT
distribution_id,
partial_funded_flag,
req_line_quantity,
prl.Unit_Price,
Nvl(prd.nonrecoverable_tax,0),
source_req_distribution_id
FROM po_requisition_lines_all prl, po_req_distributions_all prd
WHERE prl.Requisition_Line_Id =p_new_Req_line_id
and prd.Requisition_Line_Id = prl.Requisition_Line_Id;
Update po_Req_Distributions_all
Set funded_value = l_funded_Value_tbl(i),
Quantity_Funded = l_Qty_Funded_Tbl(i),
Change_In_Funded_Value = l_Chg_Fund_Value_Tbl(i)
Where Distribution_Id = l_Distribution_id_tbl(i);
SELECT Nvl(funded_value,0)
INTO l_funded_value
FROM po_req_distributions_all
WHERE distribution_id = l_distribution_id;
SELECT Sum(Nvl(funded_value,0))
INTO l_funded_value
FROM po_req_distributions_all d,
po_requisition_lines_all l
WHERE d.requisition_line_id = l.requisition_line_id
AND l.requisition_line_id = l_line_id;
SELECT Sum(Nvl(funded_value,0))
INTO l_funded_value
FROM po_req_distributions_all d,
po_requisition_lines_all l
WHERE d.requisition_line_id = l.requisition_line_id
AND l.requisition_header_id = l_header_id;
SELECT Nvl(funded_value,0)
INTO l_funded_value
FROM po_distributions_all
WHERE po_distribution_id = l_distribution_id;
SELECT Sum(Nvl(funded_value,0))
INTO l_funded_value
FROM po_distributions_all d
WHERE d.line_location_id = l_shipment_id;
SELECT Sum(Nvl(funded_value,0))
INTO l_funded_value
FROM po_distributions_all d
WHERE d.po_line_id = l_line_id;
SELECT Sum(Nvl(funded_value,0))
INTO l_funded_value
FROM po_distributions_all d
WHERE d.po_header_id = l_header_id;