|
|
REM dbdrv: none
/*=======================================================================+
| Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA|
| All rights reserved. |
+=======================================================================*/
/*
------------------------------------
Data Manipulation Scripts Disclaimer
------------------------------------
As always please ask customer to run the scripts on their test instance
first before applying it on production. Make sure the data is validated
for correctness and related functionality is verified after the script
has been run on a test instance. Customer is responsible to authenticate
and verify correctness of data manipulation scripts.
*/
/* #1. Avoid using this script when an open notification is pending for response. If a valid
open notification exists for a requisition then try the notification first to respond to the requisition, Only if, for some reason the notification response is unsuccessful, Use this script */
/* Algorithm :
* The script picks up requisitions which are :
1. Not cancelled
2. Not finally closed
3. In statuses - 'IN PROCESS' or 'PRE-APPROVED'
* Performs the following checks before proceeding to reset the requisition:
1. If open notification is present, warn the user.
2. If any PO or SO is associated with the req then do not reset.
* If the above two checks pass, it aborts all the wf processes
associated with the approval.
* Updates action history.
* deletes the supply records.
* Updates the PO tables to set the status of the requisition correctly. */
set serveroutput on size 100000
prompt
prompt
accept sql_req_number prompt 'Please enter the Requisition number to reset : ';
accept sql_org_id default NULL prompt 'Please enter the organization id to which the Requisition belongs (Default NULL) : ';
prompt
declare
x_stmt varchar2(3000);
NAME_ALREADY_USED EXCEPTION;
PRAGMA Exception_Init(NAME_ALREADY_USED, -955);
begin
x_stmt := 'create table po_manual_postings_temp
(po_distribution_id number,
budget_account_id number,
debit_amount number,
timestamp date,
type varchar2(5),
account varchar2(285))';
EXECUTE immediate (x_stmt);
exception
WHEN NAME_ALREADY_USED then
dbms_output.put_line('table already exists, hence not creating again');
when others then
dbms_output.put_line('An exception occured creating po_manual_postings_temp table');
dbms_output.put_line(SQLCODE || '-' || SQLERRM);
dbms_output.put_line('Please contact Oracle Support');
rollback;
return;
end;
/
DECLARE
CURSOR reqtoreset (x_req_number varchar2, x_org_id number)
IS
SELECT wf_item_type, wf_item_key, requisition_header_id , segment1,
type_lookup_code
FROM po_requisition_headers_all h
WHERE h.segment1 = x_req_number
AND h.org_id = x_org_id
AND h.authorization_status in ('IN PROCESS','PRE-APPROVED')
AND NVL(h.closed_code, 'OPEN') <> 'FINALLY_CLOSED'
AND NVL(h.cancel_flag, 'N') = 'N';
cursor wfstoabort(st_item_type varchar2,st_item_key varchar2) is
select level,item_type,item_key,end_date
from wf_items
start with
item_type = st_item_type and
item_key = st_item_key
connect by
prior item_type = parent_item_type and
prior item_key = parent_item_key
order by level desc;
wf_rec wfstoabort%ROWTYPE;
TYPE enc_tbl_number is TABLE OF NUMBER;
TYPE enc_tbl_flag is TABLE OF VARCHAR2(1);
x_org_id number ;
x_req_number varchar2(15);
req_enc_flag varchar2(1);
x_open_notif_exist varchar2(1);
ros reqtoreset%ROWTYPE;
x_progress varchar2(500);
x_count_po_assoc number;
x_active_wf_exists varchar2(1);
l_tax NUMBER;
l_amount NUMBER;
nullseq number;
l_req_dist_id enc_tbl_number;
l_req_enc_flag enc_tbl_flag;
l_req_enc_amount enc_tbl_number;
l_req_gl_amount enc_tbl_number;
l_req_price enc_tbl_number;
l_req_dist_qty enc_tbl_number;
l_req_dist_rate enc_tbl_number;
l_manual_cand enc_tbl_flag;
g_po_debug VARCHAR2(1) := 'Y';
l_timestamp DATE := sysdate;
l_precision fnd_currencies.precision%type;
l_min_acc_unit fnd_currencies.minimum_accountable_unit%TYPE;
l_disallow_script VARCHAR2(1);
l_req_encumbrance VARCHAR2(1);
BEGIN
select &sql_org_id
into x_org_id
from dual;
select '&sql_req_number'
into x_req_number
from dual;
dbms_output.put_line ('req '||x_req_number||' in org '||x_org_id);
BEGIN
select 'Y'
into x_open_notif_exist
from dual
where exists (select 'open notifications'
from wf_item_activity_statuses wias,
wf_notifications wfn,
po_requisition_headers_all porh
where wias.notification_id is not null
and wias.notification_id = wfn.group_id
and wfn.status = 'OPEN'
and wias.item_type = 'REQAPPRV'
and wias.item_key = porh.wf_item_key
and porh.org_id = x_org_id
and porh.segment1=x_req_number
and porh.authorization_status IN ('IN PROCESS', 'PRE-APPROVED'));
EXCEPTION
when NO_DATA_FOUND then
null;
END;
IF (x_open_notif_exist = 'Y') THEN
dbms_output.put_line(' ');
dbms_output.put_line('An Open notification exists for this document, you may want to use the notification to process this document. Do not commit if you wish to use the notification');
END IF;
select count(*)
into x_count_po_assoc
from po_requisition_lines_all prl,
po_requisition_headers_all prh
where prh.segment1= x_req_number
and prh.org_id = x_org_id
and prh.requisition_header_id = prl.requisition_header_id
and (prl.line_location_id is not null or
nvl(prh.transferred_to_oe_flag,'N') = 'Y');
IF (x_count_po_assoc > 0) THEN
dbms_output.put_line('This requisition is associated with a PO or sales order and hence cannot be reset. Please contact Oracle support');
return;
END IF;
open reqtoreset(x_req_number, x_org_id);
fetch reqtoreset into ros;
if reqtoreset%NOTFOUND then
dbms_output.put_line('No such requisition with req number '||x_req_number||' exists which requires to be reset');
return;
end if;
IF (g_po_debug = 'Y') then
dbms_output.put_line('Processing '||ros.type_lookup_code
||' Req Number: '
||ros.segment1);
dbms_output.put_line('......................................'); --116
END IF;
begin
select 'Y'
into l_disallow_script
from dual
where exists (select 'dist with USSGL code'
from po_req_distributions_all prd,
po_requisition_lines_all prl
where prd.requisition_line_id = prl.requisition_line_id
AND prl.requisition_header_id = ros.requisition_header_id
and prd.ussgl_transaction_code is not null);
Exception
when NO_DATA_FOUND then
null;
end;
if l_disallow_script = 'Y' then
dbms_output.put_line('You have a public sector installation and USSGL transaction codes are used');
dbms_output.put_line('The reset script is not allowed in such a scenario, please contact Oracle Support');
CLOSE reqtoreset;
return;
end if;
/* abort workflow processes if they exists */
-- first check whether the wf process exists or not
begin
select 'Y'
into x_active_wf_exists
from wf_items wfi
where wfi.item_type = ros.wf_item_type
and wfi.item_key = ros.wf_item_key
and wfi.end_date is null;
exception
when NO_DATA_FOUND then
x_active_wf_exists := 'N';
end;
-- if the wf process is not already aborted then abort it.
if (x_active_wf_exists = 'Y') THEN
IF (g_po_debug = 'Y') then
dbms_output.put_line('Aborting Workflow...');
END IF;
open wfstoabort(ros.wf_item_type,ros.wf_item_key);
loop
fetch wfstoabort into wf_rec;
IF (g_po_debug = 'Y') then
dbms_output.put_line(wf_rec.item_type||wf_rec.item_key);
END IF;
if wfstoabort%NOTFOUND then
close wfstoabort;
exit;
end if;
if (wf_rec.end_date is null) then
BEGIN
WF_Engine.AbortProcess(wf_rec.item_type, wf_rec.item_key);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Could not abort the workflow for PO :'
||ros.segment1 ||' Please contact Oracle Support ');
rollback;
return;
END;
end if;
end loop;
end if;
/* Update the authorization status of the requisition to incomplete */
IF (g_po_debug = 'Y') then
dbms_output.put_line('Updating Requisition Status...');
END IF;
UPDATE po_requisition_headers_all
set authorization_status = 'INCOMPLETE',
wf_item_type = NULL,
wf_item_key = NULL
where requisition_header_id = ros.requisition_header_id;
/* Update Action history setting the last null action code to NO ACTION */
IF (g_po_debug = 'Y') then
dbms_output.put_line('Updating PO Action History...');
END IF;
SELECT nvl(max(sequence_num), 0)
into nullseq
FROM po_action_history
WHERE object_type_code = 'REQUISITION'
AND object_sub_type_code = ros.type_lookup_code
AND object_id = ros.requisition_header_id
AND action_code is NULL;
Update po_action_history
set action_code = 'NO ACTION',
action_date = trunc(sysdate),
note = 'updated by reset script on '||to_char(trunc(sysdate))
WHERE object_id = ros.requisition_header_id
AND object_type_code = 'REQUISITION'
AND object_sub_type_code = ros.type_lookup_code
AND sequence_num = nullseq
AND action_code is NULL;
SELECT NVL(req_encumbrance_flag ,'N')
INTO l_req_encumbrance
FROM financials_system_params_all
WHERE org_id = x_org_id;
IF l_req_encumbrance = 'N' then
dbms_output.put_line('Done Processing.');
dbms_output.put_line('................');
dbms_output.put_line('Please issue commit, if no errors found.');
RETURN;
END IF;
/****************************************************************************************
Encumbrance actions:
1. Find out distributions that are reserved, and have the encumbrance entries correct.
2. Not to consider the delivered quantities, since the reqs which have been autocreated to
OM / PO should not be considered.
****************************************************************************************/
IF (g_po_debug = 'Y') then
dbms_output.put_line('starting Encumbrance actions .....req '||ros.segment1||' header id'||ros.requisition_header_id);
END IF;
/***************************************************************************************
get the currency code and precision
***************************************************************************************/
select fc.precision, fc.minimum_accountable_unit
into l_precision, l_min_acc_unit
from fnd_currencies fc,
gl_sets_of_books gsob,
financials_system_params_all fspa
where fspa.org_id = x_org_id
and fspa.set_of_books_id = gsob.set_of_books_id
and gsob.currency_code = fc.currency_code;
select prd.distribution_id,
nvl(prd.encumbered_flag, 'N'),
nvl(prd.encumbered_amount, '0'),
sum(nvl(gl.entered_dr, 0) - nvl(gl.entered_cr, 0)),
'N', -- initialize manual postings flag,
prl.unit_price,
prd.req_line_quantity
BULK COLLECT
INTO l_req_dist_id,
l_req_enc_flag,
l_req_enc_amount,
l_req_gl_amount,
l_manual_cand,
l_req_price,
l_req_dist_qty -- 229
from po_req_distributions_all prd,
gl_je_lines gl ,
gl_je_headers glh ,
po_requisition_lines_all prl
where prd.requisition_line_id = prl.requisition_line_id
AND gl.reference_3(+) = to_char(prd.distribution_id)
AND gl.reference_1(+) = 'REQ'
AND nvl(prd.prevent_encumbrance_flag, 'N') = 'N'
AND prd.ussgl_transaction_code is null
AND prl.requisition_header_id = ros.requisition_header_id
and glh.je_header_id = gl.je_header_id
and glh.je_category = 'Requisitions'
and glh.je_source = 'Purchasing'
and glh.actual_flag = 'E'
GROUP BY prd.distribution_id,
prd.encumbered_flag,
prd.encumbered_amount,
prl.unit_price,
prd.req_line_quantity;
IF (g_po_debug = 'Y') then
dbms_output.put_line('No of distributions found '||l_req_dist_id.count);
END IF;
if l_req_dist_id.count <> 0 then
for i in 1 .. l_req_dist_id.count loop
dbms_output.put_line('checking dist '|| i|| '- id :'||l_req_dist_id(i));
l_tax := po_tax_sv.get_tax('REQ', l_req_dist_id(i));
l_amount := l_tax +
l_req_price(i) * l_req_dist_qty(i);
if (l_min_acc_unit is NULL) then
l_amount := round(l_amount, l_precision);
else
l_amount := round(l_amount / l_min_acc_unit) * l_min_acc_unit;
end if;
-- debug <start>
IF (g_po_debug = 'Y') then
dbms_output.put_line('dist '|| i|| '- id :'||l_req_dist_id(i)||' tax : '||l_tax);
dbms_output.put_line('dist '|| i|| '- id :'||l_req_dist_id(i)||' amount : '||l_amount);
dbms_output.put_line('dist '|| i|| '- id :'||l_req_dist_id(i)||'gl amt : '||l_req_gl_amount(i));
dbms_output.put_line('dist '|| i|| '- id :'||l_req_dist_id(i)||
'dist enc amt : '||l_req_enc_amount(i));
-- debug <end..>
END IF;
if (l_req_gl_amount(i) <> 0) and (l_req_gl_amount(i) <> l_amount) then
l_manual_cand(i) := 'Y';
if l_req_enc_amount(i) <> l_amount then
l_req_enc_amount(i) := l_amount;
end if;
if l_req_enc_flag(i) <> 'Y' then
l_req_enc_flag(i) := 'Y';
end if;
elsif (l_req_gl_amount(i) <> 0) and (l_req_gl_amount(i) = l_amount)
and (l_req_enc_amount(i) <> l_amount) then
l_req_enc_amount(i) := l_amount;
if l_req_enc_flag(i) <> 'Y' then
l_req_enc_flag(i) := 'Y';
end if;
elsif (l_req_gl_amount(i) = 0) and (l_req_enc_amount(i)<> 0) then
l_req_enc_amount(i):= 0;
l_req_enc_flag(i) := 'N';
end if;
end loop;
end if;
forall upd in 1 .. l_req_dist_id.count
update po_req_distributions_all
set encumbered_flag = l_req_enc_flag(upd),
encumbered_amount = l_req_enc_amount(upd)
where distribution_id = l_req_dist_id(upd);
forall k in 1 .. l_req_dist_id.count
insert into po_manual_postings_temp
(po_distribution_id,
budget_account_id,
debit_amount,
timestamp,
type,
account)
select prd.distribution_id,
prd.budget_account_id,
nvl(prd.encumbered_amount, 0) -
sum(nvl(entered_dr, 0) - nvl(entered_cr, 0)),
L_timestamp,
'REQ',
glcck.concatenated_segments
from po_req_distributions_all prd,
gl_je_lines gl,
gl_je_headers glh,
gl_code_combinations_kfv glcck
where gl.reference_3(+) = to_char(prd.distribution_id)
and gl.reference_1(+) = 'REQ'
and prd.distribution_id = l_req_dist_id(k)
and nvl(l_manual_cand(k), 'N') = 'Y'
and prd.budget_account_id = glcck.code_combination_id
and glh.je_header_id = gl.je_header_id
and glh.je_category = 'Requisitions'
and glh.je_source = 'Purchasing'
and glh.actual_flag = 'E'
group by distribution_id,
prd.budget_account_id,
prd.encumbered_amount,
L_timestamp,
glcck.concatenated_segments;
close reqtoreset;
dbms_output.put_line('Done Processing.');
dbms_output.put_line('................');
dbms_output.put_line('Please issue commit, if no errors found.');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('some exception occured '||sqlerrm||' rolling back'||x_progress);
rollback;
close reqtoreset;
return;
END;
/ |
|
楼主: yaohua0214
IP卡
狗仔卡
发表于 2007-12-3 17:53
显身卡
发表于 2008-2-28 14:45
