The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Effects: Return updated distribution qty X new price
from po_change_requests if any changes were made
* Else return req_line_quantity X unit price
* from po_req_distributions and po_requisition_lines
* If line was cancelled return 0
* Returns:
* Revised req total coming from a distribution
* If something fails returns 0
*/
FUNCTION get_req_distribution_total(p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_distribution_id IN NUMBER)
RETURN NUMBER
IS
l_matching_basis po_requisition_lines_all.matching_basis%type;
SELECT prl.matching_basis, pcr.action_type,
decode (prl.matching_basis, 'AMOUNT' , prd.req_line_amount,
prd.req_line_quantity*prl.unit_price) +
nvl(prd.nonrecoverable_tax,0),
pcr.change_request_group_id
INTO l_matching_basis, l_action_type, l_dist_total,
l_change_request_group_id
FROM po_requisition_lines_all prl,
po_req_distributions_all prd,
po_change_requests pcr
WHERE prl.requisition_line_id = p_line_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prd.distribution_id = p_distribution_id
AND nvl(prl.cancel_flag,'N') = 'N'
AND nvl(prl.modified_by_agent_flag, 'N') = 'N'
AND pcr.document_line_id(+) = prl.requisition_line_id
AND pcr.document_type(+) = 'REQ'
AND pcr.action_type(+) <> 'DERIVED'
AND pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
AND rownum =1;
* Effects: Return updated requisition total
* for a distribution
* using revised values of distribution qty and line price
* Returns:
* Revised req total coming from a distribution
* If something fails returns 0
*/
FUNCTION get_new_distribution_total(p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_distribution_id IN NUMBER,
p_matching_basis IN VARCHAR2,
p_change_request_group_id IN NUMBER)
RETURN NUMBER
IS
l_nonrec_tax NUMBER := 0;
SELECT prd.req_line_amount,
nvl(pcr.new_amount, prd.req_line_amount),
prd.nonrecoverable_tax
INTO l_old_dist_total, l_new_dist_total, l_nonrec_tax
FROM po_change_requests pcr,
po_req_distributions_all prd
WHERE prd.distribution_id = p_distribution_id
AND pcr.document_line_id(+) = prd.requisition_line_id
AND pcr.document_distribution_id(+) = prd.distribution_id
AND pcr.request_status(+) NOT IN ('ACCEPTED','REJECTED');
SELECT prd.req_line_quantity*prl.unit_price,
nvl(pcr1.new_quantity, prd.req_line_quantity)*nvl(pcr.new_price, prl.unit_price),
prd.nonrecoverable_tax
INTO l_old_dist_total, l_new_dist_total, l_nonrec_tax
FROM po_change_requests pcr,
po_change_requests pcr1,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prd.distribution_id = p_distribution_id
AND pcr1.document_distribution_id(+) = prd.distribution_id
AND pcr1.document_line_id(+) = prd.requisition_line_id
AND pcr1.request_status(+) NOT IN ('ACCEPTED','REJECTED')
AND prd.requisition_line_id = prl.requisition_line_id
AND pcr.document_line_id(+) = prl.requisition_line_id
AND pcr.new_price(+) IS NOT NULL
AND pcr.old_price(+) IS NOT NULL
AND pcr.document_type(+) = 'REQ'
AND pcr.request_status(+) NOT IN ('ACCEPTED','REJECTED')
AND pcr.request_level(+) = 'LINE'
AND pcr.action_type(+) <> 'CANCELLATION'
AND pcr.change_request_group_id(+) = p_change_request_group_id;