Subledger-Ledger Linkage (Purchasing)

PO (Receipt)

select distinct
       rsh.receipt_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
  from rcv_shipment_headers rsh
      ,rcv_shipment_lines rsl
      ,rcv_transactions rt
      ,rcv_receiving_sub_ledger rrsl
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where rsl.shipment_header_id = rsh.shipment_header_id
   and rt.shipment_line_id = rsl.shipment_line_id
   and rrsl.rcv_transaction_id = rt.transaction_id
   and rrsl.rcv_sub_ledger_id = xdl.source_distribution_id_num_1
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and xah.ae_header_id = xdl.ae_header_id
   and xah.ae_header_id = xal.ae_header_id
   and xdl.event_id = xah.event_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and gcc.code_combination_id = gjl.code_combination_id
   --and rsh.receipt_num = '23478'
;

PO (Transaction)

select pha.segment1 po#
      ,plla.shipment_num
      ,pra.release_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
  from po_headers_all pha
      ,po_lines_all pla
      ,po_line_locations_all plla
      ,po_distributions_all pda
      ,po_releases_all pra
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where pla.po_header_id = pha.po_header_id
   and plla.po_line_id = pla.po_line_id
   and pda.line_location_id = plla.line_location_id
   and pda.po_release_id = pra.po_release_id(+) 
   and pda.po_distribution_id = xdl.source_distribution_id_num_1
   and xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
   and xdl.application_id = 201
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num  
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and gjl.code_combination_id = gcc.code_combination_id
   --and pha.segment1= 'P12367450'
;

Project related Purchase Orders not accessible from iProcurement

Reason: Only if the user is a key member of the project can access that project related Purchase Order from iProcurement.

There are two ways to resolve this

1. Set the user as member of the related project
OR
2. Set PO: Enforce Project Security profile to NO

> There is another way to get the Purchase Order Details from iProcurement portal

Navigation:
iProcurement Responsibility > Requests > Submit Requests

Search for program 'PO Output for Communication'

> Run for that purchase Order, the output will give you the PO Document.

Few iProcurement Requisitions Are Not Visible From Application

If you create a requisition from iProcurement and exits the creation before submitting it for approval, a requisition number will be assigned and the status of the requisition will be SYSTEM_SAVED. 

 select *
   from po_requisition_headers_all
  where authorization_status = 'SYSTEM_SAVED' ;

These System Saved Requisitions are not accessible from iProcurement/Core Purchasing.

You can avoid this by saving the cart after each step of before the approver adding step else a Concurrent Program "Purge System Saved Requisition" is available under the Purchasing  Responsibility to purge these type of requisitions.
         Parameter
         Age of Requisition: Number in days, it will purge all the requisitions having last update date less than (current date - number of days)

Selection Query of this program

select distinct prh.requisition_header_id,active_shopping_cart_flag
from po_requisition_headers_all prh, po_requisition_lines_all prl
where prh.authorization_status ='SYSTEM_SAVED'
      and prh.requisition_header_id = prl.requisition_header_id (+)
      and prh.last_update_date < (sysdate- <Age of Requisition>)
      and prl.line_location_id is null
order by prh.requisition_header_id; 

You can use below script to purge specific requisition
 ------
declare
   v_requi_hdr_id number;
   v_requi_num    po_requisition_headers_all.segment1%type := <Requisition_number> ;
begin
   select requisition_header_id
     into v_requi_hdr_id
     from po_requisition_headers_all
    where authorization_status = 'SYSTEM_SAVED'
        and segment1 = v_requi_num ;
    -->'Purge System Saved Requisition' program calling por_purge_sys_saved_req.purge_req
    --  >> and internally below packaged procedure
   por_util_pkg.purge_requisition(v_requi_hdr_id);
   dbms_output.put_line('Reuisition '||v_requi_num||' Purged');
   -- Commit the changes
   commit;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;
---------

For Project Requisitions, by default Oracle Projects defines these as a 'commitments'.

You can view these requisitions list as commitments in 'Project Status Inquiry' form
Projects Responsibility > Project Status Inquiry > Project Staus Inquiry

After running 'Purge System Saved Requisition' program you need to run 'PRC: Update Project Summary Amounts' from Projects responsibility.

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;