DECLARE p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE; p_object_version_number NUMBER; x_return_status VARCHAR2 (4000); x_msg_count NUMBER; x_msg_data VARCHAR2 (4000); BEGIN DBMS_OUTPUT.PUT_LINE ('Start of Script'); mo_global.init ('AR'); fnd_global.apps_initialize (user_id => 11, resp_id => 551122, resp_appl_id => 457); mo_global.set_policy_context ('S', 123); p_object_version_number := 1; p_location_rec.location_id := 28195; p_location_rec.address1 := 'TEST1'; p_location_rec.address2 := 'TEST2'; p_location_rec.address3 := 'TEST3'; p_location_rec.address4 := 'TEST4'; p_location_rec.CITY := 'TEST5'; p_location_rec.STATE := 'TEST6'; p_location_rec.POSTAL_CODE := '123456'; p_location_rec.COUNTRY := 'US'; DBMS_OUTPUT.PUT_LINE ('Calling the API hz_location_v2pub.update_location'); hz_location_v2pub.update_location ( p_init_msg_list => FND_API.G_TRUE, p_location_rec => p_location_rec, p_object_version_number => p_object_version_number, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data); IF x_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.PUT_LINE ('Successfully Location address updated '); ELSE ROLLBACK; FOR i IN 1 .. x_msg_count LOOP x_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F'); DBMS_OUTPUT.put_line (i || ') ' || x_msg_data); END LOOP; DBMS_OUTPUT.put_line ('Location address update failed:' || x_msg_data); END IF; DBMS_OUTPUT.PUT_LINE ('End of Script'); END; /
Script to update Customer Location Address

Submitting Request set from backend
CREATE OR REPLACE PROCEDURE XX_SUBMIT_REQUEST_SET (P_errbuf OUT VARCHAR2, P_retcode OUT NUMBER) AS V_REQUEST_SET_EXIST BOOLEAN := FALSE; req_id INTEGER := 0; l_CONC_PROG_SUBMIT BOOLEAN := FALSE; srs_failed EXCEPTION; submitprog_failed EXCEPTION; submitset_failed EXCEPTION; l_start_date VARCHAR2 (250); BEGIN fnd_file.put_line (fnd_file.LOG, 'Calling set_request_set'); V_REQUEST_SET_EXIST := FND_SUBMIT.set_request_set (application => ‘XXAP’, request_set => ‘FNDRSSUB1786’); IF (NOT V_REQUEST_SET_EXIST) THEN RAISE srs_failed; END IF; fnd_file.put_line (fnd_file.LOG, 'Calling submit program first stage'); l_CONC_PROG_SUBMIT := fnd_submit.submit_program (‘XXAP’, ‘XXAP_FIRST_PROGRAM’, ‘STAGE10’, ‘ARGUMENT1’); IF (NOT l_CONC_PROG_SUBMIT) THEN RAISE submitprog_failed; END IF; l_CONC_PROG_SUBMIT := fnd_submit.submit_program (‘XXAP’, ‘XXAP_SECOND_PROGRAM’, ‘STAGE20’); IF (NOT l_CONC_PROG_SUBMIT) THEN RAISE submitprog_failed; END IF; l_CONC_PROG_SUBMIT := fnd_submit.submit_program (‘XXAP’, ‘XXAP_THIRD_PROGRAM’, ‘STAGE30’); IF (NOT l_CONC_PROG_SUBMIT) THEN RAISE submitprog_failed; END IF; l_CONC_PROG_SUBMIT := fnd_submit.submit_program (‘XXAP’, ‘XXAP_FOURTH_PROGRAM’, ‘STAGE40’); IF (NOT l_CONC_PROG_SUBMIT) THEN RAISE submitprog_failed; END IF; fnd_file.put_line (fnd_file.LOG, 'Calling submit_set'); --l_start_date is to schedule the request SELECT TO_CHAR (SYSDATE, 'DD - MON - YYYY HH24:MI:SS') INTO l_start_date FROM DUAL; req_id := FND_SUBMIT.submit_set (start_time => l_start_date, sub_request => FALSE); IF (req_id = 0) THEN RAISE submitset_failed; END IF; EXCEPTION WHEN srs_failed THEN p_errbuf := 'Call to set_request_set failed: ' || fnd_message.get; p_retcode := 2; fnd_file.put_line (fnd_file.LOG, p_errbuf); WHEN submitprog_failed THEN p_errbuf := 'Call to submit_program failed: ' || fnd_message.get; p_retcode := 2; fnd_file.put_line (fnd_file.LOG, p_errbuf); WHEN submitset_failed THEN p_errbuf := 'Call to submit_set failed: ' || fnd_message.get; p_retcode := 2; fnd_file.put_line (fnd_file.LOG, p_errbuf); WHEN OTHERS THEN p_errbuf := 'Request set submission failed - unknown error: ' || SQLERRM; p_retcode := 2; fnd_file.put_line (fnd_file.LOG, p_errbuf); END;
Source: https://oraclesoadiscussion.wordpress.com/2015/03/05/submitting-request-set-from-backend/
API to get on hand and available to reserve quantities in Oracle Apps
DECLARE x_return_status VARCHAR2 (50); x_msg_count VARCHAR2 (50); x_msg_data VARCHAR2 (50); v_inv_item_id NUMBER; v_org_id NUMBER; v_qty_on_hand NUMBER; v_res_qty_on_hand NUMBER; v_atreserve NUMBER; v_attransact NUMBER; v_qty_reserve NUMBER; v_qty_sugstd NUMBER; v_lot_control_code BOOLEAN; v_serial_control_code BOOLEAN; BEGIN -- Set the variable values v_inv_item_id := :item_id; v_org_id := :org_id; v_qty_on_hand := NULL; v_res_qty_on_hand := NULL; v_atreserve := NULL; v_lot_control_code := FALSE; v_serial_control_code := FALSE; -- org context fnd_client_info.set_org_context (1); -- Calling API inv_quantity_tree_pub.query_quantities ( p_api_version_number => 1.0, p_init_msg_lst => 'F', x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_organization_id => v_org_id, p_inventory_item_id => v_inv_item_id, p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode, p_is_revision_control => FALSE, p_is_lot_control => v_lot_control_code, p_is_serial_control => v_serial_control_code, p_revision => NULL, p_lot_number => NULL, p_lot_expiration_date => SYSDATE, p_subinventory_code => NULL, p_locator_id => NULL, p_onhand_source => 3, x_qoh => v_qty_on_hand, x_rqoh => v_res_qty_on_hand, x_qr => v_qty_reserve, x_qs => v_qty_sugstd, x_att => v_attransact, x_atr => v_atreserve); COMMIT; DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qty_on_hand); DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qty_reserve); DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qty_sugstd); DBMS_OUTPUT.put_line ('Available to Transact: ' || v_attransact); DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atreserve); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM); END;
Deactivate Suppliers/Vendors in Oracle Apps using API
--www.askhareesh.com DECLARE LV_RETURN_STATUS VARCHAR2 (1) := NULL; LV_MSG_COUNT NUMBER := 0; LV_MSG_DATA VARCHAR2 (2000) := NULL; LV_VENDOR_REC AP_VENDOR_PUB_PKG.R_VENDOR_REC_TYPE; BEGIN LV_VENDOR_REC.END_DATE_ACTIVE := SYSDATE; --End Date AP_VENDOR_PUB_PKG.UPDATE_VENDOR ( P_API_VERSION => 1.0, P_INIT_MSG_LIST => FND_API.G_TRUE, P_COMMIT => FND_API.G_FALSE, P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL, X_RETURN_STATUS => LV_RETURN_STATUS, X_MSG_COUNT => LV_MSG_COUNT, X_MSG_DATA => LV_MSG_DATA, P_VENDOR_REC => LV_VENDOR_REC, P_VENDOR_ID => 33164 --Vendor id ); BEGIN IF NVL (LV_RETURN_STATUS, 'XX') = 'S' THEN DBMS_OUTPUT.PUT_LINE ('Vendor deactivated successfully'); ELSE DBMS_OUTPUT.PUT_LINE ( 'Error while deactivating the supplier, Msg Count' || LV_MSG_COUNT || ', Error : ' || LV_MSG_DATA); END IF; END; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( 'Exception while deactivating supplier : ' || SQLERRM); END;
Query for Employee as Supplier Bank Details
--Query for employee as supplier bank details SELECT aps.vendor_id, apss.vendor_site_id, aps.vendor_name, apss.vendor_site_code, ieb.bank_name, ieb.country, iebb.bank_branch_name, iebb.eft_swift_code, iebb.branch_number, ieba.bank_account_num, ieba.bank_account_name FROM ap.ap_suppliers aps, per_all_people_f papf, ap.ap_supplier_sites_all apss, apps.iby_ext_bank_accounts ieba, apps.iby_account_owners iao, apps.iby_ext_banks_v ieb, apps.iby_ext_bank_branches_v iebb WHERE aps.vendor_id = apss.vendor_id AND iao.account_owner_party_id = aps.party_id AND ieba.ext_bank_account_id = iao.ext_bank_account_id AND ieb.bank_party_id = iebb.bank_party_id AND ieba.branch_id = iebb.branch_party_id AND ieba.bank_id = ieb.bank_party_id AND aps.employee_id = papf.person_id AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
FTP the file from other server to local server
#!/bin/bash
HOST='ftp.askhareesh.com' #HostName
USER='AskHareesh' #UserName
PASSWD='@5kh@r335h' #Password
DIREC='/out/tmp/' #Directory of the file to save
FILE='AHSalesOrders' #File Name starts with
echo "Host : $HOST"
echo "User Name : $USER"
echo "Directory : $DIREC"
echo "File Name starts with : $FILE"
echo "Start of the Script"
cd $DIREC
#Start of FTP script
ftp -n -v $HOST << EOT
ascii
user $USER $PASSWD
prompt
cd OUT
mget $FILE*.csv
bye
EOT
#Loop for renaming/archiving the files
for FNAME in $FILE*.csv
do
echo "File Name : $FNAME"
ftp -n -v $HOST << EndLOOP
ascii
user $USER $PASSWD
prompt
cd OUT
rename $FNAME $FNAME.done.$(date "+%m%d%Y%H%M")
bye
EndLOOP
done
echo "End of the Script"
Query to get Descriptive Flex Field and Attributes
SELECT FFV.APPLICATION_TABLE_NAME, FFV.DESCRIPTIVE_FLEXFIELD_NAME, FFV.CONTEXT_COLUMN_NAME, FFV.TITLE, ATT.APPLICATION_COLUMN_NAME, ATT.END_USER_COLUMN_NAME, ATT.COLUMN_SEQ_NUM, ATT.ENABLED_FLAG, ATT.REQUIRED_FLAG, ATT.SECURITY_ENABLED_FLAG, ATT.DISPLAY_FLAG, ATT.FLEX_VALUE_SET_ID, ATT.FORM_LEFT_PROMPT, FFV.* FROM FND_DESCRIPTIVE_FLEXS_VL FFV, FND_DESCR_FLEX_COL_USAGE_VL ATT WHERE FFV.DESCRIPTIVE_FLEXFIELD_NAME = ATT.DESCRIPTIVE_FLEXFIELD_NAME AND FFV.DESCRIPTIVE_FLEXFIELD_NAME = 'JTF_RS_RESOURCE_EXTNS' AND FFV.TITLE LIKE '%Resource%'