[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Global tables that are defined for inserting into the global temp
* table po_retroprice_gt.
*/
g_row_id_table char30_table;
g_communicate_update VARCHAR2(1) := 'N';
* Private Procedure: MassUpdate_Releases
* Modifies: Column price_override, retroactive_date in po_line_locations,
* Authorization_status, revision_num in po_headers and po_releases.
* Effects: Selects the agreements( blankets and contracts) as specified
* by the concurrent parameters and selects the execution docs
* refering these agreements for retroactive price updates.
* Get the new price based on the release/Std PO shipment values.
* If they are different, then update po_line_locations with the
* new price. In either case, update retoractive_date in
* po_line_locations with the retroactive_date in po_lines so that
* this shipment will not be picked up again unless blanket line
* is retroactively changed. Once all the releases are done, update
* po_headers or po_releases with the new revision number and set
* authorization_status to "Requires Reapproval" and initiate
* Workflow if the document was already in Approved state.
* Returns:
* x_return_status - FND_API.G_RET_STS_SUCCESS if control action succeeds
* FND_API.G_RET_STS_ERROR if control action fails
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
*/
--------------------------------------------------------------------------------
--Start of Comments
--Name: MassUpdate_Releases
--Pre-reqs:
-- None.
--Modifies:
-- Column price_override, retroactive_date in po_line_locations,
-- Authorization_status, revision_num in po_headers and po_releases.
--Locks:
-- None.
--Function:
-- This API is called from the Approval Window or by the
-- Concurrent Program. This procedure will update all
-- the releases against Blanket Agreeements or Standard
-- POs against Global Agreements that have lines that
-- are retroactively changed.
-- Selects the blanket lines that have been retroactively changed
-- and selects all the releases againt BA (or std PO against GA).
-- Get the new price based on the release/Std PO shipment values.
-- If they are different, then update po_line_locations with the
-- new price. In either case, update retoractive_date in
-- po_line_locations with the retroactive_date in po_lines so that
-- this shipment will not be picked up again unless blanket line
-- is retroactively changed. Once all the releases are done, update
-- po_headers or po_releases with the new revision number and set
-- authorization_status to "Requires Reapproval" and initiate
-- Workflow if the document was already in Approved state.
--Parameters:
--IN:
--p_api_version
-- Version number of API that caller expects. It
-- should match the l_api_version defined in the
-- procedure (expected value : 1.0)
--p_validation_level
-- validation level api uses
--p_vendor_id
-- Site_id of the Supplier site selected by the user.
--p_po_header_id
-- Header_id of the Blanket/Global Agreement selected by user.
--p_category_struct_id
-- Purchasing Category structure Id
--p_category_from / p_category_to
-- Category Range that user selects to process retroactive changes
--p_item_num_from / p_item_num_to
-- Item Range that user selects to process retroactive changes
--p_date
-- All releases or Std PO created on or after this date must be changed.
--p_communicate_update
-- Communicate Price Updates to Supplier
--OUT:
--x_return_status
-- FND_API.G_RET_STS_SUCCESS if API succeeds
-- FND_API.G_RET_STS_ERROR if API fails
-- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
--Testing:
--
--End of Comments
--------------------------------------------------------------------------------
Procedure MassUpdate_Releases ( p_api_version IN NUMBER,
p_validation_level IN NUMBER,
p_vendor_id IN Number,
p_vendor_site_id IN Number,
p_po_header_id IN Number,
p_category_struct_id IN Number,
p_category_from IN Varchar2,
p_category_to IN Varchar2,
p_item_from IN Varchar2,
p_item_to IN Varchar2,
p_date IN Date,
--
p_communicate_update IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2)
IS
--Bug 4176111: Add the paratemeter p_qp_license_on to conditionally enable logic to test retroactive_date
cursor select_open_releases(l_po_line_id number, l_retroactive_date date,p_date date, p_qp_license_on VARCHAR2 ) is
select poll.rowid,poll.line_location_id, poll.quantity,
poll.ship_to_organization_id, poll.ship_to_location_id,
poll.price_override, nvl(poll.need_by_date,sysdate),
por.po_release_id,
por.authorization_status, por.revision_num,
pora.revision_num
from po_line_locations poll,
po_releases_all por, --
po_releases_archive pora
where nvl(por.frozen_flag, 'N') = 'N'
and nvl(por.authorization_status, 'INCOMPLETE') IN
('APPROVED', 'INCOMPLETE', 'REJECTED',
'REQUIRES REAPPROVAL')
and nvl(por.closed_code,'OPEN') IN ('OPEN','CLOSED',
'CLOSED FOR RECEIVING',
/* Bug 3334043: Releases that are closed by setting 'Invoice Close
* Tolerance to 100%' should also be picked up
*/
'CLOSED FOR INVOICE')
and nvl(por.cancel_flag,'N') <> 'Y'
and nvl(por.consigned_consumption_flag,'N') ='N'
and ((poll.accrue_on_receipt_flag = 'Y' and
quantity_received =0 and quantity_billed =0)
OR
(poll.accrue_on_receipt_flag = 'N' and
quantity_billed = 0))
/* Bug 2725744. Added the condition to check for closed_code
* and cancel_flag flag for the release shipments.
*/
and nvl(poll.closed_code,'OPEN') IN ('OPEN','CLOSED',
'CLOSED FOR RECEIVING',
/* Bug 3334043: Releases that are closed by setting 'Invoice Close
* Tolerance to 100%' should also be picked up
*/
'CLOSED FOR INVOICE')
and nvl(poll.cancel_flag,'N') <> 'Y'
and por.po_release_id = poll.po_release_id
and poll.po_release_id is not null
and poll.po_line_id = l_po_line_id
--Bug 4176111: Only when the Advanced Pricing API is not enabled for PO, then use logic of comparing retroactive_date
--to rule out unneccesary release linelocations
and ( p_qp_license_on = 'Y' OR
( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --
and nvl(poll.need_by_date,por.creation_date) >=
nvl(p_date,nvl(poll.need_by_date,por.creation_date))
AND POR.po_release_id = PORA.po_release_id (+)
AND PORA.latest_external_flag (+) = 'Y'
order by por.po_release_id
for update of poll.retroactive_date;
cursor select_open_stdpo(l_po_line_id number, l_retroactive_date date,
p_date date, p_qp_license_on VARCHAR2) is
select pol.rowid,poll.line_location_id,pol.quantity,
poll.ship_to_organization_id, poll.ship_to_location_id,
poll.price_override, nvl(poll.need_by_date,sysdate),
poh.po_header_id,
poh.authorization_status, poh.revision_num,
poha.revision_num
from po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
po_headers_archive_all poha,financials_system_params_all fsp
where pol.from_line_id = l_po_line_id
and poh.po_header_id = pol.po_header_id
and poh.org_id = fsp.org_id -- -- Bug 3573266
and nvl(fsp.purch_encumbrance_flag,'N') = 'N' -- Bug 3573266
--Bug 4176111: Only when the Advanced Pricing API is not enabled for PO, then use logic of comparing retroactive_date
--to rule out unneccesary spo linelocations
and ( p_qp_license_on = 'Y' OR
( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --
and poll.shipment_num = (select min(poll.shipment_num)
from po_line_locations_all polt
where polt.po_line_id=pol.po_line_id)
and nvl(poh.frozen_flag, 'N') = 'N'
and nvl(poh.authorization_status, 'INCOMPLETE') IN
('APPROVED', 'INCOMPLETE', 'REJECTED',
'REQUIRES REAPPROVAL')
and nvl(poh.closed_code,'OPEN') IN ('OPEN','CLOSED',
'CLOSED FOR RECEIVING',
/* Bug 3334043: Std.POs that are closed by setting 'Invoice Close
* Tolerance to 100%' should also be picked up
*/
'CLOSED FOR INVOICE')
and nvl(poh.cancel_flag,'N') <> 'Y'
and nvl(poh.consigned_consumption_flag,'N') ='N'
and pol.po_line_id = poll.po_line_id
and not exists (Select 'billed received shipments'
from po_line_locations_all poll1
where poll1.po_line_id = pol.po_line_id
and ((poll1.accrue_on_receipt_flag = 'Y' and
poll1.quantity_received <> 0)
or
poll1.quantity_billed <> 0))
/* Bug 2725744. Added the condition to check for closed_code
* and cancel_flag for the StdPO lines.
*/
and nvl(pol.closed_code,'OPEN') IN ('OPEN','CLOSED',
'CLOSED FOR RECEIVING',
/* Bug 3334043: Std.POs that are closed by setting 'Invoice Close
* Tolerance to 100%' should also be picked up
*/
'CLOSED FOR INVOICE')
and nvl(pol.cancel_flag,'N') <> 'Y'
and nvl(poll.need_by_date,poh.creation_date) >=
nvl(p_date,nvl(poll.need_by_date,poh.creation_date))
AND poh.po_header_id = poha.po_header_id (+)
AND poha.latest_external_flag (+) = 'Y'
AND pol.order_type_lookup_code not in ('RATE', 'FIXED PRICE') -- Bug 3524527
order by poh.po_header_id
for update of poll.retroactive_date;
cursor select_all_releases(l_po_line_id number, l_retroactive_date date,p_date date, p_qp_license_on VARCHAR2) is
select poll.rowid,poll.line_location_id, poll.quantity,
poll.ship_to_organization_id, poll.ship_to_location_id,
poll.price_override, nvl(poll.need_by_date,sysdate),
por.po_release_id,
por.authorization_status, por.revision_num,
pora.revision_num
from po_line_locations poll,
po_releases_all por, --
po_releases_archive pora
where nvl(por.frozen_flag, 'N') = 'N'
and nvl(por.authorization_status, 'INCOMPLETE') IN
('APPROVED', 'INCOMPLETE', 'REJECTED',
'REQUIRES REAPPROVAL')
and nvl(por.closed_code,'OPEN') <> 'FINALLY CLOSED'
and nvl(por.cancel_flag,'N') <> 'Y'
/* Bug 2725744. Added the condition to check for closed_code
* and cancel_flag flag for the release shipments.
*/
and nvl(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
and nvl(poll.cancel_flag,'N') <> 'Y'
and por.po_release_id = poll.po_release_id
and poll.po_release_id is not null
and poll.po_line_id = l_po_line_id
--Bug 4176111: Only when the Advanced Pricing API is not enabled for PO, then use logic of comparing retroactive_date
--to rule out unneccesary release linelocations
and ( p_qp_license_on = 'Y' OR
( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --
and nvl(poll.need_by_date,por.creation_date) >=
nvl(p_date,nvl(poll.need_by_date,por.creation_date))
AND POR.po_release_id = PORA.po_release_id (+)
AND PORA.latest_external_flag (+) = 'Y'
order by por.po_release_id
for update of poll.retroactive_date;
cursor select_all_stdpo(l_po_line_id number, l_retroactive_date date,
p_date date, p_qp_license_on VARCHAR2) is
select pol.rowid,poll.line_location_id,pol.quantity,
poll.ship_to_organization_id, poll.ship_to_location_id,
poll.price_override, nvl(poll.need_by_date,sysdate),
poh.po_header_id,
poh.authorization_status, poh.revision_num,
poha.revision_num
from po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
po_headers_archive_all poha, po_document_types_all_b pdt,
financials_system_params_all fsp
where pol.from_line_id = l_po_line_id
and poh.po_header_id = pol.po_header_id
and poh.org_id = fsp.org_id -- -- Bug 3573266
and nvl(fsp.purch_encumbrance_flag,'N') = 'N' -- Bug 3573266
and poh.org_id = pdt.org_id -- -- Bug 3573266
and pdt.document_type_code = 'PO' -- Bug 3573266
and pdt.document_subtype = 'STANDARD' -- Bug 3573266
and (nvl(pdt.archive_external_revision_code,'PRINT') = 'APPROVE' -- Bug 3573266
or (not exists (Select 'billed received shipments' -- Bug 3565522
from po_line_locations_all poll1
where poll1.po_line_id = pol.po_line_id
and ((poll1.accrue_on_receipt_flag = 'Y' and
poll1.quantity_received <> 0)
or
poll1.quantity_billed <> 0))) )
--Bug 4176111: Only when the Advanced Pricing API is not enabled for PO, then use logic of comparing retroactive_date
--to rule out unneccesary spo linelocations
and ( p_qp_license_on = 'Y' OR
( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --
and poll.shipment_num = (select min(poll.shipment_num)
from po_line_locations_all polt
where polt.po_line_id=pol.po_line_id)
and nvl(poh.frozen_flag, 'N') = 'N'
and nvl(poh.authorization_status, 'INCOMPLETE') IN
('APPROVED', 'INCOMPLETE', 'REJECTED',
'REQUIRES REAPPROVAL')
and nvl(poh.closed_code,'OPEN') <> 'FINALLY CLOSED'
and nvl(poh.cancel_flag,'N') <> 'Y'
and pol.po_line_id = poll.po_line_id
/* Bug 2725744. Added the condition to check for closed_code
* and cancel_flag for the StdPO lines.
*/
and nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
and nvl(pol.cancel_flag,'N') <> 'Y'
and nvl(poll.need_by_date,poh.creation_date) >=
nvl(p_date,nvl(poll.need_by_date,poh.creation_date))
AND poh.po_header_id = poha.po_header_id (+)
AND poha.latest_external_flag (+) = 'Y'
AND pol.order_type_lookup_code not in ('RATE', 'FIXED PRICE') -- Bug 3524527
order by poh.po_header_id
for update of poll.retroactive_date;
cursor select_open_contract_exec_docs(l_po_header_id number, p_date date) is
SELECT
pol.rowid,
poll.line_location_id,
pol.quantity,
poll.ship_to_organization_id,
poll.ship_to_location_id,
poll.price_override,
nvl(poll.need_by_date,sysdate),
poh.po_header_id,
poh.authorization_status,
poh.revision_num,
poha.revision_num
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_headers_archive_all poha,
financials_system_params_all fsp
WHERE pol.Contract_id = l_po_header_id
AND pol.from_header_id IS NULL
AND poh.po_header_id = pol.po_header_id
AND poh.org_id = fsp.org_id
AND nvl(fsp.purch_encumbrance_flag,'N') = 'N'
AND poll.shipment_num =
(
SELECT
min(poll.shipment_num)
FROM po_line_locations_all polt
WHERE polt.po_line_id=pol.po_line_id
)
AND nvl(poh.frozen_flag, 'N') = 'N'
AND nvl(poh.authorization_status, 'INCOMPLETE') IN ('APPROVED', 'INCOMPLETE', 'REJECTED', 'REQUIRES REAPPROVAL')
AND nvl(poh.closed_code,'OPEN') IN ('OPEN','CLOSED', 'CLOSED FOR RECEIVING', 'CLOSED FOR INVOICE')
AND nvl(poh.cancel_flag,'N') <> 'Y'
AND nvl(poh.consigned_consumption_flag,'N') ='N'
AND pol.po_line_id = poll.po_line_id
AND not exists
(
SELECT
'billed received shipments'
FROM po_line_locations_all poll1
WHERE poll1.po_line_id = pol.po_line_id
AND
(
(
poll1.accrue_on_receipt_flag = 'Y'
AND poll1.quantity_received <> 0
)
OR poll1.quantity_billed <> 0
)
)
AND nvl(pol.closed_code,'OPEN') IN ('OPEN','CLOSED', 'CLOSED FOR RECEIVING', 'CLOSED FOR INVOICE')
AND nvl(pol.cancel_flag,'N') <> 'Y'
AND nvl(poll.need_by_date,poh.creation_date) >= nvl(p_date,nvl(poll.need_by_date,poh.creation_date))
AND poh.po_header_id = poha.po_header_id (+)
AND poha.latest_external_flag (+) = 'Y'
AND pol.order_type_lookup_code not in ('RATE', 'FIXED PRICE')
ORDER BY poh.po_header_id for UPDATE of poll.retroactive_date;
cursor select_all_contract_exec_docs(l_po_header_id number, p_date date) is
SELECT
pol.rowid,
poll.line_location_id,
pol.quantity,
poll.ship_to_organization_id,
poll.ship_to_location_id,
poll.price_override,
nvl(poll.need_by_date,sysdate),
poh.po_header_id,
poh.authorization_status,
poh.revision_num,
poha.revision_num
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_headers_archive_all poha,
po_document_types_all_b pdt,
financials_system_params_all fsp
WHERE pol.Contract_id = l_po_header_id
AND pol.from_header_id IS NULL
AND poh.po_header_id = pol.po_header_id
AND poh.org_id = fsp.org_id
AND nvl(fsp.purch_encumbrance_flag,'N') = 'N'
AND poh.org_id = pdt.org_id
AND pdt.document_type_code = 'PO'
AND pdt.document_subtype = 'STANDARD'
AND
(
nvl(pdt.archive_external_revision_code,'PRINT') = 'APPROVE'
OR
(
not exists
(
SELECT
'billed received shipments'
FROM po_line_locations_all poll1
WHERE poll1.po_line_id = pol.po_line_id
AND
(
(
poll1.accrue_on_receipt_flag = 'Y'
AND poll1.quantity_received <> 0
)
OR poll1.quantity_billed <> 0
)
)
)
)
AND poll.shipment_num =
(
SELECT
min(poll.shipment_num)
FROM po_line_locations_all polt
WHERE polt.po_line_id=pol.po_line_id
)
AND nvl(poh.frozen_flag, 'N') = 'N'
AND nvl(poh.authorization_status, 'INCOMPLETE') IN ('APPROVED', 'INCOMPLETE', 'REJECTED', 'REQUIRES REAPPROVAL')
AND nvl(poh.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(poh.cancel_flag,'N') <> 'Y'
AND pol.po_line_id = poll.po_line_id
AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(pol.cancel_flag,'N') <> 'Y'
AND nvl(poll.need_by_date,poh.creation_date) >= nvl(p_date,nvl(poll.need_by_date,poh.creation_date))
AND poh.po_header_id = poha.po_header_id (+)
AND poha.latest_external_flag (+) = 'Y'
AND pol.order_type_lookup_code not in ('RATE', 'FIXED PRICE')
ORDER BY poh.po_header_id for UPDATE
of poll.retroactive_date;
cursor update_ship_price is
SELECT row_id, new_price
FROM po_retroprice_gt
WHERE po_release_id is not null;
cursor update_line_price is
SELECT row_id, new_price
FROM po_retroprice_gt
WHERE po_header_id is not null;
l_api_name CONSTANT VARCHAR2(30) := 'MassUpdate_Releases';
l_retroactive_update VARCHAR2(30) := 'NEVER';
* created against the line and update the shipments with the
* new price.
*/
l_module := g_log_head||l_api_name||'.'||'000'||'.';
l_retroactive_update := Get_Retro_Mode;
PO_DEBUG.put_line('Retro Mode :' || l_retroactive_update);
IF (l_retroactive_update = 'NEVER') THEN
PO_DEBUG.put_line('Retroactive Profile is set to Never or Financials patchset is not at the right level');
IF (p_communicate_update = 'Y') THEN
g_communicate_update := 'Y';
g_communicate_update := 'N';
l_sql_str := 'select poh.po_header_id,pol.po_line_id, pol.retroactive_date, ' ||
'poh.global_agreement_flag ' ||
'from po_headers_all poh, ' ||
'po_lines pol, ' ||
'mtl_system_items msi, ' ||
'financials_system_params_all fsp, ' ; --
' (select ''has releases'' from ' ||
' po_line_locations poll where '||
' poll.po_line_id = pol.po_line_id '||
' and poll.po_release_id is not null)) ';
'(select ''has stdpo'' from po_lines_all pl where '||
' pl.from_line_id = pol.po_line_id))) ' ;
'select poh.po_header_id,NULL, ' ||
'NULL, poh.global_agreement_flag ' ||
'from po_headers poh ' ||
'where poh.type_lookup_code = ''CONTRACT'' ' ||
'and poh.po_header_id = nvl(:p_po_header_id, poh.po_header_id) ' ||
'and poh.vendor_id = :p_vendor_id ' ||
'and poh.vendor_site_id = ' ||
'nvl(:p_vendor_site_id, poh.vendor_site_id) '||
'and nvl(poh.authorization_status,''INCOMPLETE'') = ''APPROVED'' ' ||
'and nvl(poh.frozen_flag, ''N'') = ''N'' ' ||
'and nvl(poh.consigned_consumption_flag,''N'') =''N'' '||
'and exists ' ||
' ( SELECT ''has stdpo'' FROM po_lines_all pl '||
' WHERE pl.contract_id = poh.po_header_id ) ';
g_exclude_row_id_table.delete;
g_row_id_table.delete;
g_new_price_table.delete;
g_po_header_id_table.delete;
g_po_release_id_table.delete;
g_archived_rev_num_table.delete;
g_auth_status_table.delete;
IF (l_retroactive_update = 'OPEN_RELEASES') THEN
OPEN select_open_stdpo(l_po_line_id_table(i),
l_retroactive_date_table(i),
p_date,
--Bug 4176111: Pass in the variable for Adv Pricing API
l_qp_license_on);
OPEN select_all_stdpo(l_po_line_id_table(i),
l_retroactive_date_table(i),
p_date,
--Bug 4176111: Pass in the variable for Adv Pricing API
l_qp_license_on);
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
IF (l_retroactive_update = 'OPEN_RELEASES') THEN
FETCH select_open_stdpo BULK COLLECT INTO
l_row_id_table,
l_po_line_loc_table,
l_quantity_table,
l_ship_to_org_id_table,
l_ship_to_location_id_table,
l_old_price_override_table,
l_need_by_date_table,
l_po_header_id_table,
l_auth_status_table,
l_rev_num_table,
l_archived_rev_num_table
LIMIT G_BULK_LIMIT;
FETCH select_all_stdpo BULK COLLECT INTO
l_row_id_table,
l_po_line_loc_table,
l_quantity_table,
l_ship_to_org_id_table,
l_ship_to_location_id_table,
l_old_price_override_table,
l_need_by_date_table,
l_po_header_id_table,
l_auth_status_table,
l_rev_num_table,
l_archived_rev_num_table
LIMIT G_BULK_LIMIT;
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
* will not be selecting these Std PO
* shipments whose retroactive_date is
* greater than the retroactive_date in
* po_lines. This means that this PO
* shipment was processed after the blanket
* line was changed.
*/
-- Bulk Update
l_module := g_log_head||l_api_name||'.'||
'060'||'.';
* We need to update po_line_locations_all with
* time stamp since we process the Std POs
* against GA in other operating units.
*/
FORALL processed_index in
1..l_po_line_loc_table.COUNT
UPDATE po_line_locations_all
SET retroactive_date= l_retroactive_date_table(i),
last_update_date = g_sysdate,
last_updated_by = g_user_id
WHERE line_location_id =
l_po_line_loc_table(processed_index);
IF (l_retroactive_update = 'OPEN_RELEASES') THEN
EXIT WHEN select_open_stdpo%NOTFOUND;
EXIT WHEN select_all_stdpo%NOTFOUND;
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
end loop; /*select_stdpo */
IF (l_retroactive_update = 'OPEN_RELEASES') THEN
CLOSE select_open_stdpo;
CLOSE select_all_stdpo;
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
IF (l_retroactive_update = 'OPEN_RELEASES')OR
(l_retroactive_update = 'ALL_RELEASES' AND
l_archive_mode_rel <> 'APPROVE') -- Bug 3565522
THEN
PO_DEBUG.put_line('Getting all open releases');
OPEN select_open_releases(l_po_line_id_table(i),
l_retroactive_date_table(i),
p_date,
--Bug 4176111: Pass in the variable for Adv Pricing API
l_qp_license_on);
OPEN select_all_releases( l_po_line_id_table(i),
l_retroactive_date_table(i),
p_date,
--Bug 4176111: Pass in the variable for Adv Pricing API
l_qp_license_on);
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
IF (l_retroactive_update = 'OPEN_RELEASES')OR
(l_retroactive_update = 'ALL_RELEASES' AND
l_archive_mode_rel <> 'APPROVE') -- Bug 3565522
THEN
FETCH select_open_releases BULK COLLECT INTO
l_row_id_table,
l_po_line_loc_table,
l_quantity_table,
l_ship_to_org_id_table,
l_ship_to_location_id_table,
l_old_price_override_table,
l_need_by_date_table,
l_po_release_id_table,
l_auth_status_table,
l_rev_num_table,
l_archived_rev_num_table
LIMIT G_BULK_LIMIT;
FETCH select_all_releases BULK COLLECT INTO
l_row_id_table,
l_po_line_loc_table,
l_quantity_table,
l_ship_to_org_id_table,
l_ship_to_location_id_table,
l_old_price_override_table,
l_need_by_date_table,
l_po_release_id_table,
l_auth_status_table,
l_rev_num_table,
l_archived_rev_num_table
LIMIT G_BULK_LIMIT;
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
* will not be selecting these Std PO
* shipments whose retroactive_date is
* greater than the retroactive_date in
* po_lines. This means that this PO
* shipment was processed after the blanket
* line was changed.
*/
-- Bulk Update
FORALL processed_index in
1..l_po_line_loc_table.COUNT
UPDATE po_line_locations
SET retroactive_date= l_retroactive_date_table(i),
last_update_date = g_sysdate,
last_updated_by = g_user_id
WHERE line_location_id =
l_po_line_loc_table(processed_index);
UPDATE po_line_locations
SET retroactive_date = null,
last_update_date = g_sysdate,
last_updated_by = g_user_id
WHERE rowid = g_exclude_row_id_table(exclude_index);
IF (l_retroactive_update = 'OPEN_RELEASES')OR
(l_retroactive_update = 'ALL_RELEASES' AND
l_archive_mode_rel <> 'APPROVE') -- Bug 3565522
THEN
EXIT WHEN select_open_releases%NOTFOUND;
EXIT WHEN select_all_releases%NOTFOUND;
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
end loop;/*select_releases*/
IF (l_retroactive_update = 'OPEN_RELEASES')OR
(l_retroactive_update = 'ALL_RELEASES' AND
l_archive_mode_rel <> 'APPROVE') -- Bug 3565522
THEN
CLOSE select_open_releases;
CLOSE select_all_releases;
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
IF (l_retroactive_update = 'OPEN_RELEASES') THEN
OPEN select_open_contract_exec_docs(l_po_agreement_id_table(i),
p_date);
OPEN select_all_contract_exec_docs(l_po_agreement_id_table(i),
p_date);
IF (l_retroactive_update = 'OPEN_RELEASES') THEN
FETCH select_open_contract_exec_docs BULK COLLECT INTO
l_row_id_table,
l_po_line_loc_table,
l_quantity_table,
l_ship_to_org_id_table,
l_ship_to_location_id_table,
l_old_price_override_table,
l_need_by_date_table,
l_po_header_id_table,
l_auth_status_table,
l_rev_num_table,
l_archived_rev_num_table
LIMIT G_BULK_LIMIT;
FETCH select_all_contract_exec_docs BULK COLLECT INTO
l_row_id_table,
l_po_line_loc_table,
l_quantity_table,
l_ship_to_org_id_table,
l_ship_to_location_id_table,
l_old_price_override_table,
l_need_by_date_table,
l_po_header_id_table,
l_auth_status_table,
l_rev_num_table,
l_archived_rev_num_table
LIMIT G_BULK_LIMIT;
UPDATE po_line_locations_all
SET retroactive_date= l_retroactive_date_table(i),
last_update_date = g_sysdate,
last_updated_by = g_user_id
WHERE line_location_id =
l_po_line_loc_table(processed_index);
IF (l_retroactive_update = 'OPEN_RELEASES') THEN
EXIT WHEN select_open_contract_exec_docs%NOTFOUND;
EXIT WHEN select_all_contract_exec_docs%NOTFOUND;
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
end loop; /*select_stdpo */
IF (l_retroactive_update = 'OPEN_RELEASES') THEN
CLOSE select_open_contract_exec_docs;
CLOSE select_all_contract_exec_docs;
END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
/* Insert the values in the temp table po_retroprice_gt
* for each Blanket Line that we process.
*/
l_module := g_log_head||l_api_name||'.'||
'090'||'.';
FORALL insert_index in 1..g_row_id_table.COUNT
INSERT into po_retroprice_gt(
row_id,
new_price,
po_header_id,
po_release_id,
archived_revision_num,
authorization_status)
VALUES(
g_row_id_table(insert_index),
g_new_price_table(insert_index),
g_po_header_id_table(insert_index),
g_po_release_id_table(insert_index),
g_archived_rev_num_table(insert_index),
g_auth_status_table(insert_index));
/* Update PO shipments with the new Price */
-- Bulk Select
l_module := g_log_head||l_api_name||'.'||'100'||'.';
OPEN update_ship_price;
fetch update_ship_price BULK COLLECT INTO
l_temp_row_id_table,
l_temp_new_price_table
LIMIT G_BULK_LIMIT;
FORALL price_update_index in 1..l_temp_row_id_table.COUNT
UPDATE po_line_locations_all
SET price_override =
l_temp_new_price_table(price_update_index),
calculate_tax_flag = 'Y',
manual_price_change_flag = 'N', --
last_update_date = g_sysdate,
last_updated_by = g_user_id,
--
tax_attribute_update_code =
NVL(tax_attribute_update_code,'UPDATE')
--
WHERE rowid =
l_temp_row_id_table(price_update_index);
exit when update_ship_price%notfound;
CLOSE update_ship_price; /* 2857628 Close the cursor */
OPEN update_line_price;
fetch update_line_price BULK COLLECT INTO
l_temp_row_id_table,
l_temp_new_price_table
LIMIT G_BULK_LIMIT;
FORALL price_update_index in 1..l_temp_row_id_table.COUNT
UPDATE po_lines_all
SET unit_price =
l_temp_new_price_table(price_update_index),
manual_price_change_flag = 'N', --
last_update_date = g_sysdate,
last_updated_by = g_user_id,
--
tax_attribute_update_code =
NVL(tax_attribute_update_code,'UPDATE')
--
WHERE rowid =
l_temp_row_id_table(price_update_index);
FORALL price_update_index in 1..l_temp_row_id_table.COUNT
UPDATE po_line_locations_all poll
SET poll.price_override =
l_temp_new_price_table(price_update_index),
poll.calculate_tax_flag = 'Y',
poll.last_update_date = g_sysdate,
poll.last_updated_by = g_user_id,
--
tax_attribute_update_code =
NVL(tax_attribute_update_code,'UPDATE')
--
WHERE poll.po_line_id =
(select pll.po_line_id
from po_lines_all pll where
rowid=l_temp_row_id_table(price_update_index));
exit when update_line_price%notfound;
CLOSE update_line_price; /* 2857628 Close the cursor */
g_po_release_id_table.delete;
g_po_header_id_table.delete;
if (select_open_releases%ISOPEN) then
close select_open_releases;
if (select_open_stdpo%ISOPEN) then
close select_open_stdpo;
IF (select_all_stdpo%ISOPEN) THEN
CLOSE select_all_stdpo;
IF (select_all_releases%ISOPEN) THEN
CLOSE select_all_releases;
END MASSUPDATE_RELEASES;
* Category flexfields. This is called from massupdate_releases.
* Returns: x_item_cursor - Sql string which contains the WHERE clause
* to be used in getting the blanket line that is retroactively
* changed.
*/
PROCEDURE Build_Item_Cursor
( p_cat_structure_id IN NUMBER
, p_cat_from IN VARCHAR2
, p_cat_to IN VARCHAR2
, p_item_from IN VARCHAR2
, p_item_to IN VARCHAR2
, x_item_cursor IN OUT NOCOPY VARCHAR2
)
IS
l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
* Effects: If any release shipment is updated with the new price, then
* revision_num must be incremented and authorization_Status
* has to be updated to Requires approval if the status is
* Approved. This is called from massupdate_releases procedure.
*/
PROCEDURE WrapUp_Releases IS
l_global_arch_rev_num_table num_table;
SELECT distinct po_release_id,
nvl(authorization_status,'INCOMPLETE'),
nvl(archived_revision_num,-999)
BULK COLLECT INTO
g_po_release_id_table,
g_rel_auth_table,
l_global_arch_rev_num_table
FROM po_retroprice_gt prp
WHERE prp.po_release_id is not null;
* updated columns.
*/
-- Bug 5168776 Update the Revised Date also
l_module := g_log_head||l_api_name||'.'||'010'||'.';
FORALL doc_update_index in 1..g_po_release_id_table.COUNT
UPDATE po_releases por
SET por.revision_num = decode(por.revision_num,
l_global_arch_rev_num_table(doc_update_index),
por.revision_num +1,por.revision_num),
por.revised_date = decode(por.revision_num,
l_global_arch_rev_num_table(doc_update_index),
sysdate,por.revised_date),
por.authorization_status = decode(por.authorization_status,
'APPROVED', 'REQUIRES REAPPROVAL',
por.authorization_status),
por.approved_flag = decode(por.authorization_status,
'APPROVED','R',por.approved_flag),
por.last_update_date = g_sysdate,
por.last_updated_by = g_user_id
WHERE po_release_id = g_po_release_id_table(doc_update_index);
* Update approved_flag to 'R', last_update_date and
* last_updated_by columns in po_line_locations for which
* the price has been updated .
*/
l_module := g_log_head||l_api_name||'.'||'020'||'.';
SELECT row_id
BULK COLLECT INTO
l_row_id_table
FROM po_retroprice_gt prp
WHERE prp.po_release_id is not null
and nvl(authorization_status,'INCOMPLETE') = 'APPROVED';
FORALL release_update_index in 1..l_row_id_table.COUNT
UPDATE po_line_locations poll
SET poll.approved_flag = 'R',
poll.last_update_date = g_sysdate,
poll.last_updated_by = g_user_id
WHERE rowid = l_row_id_table(release_update_index);
* Effects: If any release shipment is updated with the new price, then
* revision_num must be incremented and authorization_Status
* has to be updated to Requires approval if the status is
* Approved. This is called from massupdate_releases procedure.
*/
PROCEDURE WrapUp_Standard_PO IS
l_global_arch_rev_num_table num_table;
SELECT distinct po_header_id,
nvl(authorization_status,'INCOMPLETE'),
archived_revision_num
BULK COLLECT INTO
g_po_header_id_table,
g_po_auth_table,
l_global_arch_rev_num_table
FROM po_retroprice_gt prp
WHERE prp.po_header_id is not null;
* we need to make the approved_flag in po_headers and the last updated
* columns.
*/
-- Bug 5168776 Update the Revised Date also
FORALL doc_update_index in 1..g_po_header_id_table.COUNT
UPDATE po_headers_all poh
SET poh.revision_num = decode(poh.revision_num,
l_global_arch_rev_num_table(doc_update_index),
poh.revision_num +1,poh.revision_num),
poh.revised_date = decode(poh.revision_num,
l_global_arch_rev_num_table(doc_update_index),
sysdate, poh.revised_date ),
poh.authorization_status = decode(poh.authorization_status,
'APPROVED', 'REQUIRES REAPPROVAL',
poh.authorization_status),
poh.approved_flag = decode(poh.authorization_status,
'APPROVED','R',poh.approved_flag),
poh.last_update_date = g_sysdate,
poh.last_updated_by = g_user_id
WHERE po_header_id = g_po_header_id_table(doc_update_index);
* Update approved_flag to 'R', last_update_date and
* last_updated_by columns in po_line_locations for which
* the price has been updated .
*/
l_module := g_log_head||l_api_name||'.'||'020'||'.';
SELECT row_id
BULK COLLECT INTO
l_row_id_table
FROM po_retroprice_gt prp
WHERE prp.po_header_id is not null
and nvl(authorization_status,'INCOMPLETE') = 'APPROVED';
FORALL ship_update_index in 1..l_row_id_table.COUNT
UPDATE po_line_locations_all poll
SET poll.approved_flag = 'R',
poll.last_update_date = g_sysdate,
poll.last_updated_by = g_user_id
WHERE poll.po_line_id =
(select pll.po_line_id
from po_lines_all pll where
rowid=l_row_id_table(ship_update_index));
* Modifies: updates the global variables with the release_id, revision_num
* from the archive table, authorization_status, row_id of the
* po_line_locations to be updated and the new price if it is different
* from the old price.
* Effects: Get the new price for the release shipment attributes and if
* different update the global variables.This is called from
* massupdate_releases procedure.
*/
PROCEDURE Process_Price_Change
(p_row_id IN VARCHAR2,
p_document_id IN NUMBER,
p_po_line_location_id IN NUMBER,
p_retroactive_date IN DATE,
p_quantity IN NUMBER,
p_ship_to_organization_id IN NUMBER,
p_ship_to_location_id IN NUMBER,
p_po_line_id IN NUMBER,
p_old_price_override IN NUMBER,
p_need_by_date IN DATE,
p_global_agreement_flag IN VARCHAR2,
p_authorization_status IN VARCHAR2,
p_rev_num IN Number,
p_archived_rev_num IN Number,
p_contract_id IN NUMBER) IS --
l_new_price_override number;
l_retroactive_update VARCHAR2(30) := 'NEVER';
select po_line_id
into l_po_line_id
from po_line_locations_all
where line_location_id = p_po_line_location_id ;
select po_line_id
into l_po_line_id
from po_line_locations_all
where line_location_id = p_po_line_location_id ;
l_retroactive_update := Get_Retro_Mode;
IF (l_retroactive_update = 'ALL_RELEASES') AND
(is_inv_org_period_open(l_std_po_price_change,
l_po_line_id,
p_po_line_location_id) = 'N')
THEN
l_error_message := 'Can not retroactively update price on a consumption '||
'advice, since the Inventory Org period is not open.';
IF l_retroactive_update = 'ALL_RELEASES'
AND (Is_Adjustment_Account_Valid(l_std_po_price_change,
l_po_line_id,
p_po_line_location_id) = 'N')
THEN
FND_MESSAGE.set_name('PO', 'PO_RETRO_PRICING_NOT_ALLOWED');
IF (l_retroactive_update = 'ALL_RELEASES' AND
(Is_Retro_Project_Allowed(l_std_po_price_change,
l_po_line_id,
p_po_line_location_id) = 'N'))
THEN
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,l_module,
'Can not update price since project 11i10 is not enabled');
PO_DEBUG.put_line('Can not update price since project 11i10 is not enabled');
END IF; /*IF (l_retroactive_update = 'ALL_RELEASES' AND*/
SELECT NVL(p_quantity,POL.quantity),
POL.from_line_id,
NVL(p_ship_to_location_id, PLL.ship_to_location_id),
NVL(p_need_by_date, NVL(PLL.need_by_date, sysdate)),
NVL(p_ship_to_organization_id,PLL.ship_to_organization_id),
POL.org_id,
POL.contract_id,
POL.po_header_id,
POL.po_line_id,
POL.creation_date,
POL.item_id,
POL.item_revision,
POL.category_id,
POL.line_type_id,
POL.vendor_product_num,
POH.vendor_id,
POH.vendor_site_id,
POL.unit_meas_lookup_code,
POL.base_unit_price,
POH.currency_code
INTO l_quantity,
l_from_line_id,
l_ship_to_location_id,
l_need_by_date,
l_ship_to_org_id,
l_org_id,
l_contract_id,
l_order_header_id,
l_order_line_id,
l_creation_date,
l_item_id,
l_item_revision,
l_category_id,
l_line_type_id,
l_vendor_product_num,
l_vendor_id,
l_vendor_site_id,
l_uom,
l_in_unit_price,
l_currency_code
FROM po_line_locations_all PLL, po_lines_all POL,
po_headers_all POH
WHERE PLL.line_location_id = p_po_line_location_id
AND POL.po_line_id = PLL.po_line_id
AND POH.po_header_id = POL.po_header_id;
* which has all the document ids that have been updated with
* new price. If the document is in the approved state, then
* call submission_check and if it is successful, initiate
* approval. This is called from massupdate_releases procedure.
*/
PROCEDURE Launch_PO_Approval IS
l_progress varchar2(3);
select org_id,
NVL(consigned_consumption_flag, 'N') -- Bug 3318625
into l_doc_org_id,
l_consigned_flag -- Bug 3318625
from po_headers_all
where po_header_id = g_po_header_id_table(i);
* which has all the document ids that have been updated with
* new price. If the document is in the approved state, then
* call submission_check and if it is successful, initiate
* approval. This is called from massupdate_releases procedure.
*/
PROCEDURE Launch_REL_Approval IS
l_progress varchar2(3);
select NVL(consigned_consumption_flag, 'N') -- Bug 3318625
into l_consigned_flag
from po_releases_all
where po_release_id = g_po_release_id_table(i);
* massupdate_releases procedure.
*/
Procedure Retroactive_Launch_Approval(
p_doc_id IN Number,
p_doc_type IN Varchar2,
p_doc_subtype IN Varchar2) IS
l_workflow_process varchar2(40) := null;
l_update_sourcing_rule varchar2(30) := null;
SELECT poh.org_id
into l_org_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_doc_id;
SELECT por.org_id
into l_org_id
FROM po_releases_all por
WHERE por.po_release_id = p_doc_id;
UpdateSourcingRule => l_update_sourcing_rule,
ReleaseGenMethod => l_rel_gen_method,
MassUpdateReleases => 'N',
--Bug 3574895. Retroactively updated releases were not
-- getting communicated to supplier
CommunicatePriceChange => g_communicate_update,
RetroactivePriceChange => 'Y');
l_retroactive_update VARCHAR2(30) := 'NEVER';
FND_PROFILE.get('PO_ALLOW_RETROPRICING_OF_PO',l_retroactive_update);
IF (l_retroactive_update IS NULL) THEN
l_retroactive_update := 'NEVER';
END IF; /* IF (l_retroactive_update IS NULL) */
PO_DEBUG.debug_var(l_log_head,l_progress,'l_retroactive_update', l_retroactive_update);
IF (l_retroactive_update = 'NEVER') THEN
RETURN l_retroactive_update;
END IF; /* IF (l_retroactive_update = 'NEVER') */
PO_DEBUG.debug_var(l_log_head,l_progress,'l_retroactive_update', l_retroactive_update);
IF (l_retroactive_update = 'ALL_RELEASES') THEN
-- Bug 3614598 START
-- Remove checking for inventory since it is now part of SCM
-- Use AD_VERSION_UTIL.get_product_patch_level instead of direct query
AD_VERSION_UTIL.get_product_patch_level
( p_appl_id => 200, -- AP
p_patch_level => l_ap_family_pack
);
l_retroactive_update := 'OPEN_RELEASES';
END IF; /* IF (l_retroactive_update = 'ALL_RELEASES') */
PO_DEBUG.debug_var(l_log_head,l_progress,'l_retroactive_update', l_retroactive_update);
return l_retroactive_update;
FUNCTION Is_Retro_Update(p_document_id IN NUMBER,
p_document_type IN VARCHAR2)
RETURN VARCHAR2
IS
l_retro_change VARCHAR2(1) := 'N';
SELECT 'Y'
INTO l_retro_change
FROM dual
WHERE EXISTS (SELECT 'retroactive pricing changes'
FROM po_line_locations poll,
po_line_locations_archive polla
WHERE poll.po_header_id = p_document_id
AND poll.po_header_id =polla.po_header_id
AND poll.line_location_id = polla.line_location_id
AND polla.latest_external_flag = 'Y'
AND poll.price_override <> polla.price_override);
SELECT 'Y'
INTO l_retro_change
FROM dual
WHERE EXISTS (SELECT 'retroactive pricing changes'
FROM po_line_locations poll,
po_line_locations_archive polla
WHERE poll.po_release_id = p_document_id
AND poll.po_header_id =polla.po_header_id
AND poll.line_location_id = polla.line_location_id
AND polla.latest_external_flag = 'Y'
AND poll.price_override <> polla.price_override);
END Is_Retro_Update;
PROCEDURE Reset_Retro_Update(p_document_id IN NUMBER,
p_document_type IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT varchar2(30) := 'Reset_Retro_Update';
UPDATE po_lines_all
SET retroactive_date = NULL,
last_update_date = SYSDATE,
last_updated_by = l_user_id
WHERE po_header_id = p_document_id;
UPDATE po_line_locations_all pll
SET retroactive_date = (SELECT pl.retroactive_date
FROM po_lines_all pl
WHERE pl.po_line_id = pll.po_line_id),
last_update_date = SYSDATE,
last_updated_by = l_user_id
WHERE pll.po_release_id = p_document_id;
END Reset_Retro_Update;
SELECT poll.po_header_id,
poll.po_release_id,
poll.po_line_id,
poll.line_location_id,
poll.quantity_billed,
poll.price_override new_price,
polla.price_override old_price
FROM po_line_locations poll,
po_line_locations_archive polla
WHERE poll.po_header_id = p_po_header_id
AND poll.po_release_id IS NULL
AND ((poll.accrue_on_receipt_flag = 'Y' AND
(poll.quantity_received > 0 OR
poll.quantity_billed > 0)) OR
NVL(poll.accrue_on_receipt_flag, 'N') = 'N') --
AND poll.line_location_id = polla.line_location_id
AND polla.latest_external_flag = 'Y'
AND poll.price_override <> polla.price_override; -- Bug 3526448
SELECT poll.po_header_id,
poll.po_release_id,
poll.po_line_id,
poll.line_location_id,
poll.quantity_billed,
poll.price_override new_price,
polla.price_override old_price
FROM po_line_locations poll,
po_line_locations_archive polla
WHERE poll.po_release_id = p_po_release_id
AND ((poll.accrue_on_receipt_flag = 'Y' AND
(poll.quantity_received > 0 OR
poll.quantity_billed > 0)) OR
NVL(poll.accrue_on_receipt_flag, 'N') = 'N') --
AND poll.line_location_id = polla.line_location_id
AND polla.latest_external_flag = 'Y'
AND poll.price_override <> polla.price_override; -- Bug 3526448
SELECT poh.po_header_id,
to_number(NULL) po_release_id, --
pol.from_header_id, --
poh.currency_code,
poh.rate_type,
poh.rate_date,
poh.rate,
pol.po_line_id,
pol.item_id inventory_item_id,
-- Bug 3393219, Consumption transaction owning org
-- fsp.inventory_organization_id organization_id,
pod.destination_organization_id organization_id,
poll.line_location_id,
poll.quantity_billed,
pol.unit_meas_lookup_code transaction_uom,
-- Bug 3314204, Pass Inventory API price in functional price
-- Bug 3303148, Include Non-Recovery Tax
-- Bug 3834275, added nvl to non recoverable tax
round((round(poll.price_override * poll.quantity,
l_base_curr_precision) +
nvl(pod.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
l_base_curr_precision) / poll.quantity new_price,
-- poll.price_override new_price,
round((round(polla.price_override * poll.quantity,
l_base_curr_precision) +
nvl(poda.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
l_base_curr_precision) / poll.quantity old_price,
-- polla.price_override old_price,
poll.quantity transaction_quantity,
round((round(poll.price_override * poll.quantity,
l_base_curr_precision) +
nvl(pod.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
l_base_curr_precision) / poll.quantity -
round((round(polla.price_override * poll.quantity,
l_base_curr_precision) +
nvl(poda.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
l_base_curr_precision) / poll.quantity transaction_cost,
-- poll.price_override - polla.price_override transaction_cost,
pod.po_distribution_id,
pod.project_id,
pod.task_id,
pod.accrual_account_id distribute_account_id
FROM po_headers poh,
po_lines_all pol, --
-- Bug 3393219, Consumption transaction owning org
-- financials_system_parameters fsp,
po_line_locations_all poll, --
po_line_locations_archive_all polla, --
po_distributions_all pod, --
-- Bug 3314204, 3303148
po_distributions_archive_all poda --
WHERE pol.po_header_id = p_po_header_id
AND pol.po_header_id = poh.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.line_location_id = pod.line_location_id
AND poll.line_location_id = polla.line_location_id
AND polla.latest_external_flag = 'Y'
-- Bug 3314204, 3303148
AND pod.po_distribution_id = poda.po_distribution_id
AND poda.latest_external_flag = 'Y'
AND poll.price_override <> polla.price_override; -- Bug 3526448
SELECT to_number(NULL) po_header_id, --
por.po_release_id,
poh.po_header_id from_header_id,
poh.currency_code,
poh.rate_type,
poh.rate_date,
poh.rate,
pol.po_line_id,
pol.item_id inventory_item_id,
-- Bug 3393219, Consumption transaction owning org
-- fsp.inventory_organization_id organization_id,
pod.destination_organization_id organization_id,
poll.line_location_id,
poll.quantity_billed,
pol.unit_meas_lookup_code transaction_uom,
-- Bug 3314204, Pass Inventory API price in functional price
-- Bug 3303148, Include Non-Recovery Tax
-- Bug 3834275, added nvl to non recoverable tax
round((round(poll.price_override * poll.quantity,
l_base_curr_precision) +
nvl(pod.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
l_base_curr_precision) / poll.quantity new_price,
-- poll.price_override new_price,
round((round(polla.price_override * poll.quantity,
l_base_curr_precision) +
nvl(poda.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
l_base_curr_precision) / poll.quantity old_price,
-- polla.price_override old_price,
poll.quantity transaction_quantity,
round((round(poll.price_override * poll.quantity,
l_base_curr_precision) +
nvl(pod.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
l_base_curr_precision) / poll.quantity -
round((round(polla.price_override * poll.quantity,
l_base_curr_precision) +
nvl(poda.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
l_base_curr_precision) / poll.quantity transaction_cost,
-- poll.price_override - polla.price_override transaction_cost,
pod.po_distribution_id,
pod.project_id,
pod.task_id,
pod.accrual_account_id distribute_account_id
FROM po_releases por,
po_headers_all poh, --
po_lines pol,
-- Bug 3393219, Consumption transaction owning org
-- financials_system_parameters fsp,
po_line_locations_all poll, --
po_line_locations_archive_all polla, --
po_distributions_all pod, --
-- Bug 3314204, 3303148
po_distributions_archive poda
WHERE por.po_release_id = p_po_release_id
AND por.po_release_id = poll.po_release_id
AND poll.po_header_id = poh.po_header_id
AND poll.po_line_id = pol.po_line_id
AND poll.line_location_id = pod.line_location_id
AND poll.line_location_id = polla.line_location_id
AND polla.latest_external_flag = 'Y'
-- Bug 3314204, 3303148
AND pod.po_distribution_id = poda.po_distribution_id
AND poda.latest_external_flag = 'Y'
AND poll.price_override <> polla.price_override; -- Bug 3526448
SELECT nvl(FND.precision, 2)
INTO l_base_curr_precision
FROM FND_CURRENCIES FND,
FINANCIALS_SYSTEM_PARAMETERS FSP,
GL_SETS_OF_BOOKS GSB
WHERE FSP.set_of_books_id = GSB.set_of_books_id AND
FND.currency_code = GSB.currency_code;
SELECT NVL(consigned_consumption_flag, 'N')
INTO l_consigned_flag
FROM PO_HEADERS
WHERE po_header_id = p_document_id;
SELECT NVL(consigned_consumption_flag, 'N')
INTO l_consigned_flag
FROM PO_RELEASES
WHERE po_release_id = p_document_id;
UPDATE po_distributions_all
SET invoice_adjustment_flag = 'R'
WHERE line_location_id = l_line_location_ids_tbl(i)
AND l_quantity_billeds_tbl(i) > 0;
'updated the invoice_adjustment_flag to R -- Rowcount: ' || SQL%ROWCOUNT);
l_po_header_ids_tbl.DELETE;
l_po_release_ids_tbl.DELETE;
l_po_line_ids_tbl.DELETE;
l_line_location_ids_tbl.DELETE;
l_quantity_billeds_tbl.DELETE;
l_new_prices_tbl.DELETE;
l_old_prices_tbl.DELETE;
UPDATE po_distributions
SET invoice_adjustment_flag = 'R'
WHERE po_distribution_id = l_distribution_ids_tbl(i)
AND l_quantity_billeds_tbl(i) > 0;
'updated the invoice_adjustment_flag to R -- Rowcount: ' || SQL%ROWCOUNT);
SELECT mum.uom_code
INTO l_uom_code
FROM mtl_units_of_measure mum
WHERE mum.unit_of_measure = l_transaction_uoms_tbl(i);
l_mtl_trx_rec.transaction_type_id := 20; -- Retroactive Price Update
l_mtl_trx_rec.transaction_action_id := 25; -- Retroactive Price Update
l_po_header_ids_tbl.DELETE;
l_po_release_ids_tbl.DELETE;
l_from_header_ids_tbl.DELETE;
l_currency_codes_tbl.DELETE;
l_rate_types_tbl.DELETE;
l_rate_dates_tbl.DELETE;
l_rates_tbl.DELETE;
l_po_line_ids_tbl.DELETE;
l_inventory_item_ids_tbl.DELETE;
l_organization_ids_tbl.DELETE;
l_line_location_ids_tbl.DELETE;
l_quantity_billeds_tbl.DELETE;
l_transaction_uoms_tbl.DELETE;
l_new_prices_tbl.DELETE;
l_old_prices_tbl.DELETE;
l_transaction_quantitys_tbl.DELETE;
l_transaction_costs_tbl.DELETE;
l_distribution_ids_tbl.DELETE;
l_project_ids_tbl.DELETE;
l_task_ids_tbl.DELETE;
l_dist_account_ids_tbl.DELETE;
l_mtl_trx_tbl.DELETE;
SELECT 'N'
INTO l_retro_proj_allowed
FROM DUAL
WHERE EXISTS (SELECT 'has project information'
FROM PO_LINE_LOCATIONS_ALL pll,
PO_DISTRIBUTIONS_ALL pod
WHERE pll.po_line_id = p_po_line_id
AND pod.line_location_id = pll.line_location_id
AND ((NVL(pll.quantity_received,0) > 0 AND
NVL(pll.accrue_on_receipt_flag,'N') = 'Y') OR
NVL(pll.quantity_billed,0) > 0)
AND pod.project_id IS NOT NULL);
SELECT 'N'
INTO l_retro_proj_allowed
FROM DUAL
WHERE EXISTS (SELECT 'has project information'
FROM PO_LINE_LOCATIONS_ALL pll,
PO_DISTRIBUTIONS_ALL pod
WHERE pll.line_location_id = p_po_line_loc_id
AND pod.line_location_id = pll.line_location_id
AND ((NVL(pll.quantity_received,0) > 0 AND
NVL(pll.accrue_on_receipt_flag,'N') = 'Y') OR
NVL(pll.quantity_billed,0) > 0)
AND pod.project_id IS NOT NULL);
select NVL(pll.quantity_received,0),
NVL(pll.accrue_on_receipt_flag,'N'),
NVL(pll.quantity_billed,0),
-- Bug 3541961
NVL(poh.consigned_consumption_flag,'N'),
pll.ship_to_organization_id,
poh.org_id,
pll.transaction_flow_header_id -- Bug 3880758
from po_line_locations_all pll,
-- Bug 3541961
po_headers_all poh
where pll.po_line_id = p_po_line_id
-- Bug 3541961
and pll.po_header_id = poh.po_header_id;
select NVL(pll.quantity_received,0),
NVL(pll.accrue_on_receipt_flag,'N'),
NVL(pll.quantity_billed,0),
NVL(por.consigned_consumption_flag,'N'),
pll.ship_to_organization_id,
por.org_id,
null -- Bug 3880758
from po_line_locations_all pll,
-- Bug 3541961
po_releases_all por
where pll.line_location_id = p_po_line_loc_id
-- Bug 3541961
and pll.po_release_id = por.po_release_id;
select retroprice_adj_account_id
into l_retroprice_adj_account_id
from rcv_parameters
where organization_id = l_ship_to_organization_id;
select retroprice_adj_account_id
into l_retroprice_adj_account_id
from rcv_parameters
where organization_id = l_logical_inv_org_id;
SELECT distinct 'Y'
INTO l_account_valid
FROM gl_code_combinations gcc,
gl_sets_of_books sob,
financials_system_params_all fsp
WHERE gcc.code_combination_id = l_retroprice_adj_account_id
AND gcc.enabled_flag = 'Y'
AND trunc(SYSDATE) BETWEEN
trunc(nvl(start_date_active,SYSDATE - 1) )
AND
trunc(nvl (end_date_active,SYSDATE + 1) )
AND gcc.detail_posting_allowed_flag = 'Y'
AND gcc.summary_flag = 'N'
AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
AND fsp.org_id = l_org_id -- Bug 3610693
AND sob.set_of_books_id = fsp.set_of_books_id;
SELECT NVL(poh.consigned_consumption_flag, 'N'),
pll.ship_to_organization_id,
pll.transaction_flow_header_id
FROM po_line_locations_all pll,
po_headers_all poh
WHERE pll.po_line_id = p_po_line_id
AND pll.po_header_id = poh.po_header_id;
SELECT NVL(por.consigned_consumption_flag, 'N'),
pll.ship_to_organization_id,
NULL
FROM po_line_locations_all pll,
po_releases_all por
WHERE pll.line_location_id = p_po_line_loc_id
AND pll.po_release_id = por.po_release_id;