Script to update Customer Location Address

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;
/

Read More »

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/

Read More »

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;

Read More »

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;

Read More »

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

Read More »

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"

Read More »

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%'

Read More »