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.

Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports. 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