Scribd Logo
Upload

Welcome to Scribd!

  • 232 views

    Po Cancellation Script

    Uploaded by

    Jithesh Pavithran
    The document provides SQL queries to: 1) Select purchase order header and line information for a given PO number. 2) Backup all data for a given PO header ID. 3) Update fields in multiple purchase order tables to cancel the entire PO and set it to a closed status.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as TXT, PDF, TXT or read online from Scribd

    Po Cancellation Script

    Uploaded by

    Jithesh Pavithran
    232 views2 pages
    The document provides SQL queries to: 1) Select purchase order header and line information for a given PO number. 2) Backup all data for a given PO header ID. 3) Update fields in multiple purchase order tables to cancel the entire PO and set it to a closed status.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides SQL queries to: 1) Select purchase order header and line information for a given PO number. 2) Backup all data for a given PO header ID. 3) Update fields in multiple purchase order tables to cancel the entire PO and set it to a closed status.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    232 views2 pages

    Po Cancellation Script

    Uploaded by

    Jithesh Pavithran
    The document provides SQL queries to: 1) Select purchase order header and line information for a given PO number. 2) Backup all data for a given PO header ID. 3) Update fields in multiple purchase order tables to cancel the entire PO and set it to a closed status.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 2

    select po_header_id, org_id from po_headers_all where segment1 = '&po_number'; select quantity, quantity_received, quantity_billed, encumbered_flag from po_line_locations_all

    where org_id = &org_id and po_header_id in (select po_header_id from po_headers_all where segment1 = '&po_number'); please make sure: quantity_received = 0 or null quantity_billed = 0 or null encumbered_flag = N or null Backup the information: ------------------------------------select * from po_headers_all where po_header_id = &po_header_id; select * from po_lines_all where po_header_id = &po_header_id; select * from po_line_locations_all where po_header_id = &po_header_id; select * from po_distributions_all where po_header_id = &po_header_id; select * from po_headers_archive_all where po_header_id = &po_header_id; select * from po_lines_archive_all where po_header_id = &po_header_id; select * from po_line_locations_archive_all where po_header_id =&po_header_id; select * from po_distributions_archive_all where po_header_id = &po_header_id; select * from mtl_supply where po_header_id = &po_header_id; ----------------------------------Here is the data fix to set the entire po cancelled and closed. update po_headers_all set cancel_flag = 'Y' , closed_code = 'CLOSED', closed_date = sysdate, authorization_status = 'APPROVED' where po_header_id =&po_header_id; update po_headers_archive_all set cancel_flag = 'Y' , closed_code = 'CLOSED', closed_date = sysdate, authorization_status = 'APPROVED' where po_header_id = &po_header_id

    and latest_external_flag = 'Y'; update po_lines_all set cancel_flag = 'Y' , cancelled_by = 1 , cancel_date = sysdate , closed_code = 'CLOSED' , closed_date = sysdate where po_header_id = &po_header_id; update po_lines_archive_all set cancel_flag = 'Y' , cancelled_by = 1 , cancel_date = sysdate , closed_code = 'CLOSED' , closed_date = sysdate where po_header_id = &po_header_id and latest_external_flag = 'Y'; update po_line_locations_all set cancel_flag = 'Y' , cancelled_by = 1 , cancel_date = sysdate , quantity_cancelled = quantity , closed_code = 'CLOSED' , closed_date = sysdate where po_header_id = &po_header_id; update po_line_locations_archive_all set cancel_flag = 'Y' , cancelled_by = 1 , cancel_date = sysdate , quantity_cancelled = quantity , closed_code = 'CLOSED' , closed_date = sysdate where po_header_id = &po_header_id and latest_external_flag = 'Y'; update po_distributions_all set quantity_cancelled = quantity_ordered where po_header_id = &po_header_id; update po_distributions_archive_all set quantity_cancelled = quantity_ordered where po_header_id = &po_header_id and latest_external_flag = 'Y'; delete from mtl_supply where po_header_id = &po_header_id; commit;

    You might also like