This is workaround to cancel inprocess PO using below given script.
========================================================================
create or replace procedure apps.xx_cancel_inprocess_po
(p_po_num varchar2,
p_org_id number)
is
l_header_id number;
--
BEGIN
--
SELECT po_header_id into l_header_id
FROM po_headers_all WHERE segment1 =p_po_num;
--
UPDATE po_headers_all c
SET c.authorization_status ='approved',
c.approved_flag ='Y',
c.approved_date =SYSDATE,
c.cancel_flag ='Y',
c.closed_code ='closed',
c.closed_date =SYSDATE
WHERE segment1 =p_po_num
and org_id =p_org_id;
COMMIT;
--
UPDATE po_headers_archive_all c
SET c.authorization_status ='approved',
c.approved_flag ='Y',
c.approved_date =SYSDATE,
c.cancel_flag ='Y',
c.closed_code ='closed',
c.closed_date =SYSDATE
WHERE segment1 =p_po_num
and org_id =p_org_id;
--
UPDATE po_lines_all c
SET
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_lines_archive_all c
SET
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_line_locations_all c
SET
c.approved_date =SYSDATE,
c.approved_flag ='Y',
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_by=-1,
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_line_locations_archive_all c
SET
c.approved_date =SYSDATE,
c.approved_flag ='Y',
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_by=-1,
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
COMMIT;
--
END ;
/
=========================================================================
========================================================================
create or replace procedure apps.xx_cancel_inprocess_po
(p_po_num varchar2,
p_org_id number)
is
l_header_id number;
--
BEGIN
--
SELECT po_header_id into l_header_id
FROM po_headers_all WHERE segment1 =p_po_num;
--
UPDATE po_headers_all c
SET c.authorization_status ='approved',
c.approved_flag ='Y',
c.approved_date =SYSDATE,
c.cancel_flag ='Y',
c.closed_code ='closed',
c.closed_date =SYSDATE
WHERE segment1 =p_po_num
and org_id =p_org_id;
COMMIT;
--
UPDATE po_headers_archive_all c
SET c.authorization_status ='approved',
c.approved_flag ='Y',
c.approved_date =SYSDATE,
c.cancel_flag ='Y',
c.closed_code ='closed',
c.closed_date =SYSDATE
WHERE segment1 =p_po_num
and org_id =p_org_id;
--
UPDATE po_lines_all c
SET
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_lines_archive_all c
SET
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_line_locations_all c
SET
c.approved_date =SYSDATE,
c.approved_flag ='Y',
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_by=-1,
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_line_locations_archive_all c
SET
c.approved_date =SYSDATE,
c.approved_flag ='Y',
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_by=-1,
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
COMMIT;
--
END ;
/
=========================================================================
No comments:
Post a Comment