PO Update Script in Oracle R12

PO Update needs to be done? Programmatically? Well, Oracle has provided an API to do just that. API Name is po_document_update_grp.update_document.
Below is a sample PO Update Script which we can use to delete the shipments. Alter the cursor for your requirement.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Set Serveroutput on;
/

–PO Update Script
DECLARE
LV_PO_HEADER_ID NUMBER := 11551815;

CURSOR GET_rECORDS(p_in_n_po_header_id IN NUMBER )
IS
select l.line_num, s.shipment_num,s.line_location_id, e.*
from po_lines_all l,
po_line_locations_all s
where l.po_line_id = s.po_line_id
AND l.po_header_id = p_in_n_po_header_id
order by e.po_line_id;

l_chg po_changes_rec_type;
L_ERRORS PO_API_ERRORS_REC_TYPE;
lv_count number :=0;
l_return_status varchar2(1);
L_ERROR_MESSAGE VARCHAR2 (2000) := NULL;
l_n_user_Id NUMBER := NULL ;
l_n_resp_id NUMBER := NULL ;
l_n_appl_id NUMBER := NULL ;
l_n_org_id NUMBER := NULL ;
l_v_dist_num po_distributions_all.distribution_num%TYPE := NULL;
l_n_line_num po_lines_all.line_num%TYPE:= NULL;
l_v_po_num po_headers_all.segment1%TYPE:= NULL;
l_v_task_num pa_tasks.task_number%TYPE;
l_v_project_num PA_projects_all.segment1%TYPE;

BEGIN
–Get the user id
SELECT user_id
INTO l_n_user_Id
FROM fnd_user
WHERE user_name = ‘YourUserName’;
dbms_output.put_line(‘l_n_user_Id: ‘||l_n_user_Id);

  --Get the responsibility and application id 
select responsibility_id, 
        application_id 
  INTO l_n_resp_id, 
        l_n_appl_id
from fnd_responsibility_tl 
where responsibility_name = 'Purchasing Super User'
and language = 'US';
 dbms_output.put_line('l_n_resp_id: '||l_n_resp_id); 
 dbms_output.put_line('l_n_appl_id: '||l_n_appl_id); 

  --Get the org id 
  SELECT org_id 
    INTO l_n_org_id 
    FROM po_headers_all 
    WHERE po_header_id = LV_PO_HEADER_ID;
  dbms_output.put_line('l_n_org_id: '||l_n_org_id); 

fnd_global.apps_initialize (user_id => l_n_user_Id, resp_id => l_n_resp_id, resp_appl_id => l_n_appl_id );
— mo_global.init(‘PO’);
MO_GLOBAL.SET_POLICY_CONTEXT (‘S’,l_n_org_id);

l_chg :=  po_changes_rec_type.create_object(p_po_header_id       => LV_PO_HEADER_ID);



FOR LCU_REC IN  GET_RECORDS(LV_PO_HEADER_ID)
LOOP
  lv_count := lv_count+1;
  IF (lv_count =1)
  THEN 
    dbms_output.put_line('Deleting the following records: ');
    dbms_output.put_line('Line Number         Shipment Number     ');
  END IF ;
    dbms_output.put_line(RPAD(LCU_REC.line_num,20, ' ')||RPAD(LCU_REC.SHIPMENT_NUM,20, ' '));
  L_CHG.shipment_changes.ADD_CHANGE (
                      p_po_line_location_id   => LCU_REC.line_location_id,
                        p_delete_record => 'Y'
                      );

END LOOP;

po_document_update_grp.update_document(p_api_version                        => 1.0
                                             , p_init_msg_list              => fnd_api.g_true
                                             , x_return_status              => l_return_status
                                             , p_changes                    => L_CHG
                                             , p_run_submission_checks      => fnd_api.g_false
                                             , p_launch_approvals_flag      => fnd_api.g_false
                                             , p_buyer_id                   => NULL
                                             , p_update_source              => NULL
                                             , p_override_date              => NULL
                                             , x_api_errors                 => l_errors
                                             , p_approval_background_flag   => NULL
                                             , p_mass_update_releases       => NULL);

  DBMS_OUTPUT.PUT_LINE(' return status: '||l_return_status);
  IF l_return_status <> 'S'
  THEN
     FOR I IN 1 .. L_ERRORS.MESSAGE_TEXT.COUNT
              LOOP
                L_ERROR_MESSAGE := L_ERRORS.MESSAGE_TEXT (I);
                DBMS_OUTPUT.PUT_LINE('Error Message:'||L_ERROR_MESSAGE);
              END LOOP;
     ROLLBACK;
   ELSE 
    COMMIT;
   END IF;           

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error while calling API’||SQLERRM);
END;
/

Reference:
support.oracle.com