Fix Encumbrance Amount
|
5 min read
Week Of: 2022-08-21
Problems solved by this DATAFIX
If there is some canceled of finally closed PO that still have an Active encumbered amount, this script will free them up
Hoe to run
Phase 1 - Collect the data and identify the problematic PO
run it from SQL-Plus or equivalent tool:
REM dbdrv: none
/*<mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark>===+
| Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA|
| All rights reserved. |
+<mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark>===*/
/* This script is applicable for R12 and above versions
File : Data Collection Script to address incorrect encumbrance amount on
a Purchase Order.
Description : Encumbrance amount in PO Distribution is Incorrect. Collection
script will pick all such Distributions for a specific Org.
Table : Po_Session_Gt
Column : Index_Char1 UPDT_OPEN/UPDT_CANCEL/UPDT_FINALCLOSE
Key Po Distribution Id
Num1 Po Header Id
Num2 Set Of Books Id
Num3 Budget Account Id
Num4 Org Id
Num5 Correct Encumbered Amount
Num6 PO Encumbered Amount
Num7 Rel Number
Num8 Line Number
Num9 Shipment Number
Num10 Dist Number
Char1 Po Number
Char2 Shipment Type
Date1 Encumbered Date
History:
Date Action By Description
21-Jan-2009 Created VRECHARL
29-Jul-2011 Modified DTOSHNIW Modified to work for amount based lines also.
23-Apr-2013 Modified JYOTHI Modified to considered cancelled quantity for finally
closed PO.
30-Mar-2015 Modified VERECHAR Consider only approved shipment's distributions as
part of OPEN collection.
Sql to retrieve data from Session Specific Table for latter Reference :
SELECT g.Key Po_Distribution_Id ,
g.Char1 PONumber ,
g.Num7 Release_Num ,
g.Num8 Line_Num ,
g.Num9 Shipment_Num ,
g.Num10 Distribution_Num ,
g.Num4 Org_Id ,
g.Num2 Set_Of_Books_Id ,
g.Num3 Budget_Account_Id ,
g.Num5 Correct_Encumbered_Amount,
g.Num6 PO_Encumbered_Amount ,
g.Char2 Shipment_Type
FROM Po_Session_Gt g
WHERE g.Index_Char1 IN ('UPDT_OPEN','UPDT_CANCEL','UPDT_FINALCLOSE');
*/
SET SERVEROUTPUT ON
SET VERIFY OFF;
PROMPT
PROMPT
accept sql_po_number prompt 'Please enter the PO number : ';
--accept sql_release_number prompt 'Please enter the Release number (Default value is NULL): ';
accept sql_org_id default NULL prompt 'Please enter the organization id to which the PO belongs (Default NULL) : ';
accept sql_cur_precision default NULL prompt 'Please enter the currency precision (Default value is 2) : ';
PROMPT
DECLARE
l_cur_precision NUMBER := 2;
l_organization_id number ;
l_po_number varchar2(100);
l_release_number varchar2(15);
BEGIN
--#1
DELETE
FROM Po_Session_Gt
WHERE Index_Char1 IN ('UPDT_OPEN','UPDT_CANCEL','UPDT_FINALCLOSE');
select &sql_po_number
into l_po_number
from dual;
select &sql_org_id
into l_organization_id
from dual;
select &sql_cur_precision
into l_cur_precision
from dual;
IF l_cur_precision IS NULL THEN
l_cur_precision := 2;
END IF;
--#2
/* Open Shipments */
INSERT
INTO po_Session_gt
(
Index_Char1 -- UPDT_OPEN/UPDT_CANCEL/UPDT_FINALCLOSE
,
KEY -- Po Distribution Id
,
Num1 -- Po Header Id
,
Num2 -- Set Of Books Id
,
Num3 -- Budget Account Id
,
Num4 -- Org Id
,
Num5 -- Correct Encumbered Amount
,
Num6 -- PO Encumbered Amount
,
Char2 -- Shipment Type
,
Date1 -- Encumbered Date
)
SELECT ENC_OUT.* FROM
( SELECT 'UPDT_OPEN' ,
d.po_Distribution_Id ,
d.po_header_id ,
d.SET_OF_BOOKS_ID ,
d.Budget_Account_id ,
d.org_id ,
Decode( pll.matching_basis,
'AMOUNT', Round ((Nvl(d.amount_ordered,0)+NVL(D.NONRECOVERABLE_TAX,0)) * NVL(D.RATE,1), l_cur_precision),
ROUND(L.PRICE_OVERRIDE * D.QUANTITY_ORDERED * NVL(D.RATE,1) +(NVL(D.NONRECOVERABLE_TAX,0)*NVL(D.rate,1)), l_cur_precision)
)
AS CORRECT_ENCUMBERED_AMOUNT,
d.Encumbered_Amount ,
l.Shipment_Type ,
d.Gl_Encumbered_Date
FROM po_Line_Locations_All l,
po_Distributions_All d,
po_headers_all h,
po_lines_all pll
WHERE l.Line_Location_Id = d.Line_Location_Id
AND h.po_header_id = d.po_header_id
AND pll.po_line_id = l.po_line_id
AND Nvl(l.Cancel_Flag,'N') = 'N'
AND Nvl(l.Closed_Code,'OPEN') <> 'FINALLY CLOSED'
AND Nvl(d.Prevent_Encumbrance_Flag,'N') = 'N'
AND d.Ussgl_Transaction_Code IS NULL
AND d.Budget_Account_id IS NOT NULL
AND h.segment1 = l_po_number
AND h.org_id = l_organization_id
AND l.Shipment_Type IN ('SCHEDULED', 'STANDARD', 'BLANKET')
-- Bug#20641580: Consider only approved shipment's distributions as part of OPEN collection.
AND Nvl(l.approved_flag,'N') = 'Y'
) ENC_OUT
WHERE Nvl(ENC_OUT.CORRECT_ENCUMBERED_AMOUNT,0) <> ROUND(NVL(ENC_OUT.Encumbered_Amount,0),l_cur_precision)
UNION
SELECT ENC_OUT.* FROM
( SELECT 'UPDT_CANCEL',
d.po_Distribution_Id,
d.po_Header_Id,
d.Set_Of_Books_Id,
d.Budget_Account_Id,
d.Org_Id,
decode( pll.matching_basis,
'AMOUNT',
/***********ENC CALCULATION FOR AMOUNT BASED LINES***************/
Round(
Least(
d.amount_Ordered,
DECODE(d.accrue_on_receipt_flag,
'Y',Nvl(d.amount_delivered,0),
(Nvl(d.amount_Ordered,0) - Nvl(d.amount_cancelled,0)))
)
* Nvl(d.rate,1) *
(1 + (Nvl(d.nonrecoverable_tax,0) / decode(d.amount_Ordered,0,1,d.amount_ordered))),
l_cur_precision) ,
/*************ENC CALCULATION FOR AMOUNT BASED LINES END************/
/********ENC CALCULATION FOR NON AMOUNT BASED LINES**************/
Round(
Least(d.quantity_ordered,
DECODE(d.accrue_on_receipt_flag,
'Y',Nvl(d.quantity_delivered,0),
(Nvl(d.quantity_ordered,0) - Nvl(d.quantity_cancelled,0)))
)
* Nvl(d.rate,1) * (l.price_override + (Nvl(d.nonrecoverable_tax,0) / decode (d.Quantity_Ordered,0,1,d.Quantity_Ordered))),
l_cur_precision)
/********ENC CALCULATION FOR NON AMOUNT BASED LINES END**************/
) Correct_Encumbered_amount,
d.Encumbered_Amount,
l.Shipment_Type,
d.gl_Encumbered_Date
FROM po_Line_Locations_All l,
po_Distributions_All d,
po_Headers_All h,
po_lines_all pll
WHERE l.Line_Location_Id = d.Line_Location_Id
AND h.po_Header_Id = d.po_Header_Id
AND pll.po_line_id = l.po_line_id
AND Nvl(l.Approved_Flag,'N') = 'Y'
AND Nvl(l.Cancel_Flag,'N') = 'Y'
AND Nvl(l.Closed_Code,'OPEN') <> 'FINALLY CLOSED'
AND Nvl(d.Prevent_Encumbrance_Flag,'N') = 'N'
AND d.Ussgl_Transaction_Code IS NULL
AND d.Budget_Account_id IS NOT NULL
AND h.Segment1 = l_po_Number
AND h.Org_Id = l_Organization_Id
AND l.Shipment_Type IN ('SCHEDULED', 'STANDARD', 'BLANKET')
) ENC_OUT
WHERE Nvl(ENC_OUT.CORRECT_ENCUMBERED_AMOUNT,0) <> ROUND(NVL(ENC_OUT.Encumbered_Amount,0),l_cur_precision)
UNION
SELECT ENC_OUT.* FROM
( SELECT 'UPDT_FINALCLOSE',
d.po_Distribution_Id,
d.po_Header_Id,
d.Set_Of_Books_Id,
d.Budget_Account_Id,
d.Org_Id,
decode( pll.matching_basis,
'AMOUNT',
/*CALCULATION OF ENC AMOUNT FOR AMOUNT BASED LINES--------START*/
Round(
DECODE(
d.Accrue_On_Receipt_Flag,
'N',Least((NVL(d.amount_Ordered,0)- NVL(d.amount_cancelled,0) ),Nvl(d.amount_Billed,0)),
'Y',Least(NVL(d.Amount_Ordered,0),Nvl(d.Amount_Delivered,0))
) *
Nvl(d.Rate,1)
+
Nvl(d.NonRecoverable_Tax,0) * Nvl(d.Rate,1) *
DECODE(d.Accrue_On_Receipt_Flag,
'N',Least((NVL(d.amount_Ordered,0)- NVL(d.amount_cancelled,0) ),Nvl(d.Amount_Billed,0)),
'Y',Least(nvl(d.Amount_Ordered,0),Nvl(d.Amount_Delivered,0))
) / decode (d.amount_Ordered,0,1,d.amount_Ordered),
l_cur_precision) ,
/*CALCULATION OF ENC AMOUNT FOR AMOUNT BASED LINES--------END*/
/*CALCULATION OF ENC AMOUNT FOR non AMOUNT BASED LINES--------START*/
Round(
l.Price_Override *
DECODE(
d.Accrue_On_Receipt_Flag,
'N',Least((d.Quantity_Ordered-NVL(d.quantity_cancelled,0)),Nvl(d.Quantity_Billed,0)),
'Y',Least(d.Quantity_Ordered,Nvl(d.Quantity_Delivered,0))
) *
Nvl(d.Rate,1)
+
Nvl(d.NonRecoverable_Tax,0) * Nvl(d.Rate,1) *
DECODE(d.Accrue_On_Receipt_Flag,
'N',Least((d.Quantity_Ordered-NVL(d.quantity_cancelled,0)),Nvl(d.Quantity_Billed,0)),
'Y',Least(d.Quantity_Ordered,Nvl(d.Quantity_Delivered,0))
) / decode (d.Quantity_Ordered,0,1,d.Quantity_Ordered) ,
l_cur_precision)
/*CALCULATION OF ENC AMOUNT FOR non AMOUNT BASED LINES--------END*/
) AS CORRECT_ENCUMBERED_AMOUNT ,
d.Encumbered_Amount,
l.Shipment_Type,
d.gl_Encumbered_Date
FROM po_Line_Locations_All l,
po_Distributions_All d,
po_Headers_All h,
po_lines_all pll
WHERE l.Line_Location_Id = d.Line_Location_Id
AND h.po_Header_Id = d.po_Header_Id
AND pll.po_line_id = l.po_line_id
AND Nvl(l.Approved_Flag,'N') = 'Y'
AND Nvl(l.Closed_Code,'OPEN') = 'FINALLY CLOSED'
AND Nvl(d.Prevent_Encumbrance_Flag,'N') = 'N'
AND d.Ussgl_Transaction_Code IS NULL
AND d.Budget_Account_id IS NOT NULL
AND h.Segment1 = l_po_Number
AND h.Org_Id = l_Organization_Id
AND l.Shipment_Type IN ('SCHEDULED', 'STANDARD', 'BLANKET')
) ENC_OUT
WHERE Nvl(ENC_OUT.CORRECT_ENCUMBERED_AMOUNT,0) <> ROUND(NVL(ENC_OUT.Encumbered_Amount,0),l_cur_precision);
dbms_output.put_line('Done with Inserting the Candidate Data into PO Sesion GT. '||SQL%ROWCOUNT);
--#3
FOR crec IN
(SELECT g.key po_distribution_id ,
h.Segment1 PO_Number ,
r.release_num ,
l.line_num ,
s.shipment_num ,
d.distribution_num
FROM po_session_gt g ,
po_headers_all h ,
po_lines_all l ,
po_line_locations_all s,
po_distributions_all d ,
po_releases_all r
WHERE g.Index_Char1 IN ('UPDT_OPEN','UPDT_CANCEL','UPDT_FINALCLOSE')
AND d.po_distribution_id = g.key
AND d.line_location_id = s.line_location_id
AND d.po_line_id = l.po_line_id
AND d.po_header_id = h.po_header_id
AND d.po_release_id = r.po_release_id(+)
)
LOOP
UPDATE PO_Session_Gt
SET Char1 = crec.Po_Number ,
Num7 = crec.Release_Num ,
Num8 = crec.Line_Num ,
Num9 = crec.Shipment_Num,
Num10 = crec.Distribution_Num
WHERE KEY = Crec.Po_Distribution_Id
AND Index_Char1 IN ('UPDT_OPEN','UPDT_CANCEL','UPDT_FINALCLOSE');
END LOOP;
Dbms_output.put_line('Done with Updating the Distribution details in PO Sesion GT.');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Data Collection : '||SQLERRM||SQLCODE);
END;
/
SELECT g.KEY Po_Distribution_Id,
g.Char1 PONumber,
g.Num7 Release_Num,
g.Num8 Line_Num,
g.Num9 Shipment_Num,
g.Num10 Distribution_Num,
g.Num4 Org_Id,
g.Num2 Set_Of_Books_Id,
g.Num3 Budget_Account_Id,
g.Num5 Correct_Encumbered_Amount,
g.Num6 PO_Encumbered_Amount,
g.Char2 Shipment_Type,
g.Index_Char1
FROM Po_Session_Gt g
WHERE g.Index_Char1 IN ('UPDT_OPEN','UPDT_CANCEL','UPDT_FINALCLOSE');Phase 2 - Commit the correctness
After reviewing & approving the records of the table Po_Session_Gt, run:
REM dbdrv: none
/*<mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark>===+
| Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA|
| All rights reserved. |
+<mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark><mark></mark>===*/
/* This script is applicable for R12 and above versions */
/*
File : Data fix Script to address incorrect encumbrance amount on
an Purchase Order.
Description : Encumbrance amount in PO Distribution is Incorrect. Fix script
will fix all the data collected before by POXENC_v1.sql
Table : Po_Session_Gt
Column : Index_Char1 UPDT_OPEN/UPDT_CANCEL/UPDT_FINALCLOSE/UPDT_CANCEL_FINALCLOSE
Key Po Distribution Id
Num1 Po Header Id
Num2 Set Of Books Id
Num3 Budget Account Id
Num4 Org Id
Num5 Correct Encumbered Amount
Num6 PO Encumbered Amount
Num7 Rel Number
Num8 Line Number
Num9 Shipment Number
Num10 Dist Number
Char1 Po Number
Char2 Shipment Type
Date1 Encumbered Date
History:
Date Action By Description
16-Jun-2007 Created CVARDIA
*/
PROMPT ************************************
PROMPT Data Manipulation Scripts Disclaimer
PROMPT ************************************
PROMPT As always please run the scripts on test instance first before applying it
PROMPT on production. Make sure the data is validated for correctness and related
PROMPT functionality is verified after the script has been run on a test instance.
PROMPT Customers are responsible to authenticate and verify correctness of data
PROMPT updated by data manipulation scripts.
PROMPT
SET SERVEROUTPUT ON
SET VERIFY OFF;
BEGIN
FOR crec IN (SELECT g.KEY po_Distribution_Id,
g.Index_Char1 Index_Char1,
g.Char1 pOnUmber,
g.num7 Release_num,
g.num8 Line_num,
g.num9 Shipment_num,
g.num10 Distribution_num,
g.num4 Org_Id,
g.num2 Set_Of_Books_Id,
g.num3 Budget_Account_Id,
g.num5 Correct_Encumbered_Amount,
g.num6 po_Encumbered_Amount,
g.Char2 Shipment_Type
FROM po_Session_gt g
WHERE g.Index_Char1 IN ('UPDT_OPEN',
'UPDT_CANCEL',
'UPDT_FINALCLOSE',
'UPDT_CANCEL_FINALCLOSE'))
LOOP
UPDATE po_Distributions_All d
SET Encumbered_Amount = crec.Correct_Encumbered_Amount,
Encumbered_Flag = DECODE(crec.Index_Char1,'UPDT_OPEN','Y','N')
WHERE d.po_Distribution_Id = crec.Po_Distribution_Id;
END LOOP;
dbms_Output.Put_Line('Done Updating the Encumbered Amount on PO Distributions');
EXCEPTION
WHEN OTHERS THEN
dbms_Output.Put_Line('Error :: '
||SQLCODE
||'::'
||SQLERRM);
ROLLBACK;
END;
/