Wednesday, June 29, 2011

API to delete purchase Orders

API "po_headers_sv1.delete_po" can be used to delete POs.

Parameters :
            X_po_header_id     => PO Header ID
            X_type_lookup_code => Type Lookup Code
            -- STANDARD/PLANNED/BLANKET/CONTRACT
            p_skip_validation  => N/Y
p_skip_validation -> By default it is 'N', if it is 'Y', It checks the encumbered flag in po distribution table against the po header.

Note:- INCOMPLETE POs can be deleted from Purchase Order form itself.
       Though this API can delete Approved POs as well, it is recomended that use this API to delete only INCOMPLETE Pos, because APPROVED POs have entry in inventory and archive tables and this API does not delete the related entries from these tables. Records will be deleted only from base tables.

R12 tested Sample Script :
DECLARE
   l_result                     BOOLEAN                                      ;
   l_po_header_id        NUMBER          := <po_header_id> ;
   l_type_lookup_code VARCHAR2(8) := <lookup_code>  ;
   l_validation               VARCHAR2(1) := <validation_flag> ;
BEGIN
   DBMS_OUTPUT.put_line ( 'API PO_HEADERS_SV1 Call to delete PO with header :'||l_po_header_id);
   l_result := po_headers_sv1.delete_po (X_po_header_id         => l_po_header_id
                                                             ,X_type_lookup_code => l_type_lookup_code
                                                             ,p_skip_validation        => l_validation);

   IF l_result = TRUE THEN
      COMMIT;
      DBMS_OUTPUT.put_line ( 'PO with header: '||l_po_header_id||',Deleted Successfully');
   ELSE
      ROLLBACK;
      DBMS_OUTPUT.put_line ( 'PO with header: '||l_po_header_id||',Failed to Delete');
   END IF;
   DBMS_OUTPUT.put_line ( 'Deletion Process Over');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ( 'Error : '|| SQLERRM);
END;