[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select organization_name,
set_of_books_id
into org_name,
sob_id
FROM org_organization_definitions
WHERE organization_id = org_id ;
SELECT fsp.inventory_organization_id,
fsp.set_of_books_id,
sob.name
INTO org_id,
sob_id,
org_name
FROM financials_system_parameters fsp,
gl_sets_of_books sob
WHERE fsp.set_of_books_id = sob.set_of_books_id;
SELECT sob.currency_code,
sob.chart_of_accounts_id,
nvl(fsp.purch_encumbrance_flag,'N'),
nvl(fsp.req_encumbrance_flag,'N'),
sob.set_of_books_id,
fsp.ship_to_location_id,
fsp.bill_to_location_id,
fsp.fob_lookup_code,
fsp.freight_terms_lookup_code,
aps.terms_id, -- bug5701539
psp.default_rate_type,
--togeorge 07/03/2001
--Bug# 1839659
--We are no more using this flag from psp.
--psp.taxable_flag,
null,
--
psp.receiving_flag,
nvl(psp.enforce_buyer_name_flag, 'N'),
nvl(psp.enforce_buyer_authority_flag,'N'),
psp.line_type_id,
psp.manual_po_num_type,
psp.user_defined_po_num_code,
psp.price_type_lookup_code,
psp.invoice_close_tolerance,
psp.receive_close_tolerance,
psp.security_position_structure_id,
psp.expense_accrual_code,
fsp.inventory_organization_id,
fsp.revision_sort_ordering,
psp.min_release_amount,
nvl(psp.notify_if_blanket_flag,'N'),
nvl(sob.enable_budgetary_control_flag,'N'),
psp.user_defined_req_num_code,
nvl(psp.rfq_required_flag,'N'),
psp.manual_req_num_type,
psp.enforce_full_lot_quantities,
psp.disposition_warning_flag,
nvl(fsp.reserve_at_completion_flag,'N'),
psp.user_defined_receipt_num_code,
psp.manual_receipt_num_type,
fsp.use_positions_flag,
psp.default_quote_warning_delay,
psp.inspection_required_flag,
psp.user_defined_quote_num_code,
psp.manual_quote_num_type,
psp.user_defined_rfq_num_code,
psp.manual_rfq_num_type,
fsp.ship_via_lookup_code,
rcv.qty_rcv_tolerance,
psp.acceptance_required_flag
INTO x_currency_code ,
x_coa_id ,
x_po_encumberance_flag ,
x_req_encumberance_flag ,
x_sob_id ,
x_ship_to_location_id ,
x_bill_to_location_id ,
x_fob_lookup_code ,
x_freight_terms_lookup_code,
x_terms_id ,
x_default_rate_type ,
x_taxable_flag ,
x_receiving_flag ,
x_enforce_buyer_name_flag,
x_enforce_buyer_auth_flag,
x_line_type_id ,
x_manual_po_num_type ,
x_po_num_code ,
x_price_lookup_code ,
x_invoice_close_tolerance,
x_receive_close_tolerance,
x_security_structure_id,
x_expense_accrual_code,
x_inv_org_id ,
x_rev_sort_ordering,
x_min_rel_amount ,
x_notify_blanket_flag,
x_budgetary_control_flag,
x_user_defined_req_num_code,
x_rfq_required_flag,
x_manual_req_num_type,
x_enforce_full_lot_qty,
x_disposition_warning_flag,
x_reserve_at_completion_flag,
x_user_defined_rcpt_num_code,
x_manual_rcpt_num_type,
x_use_positions_flag,
x_default_quote_warning_delay,
x_inspection_required_flag,
x_user_defined_quote_num_code,
x_manual_quote_num_type,
x_user_defined_rfq_num_code,
x_manual_rfq_num_type,
x_ship_via_lookup_code,
x_qty_rcv_tolerance,
x_acceptance_required_flag
FROM financials_system_parameters fsp,
gl_sets_of_books sob,
po_system_parameters psp,
rcv_parameters rcv,
ap_product_setup aps
WHERE fsp.set_of_books_id = sob.set_of_books_id
AND rcv.organization_id (+) = fsp.inventory_organization_id;
SELECT mdsv.category_set_id,
mdsv.structure_id
INTO x_category_set_id,
x_structure_id
FROM mtl_default_sets_view mdsv
WHERE mdsv.functional_area_id = 2;
select application_id
into x_app_id
from fnd_application
where application_short_name = x_product_name ;
SELECT plc.displayed_field,
plc.description
INTO x_disp_value,
x_description
FROM po_lookup_codes plc
WHERE plc.lookup_code = x_lookup_code
AND plc.lookup_type = x_lookup_type
AND sysdate < nvl(plc.inactive_date,sysdate + 1);
SELECT plc.displayed_field,
plc.description
INTO x_disp_value,
x_description
FROM po_lookup_codes plc
WHERE plc.lookup_code = x_lookup_code
AND plc.lookup_type = x_lookup_type;
SELECT plc.displayed_field
INTO x_disp_value
FROM po_lookup_codes plc
WHERE plc.lookup_code = x_lookup_code
AND plc.lookup_type = x_lookup_type;
SELECT nvl(sum(round(
round(
(decode(PLL.quantity,
null,
(nvl(PLL.amount, 0) -
nvl(PLL.amount_cancelled, 0)),
((nvl(PLL.quantity, 0) -
nvl(PLL.quantity_cancelled, 0)) *
nvl(PLL.price_override, 0))
)
* POD.rate
)
, X_precision) ,
X_base_precision)),0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = X_object_id
AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
AND PLL.line_location_id = POD.line_location_id;
SELECT nvl(sum(round(
round(
decode(PLL.quantity,
null,
(nvl(PLL.amount, 0) -
nvl(PLL.amount_cancelled, 0)),
((nvl(PLL.quantity, 0) -
nvl(PLL.quantity_cancelled, 0)) *
nvl(PLL.price_override, 0))
)
* POD.rate / X_min_unit
)
* X_min_unit , X_base_precision)),0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = X_object_id
AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
AND PLL.line_location_id = POD.line_location_id;
SELECT nvl(sum(round( round(
decode(PLL.quantity,
null,
(nvl(PLL.amount, 0) -
nvl(PLL.amount_cancelled, 0)),
(nvl(PLL.quantity, 0) -
nvl(PLL.quantity_cancelled, 0))
* nvl(PLL.price_override, 0)
)
* POD.rate , X_precision)
/ X_base_min_unit ) * X_base_min_unit) ,0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = X_object_id
AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
AND PLL.line_location_id = POD.line_location_id;
SELECT nvl(sum(round( round(
decode(PLL.quantity,
null,
(nvl(PLL.amount, 0) -
nvl(PLL.amount_cancelled, 0)),
(nvl(PLL.quantity, 0) -
nvl(PLL.quantity_cancelled, 0))
* nvl(PLL.price_override, 0)
)
* POD.rate /
X_min_unit) * X_min_unit / X_base_min_unit)
* X_base_min_unit) , 0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = X_object_id
AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
AND PLL.line_location_id = POD.line_location_id;
SELECT c.minimum_accountable_unit,
c.precision
INTO x_min_unit,
x_precision
FROM FND_CURRENCIES C,
PO_HEADERS_ALL PH
WHERE PH.po_header_id = x_object_id
AND C.currency_code = PH.CURRENCY_CODE;
select sum(round(
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
,x_precision))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = x_object_id
AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET');
select sum(round(
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled, 0)),
(pll.quantity - nvl(pll.quantity_cancelled, 0))
* nvl(pll.price_override,0)
)
/ x_min_unit)
* x_min_unit)
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = x_object_id
AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET');
SELECT nvl(sum(round( round((nvl(POD.quantity_ordered, 0) -
nvl(POD.quantity_cancelled, 0)) *
nvl(PLL.price_override, 0) * POD.rate, X_precision) ,
X_base_precision)),0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = X_object_id
-- Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.shipment_type = 'SCHEDULED'
-- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.line_location_id = POD.line_location_id;
SELECT nvl(sum(round( round((nvl(POD.quantity_ordered, 0) -
nvl(POD.quantity_cancelled, 0)) *
nvl(PLL.price_override, 0) * POD.rate /
X_min_unit) * X_min_unit , X_base_precision)),0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = X_object_id
-- Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.shipment_type = 'SCHEDULED'
-- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.line_location_id = POD.line_location_id;
SELECT nvl(sum(round( round((nvl(POD.quantity_ordered, 0) -
nvl(POD.quantity_cancelled, 0)) *
nvl(PLL.price_override, 0) * POD.rate , X_precision)
/ X_base_min_unit ) * X_base_min_unit) ,0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = X_object_id
-- Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.shipment_type = 'SCHEDULED'
-- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.line_location_id = POD.line_location_id;
SELECT nvl(sum(round( round((nvl(POD.quantity_ordered, 0) -
nvl(POD.quantity_cancelled, 0)) *
nvl(PLL.price_override, 0) * POD.rate /
X_min_unit) * X_min_unit / X_base_min_unit)
* X_base_min_unit) , 0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = X_object_id
-- Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.shipment_type = 'SCHEDULED'
-- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.line_location_id = POD.line_location_id;
SELECT c.minimum_accountable_unit,
c.precision
INTO x_min_unit,
x_precision
FROM FND_CURRENCIES C,
PO_HEADERS_ALL PH
WHERE PH.po_header_id = x_object_id
AND C.currency_code = PH.CURRENCY_CODE;
select sum(round((pll.quantity - nvl(pll.quantity_cancelled,0))*
nvl(pll.price_override,0),x_precision))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = x_object_id
-- Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.shipment_type = 'SCHEDULED';
select sum(round((pll.quantity -
nvl(pll.quantity_cancelled,0)) *
nvl(pll.price_override,0)/x_min_unit)*
x_min_unit)
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_header_id = x_object_id
-- Bugs 482497 and 602664, lpo, 12/22/97
AND PLL.shipment_type = 'SCHEDULED';
select sum(round(
decode(quantity,
null,
nvl(amount, 0),
(
(nvl(quantity,0) - nvl(quantity_cancelled,0))* nvl(unit_price,0)
)
)
, x_base_precision))
INTO x_result_fld
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_header_id = x_object_id
AND nvl(modified_by_agent_flag, 'N') = 'N';
select
round(sum((nvl(quantity,0) * nvl(unit_price,0)/x_base_min_unit)*
x_base_min_unit))
*/
--
-- For the new Services lines, quantity will be null.
-- Hence, added a decode statement to use amount directly
-- in the total amount calculation when quantity is null.
--< Bug 3549096 > Use _ALL tables instead of org-striped views.
--
select sum(round(
decode(quantity,
null,
nvl(amount, 0),
(
(nvl(quantity,0) - nvl(quantity_cancelled,0))* nvl(unit_price,0)
)
)
/x_base_min_unit)*
x_base_min_unit)
INTO x_result_fld
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_header_id = x_object_id
AND nvl(modified_by_agent_flag, 'N') = 'N';
select
round(sum((nvl(quantity,0) * nvl(unit_price,0))), x_base_precision)
*/
--
-- For the new Services lines, quantity will be null.
-- Hence, added a decode statement to use amount directly
-- in the total amount calculation when quantity is null.
--< Bug 3549096 > Use _ALL tables instead of org-striped views.
--
select sum(round(
decode(quantity,
null,
nvl(amount, 0),
(
(nvl(quantity,0) - nvl(quantity_cancelled,0))* nvl(unit_price,0)
)
)
, x_base_precision))
INTO x_result_fld
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_line_id = x_object_id
AND nvl(modified_by_agent_flag, 'N') = 'N';
select round(sum((
decode(quantity,
null,
nvl(amount, 0),
(
(nvl(quantity,0) - nvl(quantity_cancelled,0))* nvl(unit_price,0)
)
)
/x_base_min_unit)*
x_base_min_unit))
INTO x_result_fld
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_line_id = x_object_id
AND nvl(modified_by_agent_flag, 'N') = 'N';
SELECT
sum( decode
( x_base_min_unit, NULL,
decode(quantity, NULL,
round( nvl(PORD.req_line_amount, 0),
x_base_precision),
round( nvl(PORD.req_line_quantity, 0) *
nvl(PORL.unit_price, 0),
x_base_precision)
),
decode(quantity, NULL,
round((nvl(PORD.req_line_amount, 0) /
x_base_min_unit) *
x_base_min_unit),
round((nvl(PORD.req_line_quantity, 0) *
nvl(PORL.unit_price, 0) /
x_base_min_unit) *
x_base_min_unit)
)))
INTO x_result_fld
FROM PO_REQ_DISTRIBUTIONS_ALL PORD,
PO_REQUISITION_LINES_ALL PORL
WHERE PORD.distribution_id = x_object_id
AND PORD.requisition_line_id = PORL.requisition_line_id
AND nvl(PORL.modified_by_agent_flag, 'N') = 'N'; -- Fp of bug 12584086;
SELECT c.minimum_accountable_unit,
c.precision
INTO x_min_unit,
x_precision
FROM FND_CURRENCIES C,
PO_HEADERS_ALL PH
WHERE PH.po_header_id = x_object_id
AND C.currency_code = PH.CURRENCY_CODE;
/* 716188 - SVAIDYAN : Changed the sql stmt to select only Standard and Planned
POs that reference this contract and also to convert the amount into the
Contract's currency. This is achieved by converting the PO amt first to the
functional currency and then changing this to the Contract currency */
/* 716188 - Added an outer join on PO_DISTRIBUTIONS */
/* 866358 - BPESCHAN: Changed the sql stmt to select quantity_ordered and
quantity_cancelled from PO_DISTRIBUTIONS instead of PO_LINE_LOCATIONS.
This fix prevents incorrect calculation for amount release when more then
one distribution exists. */
/* 958792 kbenjami 8/25/99. Proprogated fix from R11.
849493 - SVAIDYAN: Do a sum(round()) instead of round(sum()) since what
we pass to GL is the round of individual dist. amounts
and the sum of these rounded values is what should be
displayed as the header total.
*/
/*Bug3760487:Purchase Order form was displaying incorrect released
amount for foreign currency contract when the PO currency is same
as the contract currency and the rates were different.Added the decode
to perform the currency conversion only when the currency code of
PO and contract are different.
Also removed the join to FND_CURRENCIES
*/
/*Bug 12723347
1)For execution documents with same currency as the Contract, get
quantity/amount from po_line_locations_all;
/* SELECT nvl ( sum (decode ( PH.currency_code, PH1.currency_code,
round (
decode ( PLL.quantity,
null,
( nvl ( PLL.amount,0) -
nvl(PLL.amount_cancelled, 0) ),
( nvl ( PLL.quantity,0) -
nvl ( PLL.quantity_cancelled,0 ) )
* nvl(pll.price_override,0) ) ,x_precision ),
round (
decode ( PLL.quantity,
null,
( nvl ( PLL.amount, 0) -
nvl ( PLL.amount_cancelled, 0 ) ),
( nvl ( PLL.quantity,0) -
nvl ( PLL.quantity_cancelled,0 ) )
* nvl(pll.price_override,0) ) * nvl(POD.rate,nvl(PH1.rate,1)) / nvl (PH.rate,1),x_precision)
) ) , 0 )
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL POD,
PO_LINES_ALL PL,
PO_HEADERS_ALL PH,
PO_HEADERS_ALL PH1
WHERE PH.po_header_id = x_object_id
AND PH.po_header_id = PL.contract_id
AND PL.po_line_id = PLL.po_line_id
AND PLL.shipment_type IN ('STANDARD','PLANNED')
AND POD.line_location_id(+) = PLL.line_location_id
AND PH1.po_header_id = PL.po_header_id;*/
SELECT nvl ( sum (round (decode ( PLL.quantity,null,
( nvl ( PLL.amount,0) -nvl(PLL.amount_cancelled, 0) ),
( nvl ( PLL.quantity,0) - nvl ( PLL.quantity_cancelled,0 ) )
* nvl(pll.price_override,0) ) , x_precision)), 0)
INTO l_rel_amt_same_curr
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_LINES_ALL PL,
PO_HEADERS_ALL PH,
PO_HEADERS_ALL PH1
WHERE PH.po_header_id = x_object_id
AND PH.po_header_id = PL.contract_id
AND PL.po_line_id = PLL.po_line_id
AND PLL.shipment_type IN ('STANDARD','PLANNED')
AND PH1.po_header_id = PL.po_header_id
and PH.currency_code = PH1.currency_code;
SELECT nvl (sum(round(decode(POD.quantity_ordered, null,
(nvl(POD.amount_ordered, 0) - nvl(POD.amount_cancelled, 0)),
(nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0))
* nvl(pll.price_override,0) * nvl(POD.rate, nvl(PH1.rate,1))
/nvl(PH.rate,1)), x_precision)
),0)
INTO l_rel_amt_diff_curr
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL POD,
PO_LINES_ALL PL,
PO_HEADERS_ALL PH,
PO_HEADERS_ALL PH1
WHERE PH.po_header_id = x_object_id
AND PH.po_header_id = PL.contract_id
AND PL.po_line_id = PLL.po_line_id
AND PLL.shipment_type IN ('STANDARD','PLANNED')
AND POD.line_location_id(+) = PLL.line_location_id
AND PH1.po_header_id = PL.po_header_id
and PH.currency_code <> PH1.currency_code;
/* SELECT
nvl(sum(decode(PH.currency_code, PH1.currency_code,
round(
decode(PLL.quantity, --Bug# 5238463
null,
(nvl(PLL.amount, 0) -
nvl(PLL.amount_cancelled, 0)),
((nvl(PLL.quantity,0) -
nvl(PLL.quantity_cancelled,0))
* nvl(pll.price_override,0)))/x_min_unit),
round(
decode(PLL.quantity,
null,
(nvl(PLL.amount, 0) -
nvl(PLL.amount_cancelled, 0)),
((nvl(PLL.quantity,0) -
nvl(PLL.quantity_cancelled,0))
* nvl(pll.price_override,0)))
* nvl(POD.rate, nvl(PH1.rate,1))/nvl(PH.rate,1)/x_min_unit))
* x_min_unit),0)
INTO x_result_fld
FROM PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL PLL,
PO_LINES_ALL PL,
PO_HEADERS_ALL PH,
PO_HEADERS_ALL PH1
--,FND_CURRENCIES C
WHERE PH.po_header_id = x_object_id
AND PH.po_header_id = PL.contract_id --
--AND PH.currency_code = C.currency_code
AND PL.po_line_id = PLL.po_line_id
AND PLL.shipment_type in ('STANDARD','PLANNED')
AND POD.line_location_id(+) = PLL.line_location_id
AND PH1.po_header_id = PL.po_header_id;*/
SELECT nvl ( sum (round (decode ( PLL.quantity,null,
( nvl ( PLL.amount,0) -nvl(PLL.amount_cancelled, 0) ),
( nvl ( PLL.quantity,0) - nvl ( PLL.quantity_cancelled,0 ) )
* nvl(pll.price_override,0) )/x_min_unit) * x_min_unit), 0)
INTO l_rel_amt_same_curr
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_LINES_ALL PL,
PO_HEADERS_ALL PH,
PO_HEADERS_ALL PH1
WHERE PH.po_header_id = x_object_id
AND PH.po_header_id = PL.contract_id
AND PL.po_line_id = PLL.po_line_id
AND PLL.shipment_type IN ('STANDARD','PLANNED')
AND PH1.po_header_id = PL.po_header_id
and PH.currency_code = PH1.currency_code;
SELECT nvl (sum(round(decode(POD.quantity_ordered, null,
(nvl(POD.amount_ordered, 0) - nvl(POD.amount_cancelled, 0)),
(nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0))
* nvl(pll.price_override,0) * nvl(POD.rate, nvl(PH1.rate,1))
/nvl(PH.rate,1))/x_min_unit)
* x_min_unit),0)
INTO l_rel_amt_diff_curr
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL POD,
PO_LINES_ALL PL,
PO_HEADERS_ALL PH,
PO_HEADERS_ALL PH1
WHERE PH.po_header_id = x_object_id
AND PH.po_header_id = PL.contract_id
AND PL.po_line_id = PLL.po_line_id
AND PLL.shipment_type IN ('STANDARD','PLANNED')
AND POD.line_location_id(+) = PLL.line_location_id
AND PH1.po_header_id = PL.po_header_id
and PH.currency_code <> PH1.currency_code;
SELECT GSB.currency_code,
POH.currency_code
INTO x_base_currency,
x_po_currency
FROM PO_HEADERS_ALL POH,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
GL_SETS_OF_BOOKS GSB,
PO_RELEASES_ALL POR
WHERE POR.po_release_id = x_object_id
AND POH.po_header_id = POR.po_header_id
AND NVL(POR.org_id,-99) = NVL(FSP.org_id,-99) --< Bug 3549096 >
AND FSP.set_of_books_id = GSB.set_of_books_id;
select nvl(sum(round(round(
decode(POD.quantity_ordered,
null,
nvl(POD.amount_ordered, 0),
(nvl(POD.quantity_ordered,0) *
nvl(PLL.price_override,0))
)
* POD.rate
,x_precision),x_base_precision)),0)
INTO x_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_release_id = x_object_id
AND PLL.line_location_id = POD.line_location_id
AND PLL.shipment_type in ('SCHEDULED','BLANKET');
SELECT nvl(sum(round( round(
decode(POD.quantity_ordered,
null,
nvl(POD.amount_ordered, 0),
(nvl(POD.quantity_ordered, 0) *
nvl(PLL.price_override, 0))
)
* POD.rate /
X_min_unit) * X_min_unit , X_base_precision)),0)
INTO x_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_release_id = x_object_id
AND PLL.line_location_id = POD.line_location_id
AND PLL.shipment_type in ('SCHEDULED','BLANKET');
SELECT nvl(sum(round( round(
decode(POD.quantity_ordered,
null,
nvl(POD.amount_ordered, 0),
(nvl(POD.quantity_ordered, 0) *
nvl(PLL.price_override, 0))
)
* POD.rate
, X_precision)
/ X_base_min_unit ) * X_base_min_unit) ,0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_release_id = x_object_id
AND PLL.line_location_id = POD.line_location_id
AND PLL.shipment_type in ('SCHEDULED','BLANKET');
SELECT nvl(sum(round( round(
decode(POD.quantity_ordered,
null,
nvl(POD.amount_ordered, 0),
(nvl(POD.quantity_ordered, 0) *
nvl(PLL.price_override, 0))
)
* POD.rate /
X_min_unit) * X_min_unit / X_base_min_unit)
* X_base_min_unit) , 0)
INTO X_result_fld
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_release_id = x_object_id
AND PLL.line_location_id = POD.line_location_id
AND PLL.shipment_type in ('SCHEDULED','BLANKET');
SELECT c.minimum_accountable_unit,
c.precision
INTO x_min_unit,
x_precision
FROM FND_CURRENCIES C,
PO_RELEASES_ALL POR,
PO_HEADERS_ALL PH
WHERE POR.po_release_id = x_object_id
AND PH.po_header_id = POR.PO_HEADER_ID
AND C.currency_code = PH.CURRENCY_CODE;
select sum(round(
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
((pll.quantity - nvl(pll.quantity_cancelled,0)) *
nvl(pll.price_override,0))
)
,x_precision))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_release_id = x_object_id
AND PLL.shipment_type in ( 'SCHEDULED','BLANKET');
select sum(round(
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
((pll.quantity - nvl(pll.quantity_cancelled,0)) *
nvl(pll.price_override,0))
)
/x_min_unit) *
x_min_unit)
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_release_id = x_object_id
AND PLL.shipment_type in ( 'SCHEDULED','BLANKET');
SELECT sum(c.minimum_accountable_unit),
sum(c.precision)
INTO x_min_unit,
x_precision
FROM FND_CURRENCIES C,
PO_HEADERS_ALL PH,
PO_LINES_ALL POL
WHERE POL.po_line_id = x_object_id
AND PH.po_header_id = POL.po_header_id
AND C.currency_code = PH.CURRENCY_CODE;
select round(sum((pll.quantity - nvl(pll.quantity_cancelled,0))*
nvl(pll.price_override,0)),x_precision)
*/
/* Bug No. 1849112 In the previous fix of 143811 by mistake x_precision
was not used.
*/
--
-- For the new Services lines, quantity will be null.
-- Hence, added a decode statement to use amount directly
-- in the total amount calculation when quantity is null.
--< Bug 3549096 > Use _ALL tables instead of org-striped views.
select sum(round((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled, 0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
),x_precision))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = x_object_id
AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
select sum(round((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
/ x_min_unit) * x_min_unit))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = x_object_id
AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
SELECT c.minimum_accountable_unit,
c.precision
INTO x_min_unit,
x_precision
FROM FND_CURRENCIES C,
PO_HEADERS_ALL PH,
PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.line_location_id = x_object_id
AND PH.po_header_id = PLL.po_header_id
AND C.currency_code = PH.CURRENCY_CODE;
select round(sum((pll.quantity - nvl(pll.quantity_cancelled,0))*
nvl(pll.price_override,0)),x_precision)
*/
--
-- For the new Services lines, quantity will be null.
-- Hence, added a decode statement to use amount directly
-- in the total amount calculation when quantity is null.
--< Bug 3549096 > Use _ALL tables instead of org-striped views.
select sum(round((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
),x_precision))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.line_location_id = x_object_id;
select round(sum((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
/x_min_unit) * x_min_unit))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.line_location_id = x_object_id;
SELECT nvl(sum(round( ( (round( ( ( decode(POD.quantity_ordered, NULL,
(nvl(POD.amount_ordered,0) -
nvl(POD.amount_cancelled,0)
),
( (nvl(POD.quantity_ordered,0) -
nvl(POD.quantity_cancelled,0)
)*
nvl(PLL.price_override, 0)
)
) *
decode(p_base_cur_result,'Y',nvl(POD.rate,1),1)
)/
nvl(l_min_unit,1)
),decode(l_min_unit,null,l_precision,0)
)*
nvl(l_min_unit,1)
)/
nvl(l_base_min_unit,1)
)
,decode(l_base_min_unit,null,l_base_precision,0)
)*
nvl(l_base_min_unit,1)
), 0
)
INTO l_archive_total_amt
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_header_id = p_object_id
AND PLL.shipment_type in ('STANDARD')
AND PLL.line_location_id = POD.line_location_id
AND PLL.LATEST_EXTERNAL_FLAG = 'Y'
AND POD.LATEST_EXTERNAL_FLAG = 'Y';
SELECT nvl(sum(round( ( (round( ( ( decode(POD.quantity_ordered, NULL,
(nvl(POD.amount_ordered,0) -
nvl(POD.amount_cancelled,0)
),
( (nvl(POD.quantity_ordered,0) -
nvl(POD.quantity_cancelled,0)
)*
nvl(PLL.price_override, 0)
)
) *
decode(p_base_cur_result,'Y',nvl(POD.rate,1),1)
)/
nvl(l_min_unit,1)
),decode(l_min_unit,null,l_precision,0)
)*
nvl(l_min_unit,1)
)/
nvl(l_base_min_unit,1)
)
,decode(l_base_min_unit,null,l_base_precision,0)
)*
nvl(l_base_min_unit,1)
), 0
)
INTO l_archive_total_amt
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_header_id = p_object_id
AND PLL.shipment_type in ('STANDARD')
AND PLL.line_location_id = POD.line_location_id
AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
WHERE pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision)
AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
pdd1.revision_num <= p_doc_revision);
SELECT c.minimum_accountable_unit,
c.precision
INTO l_min_unit,
l_precision
FROM FND_CURRENCIES C,
PO_HEADERS_ALL PH,
PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.line_location_id = p_object_id
AND PH.po_header_id = PLL.po_header_id
AND C.currency_code = PH.CURRENCY_CODE;
SELECT sum(round((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
),l_precision))
INTO l_archive_total_amt
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.line_location_id = p_object_id AND
PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision) ;
select round(sum((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
/l_min_unit) * l_min_unit))
INTO l_archive_total_amt
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.line_location_id = p_object_id AND
PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision) ;
SELECT sum(c.minimum_accountable_unit),
sum(c.precision)
INTO l_min_unit,
l_precision
FROM FND_CURRENCIES C,
PO_HEADERS_ALL PH,
PO_LINES_ALL POL
WHERE POL.po_line_id = p_object_id
AND PH.po_header_id = POL.po_header_id
AND C.currency_code = PH.CURRENCY_CODE;
select sum(round((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled, 0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
),l_precision))
INTO l_archive_total_amt
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_line_id = p_object_id
AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision) ;
select sum(round((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
/ l_min_unit) * l_min_unit))
INTO l_archive_total_amt
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_line_id = p_object_id
AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision) ;
SELECT GSB.currency_code,
POH.currency_code
INTO l_base_currency,
l_po_currency
FROM PO_HEADERS_ALL POH,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
GL_SETS_OF_BOOKS GSB,
PO_RELEASES_ALL POR
WHERE POR.po_release_id = p_object_id
AND POH.po_header_id = POR.po_header_id
AND NVL(POR.org_id,-99) = NVL(FSP.org_id,-99) --< Bug 3549096 >
AND FSP.set_of_books_id = GSB.set_of_books_id;
select nvl(sum(round(round(
decode(POD.quantity_ordered,
null,
nvl(POD.amount_ordered, 0),
(nvl(POD.quantity_ordered,0) *
nvl(PLL.price_override,0))
)
* POD.rate
,l_precision),l_base_precision)),0)
INTO l_archive_total_amt
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_release_id = p_object_id
AND PLL.line_location_id = POD.line_location_id
AND PLL.shipment_type in ('SCHEDULED','BLANKET')
AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
WHERE pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision)
AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
pdd1.revision_num <= p_doc_revision);
SELECT nvl(sum(round( round(
decode(POD.quantity_ordered,
null,
nvl(POD.amount_ordered, 0),
(nvl(POD.quantity_ordered, 0) *
nvl(PLL.price_override, 0))
)
* POD.rate /
l_min_unit) * l_min_unit , l_base_precision)),0)
INTO l_archive_total_amt
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_release_id = p_object_id
AND PLL.line_location_id = POD.line_location_id
AND PLL.shipment_type in ('SCHEDULED','BLANKET')
AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
WHERE pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision)
AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
pdd1.revision_num <= p_doc_revision);
SELECT nvl(sum(round( round(
decode(POD.quantity_ordered,
null,
nvl(POD.amount_ordered, 0),
(nvl(POD.quantity_ordered, 0) *
nvl(PLL.price_override, 0))
)
* POD.rate
, l_precision)
/ l_base_min_unit ) * l_base_min_unit) ,0)
INTO l_archive_total_amt
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_release_id = p_object_id
AND PLL.line_location_id = POD.line_location_id
AND PLL.shipment_type in ('SCHEDULED','BLANKET')
AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
WHERE pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision)
AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
pdd1.revision_num <= p_doc_revision);
SELECT nvl(sum(round( round(
decode(POD.quantity_ordered,
null,
nvl(POD.amount_ordered, 0),
(nvl(POD.quantity_ordered, 0) *
nvl(PLL.price_override, 0))
)
* POD.rate /
l_min_unit) * l_min_unit / l_base_min_unit)
* l_base_min_unit) , 0)
INTO l_archive_total_amt
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_release_id = p_object_id
AND PLL.line_location_id = POD.line_location_id
AND PLL.shipment_type in ('SCHEDULED','BLANKET')
AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
WHERE pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision)
AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
pdd1.revision_num <= p_doc_revision);
SELECT c.minimum_accountable_unit,
c.precision
INTO l_min_unit,
l_precision
FROM FND_CURRENCIES C,
PO_RELEASES_ALL POR,
PO_HEADERS_ALL PH
WHERE POR.po_release_id = p_object_id
AND PH.po_header_id = POR.PO_HEADER_ID
AND C.currency_code = PH.CURRENCY_CODE;
select sum(round(
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
((pll.quantity - nvl(pll.quantity_cancelled,0)) *
nvl(pll.price_override,0))
)
,l_precision))
INTO l_archive_total_amt
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_release_id = p_object_id
AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
WHERE pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision);
select sum(round(
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
((pll.quantity - nvl(pll.quantity_cancelled,0)) *
nvl(pll.price_override,0))
)
/l_min_unit) *
l_min_unit)
INTO l_archive_total_amt
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_release_id = p_object_id
AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
WHERE pll1.line_location_id = pll.line_location_id AND
pll1.revision_num <= p_doc_revision);
SELECT sum(c.minimum_accountable_unit),
sum(c.precision)
INTO x_min_unit,
x_precision
FROM FND_CURRENCIES C,
PO_HEADERS_ALL PH,
PO_LINES_ALL POL
WHERE POL.po_line_id = p_line_id
AND PH.po_header_id = POL.po_header_id
AND C.currency_code = PH.CURRENCY_CODE;
select sum(round((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled, 0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
),x_precision))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = p_line_id
AND PLL.po_release_id = p_release_id
AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
select sum(round((
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
/ x_min_unit) * x_min_unit))
INTO x_result_fld
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = p_line_id
AND PLL.po_release_id = p_release_id
AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
SELECT rate
INTO l_rate
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT sum ( round ( ( round ( ( ( decode(poll.quantity, null,
(poll.amount -
poll.amount_cancelled),
(( poll.quantity
- poll.quantity_cancelled )
* nvl(poll.price_override,0))
)
)
* l_rate )
/ l_po_min_unit )
* l_po_min_unit )
/ l_base_min_unit )
* l_base_min_unit )
INTO x_total
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.from_header_id = p_po_header_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT sum ( round ( ( (decode(poll.quantity, null,
(poll.amount -
poll.amount_cancelled),
(( poll.quantity
- poll.quantity_cancelled )
* nvl(poll.price_override,0))
)
)
* l_rate )
/ l_base_min_unit )
* l_base_min_unit )
INTO x_total
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.from_header_id = p_po_header_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT sum ( round ( ( (decode(poll.quantity, null,
(poll.amount -
poll.amount_cancelled),
(( poll.quantity
- poll.quantity_cancelled )
* nvl(poll.price_override,0))
)
)
* l_rate )
/ l_po_min_unit )
* l_po_min_unit )
INTO x_total
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.from_header_id = p_po_header_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT sum ( (decode(poll.quantity, null,
(poll.amount -
poll.amount_cancelled),
(( poll.quantity
- poll.quantity_cancelled )
* nvl(poll.price_override,0))
)
)
* l_rate )
INTO x_total
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.from_header_id = p_po_header_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT sum ( round ( (decode(poll.quantity, null,
(poll.amount -
poll.amount_cancelled),
(( poll.quantity
- poll.quantity_cancelled )
* nvl(poll.price_override,0))
)
)
/ l_po_min_unit )
* l_po_min_unit )
INTO x_total
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.from_header_id = p_po_header_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT sum (decode(poll.quantity, null,
(poll.amount -
poll.amount_cancelled),
(( poll.quantity
- poll.quantity_cancelled )
* nvl(poll.price_override,0))
)
)
INTO x_total
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.from_header_id = p_po_header_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT sum ( round ( (decode(pol.quantity, null,
(pod.amount_ordered -
pod.amount_cancelled),
(( pod.quantity_ordered
- pod.quantity_cancelled )
* poll.price_override))
)
/ l_po_min_unit )
* l_po_min_unit ),
sum ( pod.quantity_ordered - pod.quantity_cancelled )
INTO x_amount_released,
x_quantity_released
FROM po_distributions_all pod,
po_line_locations_all poll,
po_lines_all pol
WHERE pod.line_location_id = poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.from_line_id = p_po_line_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT sum (decode(pol.quantity, null,
(pod.amount_ordered -
pod.amount_cancelled),
(( pod.quantity_ordered
- pod.quantity_cancelled )
* poll.price_override)
)
),
sum ( pod.quantity_ordered - pod.quantity_cancelled )
INTO x_amount_released,
x_quantity_released
FROM po_distributions_all pod,
po_line_locations_all poll,
po_lines_all pol
WHERE pod.line_location_id = poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.from_line_id = p_po_line_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT NVL(
SUM(
DECODE(l_base_currency, PH1.currency_code,
ROUND(
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)
/l_po_min_unit)*l_po_min_unit
/l_base_min_unit)*l_base_min_unit
,
ROUND(
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)
/l_po_min_unit)*l_po_min_unit
* NVL(POD.rate, NVL(PH1.rate,1))
/l_base_min_unit) * l_base_min_unit
)
)
,0)
INTO l_total
FROM PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL PH1
WHERE POL.contract_id = p_po_header_id
AND POL.po_line_id = POLL.po_line_id
AND POLL.shipment_type in ('STANDARD','PLANNED')
AND POD.line_location_id = POLL.line_location_id --bug 12572504
AND PH1.po_header_id = POL.po_header_id
AND POD.po_line_id = POLL.po_line_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT NVL(
SUM(
DECODE(l_base_currency, PH1.currency_code,
ROUND(
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)
,l_po_precision)
/l_base_min_unit)*l_base_min_unit
,
ROUND(
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
) * NVL(POD.rate, NVL(PH1.rate,1))
,l_po_precision)
/l_base_min_unit) * l_base_min_unit
)
)
,0)
INTO l_total
FROM PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL PH1
WHERE POL.contract_id = p_po_header_id
AND POL.po_line_id = POLL.po_line_id
AND POLL.shipment_type in ('STANDARD','PLANNED')
AND POD.line_location_id = POLL.line_location_id --bug 12572504
AND PH1.po_header_id = POL.po_header_id
AND POD.po_line_id = POLL.po_line_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT NVL(
SUM(
DECODE(l_base_currency, PH1.currency_code,
ROUND(
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)
/l_po_min_unit) * l_po_min_unit
, l_base_precision),
ROUND(
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)* NVL(POD.rate, NVL(PH1.rate,1)
)
/l_po_min_unit) * l_po_min_unit
, l_base_precision)
)
),0)
INTO l_total
FROM PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL PoL,
PO_HEADERS_ALL PH1
WHERE POL.contract_id = p_po_header_id
AND POL.po_line_id = POLL.po_line_id
AND POLL.shipment_type in ('STANDARD','PLANNED')
AND POD.line_location_id = POLL.line_location_id --bug 12572504
AND PH1.po_header_id = POL.po_header_id
AND POD.po_line_id = POLL.po_line_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT NVL(
SUM(
DECODE(l_base_currency, PH1.currency_code,
ROUND(
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)
,l_po_precision)
, l_base_precision),
ROUND(
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)* NVL(POD.rate, NVL(PH1.rate,1)
)
,l_po_precision)
, l_base_precision)
)
),0)
INTO l_total
FROM PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL PH1
WHERE POL.contract_id = p_po_header_id
AND POL.po_line_id = POLL.po_line_id
AND POLL.shipment_type in ('STANDARD','PLANNED')
AND POD.line_location_id = POLL.line_location_id --bug 12572504
AND PH1.po_header_id = POL.po_header_id
AND POD.po_line_id = POLL.po_line_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT /*+ use_nl(POLL,POD,PH1) index(POLL PO_LINE_LOCATIONS_N1) index(PH1 PO_HEADERS_U1) */ NVL(
SUM(
DECODE(PH.currency_code, PH1.currency_code,
ROUND (
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)
/l_po_min_unit)* l_po_min_unit
,
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)* NVL(POD.rate, NVL(PH1.rate,1))
/NVL(PH.rate,1)
/l_po_min_unit)* l_po_min_unit
)
)
,0)
INTO l_total
FROM PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL PH,
PO_HEADERS_ALL PH1
WHERE PH.po_header_id = p_po_header_id
AND PH.po_header_id = POL.contract_id
AND POL.po_line_id = POLL.po_line_id
AND POLL.shipment_type in ('STANDARD','PLANNED')
AND POD.line_location_id = POLL.line_location_id --bug 12572504
AND PH1.po_header_id = POL.po_header_id
AND POD.po_line_id = POLL.po_line_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT /*+ use_nl(POLL,POD,PH1) index(POLL PO_LINE_LOCATIONS_N1) index(PH1 PO_HEADERS_U1) */ NVL(
SUM(
DECODE(PH.currency_code, PH1.currency_code,
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)
, l_po_precision ) ,
ROUND(
DECODE(POLL.value_basis
,'FIXED PRICE',
NVL(POD.amount_ordered, 0) -
NVL(POD.amount_cancelled, 0)
, 'RATE',
NVL(POD.amount_ordered, 0)-
NVL(POD.amount_cancelled, 0)
, --Qty based
(NVL(POD.quantity_ordered, 0) -
NVL(POD.quantity_cancelled,0)
) * NVL(POLL.price_override, 0)
)
* NVL(POD.rate, NVL(PH1.rate,1))
/NVL(PH.rate,1)
, l_po_precision)
)
)
,0)
INTO l_total
FROM PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL PH,
PO_HEADERS_ALL PH1
WHERE PH.po_header_id = p_po_header_id
AND PH.po_header_id = POL.contract_id --
AND POL.po_line_id = POLL.po_line_id
AND POLL.shipment_type in ('STANDARD','PLANNED')
AND POD.line_location_id = POLL.line_location_id --bug 12572504
AND PH1.po_header_id = POL.po_header_id
AND POD.po_line_id = POLL.po_line_id
AND Nvl(poll.consigned_flag,'N') <> 'Y';
SELECT inactive_date
FROM po_lookup_codes
WHERE
lookup_type = p_lookup_rec.lookup_type and
lookup_code = p_lookup_rec.lookup_code;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT PRL.requisition_header_id
FROM PO_REQUISITION_LINES_ALL PRL
WHERE PRL.requisition_line_id = p_doc_level_id_tbl(i)
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_doc_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT PRL.requisition_header_id
FROM
PO_REQUISITION_LINES_ALL PRL
, PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRL.requisition_line_id = PRD.requisition_line_id
AND PRD.distribution_id = p_doc_level_id_tbl(i)
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_doc_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT POL.po_header_id
FROM PO_LINES_ALL POL
WHERE POL.po_line_id = p_doc_level_id_tbl(i)
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_doc_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT DECODE( p_doc_type
, g_doc_type_RELEASE, POLL.po_release_id
, POLL.po_header_id
)
FROM PO_LINE_LOCATIONS_ALL POLL
WHERE POLL.line_location_id = p_doc_level_id_tbl(i)
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_doc_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT DECODE( p_doc_type
, g_doc_type_RELEASE, POD.po_release_id
, POD.po_header_id
)
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.po_distribution_id = p_doc_level_id_tbl(i)
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_doc_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES ( l_id_key, p_doc_level_id_tbl(i) )
;
SELECT PRL.requisition_line_id
BULK COLLECT INTO x_line_id_tbl
FROM
PO_REQUISITION_LINES_ALL PRL
, PO_SESSION_GT IDS
WHERE PRL.requisition_header_id = IDS.num1
AND IDS.key = l_id_key
;
SELECT POL.po_line_id
BULK COLLECT INTO x_line_id_tbl
FROM
PO_LINES_MERGE_V POL -- replaced PO_LINES_ALL
, PO_SESSION_GT IDS
WHERE POL.po_header_id = IDS.num1
AND POL.draft_id = p_draft_id
AND IDS.key = l_id_key
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT POLL.po_line_id
FROM PO_LINE_LOCATIONS_MERGE_V POLL -- replaced PO_LINE_LOCATIONS_ALL
WHERE POLL.line_location_id = p_doc_level_id_tbl(i)
AND POLL.draft_id = p_draft_id
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_line_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT PRD.requisition_line_id
FROM PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRD.distribution_id = p_doc_level_id_tbl(i)
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_line_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT POD.po_line_id
FROM PO_DISTRIBUTIONS_MERGE_V POD -- replaced PO_DISTRIBUTIONS_ALL
WHERE POD.po_distribution_id = p_doc_level_id_tbl(i)
AND POD.draft_id = p_draft_id
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_line_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES ( l_doc_level_id_key, p_doc_level_id_tbl(i) )
;
SELECT POLL.line_location_id
BULK COLLECT INTO x_line_location_id_tbl
FROM
PO_LINE_LOCATIONS_ALL POLL
, PO_SESSION_GT IDS
WHERE POLL.po_release_id = IDS.num1
AND IDS.key = l_doc_level_id_key
;
SELECT POLL.line_location_id
BULK COLLECT INTO x_line_location_id_tbl
FROM
PO_LINE_LOCATIONS_MERGE_V POLL -- replaced PO_LINE_LOCATIONS_ALL
, PO_SESSION_GT IDS
WHERE POLL.po_header_id = IDS.num1
and POLL.draft_id = p_draft_id
AND POLL.shipment_type <> g_ship_type_SCHEDULED
AND POLL.shipment_type <> g_ship_type_BLANKET
-- don't pick up release shipments for POs/PAs
AND IDS.key = l_doc_level_id_key
;
SELECT POLL.line_location_id
BULK COLLECT INTO x_line_location_id_tbl
FROM
PO_LINE_LOCATIONS_MERGE_V POLL -- replaced PO_LINE_LOCATIONS_ALL
, PO_SESSION_GT IDS
WHERE POLL.po_line_id = IDS.num1
and POLL.draft_id = p_draft_id
AND POLL.shipment_type <> g_ship_type_SCHEDULED
AND POLL.shipment_type <> g_ship_type_BLANKET
-- don't pick up release shipments for POs/PAs
AND IDS.key = l_doc_level_id_key
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES
( l_doc_level_id_key
, p_doc_level_id_tbl(i)
)
RETURNING ROWIDTOCHAR(rowid)
BULK COLLECT INTO l_rowid_char_tbl
;
UPDATE PO_SESSION_GT
SET num2 =
(
SELECT POD.line_location_id
FROM PO_DISTRIBUTIONS_MERGE_V POD -- replaced PO_DISTRIBUTIONS_ALL
WHERE POD.po_distribution_id = p_doc_level_id_tbl(i)
AND POD.draft_id = p_draft_id
)
WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
RETURNING num2
BULK COLLECT INTO x_line_location_id_tbl
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES ( l_doc_level_id_key, p_doc_level_id_tbl(i) )
;
SELECT PRD.distribution_id
BULK COLLECT INTO x_distribution_id_tbl
FROM
PO_REQUISITION_LINES_ALL PRL
, PO_REQ_DISTRIBUTIONS_ALL PRD
, PO_SESSION_GT IDS
WHERE PRL.requisition_header_id = IDS.num1
AND PRD.requisition_line_id = PRL.requisition_line_id
AND IDS.key = l_doc_level_id_key
;
SELECT PRD.distribution_id
BULK COLLECT INTO x_distribution_id_tbl
FROM
PO_REQ_DISTRIBUTIONS_ALL PRD
, PO_SESSION_GT IDS
WHERE PRD.requisition_line_id = IDS.num1
AND IDS.key = l_doc_level_id_key
;
SELECT POD.po_distribution_id
BULK COLLECT INTO x_distribution_id_tbl
FROM
PO_DISTRIBUTIONS_ALL POD
, PO_SESSION_GT IDS
WHERE POD.po_release_id = IDS.num1
AND IDS.key = l_doc_level_id_key
;
SELECT POD.po_distribution_id
BULK COLLECT INTO x_distribution_id_tbl
FROM
PO_DISTRIBUTIONS_MERGE_V POD -- replaced PO_DISTRIBUTIONS_ALL
, PO_SESSION_GT IDS
WHERE POD.po_header_id = IDS.num1
AND POD.draft_id = p_draft_id
AND POD.po_release_id IS NULL
-- Don't pick up Release distributions when acting on a PPO/BPA/GA.
-- Not using distribution_type due to dependency issues.
AND IDS.key = l_doc_level_id_key
;
SELECT POD.po_distribution_id
BULK COLLECT INTO x_distribution_id_tbl
FROM
PO_DISTRIBUTIONS_MERGE_V POD -- replaced PO_DISTRIBUTIONS_ALL
, PO_SESSION_GT IDS
WHERE POD.po_line_id = IDS.num1
AND POD.draft_id = p_draft_id
AND POD.po_release_id IS NULL
-- Don't pick up SR distributions when acting on a PPO.
-- Not using distribution_type due to dependency issues.
AND IDS.key = l_doc_level_id_key
;
SELECT POD.po_distribution_id
BULK COLLECT INTO x_distribution_id_tbl
FROM
PO_DISTRIBUTIONS_MERGE_V POD -- replaced PO_DISTRIBUTIONS_ALL
, PO_SESSION_GT IDS
WHERE POD.line_location_id = IDS.num1
AND POD.draft_id = p_draft_id
AND IDS.key = l_doc_level_id_key
;
INSERT INTO PO_SESSION_GT ( key, num1 )
VALUES ( l_doc_level_id_key, p_doc_level_id_tbl(i) )
;
SELECT POD.po_distribution_id
, POD.revision_num
BULK COLLECT INTO
x_distribution_id_tbl
, x_distribution_rev_num_tbl
FROM
PO_DISTRIBUTIONS_ARCHIVE_ALL POD
, PO_SESSION_GT IDS
WHERE POD.po_release_id = IDS.num1
AND IDS.key = l_doc_level_id_key
AND ( (l_revision_specified_flag = 'Y'
AND POD.latest_external_flag = 'Y')
OR
(l_revision_specified_flag = 'N'
AND POD.revision_num =
(SELECT max(POD2.revision_num)
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD2
WHERE POD2.po_distribution_id = POD.po_distribution_id
AND POD2.revision_num <= p_doc_revision_num) )
)
;
SELECT POD.po_distribution_id
, POD.revision_num
BULK COLLECT INTO
x_distribution_id_tbl
, x_distribution_rev_num_tbl
FROM
PO_DISTRIBUTIONS_ARCHIVE_ALL POD
, PO_SESSION_GT IDS
WHERE POD.po_header_id = IDS.num1
AND POD.po_release_id IS NULL
-- Don't pick up Release distributions when acting on a PPO/BPA/GA.
-- Not using distribution_type due to dependency issues.
AND IDS.key = l_doc_level_id_key
AND ( (l_revision_specified_flag = 'Y'
AND POD.latest_external_flag = 'Y')
OR
(l_revision_specified_flag = 'N'
AND POD.revision_num =
(SELECT max(POD2.revision_num)
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD2
WHERE POD2.po_distribution_id = POD.po_distribution_id
AND POD2.revision_num <= p_doc_revision_num) )
)
;
SELECT POD.po_distribution_id
, POD.revision_num
BULK COLLECT INTO
x_distribution_id_tbl
, x_distribution_rev_num_tbl
FROM
PO_DISTRIBUTIONS_ARCHIVE_ALL POD
, PO_SESSION_GT IDS
WHERE POD.po_line_id = IDS.num1
AND POD.po_release_id IS NULL
-- Don't pick up SR distributions when acting on a PPO.
-- Not using distribution_type due to dependency issues.
AND IDS.key = l_doc_level_id_key
AND ( (l_revision_specified_flag = 'Y'
AND POD.latest_external_flag = 'Y')
OR
(l_revision_specified_flag = 'N'
AND POD.revision_num =
(SELECT max(POD2.revision_num)
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD2
WHERE POD2.po_distribution_id = POD.po_distribution_id
AND POD2.revision_num <= p_doc_revision_num) )
)
;
SELECT POD.po_distribution_id
, POD.revision_num
BULK COLLECT INTO
x_distribution_id_tbl
, x_distribution_rev_num_tbl
FROM
PO_DISTRIBUTIONS_ARCHIVE_ALL POD
, PO_SESSION_GT IDS
WHERE POD.line_location_id = IDS.num1
AND IDS.key = l_doc_level_id_key
AND ( (l_revision_specified_flag = 'Y'
AND POD.latest_external_flag = 'Y')
OR
(l_revision_specified_flag = 'N'
AND POD.revision_num =
(SELECT max(POD2.revision_num)
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD2
WHERE POD2.po_distribution_id = POD.po_distribution_id
AND POD2.revision_num <= p_doc_revision_num) )
)
;
SELECT POD.po_distribution_id
, POD.revision_num
BULK COLLECT INTO
x_distribution_id_tbl
, x_distribution_rev_num_tbl
FROM
PO_DISTRIBUTIONS_ARCHIVE_ALL POD
, PO_SESSION_GT IDS
WHERE POD.po_distribution_id = IDS.num1
AND IDS.key = l_doc_level_id_key
AND ( (l_revision_specified_flag = 'Y'
AND POD.latest_external_flag = 'Y')
OR
(l_revision_specified_flag = 'N'
AND POD.revision_num =
(SELECT max(POD2.revision_num)
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD2
WHERE POD2.po_distribution_id = POD.po_distribution_id
AND POD2.revision_num <= p_doc_revision_num) )
)
;
SELECT FSP.req_encumbrance_flag, FSP.purch_encumbrance_flag
INTO l_req_enc_flag, l_purch_enc_flag
FROM FINANCIALS_SYSTEM_PARAMETERS FSP
;
SELECT FSP.req_encumbrance_flag, FSP.purch_encumbrance_flag
INTO l_req_enc_flag, l_purch_enc_flag
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE FSP.org_id = p_org_id
;
SELECT 'N'
into l_duplicate_exists
from sys.dual
where not exists
(SELECT 'po number is not unique'
FROM po_headers_all ph
WHERE ph.segment1 = p_segment1
AND ph.type_lookup_code IN
('STANDARD','CONTRACT','BLANKET','PLANNED')
AND nvl(ph.org_id, -99) = nvl(p_org_id, -99));
SELECT 'N'
into l_duplicate_exists
from sys.dual
where not exists
(SELECT 'po number is not unique'
FROM po_history_pos_all ph
WHERE ph.segment1 = p_segment1
AND ph.type_lookup_code IN
('STANDARD','CONTRACT','BLANKET','PLANNED')
AND nvl(ph.org_id, -99) = nvl(p_org_id, -99));
SELECT 'N'
into l_duplicate_exists
from sys.dual
where not exists
(SELECT 'rfq number is not unique'
FROM po_headers_all ph
WHERE ph.segment1 = p_segment1
AND ph.type_lookup_code = 'RFQ'
AND nvl(ph.org_id, -99) = nvl(p_org_id, -99));
SELECT 'N'
into l_duplicate_exists
from sys.dual
where not exists
(SELECT 'rfq number is not unique'
FROM po_history_pos_all ph
WHERE ph.segment1 = p_segment1
AND ph.type_lookup_code = 'RFQ'
AND nvl(ph.org_id, -99) = nvl(p_org_id, -99));
SELECT 'N'
into l_duplicate_exists
from sys.dual
where not exists
(SELECT 'quote number is not unique'
FROM po_headers_all ph
WHERE ph.segment1 = p_segment1
AND ph.type_lookup_code = 'QUOTATION'
AND nvl(ph.org_id, -99) = nvl(p_org_id, -99));
SELECT 'N'
into l_duplicate_exists
from sys.dual
where not exists
(SELECT 'quote number is not unique'
FROM po_history_pos_all ph
WHERE ph.segment1 = p_segment1
AND ph.type_lookup_code = 'QUOTATION'
AND nvl(ph.org_id, -99) = nvl(p_org_id, -99));
SELECT 'Y'
INTO l_in_sob
FROM financials_system_parameters fsp,
hr_organization_information hoi,
mtl_parameters mp
WHERE mp.organization_id = p_inv_org_id
AND mp.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = TO_CHAR(fsp.set_of_books_id);
SELECT 'Y'
INTO l_in_sob
FROM hr_organization_information hoi,
mtl_parameters mp
WHERE mp.organization_id = p_inv_org_id
AND mp.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = TO_CHAR(p_sob_id);
SELECT TO_NUMBER(hoi.org_information3)
INTO x_ou_id
FROM hr_organization_information hoi,
mtl_parameters mp
WHERE mp.organization_id = p_inv_org_id
AND mp.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information';
SELECT TO_NUMBER(hoi.org_information1)
INTO x_sob_id
FROM hr_organization_information hoi,
mtl_parameters mp
WHERE mp.organization_id = p_inv_org_id
AND mp.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information';
SELECT haou.business_group_id,
gsob.set_of_books_id,
gsob.chart_of_accounts_id,
TO_NUMBER(hoi.org_information3),
TO_NUMBER(hoi.org_information2)
INTO x_business_group_id,
x_set_of_books_id,
x_chart_of_accounts_id,
x_operating_unit_id,
x_legal_entity_id
FROM hr_organization_information hoi,
hr_all_organization_units haou,
mtl_parameters mp,
gl_sets_of_books gsob
WHERE mp.organization_id = p_inv_org_id
AND mp.organization_id = haou.organization_id
AND haou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND TO_NUMBER(hoi.org_information1) = gsob.set_of_books_id;
SELECT
-- reserved rows that are not prevent_encumbrance
-- i.e., prevent_encumbrance_flag <> Y and encumbered_flag = Y
COUNT( DECODE( PRD.prevent_encumbrance_flag
, 'Y', NULL
, DECODE( PRD.encumbered_flag
, 'Y', 'Y'
, NULL
)
)
)
-- unreserved rows that are not prevent_encumbrance
-- i.e., prevent_encumbrance_flag <> Y and encumbered_flag <> Y
, COUNT( DECODE( PRD.prevent_encumbrance_flag
, 'Y', NULL
, DECODE( PRD.encumbered_flag
, 'Y', NULL
, 'N'
)
)
)
-- prevent_encumbrance rows
-- i.e., prevent_encumbrance_flag = Y
, COUNT( DECODE( PRD.prevent_encumbrance_flag
, 'Y', 'Y'
, NULL
)
)
INTO
x_reserved_count
, x_unreserved_count
, x_prevented_count
FROM
PO_REQ_DISTRIBUTIONS_ALL PRD
, PO_REQUISITION_LINES_ALL PRL
, (
SELECT
p_doc_level_id dist_id
FROM DUAL
WHERE p_doc_level = g_doc_level_DISTRIBUTION
UNION ALL
SELECT
PRD1.distribution_id dist_id
FROM PO_REQ_DISTRIBUTIONS_ALL PRD1
WHERE p_doc_level = g_doc_level_LINE
AND PRD1.requisition_line_id = p_doc_level_id
UNION ALL
SELECT
PRD2.distribution_id dist_id
FROM
PO_REQ_DISTRIBUTIONS_ALL PRD2
, PO_REQUISITION_LINES_ALL PRL2
WHERE p_doc_level = g_doc_level_HEADER
AND PRD2.requisition_line_id = PRL2.requisition_line_id
AND PRL2.requisition_header_id = p_doc_level_id
) DIST_IDS
WHERE PRL.requisition_line_id = PRD.requisition_line_id
AND NVL(PRL.cancel_flag,'N') <> 'Y'
AND NVL(PRL.closed_code,g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
AND PRD.distribution_id = DIST_IDS.dist_id
;
SELECT
-- reserved rows that are not prevent_encumbrance
-- i.e., prevent_encumbrance_flag <> Y and encumbered_flag = Y
COUNT( DECODE( POD.prevent_encumbrance_flag
, 'Y', NULL
, DECODE( POD.encumbered_flag
, 'Y', 'Y'
, NULL
)
)
)
-- unreserved rows that are not prevent_encumbrance
-- i.e., prevent_encumbrance_flag <> Y and encumbered_flag <> Y
, COUNT( DECODE( POD.prevent_encumbrance_flag
, 'Y', NULL
, DECODE( POD.encumbered_flag
, 'Y', NULL
, 'N'
)
)
)
-- prevent_encumbrance rows
-- i.e., prevent_encumbrance_flag = Y
, COUNT( DECODE( POD.prevent_encumbrance_flag
, 'Y', 'Y'
, NULL
)
)
INTO
x_reserved_count
, x_unreserved_count
, x_prevented_count
FROM
PO_DISTRIBUTIONS_ALL POD
, PO_LINE_LOCATIONS_ALL POLL
, PO_HEADERS_ALL POH
WHERE POLL.line_location_id(+) = POD.line_location_id
AND POH.po_header_id = POD.po_header_id
AND
( (p_doc_type <> g_doc_type_PA AND NVL(POLL.cancel_flag,'N') <> 'Y')
OR (p_doc_type = g_doc_type_PA AND NVL(POH.cancel_flag,'N') <> 'Y')
)
AND
(
( p_doc_type <> g_doc_type_PA
AND NVL(POLL.closed_code,g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
)
OR
( p_doc_type = g_doc_type_PA
AND NVL(POH.closed_code,g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
)
)
AND
(
( p_doc_level = g_doc_level_DISTRIBUTION
AND POD.po_distribution_id = p_doc_level_id
)
OR
( p_doc_level = g_doc_level_SHIPMENT
AND POD.line_location_id = p_doc_level_id
)
OR
( p_doc_level = g_doc_level_LINE
AND POD.po_line_id = p_doc_level_id
)
OR
( p_doc_level = g_doc_level_HEADER
AND p_doc_type = g_doc_type_RELEASE
AND POD.po_release_id = p_doc_level_id
)
OR
( p_doc_level = g_doc_level_HEADER
AND p_doc_type <> g_doc_type_RELEASE
AND POD.po_header_id = p_doc_level_id
)
)
-- Make sure that release dists are not picked up for BPAs, PPOs.
AND
(
( p_doc_type <> g_doc_type_RELEASE
AND POD.po_release_id IS NULL
)
OR
( p_doc_type = g_doc_type_RELEASE
AND POD.po_release_id IS NOT NULL
)
)
;
SELECT POLC.displayed_field
INTO x_displayed_field
FROM PO_LOOKUP_CODES POLC
WHERE POLC.lookup_type = 'DOCUMENT STATE'
AND POLC.lookup_code = 'RESERVED'
;
SELECT PO_SESSION_GT_S.nextval
INTO x_nextval
FROM DUAL
;
SELECT plc_sta.displayed_field
, prh.authorization_status
, DECODE(nvl(prh.closed_code,'OPEN'), 'OPEN', NULL, plc_clo.displayed_field)
INTO l_status_code, l_auth_status, l_closed_status
FROM po_requisition_headers prh, po_lookup_codes plc_sta, po_lookup_codes plc_clo
WHERE plc_sta.lookup_code = DECODE(prh.authorization_status,
'SYSTEM_SAVED', 'INCOMPLETE',
nvl(prh.authorization_status, 'INCOMPLETE')
)
AND plc_clo.lookup_code = nvl(prh.closed_code, 'OPEN')
AND plc_clo.lookup_type = 'DOCUMENT STATE'
AND plc_sta.lookup_type = 'AUTHORIZATION STATUS'
AND prh.requisition_header_id = p_document_id;
SELECT polc.displayed_field, nvl(poah.offline_code, 'N')
INTO l_offline_status, l_offline_flag
FROM po_lookup_codes polc, po_action_history poah
WHERE polc.lookup_type = 'DOCUMENT STATE'
AND polc.lookup_code = poah.offline_code
AND poah.object_id = p_document_id
AND poah.object_type_code = 'REQUISITION'
AND poah.action_code IS NULL;
SELECT plc_sta.displayed_field
, DECODE(poh.cancel_flag, 'Y', plc_can.displayed_field, NULL)
, DECODE(nvl(poh.closed_code,'OPEN'), 'OPEN', NULL, plc_clo.displayed_field)
, DECODE(poh.frozen_flag, 'Y', plc_fro.displayed_field, NULL)
, DECODE(poh.user_hold_flag, 'Y', plc_hld.displayed_field, NULL)
, poh.authorization_status
, nvl(poh.cancel_flag, 'N')
, poh.closed_code
, nvl(poh.frozen_flag, 'N')
, nvl(poh.user_hold_flag,'N')
INTO l_status_code
, l_cancel_status
, l_closed_status
, l_frozen_status
, l_hold_status
, l_auth_status
, l_cancel_flag
, l_closed_code
, l_frozen_flag
, l_user_hold_flag
FROM po_headers poh
, po_lookup_codes plc_sta
, po_lookup_codes plc_can
, po_lookup_codes plc_clo
, po_lookup_codes plc_fro
, po_lookup_codes plc_hld
WHERE plc_sta.lookup_code = DECODE(poh.approved_flag,
'R', poh.approved_flag,
nvl(poh.authorization_status, 'INCOMPLETE')
)
AND plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
AND plc_can.lookup_code = 'CANCELLED'
AND plc_can.lookup_type = 'DOCUMENT STATE'
AND plc_clo.lookup_code = nvl(poh.closed_code, 'OPEN')
AND plc_clo.lookup_type = 'DOCUMENT STATE'
AND plc_fro.lookup_code = 'FROZEN'
AND plc_fro.lookup_type = 'DOCUMENT STATE'
AND plc_hld.lookup_code = 'ON HOLD'
AND plc_hld.lookup_type = 'DOCUMENT STATE'
AND poh.po_header_id = p_document_id;
SELECT polc.displayed_field, nvl(poah.offline_code, 'N')
INTO l_offline_status, l_offline_flag
FROM po_lookup_codes polc, po_action_history poah
WHERE polc.lookup_type = 'DOCUMENT STATE'
AND polc.lookup_code = poah.offline_code
AND poah.object_id = p_document_id
AND poah.object_type_code IN ('PO', 'PA')
AND poah.action_code IS NULL;
SELECT plc_sta.displayed_field
, DECODE(por.cancel_flag, 'Y', plc_can.displayed_field, NULL)
, DECODE(nvl(por.closed_code,'OPEN'), 'OPEN', NULL, plc_clo.displayed_field)
, DECODE(por.frozen_flag, 'Y', plc_fro.displayed_field, NULL)
, DECODE(por.hold_flag, 'Y', plc_hld.displayed_field, NULL)
, por.authorization_status
, nvl(por.cancel_flag, 'N')
, por.closed_code
, nvl(por.frozen_flag, 'N')
, nvl(por.hold_flag,'N')
INTO l_status_code
, l_cancel_status
, l_closed_status
, l_frozen_status
, l_hold_status
, l_auth_status
, l_cancel_flag
, l_closed_code
, l_frozen_flag
, l_user_hold_flag
FROM po_releases por
, po_lookup_codes plc_sta
, po_lookup_codes plc_can
, po_lookup_codes plc_clo
, po_lookup_codes plc_fro
, po_lookup_codes plc_hld
WHERE plc_sta.lookup_code = DECODE(por.approved_flag,
'R', por.approved_flag,
nvl(por.authorization_status, 'INCOMPLETE')
)
AND plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
AND plc_can.lookup_code = 'CANCELLED'
AND plc_can.lookup_type = 'DOCUMENT STATE'
AND plc_clo.lookup_code = nvl(por.closed_code, 'OPEN')
AND plc_clo.lookup_type = 'DOCUMENT STATE'
AND plc_fro.lookup_code = 'FROZEN'
AND plc_fro.lookup_type = 'DOCUMENT STATE'
AND plc_hld.lookup_code = 'ON HOLD'
AND plc_hld.lookup_type = 'DOCUMENT STATE'
AND por.po_release_id = p_document_id;
SELECT polc.displayed_field, nvl(poah.offline_code, 'N')
INTO l_offline_status, l_offline_flag
FROM po_lookup_codes polc, po_action_history poah
WHERE polc.lookup_type = 'DOCUMENT STATE'
AND polc.lookup_code = poah.offline_code
AND poah.object_id = p_document_id
AND poah.object_type_code = 'RELEASE'
AND poah.action_code IS NULL;
SELECT ROUND(poll.price_override * DECODE(poh.rate, 0, 1, null, 1, ROUND(poh.rate,5)), 5)
, poh.rate_date
, poh.rate
, poh.currency_code
, poh.rate_type
, poll.price_discount
, poll.price_override
, DECODE(poll.line_location_id, NULL, pol.unit_meas_lookup_code, poll.unit_meas_lookup_code)
FROM po_headers poh, po_lines pol, po_line_locations poll
WHERE poh.po_header_id = p_header_id
AND poh.po_header_id = pol.po_header_id
AND pol.line_num = p_line_num
AND pol.po_line_id = poll.po_line_id (+)
AND (p_required_curr IS NULL or poh.currency_code = p_required_curr)
AND (p_required_rate_type is null or poh.rate_type = p_required_rate_type)
AND NVL(poll.unit_meas_lookup_code, NVL(p_uom, pol.unit_meas_lookup_code))
= NVL(p_uom, pol.unit_meas_lookup_code)
AND trunc(sysdate) BETWEEN NVL(poll.start_date, trunc(sysdate)) AND NVL(poll.end_date, trunc(sysdate))
AND poll.quantity <= p_qty
AND poll.ship_to_location_id = p_ship_to_loc_id
AND poll.shipment_type in ('PRICE BREAK', 'QUOTATION')
ORDER BY 1 ASC;
SELECT ROUND(DECODE(poll.shipment_type,
'PRICE BREAK', DECODE(poll.ship_to_location_id,
NULL, poll.price_override,pol.unit_price),
'QUOTATION', DECODE(poll.ship_to_location_id,
NULL,poll.price_override, pol.unit_price),
pol.unit_price)
* DECODE(poh.rate, 0, 1, null, 1, ROUND(poh.rate,5)), 5)
, poh.rate_date
, poh.rate
, poh.currency_code
, poh.rate_type
, poll.price_discount
, DECODE(poll.shipment_type,
'PRICE BREAK', DECODE(poll.ship_to_location_id,
NULL, poll.price_override ,pol.unit_price),
'QUOTATION', DECODE(poll.ship_to_location_id,
NULL,poll.price_override, pol.unit_price),
pol.unit_price)
, DECODE(poll.line_location_id, NULL, pol.unit_meas_lookup_code, poll.unit_meas_lookup_code)
FROM po_headers poh, po_lines pol, po_line_locations poll
WHERE poh.po_header_id = p_header_id
AND poh.po_header_id = pol.po_header_id
AND pol.line_num = p_line_num
AND pol.po_line_id = poll.po_line_id (+)
AND (p_required_curr IS NULL or poh.currency_code = p_required_curr)
AND (p_required_rate_type is null or poh.rate_type = p_required_rate_type)
AND NVL(poll.unit_meas_lookup_code, NVL(p_uom, pol.unit_meas_lookup_code))
= NVL(p_uom, pol.unit_meas_lookup_code)
AND trunc(sysdate) BETWEEN NVL(poll.start_date, trunc(sysdate)) AND NVL(poll.end_date, trunc(sysdate))
AND poll.quantity <= p_qty
ORDER BY 1 ASC;
SELECT hrl.ship_to_location_id
INTO l_ship_to_loc_id
FROM hr_locations hrl
WHERE hrl.location_id = p_deliver_to_loc_id;
FUNCTION get_last_update_date_for_doc(p_doc_header_id IN NUMBER) RETURN DATE
IS
header_last_update_date PO_HEADERS_ALL.last_update_date%type;
line_last_update_date PO_LINES_ALL.last_update_date%type;
line_loc_last_update_date PO_LINE_LOCATIONS_ALL.last_update_date%type;
dist_last_update_date PO_DISTRIBUTIONS_ALL.last_update_date%type;
doc_last_update_date PO_HEADERS_ALL.last_update_date%type;
min_date PO_HEADERS_ALL.last_update_date%type := to_date('01/01/-4712','DD/MM/SYYYY');
l_module_name CONSTANT VARCHAR2(100) := 'GET_LAST_UPDATE_DATE_FOR_DOC';
select nvl(max(last_update_date), min_date) into header_last_update_date from po_headers_all where po_header_id = p_doc_header_id;
select nvl(max(last_update_date), min_date) into line_last_update_date from po_lines_all where po_header_id = p_doc_header_id;
select nvl(max(last_update_date), min_date) into line_loc_last_update_date from po_line_locations_all where po_header_id = p_doc_header_id;
select nvl(max(last_update_date), min_date) into dist_last_update_date from po_distributions_all where po_header_id = p_doc_header_id;
doc_last_update_date := greatest( header_last_update_date
,line_last_update_date
,line_loc_last_update_date
,dist_last_update_date);
PO_LOG.stmt(d_module_base, d_pos, 'doc_last_update_date', doc_last_update_date);
return(doc_last_update_date);
FUNCTION get_last_update_date_for_mod(p_doc_id IN NUMBER) RETURN DATE
IS
draft_last_update_date PO_DRAFTS.last_update_date%type;
header_last_update_date PO_HEADERS_DRAFT_ALL.last_update_date%type;
line_last_update_date PO_LINES_DRAFT_ALL.last_update_date%type;
line_loc_last_update_date PO_LINE_LOCATIONS_DRAFT_ALL.last_update_date%type;
dist_last_update_date PO_DISTRIBUTIONS_DRAFT_ALL.last_update_date%type;
mod_last_update_date PO_HEADERS_DRAFT_ALL.last_update_date%type;
address_last_update_date po_headers_all_ext_b.last_update_date%type;
org_assign_last_update_date po_ga_org_assign_draft.last_update_date%type;
min_date PO_HEADERS_DRAFT_ALL.last_update_date%type := to_date('01/01/-4712','DD/MM/SYYYY');
l_module_name CONSTANT VARCHAR2(100) := 'GET_LAST_UPDATE_DATE_FOR_MOD';
select nvl(max(last_update_date), min_date) into header_last_update_date from po_headers_draft_all where draft_id = p_doc_id;
select nvl(max(last_update_date), min_date) into line_last_update_date from po_lines_draft_all where draft_id = p_doc_id;
select nvl(max(last_update_date), min_date) into line_loc_last_update_date from po_line_locations_draft_all where draft_id = p_doc_id;
select nvl(max(last_update_date), min_date) into dist_last_update_date from po_distributions_draft_all where draft_id = p_doc_id;
select nvl(max(last_update_date), min_date) into draft_last_update_date from po_drafts where draft_id = p_doc_id;
select nvl(max(last_update_date), min_date) into address_last_update_date from po_headers_all_ext_b where draft_id = p_doc_id;
select nvl(max(last_update_date), min_date) into org_assign_last_update_date from po_ga_org_assign_draft where draft_id = p_doc_id;
mod_last_update_date := greatest( header_last_update_date
,line_last_update_date
,line_loc_last_update_date
,dist_last_update_date
,draft_last_update_date
,address_last_update_date
,org_assign_last_update_date);
PO_LOG.stmt(d_module_base, d_pos, 'mod_last_update_date', mod_last_update_date);
return(mod_last_update_date);
SELECT CLM_CONTRACT_OFFICER
INTO l_contracting_officer
FROM po_user_preferences
WHERE fnd_user_id=p_user_id
AND PREFERENCE_TYPE='CONTRACT_OFFICER'
AND FUNCTIONAL_AREA='PURCHASING'
AND CLM_CONTRACT_OFFICER IS NOT NULL;
SELECT To_Char(EMPLOYEE_ID)
INTO l_contracting_officer
FROM po_buyers_val_v
WHERE EMPLOYEE_ID = p_emp_id
AND CONTRACT_OFFICER='Y';
l_query := 'SELECT ' || p_option_column
|| ' FROM PO_SYSTEM_PARAMETERS_ALL '
|| ' WHERE ORG_ID=' || p_org_id;