[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(SUM(get_new_distribution_qty(prl.requisition_header_id, p_grp_id, prl.requisition_line_id,
prd.distribution_id)),
0)
INTO l_new_line_qty
FROM po_req_distributions prd,
po_requisition_lines prl
WHERE prl.requisition_header_id = p_req_id
AND prl.requisition_line_id = p_req_line_id
AND prl.requisition_line_id = prd.requisition_line_id
AND nvl(prl.cancel_flag, 'N') = 'N'
AND nvl(prl.modified_by_agent_flag, 'N') = 'N';
* Effects: Return updated distribution qty from po_change_requests if any
* Else Return the req_line_quantity from po_req_distributions
* If line was cancelled return 0
* Returns:
* Revised distribution quantity
* If something fails returns 0
*/
FUNCTION get_new_distribution_qty
(p_req_id IN NUMBER,
p_grp_id IN NUMBER,
p_req_line_id IN NUMBER,
p_req_dist_id IN NUMBER)
RETURN NUMBER
IS
l_new_qty NUMBER := 0;
SELECT COUNT(* )
INTO l_qty_changed
FROM po_change_requests
WHERE document_distribution_id = p_req_dist_id
AND document_line_id = p_req_line_id
AND document_header_id = p_req_id
AND request_status = 'SYSTEMSAVE'
AND new_quantity IS NOT NULL ;
SELECT new_quantity
INTO l_new_qty
FROM po_change_requests
WHERE document_distribution_id = p_req_dist_id
AND document_line_id = p_req_line_id
AND document_header_id = p_req_id
AND request_status = 'SYSTEMSAVE';
SELECT req_line_quantity
INTO l_new_qty
FROM po_req_distributions
WHERE distribution_id = p_req_dist_id;
SELECT 'N'
INTO l_return_val
FROM dual
WHERE EXISTS (SELECT 'N'
FROM po_change_requests
WHERE change_request_group_id = p_reqgrp_id
AND action_type = 'MODIFICATION'
AND request_status = 'SYSTEMSAVE'
AND request_level = 'LINE'
AND (change_within_tol_date(old_need_by_date, new_need_by_date, l_tolerances_tbl(TOL_INT_NEEDBY_IND).max_increment,
l_tolerances_tbl(TOL_INT_NEEDBY_IND).max_decrement) = 'N')
);
SELECT 'N'
INTO l_return_val
FROM dual
WHERE EXISTS (SELECT 'N'
FROM po_change_requests
WHERE change_request_group_id = p_reqgrp_id
AND action_type = 'MODIFICATION'
AND request_status = 'SYSTEMSAVE'
AND request_level = 'LINE'
AND ((change_within_tol_date(old_need_by_date, new_need_by_date, l_tolerances_tbl(tol_needby_ind).max_increment,
l_tolerances_tbl(tol_needby_ind).max_decrement) = 'N')
OR (change_within_tol_date(old_start_date, new_start_date, l_tolerances_tbl(tol_startdate_ind).max_increment,
l_tolerances_tbl(tol_startdate_ind).max_decrement) = 'N')
OR (change_within_tol_date(old_expiration_date, new_expiration_date, l_tolerances_tbl(tol_enddate_ind).max_increment,
l_tolerances_tbl(tol_enddate_ind).max_decrement) = 'N')
OR (change_within_tol_percent(old_price, new_price, l_tolerances_tbl(tol_unitprice_ind).max_increment,
l_tolerances_tbl(tol_unitprice_ind).max_decrement) = 'N')));
SELECT 'N'
INTO l_return_val
FROM dual
WHERE EXISTS (SELECT 'N' FROM (
SELECT PRL.UNIT_PRICE AS UNIT_PRICE, PRL.QUANTITY AS QUANTITY,PCR.NEW_QUANTITY AS NEW_QUANTITY
FROM PO_CHANGE_REQUESTS PCR,
PO_REQUISITION_LINES_ALL PRL
WHERE PCR.CHANGE_REQUEST_GROUP_ID = p_reqgrp_id
AND PCR.ACTION_TYPE = 'MODIFICATION'
AND PCR.REQUEST_STATUS NOT IN ('ACCEPTED',
'REJECTED')
AND PCR.REQUEST_LEVEL ='LINE'
AND PCR.INITIATOR = 'REQUESTER'
AND PCR.DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID
AND PCR.NEW_quantity IS NOT NULL) b
WHERE (((CHANGES_WITHIN_TOL( b.UNIT_PRICE * b.QUANTITY,
NVL(b.NEW_QUANTITY,b.QUANTITY) * b.UNIT_PRICE,
l_tolerances_tbl(tol_lineamt_ind).max_increment,
l_tolerances_tbl(tol_lineamt_ind).max_decrement,
l_tolerances_tbl(tol_lineamt_amt_ind).max_increment,
l_tolerances_tbl(tol_lineamt_amt_ind).max_decrement))= 'N')
OR (CHANGE_WITHIN_TOL_PERCENT( b.QUANTITY, b.NEW_QUANTITY,
l_tolerances_tbl(tol_lineqty_ind).max_increment,
l_tolerances_tbl(tol_lineqty_ind).max_decrement) = 'N')));
SELECT 'N'
INTO l_return_val
FROM dual
WHERE EXISTS (SELECT 'N'
FROM po_change_requests pcr,
po_change_requests pcr1,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prd.requisition_line_id = prl.requisition_line_id
AND pcr.change_request_group_id = p_reqgrp_id
AND pcr.action_type (+ ) = 'MODIFICATION'
AND pcr.request_status (+ ) NOT IN ('ACCEPTED',
'REJECTED')
AND pcr.request_level (+ ) = 'DISTRIBUTION'
AND pcr.initiator (+ ) = 'REQUESTER'
AND pcr.document_distribution_id (+ ) = prd.distribution_id
-- AND pcr.document_line_id = pcr1.document_line_id
AND pcr1.change_request_group_id (+ ) = p_reqgrp_id
AND pcr1.document_line_id (+ ) = prl.requisition_line_id
AND pcr1.action_type (+ ) = 'MODIFICATION'
AND pcr1.request_status (+ ) NOT IN ('ACCEPTED',
'REJECTED')
AND pcr1.request_level (+ ) = 'LINE'
AND pcr1.initiator (+ ) = 'REQUESTER'
AND pcr1.new_price (+ ) IS NOT NULL
GROUP BY pcr.document_line_id
HAVING ((changes_within_tol(SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount,
prl.unit_price * prd.req_line_quantity)),
SUM(decode(prl.matching_basis, 'AMOUNT', nvl(pcr.new_amount, prd.req_line_amount),
nvl(pcr.new_quantity, prd.req_line_quantity) * nvl(pcr1.new_price, prl.unit_price))),
l_tolerances_tbl(tol_lineamt_ind).max_increment,
l_tolerances_tbl(tol_lineamt_ind).max_decrement,
l_tolerances_tbl(tol_lineamt_amt_ind).max_increment,
l_tolerances_tbl(tol_lineamt_amt_ind).max_decrement) = 'N')
OR (change_within_tol_percent(SUM(prd.req_line_quantity), SUM(nvl(pcr.new_quantity, prd.req_line_quantity)),
l_tolerances_tbl(tol_lineqty_ind).max_increment,
l_tolerances_tbl(tol_lineqty_ind).max_decrement) = 'N')));
SELECT changes_within_tol(SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount,
prl.unit_price * (prd.req_line_quantity))),
SUM(decode(prl.matching_basis, 'AMOUNT', nvl(pcr.new_amount, prd.req_line_amount),
nvl(pcr.new_quantity, prd.req_line_quantity) * nvl(pcr1.new_price, prl.unit_price))),
l_tolerances_tbl(tol_reqtotal_ind).max_increment,
l_tolerances_tbl(tol_reqtotal_ind).max_decrement,
l_tolerances_tbl(tol_reqtotal_amt_ind).max_increment,
l_tolerances_tbl(tol_reqtotal_amt_ind).max_decrement)
INTO l_return_val
FROM po_change_requests pcr,
po_change_requests pcr1,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prl.requisition_line_id = prd.requisition_line_id
AND pcr.change_request_group_id (+ ) = p_reqgrp_id
AND pcr.action_type (+ ) = 'MODIFICATION'
AND pcr.request_status (+ ) NOT IN ('ACCEPTED',
'REJECTED')
AND pcr.request_level (+ ) = 'DISTRIBUTION'
AND pcr.initiator (+ ) = 'REQUESTER'
AND pcr.document_distribution_id (+ ) = prd.distribution_id
-- AND pcr.document_line_id = pcr1.document_line_id
AND pcr1.change_request_group_id (+ ) = p_reqgrp_id
AND prl.requisition_header_id = p_reqheader_id
AND pcr1.document_line_id (+ ) = prl.requisition_line_id
AND pcr1.action_type (+ ) = 'MODIFICATION'
AND pcr1.request_status (+ ) NOT IN ('ACCEPTED',
'REJECTED')
AND pcr1.request_level (+ ) = 'LINE'
AND pcr1.initiator (+ ) = 'REQUESTER'
AND pcr1.new_price (+ ) IS NOT NULL ;
* Modifies: Updates po_change_request with the result of the
* changes_within_reqapproval_tol_values() API
* Returns:
* approval_required_flag:Y if user cannot auto approve
* :N if he/she can auto approve
*/
PROCEDURE set_approval_required_flag
(p_chreqgrp_id IN NUMBER,
x_appr_status OUT NOCOPY VARCHAR2,
p_source_type_code IN VARCHAR2 DEFAULT NULL)
IS
l_skip_std_logic VARCHAR2(1) := 'N';
SELECT org_id,
requisition_header_id
INTO l_org_id,
l_reqheader_id
FROM po_requisition_headers prh,
po_change_requests pcr
WHERE pcr.change_request_group_id = p_chreqgrp_id
AND pcr.document_header_id = prh.requisition_header_id
AND ROWNUM = 1;
UPDATE po_change_requests
SET approval_required_flag = x_appr_status
WHERE change_request_group_id = p_chreqgrp_id;