Purchasing API Example: PO_CHANGE_API1_S.update_po

I had to modify deliver to location and PO distribution DFF on multiple purchase orders. All those purchase orders had "Authorization Status" as "Approved". I thought of using Oracle Purchasing API PO_CHANGE_API1_S.update_po. It was not easy to use this API for following reasons.

1. API did not allow update of Deliver to location although Purchase Orders form allows update of deliver to location.


2. Handling of DFF update has bugs. When I updated flexfield, I got following error
   The flexfield on this field contains a flexfield bind variable with a :BLOCK.FIELD reference. This prohibits operation in the mode that optimizes performance over a wide-area network. To continue using this form, have the system administrator either remove all :BLOCK.FIELD references, or turn off wide area operation by setting the profile option - Flexfields: Validate on Server to N. The reference may be in a WHERE_CLAUSE token passed into the flexfield definition in the form, a WHERE clause in a table-validated value set, or a default value for one of the segments of this flexfield.
   
   We could not update PO distribution DFF to remove :block.field references. By setting profile option "Flexfields:Validate On Server" to "No" did not help. Refer to FLEXFIELDS: Validate on Server Profile Option (Doc ID 273044.1) for more information on this profile option.

Since I had created a test script so I tried to see how it works by updating PO description, item description.

Updating PO Line quantity worked very well. When I updated line quantity, it updated shipment quantity and distribution quantity too. I was thinking that I have provide these values in shipment and distribution records. Sample is given below. If you a better way to initialize TYPE PO_PUB_UPDATE_REC_TYPE, please let me know.

DECLARE CURSOR c_dists_to_update IS SELECT pod.po_distribution_id , pod.distribution_num , pod.line_location_id , pll.shipment_num , pod.po_line_id , pol.line_num , pod.po_header_id , poh.org_id , poh.segment1 , pod.attribute1 pod_attr1 , pod.attribute2 pod_attr2 , 'SKM_LOCATION' new_location_code /* New Location code was determined using subquery and must exist in hr_locations */ FROM po_distributions_all pod , po_headers_all poh , po_lines_all pol , po_line_locations_all pll WHERE poh.po_header_id = pod.po_header_id AND pol.po_line_id = pod.po_line_id AND pll.line_location_id = pod.line_location_id AND poh.segment1 LIKE 'SKM-20161005-0%' ; TYPE varchar2_table IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER; TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; l_attr13 VARCHAR2_TABLE; l_attr13_new VARCHAR2_TABLE; l_line_location_id NUMBER_TABLE; l_distribution_id NUMBER_TABLE; l_hr_location_id NUMBER_TABLE; l_rec_count NUMBER; l_po_rec PO_PUB_UPDATE_REC_TYPE; l_errors PO_API_ERRORS_REC_TYPE; l_ret_status VARCHAR2(240); PROCEDURE update_po ( p_po_header_id NUMBER , p_segment1 VARCHAR2 , p_org_id NUMBER , p_line_num NUMBER , p_shipment_num NUMBER , p_distribution_num NUMBER , p_new_location_code VARCHAR2 ) IS BEGIN l_po_rec := po_pub_update_rec_type ( po_header_id => null , po_number => null , run_submission_checks => null , launch_approvals_flag => null , org_id => null , approval_background_flag => null , po_header_changes => PO_PUB_HEADER_REC_TYPE ( buyer_name => null , fob_lookup_code => null , payment_terms => null , description => null , attribute_category => null , attribute1 => null , attribute2 => null , attribute3 => null , attribute4 => null , attribute5 => null , attribute6 => null , attribute7 => null , attribute8 => null , attribute9 => null , attribute10 => null , attribute11 => null , attribute12 => null , attribute13 => null , attribute14 => null , attribute15 => null , attribute16 => null , attribute17 => null , attribute18 => null , attribute19 => null , attribute20 => null , po_line_changes => PO_PUB_LINE_TAB_TYPE ( PO_PUB_LINE_REC_TYPE ( line_num => null , line_type => null , item_category => null , unit_price => null , vendor_product_num => null , quantity => null , amount => null , unit_of_measure => null , secondary_quantity => null , preferred_grade => null , item_desc => null , attribute_category => null , attribute1 => null , attribute2 => null , attribute3 => null , attribute4 => null , attribute5 => null , attribute6 => null , attribute7 => null , attribute8 => null , attribute9 => null , attribute10 => null , attribute11 => null , attribute12 => null , attribute13 => null , attribute14 => null , attribute15 => null , attribute16 => null , attribute17 => null , attribute18 => null , attribute19 => null , attribute20 => null , po_line_loc_changes => PO_PUB_LINE_LOC_TAB_TYPE ( PO_PUB_LINE_LOC_REC_TYPE ( shipment_num => null , quantity => null , amount => null , promised_date => null , need_by_date => null , ship_to_location_code => null , split_shipment_num => null , new_split_ship_num => null , qty_rcv_tolerance => null , attribute_category => null , attribute1 => null , attribute2 => null , attribute3 => null , attribute4 => null , attribute5 => null , attribute6 => null , attribute7 => null , attribute8 => null , attribute9 => null , attribute10 => null , attribute11 => null , attribute12 => null , attribute13 => null , attribute14 => null , attribute15 => null , attribute16 => null , attribute17 => null , attribute18 => null , attribute19 => null , attribute20 => null , po_dist_changes => PO_PUB_DIST_TAB_TYPE ( PO_PUB_DIST_REC_TYPE ( distribution_num => null , deliver_to_location_code => null , project => null , task_name => null , expenditure_type => null , expenditure_organization => null , expenditure_item_date => null , award_number => null , end_item_unit_number => null , quantity_ordered => null , split_distribution_num => null , new_split_dist_num => null , amount_ordered => null , attribute_category => null , attribute1 => null , attribute2 => null , attribute3 => null , attribute4 => null , attribute5 => null , attribute6 => null , attribute7 => null , attribute8 => null , attribute9 => null , attribute10 => null , attribute11 => null , attribute12 => null , attribute13 => null , attribute14 => null , attribute15 => null , attribute16 => null , attribute17 => null , attribute18 => null , attribute19 => null , attribute20 => null ) ) ) ) ) ) ) ) ; l_po_rec.po_header_id := p_po_header_id; l_po_rec.org_id := p_org_id; l_po_rec.launch_approvals_flag := fnd_api.g_true; l_po_rec.po_header_changes.description := 'Update on ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'); l_po_rec.po_header_changes.attribute1 := 'ABC'; l_po_rec.po_header_changes.attribute3 := '2015/01/01 00:00:00'; l_po_rec.po_header_changes.attribute4 := '2018/12/31 00:00:00'; l_po_rec.po_header_changes.po_line_changes(1).line_num := p_line_num; l_po_rec.po_header_changes.po_line_changes(1).quantity := 65; l_po_rec.po_header_changes.po_line_changes(1).item_desc := 'Item Desc ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'); l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).shipment_num := p_shipment_num; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).ship_to_location_code := p_new_location_code; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).distribution_num := p_distribution_num; /* API does not allow update of deliver to location if authroiztion status is other than Incomplete. Although Purchase Order form allows update of deliver to location. Form does not allow update of shipto lication but API allows that. This behavior is weird because Form and API should behace same. l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).deliver_to_location_code := p_new_location_code ; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).attribute13 := 'aaaaaaaa wwwww~187TS5' ; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).attribute1 := l_dist_rec.pod_attr1; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).attribute2 := l_dist_rec.pod_attr2; */ fnd_file.put_line( fnd_file.log, p_segment1 || ' ' || to_char(p_line_num,'99999999999') || ' ' || to_char(p_shipment_num,'99999999') || ' ' || to_char(p_distribution_num,'999999999999') || ' ' ) ; PO_CHANGE_API1_S.update_po ( p_api_version => 1.0 , p_init_msg_list => 'T' , x_return_status => l_ret_status , p_changes => l_po_rec , x_api_errors => l_errors ); IF l_errors IS NOT NULL THEN FOR i in 1..l_errors.message_name.COUNT LOOP fnd_file.put_line(fnd_file.log, 'Message name = ' || l_errors.message_name(i)); fnd_file.put_line(fnd_file.log, 'Message text = ' || l_errors.message_text(i)); END LOOP; END IF; END update_po; -- -- Begin of main pl/sql block -- BEGIN FOR l_dist_rec IN c_dists_to_update LOOP update_po ( l_dist_rec.po_header_id , l_dist_rec.segment1 , l_dist_rec.org_id , l_dist_rec.line_num , l_dist_rec.shipment_num , l_dist_rec.distribution_num , l_dist_rec.new_location_code ) ; END LOOP; END; /