Alternativa de excluir uma Ordem de Compra Incompleta

Antes da aprovação do Pedido de Compra e quando a PO esta no estado INCOMPLETO o Oracle nos permite excluir o formulário de Pedido de Compra.

O mesmo pode ser efetuado usando a API mencionada abaixo, Mas precisamos nos lembrar de alguns pontos antes de utilizar esta API para excluir a PO incompleta.

--=================================================
DECLARE
l_deleted BOOLEAN;
CURSOR c_po_det IS
SELECT po_header_id, segment1,org_idFROM po.po_headers_all phaWHERE 1 = 1AND segment1 = '11170002356'AND org_id = 308AND NVL (approved_flag, 'N') <> 'Y'AND NVL (cancel_flag, 'N') = 'N'AND type_lookup_code = 'STANDARD'AND authorization_status = 'INCOMPLETE'AND NOT EXISTS ( SELECT 1FROM po_headers_archive_all aWHERE pha.po_header_id = a.po_header_idAND pha.org_id = a.org_id)AND NOT EXISTS ( SELECT 1FROM mtl_supply b WHERE pha.po_header_id = b.po_header_id ANDsupply_type_code = 'PO');
BEGIN
FOR c1 IN c_po_det LOOP
DBMS_OUTPUT.put_line ( 'Calling PO_HEADERS_SV1 API To Delete PO');DBMS_OUTPUT.put_line ( '==========================');
l_deleted := po_headers_sv1.delete_po (c1.po_header_id, 'STANDARD');
IF l_deleted = TRUETHENCOMMIT;DBMS_OUTPUT.put_line ( 'Successfully Deleted the PO');ELSEROLLBACK;DBMS_OUTPUT.put_line ( 'Failed to Delete the PO');END IF;END LOOP;
END;

PO com AP

Olá vou postar uma query muito util com o ralecionamento correto do AP com PO muito util para verificar as ordem de compras com seus pagamentos.

SELECTa.org_id "ORG ID",E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY",to_char(trunc(d.CREATION_DATE)) "PO Date",d.segment1 "PO NUM",d.type_lookup_code "PO Type",c.quantity_ordered "QTY ORDERED",c.quantity_cancelled "QTY CANCELLED",g.item_id "ITEM ID",g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE",(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",(select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id) "PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT",to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",a.invoice_num "INVOICE NUMBER",(select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x wherex.invoice_distribution_id = b.invoice_distribution_id) "Invoice Approved?",a.amount_paid,h.amount,h.check_id,h.invoice_payment_id "Payment Id",i.check_number "Cheque Number",to_char(trunc(i.check_DATE)) "Payment Date"FROMAP.AP_INVOICES_ALL A,AP.AP_INVOICE_DISTRIBUTIONS_ALL B,PO.PO_DISTRIBUTIONS_ALL C,PO.PO_HEADERS_ALL D,PO.PO_VENDORS E,PO.PO_VENDOR_SITES_ALL F,PO.PO_LINES_ALL G,AP.AP_INVOICE_PAYMENTS_ALL H,AP.AP_CHECKS_ALL IWHEREa.invoice_id = b.invoice_id andb.po_distribution_id = c. po_distribution_id (+) andc.po_header_id = d.po_header_id (+) ande.vendor_id (+) = d.VENDOR_ID andf.vendor_site_id (+) = d.vendor_site_id andd.po_header_id = g.po_header_id andc.po_line_id = g.po_line_id anda.invoice_id = h.invoice_id andh.check_id = i.check_id andf.vendor_site_id = i.vendor_site_id andc.PO_HEADER_ID is not null anda.payment_status_flag = 'Y' andd.type_lookup_code != 'BLANKET'