[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct contracts.po_header_id,
contracts.amount_limit ,
NVL(contracts.rate, 1), -- Bug# 4685260
contracts.currency_code --Bug# 4685260
FROM po_lines_gt pol, --
po_headers_all contracts -- --
WHERE contracts.po_header_id = pol.contract_id
--AND NVL(contracts.global_agreement_flag, 'N') = 'N' --
AND pol.po_header_id = X_po_header_id
AND contracts.amount_limit IS NOT NULL; -- bug3673292
/* Main loop to get all po_lines that you're trying to insert and then
go get the total po amounts that you've created to see if it's okay to
insert this po
*/
X_progress := 10;
blank. If it's null then the tally in the select 1 check will
always fail even though it should pass
*/
/* Bug# 2362213: kagarwal
** Desc: When getting the amount on Std PO referencing the
** contract, consider the rate on the Std PO and not that in the Contract.
*/
--
--1) Need an NVL since sum can return NULL.
--2) SELECT list cannot include both the group function SUM and
-- an individual column expression.
-- bug3673292
-- Removed table poh from the FROM clause.
--Bug# 4685260 Start
-- Checking if there are lines referring this contract PO
-- with currency different to the Contract. We will do the
-- conversion to the base currency only if there is atleast one
-- line which are referring this contract with a different currency
-- that the Contract currency. If all line referring this contrat
-- are in the same currecny as this contract we will compare the
-- amount directly without considering the rate.
--- Bug8422577 Changed all the views to _all tables to target documents
--- in other operating units also
x_diff_curr:='N';
SELECT 'Y'
INTO x_diff_curr
from dual
where exists (
SELECT 'Exists'
FROM po_lines_all pol,
po_line_locations_all pll,
po_headers_all poh2
WHERE pol.contract_id = X_contract_id --
AND pol.po_line_id = pll.po_line_id
AND poh2.po_header_id = pol.po_header_id
AND (( poh2.authorization_status = 'APPROVED')
or (poh2.authorization_status = 'IN PROCESS'))
AND pol.po_header_id <> X_po_header_id
AND poh2.currency_code <> x_currency_code
UNION ALL
SELECT 'Exists'
FROM po_lines_gt pol, --
po_line_locations_gt pll, --
po_headers_gt potoapp --
WHERE pol.contract_id = X_contract_id --
AND pol.po_line_id = pll.po_line_id
AND pol.po_header_id = potoapp.po_header_id
AND potoapp.po_header_id = X_po_header_id
AND potoapp.currency_code <> x_currency_code
);
SELECT ( nvl( -- --
sum ( decode ( PLL.quantity
, NULL , PLL.amount - nvl(PLL.amount_cancelled,0)
, ( ( PLL.quantity
- nvl(PLL.quantity_cancelled,0) )
* PLL.price_override )
)
* decode(nvl(x_diff_curr,'N'),
'Y', nvl(POH2.rate, 1),1) --Bug4685260
),
0
)
)
INTO X_purchased_amount
FROM po_lines_all pol,
po_line_locations_all pll,
po_headers_all poh2
WHERE pol.contract_id = X_contract_id --
AND pol.po_line_id = pll.po_line_id
AND poh2.po_header_id = pol.po_header_id
AND (( poh2.authorization_status = 'APPROVED')
or (poh2.authorization_status = 'IN PROCESS'))
AND pol.po_header_id <> X_po_header_id
AND pll.shipment_type = 'STANDARD'; --14795699
SELECT ( --
sum ( decode ( PLL.quantity
, NULL , PLL.amount - nvl(PLL.amount_cancelled,0)
, ( ( PLL.quantity
- nvl(PLL.quantity_cancelled,0) )
* PLL.price_override )
)
* decode(nvl(x_diff_curr,'N'),
'Y', nvl(POTOAPP.rate, 1),1) --Bug4685260
)
)
INTO X_current_amount
FROM po_lines_gt pol, --
po_line_locations_gt pll, --
po_headers_gt potoapp --
WHERE pol.contract_id = X_contract_id --
AND pol.po_line_id = pll.po_line_id
AND pol.po_header_id = potoapp.po_header_id
AND potoapp.po_header_id = X_po_header_id
AND pll.shipment_type = 'STANDARD'; --14795699