Query to find SHIP_TO_LOCATION_ID from SO's SHIP_TO_LOCATION In Oracle Apps R12

/*Query to find SHIP_TO_LOCATION_ID from SO's SHIP_TO_LOCATION*/

SELECT location_id SHIP_TO_LOCATION_ID /*wsh_delivery_details.ship_to_location_id*/
FROM   hz_locations
WHERE  location_id IN
       (
              SELECT location_id
              FROM   hz_party_sites hps ,
                     hz_cust_acct_sites_all hcas
              WHERE  hcas.party_site_id = hps.party_site_id
              AND    hcas.cust_acct_site_id IN
                     (
                            SELECT cust_acct_site_id
                            FROM   hz_cust_site_uses_all
                            WHERE  location = 140912 /*:ORDER.SHIP_TO_LOCATION*/
                            AND    site_use_code = 'SHIP_TO' ) );

(OR)

SELECT location_id ship_to_location_id
FROM   hz_locations
WHERE  location_id IN
       (
              SELECT location_id
              FROM   hz_party_sites hps ,
                     hz_cust_acct_sites_all hcas
              WHERE  hcas.party_site_id = hps.party_site_id
              AND    hcas.cust_acct_site_id IN
                     (
                            SELECT cust_acct_site_id
                            FROM   hz_cust_site_uses_all
                            WHERE  site_use_id = 140912 /*:ORDER.SHIP_TO_ORG_ID*/
                            AND    site_use_code = 'SHIP_TO' ) );

Script/API to import Daily Currency Conversion Rates in General Ledger of EBS R12

 /*API to import Daily Currency Conversion Rates in General Ledger of EBS R12*/

DECLARE
    lv_from_currency   VARCHAR2(5) := 'USD';
    lv_to_currenty     VARCHAR2(5) := 'INR';
    ln_conversion_rate NUMBER := 85.57;
    ln_batch_number    NUMBER := 10001;
    ln_dummy_char      NUMBER;
    lv_request_id      NUMBER;
    lv_result          BOOLEAN;
    lv_phase1          VARCHAR2(100);
    lv_status1         VARCHAR2(100);
    lv_dev_phase1      VARCHAR2(100);
    lv_dev_status1     VARCHAR2(100);
    lv_message1        VARCHAR2(100);
BEGIN
    -- check whether the from currency exists and enabled 
    BEGIN
        SELECT 1
        INTO   ln_dummy_char
        FROM   fnd_currencies
        WHERE  currency_flag = 'Y'
               AND enabled_flag = 'Y'
               AND currency_code = Upper(lv_from_currency);
    EXCEPTION
        WHEN no_data_found THEN
          dbms_output.Put_line('Error: From Currency is not valid');
    END;

    -- check whether the from currency exists and enabled 
    BEGIN
        SELECT 1
        INTO   ln_dummy_char
        FROM   fnd_currencies
        WHERE  currency_flag = 'Y'
               AND enabled_flag = 'Y'
               AND currency_code = Upper(lv_to_currenty);
    EXCEPTION
        WHEN no_data_found THEN
          dbms_output.Put_line('Error: To Currency is not valid');
    END;

    -- Initialize the enviroment 
    fnd_global.Apps_initialize (user_id => 2739 --User Id
    , resp_id => 20434 --Responsibility Id
    , resp_appl_id => 101 --Responsibility Application Id
    );

    -- Insert into Interface table 
    INSERT INTO gl_daily_rates_interface
                (from_currency,
                 to_currency,
                 from_conversion_date,
                 to_conversion_date,
                 user_conversion_type,
                 conversion_rate,
                 mode_flag,
                 inverse_conversion_rate,
                 user_id,
                 launch_rate_change,
                 error_code,
                 batch_number)
    VALUES      (lv_from_currency,
                 lv_to_currenty,
                 '25-JAN-2025',
                 '25-JAN-2025',
                 'Corporate',
                 ln_conversion_rate,
                 'I',
                 ( 1 / ln_conversion_rate ),
                 fnd_profile.Value('USER_ID'),
                 NULL,
                 NULL,
                 ln_batch_number );

    -- Submit Program - Daily Rates Import and Calculation
    lv_request_id := fnd_request.Submit_request(

                     application => 'SQLGL',
                     program => 'GLDRICCP',  --Program - Daily Rates Import and Calculation
                     description => NULL, 

                     start_time => NULL,
                     sub_request => NULL,
                     argument1 => ln_batch_number);

    COMMIT;

    IF lv_request_id = 0 THEN
      dbms_output.Put_line(' Failed to submit Process GLDRICCP.'|| fnd_message.get);
    ELSE
      lv_result := fnd_concurrent.Wait_for_request(lv_request_id, 1, 0, lv_phase1, lv_status1, lv_dev_phase1, lv_dev_status1, lv_message1);
    END IF;

    IF NOT lv_result THEN
      dbms_output.Put_line('No Status returned for the request Id: '|| lv_request_id);
    ELSE
      dbms_output.Put_line('The Req-Id of GLDRICCP Process is '|| lv_request_id);
    END IF;
END; 

gl_daily_rates_interface => "Program - Daily Rates Import and Calculation" => GL_DAILY_RATES table

Unable to Receive Against Internal Requisition Though Shipment is Intransit

Unable to receive against Internal Requisition though the shipment is shown as In-Transit. We should be able to receive In-Transit quantity at destination organization.

The issue can be reproduced at will with the following steps:

1. From Inventory Responsibility, Choose Destination Organization
2. Open Manage Shipments form:    Navigation: Transactions > Receiving > Manage Shipments
3. Query for the problematic shipment
4. The shipment lines are shown
5. Open Receipts form:    Navigation: Transactions > Receiving > Receipts
6. Enter problematic Shipment Number in Shipment Field and click Find
7. There are no lines available for Receipt

Missing MTL_SUPPLY records for the shipment expected to receive against Internal Requisition.

Run the below SQL to see if the problematic shipment is returned:

SELECT rsh.shipment_num "Shipment Number",
       rsl.line_num     "Shipment Line Number",
       rsl.from_organization_id,
       rsl.to_organization_id,
       rsl.shipment_line_id
FROM   rcv_shipment_lines rsl,
       rcv_shipment_headers rsh
WHERE  rsh.shipment_header_id = rsl.shipment_header_id
       AND rsl.source_document_code = 'REQ'
       AND rsl.shipment_line_status_code = 'EXPECTED'
       AND rsl.quantity_received = 0
       AND rsh.shipment_num = <-- did-->
       AND NOT EXISTS (SELECT 1
                       FROM   mtl_supply ms
                       WHERE  ms.shipment_line_id = rsl.shipment_line_id); 

Now run the below script for Missing MTL_SUPPLY entry. Here Expected Receipt date is taken as Internal requisition's need_by_date:

DECLARE
    l_to_org_prim_qty         NUMBER := -1;
    l_intransit_owning_org_id NUMBER := -1;
    l_from_sub                mtl_supply.from_subinventory%TYPE;
    l_trx_date                wsh_trip_stops.actual_departure_date%TYPE;
    l_receipt_date            mtl_supply.receipt_date%TYPE;
    l_expected_delivery_date  mtl_supply.expected_delivery_date%TYPE;
    l_ship_method_code        wsh_trips.ship_method_code%TYPE;
    l_ship_from_location_id   wsh_delivery_details.ship_from_location_id%TYPE;
    l_ship_to_site_use_id     wsh_delivery_details.ship_to_site_use_id%TYPE;
    l_intransit_time          NUMBER;
    l_lead_time               NUMBER;
    l_return_status           VARCHAR2(30);
    l_insert_ms               NUMBER := 1; --1 -> insert into MS ,
    --0 -> insert into MS ,
    l_picking_line_id         wsh_delivery_details.delivery_detail_id%TYPE;
    CURSOR c_rsl_with_no_ms IS
      SELECT rsl.*
      FROM   rcv_shipment_lines rsl,
             rcv_shipment_headers rsh
      WHERE  rsh.shipment_header_id = rsl.shipment_header_id
             AND rsl.source_document_code = 'REQ'
             AND rsl.shipment_line_status_code = 'EXPECTED'
             AND rsl.quantity_received = 0
             AND rsh.shipment_num = <-- did-->
             AND NOT EXISTS (SELECT 1
                             FROM   mtl_supply ms
                             WHERE  ms.shipment_line_id = rsl.shipment_line_id);
    rsl_rec                   c_rsl_with_no_ms%ROWTYPE;
BEGIN
    dbms_output.Put_line('mtl_supply creation script started...');

    OPEN c_rsl_with_no_ms;

    LOOP
        FETCH c_rsl_with_no_ms INTO rsl_rec;

        EXIT WHEN c_rsl_with_no_ms%NOTFOUND;

        l_insert_ms := 1;
        --reseting the value so that mtl supply record will get inserted
        SELECT mmt.owning_organization_id,
               subinventory_code,
               picking_line_id
        INTO   l_intransit_owning_org_id, l_from_sub, l_picking_line_id
        FROM   mtl_material_transactions mmt,
               rcv_shipment_lines rsl
        WHERE  mmt.transaction_id = rsl.mmt_transaction_id
               AND rsl.shipment_line_id = rsl_rec.shipment_line_id;

        IF ( rsl_rec.unit_of_measure <> rsl_rec.primary_unit_of_measure ) THEN
          l_to_org_prim_qty :=
rsl_rec.quantity_shipped *
po_uom_s.Po_uom_convert(rsl_rec.unit_of_measure, rsl_rec.primary_unit_of_measure, rsl_rec.item_id);
ELSE
  l_to_org_prim_qty := rsl_rec.quantity_shipped;
END IF;

SELECT wts.actual_departure_date,
       wt.ship_method_code,
       wdd.ship_from_location_id,
       wdd.ship_to_site_use_id
INTO   l_trx_date, l_ship_method_code, l_ship_from_location_id,
       l_ship_to_site_use_id
FROM   wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       wsh_delivery_legs wdl,
       wsh_trip_stops wts,
       wsh_trips wt
WHERE  wts.stop_id = wdl.pick_up_stop_id
       AND wts.trip_id = wt.trip_id
       AND wdl.delivery_id = wnd.delivery_id
       AND wnd.delivery_id = wda.delivery_id
       AND wda.delivery_detail_id = wdd.delivery_detail_id
       AND wdd.released_status = 'C'
       AND wdd.delivery_detail_id = l_picking_line_id;

/* Got the follwing code from WSH_SHIP_CONFIRM_ACTIONS.Interface_Detail_To_Inv()
   to calculate the expected_arrival_date.

   During Iterface Trip Stop, Shipping populates the expected arrival date in MTI and
   Inventory passes that value to Receiving during Shipment creation as
   rti.expected_receipt_date.
   (After shipment creation RTI & MTI data will be purged)

   As we are in situation, Shipment creation is done, but mtl_supply data is not there
   and not MTI and RTI data, recalculating the expected receipt date using Shipping code */
IF ( l_ship_method_code IS NOT NULL ) THEN
  --Passing only Ship_Method_Code to API WSH_MAP_LOCATION_REGION_PKG.Get_Transit_Time
  --Setting following variables to NULL so that WSH_MAP_LOCATION_REGION_PKG.Get_Transit_Time
  --will not fetch Ship_Method_Code using Carrier, Service Level and Mode of Transport.
  wsh_map_location_region_pkg.Get_transit_time (
  p_ship_from_loc_id => l_ship_from_location_id,
  p_ship_to_site_id => l_ship_to_site_use_id,
  p_ship_method_code => l_ship_method_code, p_carrier_id => NULL,
  ---l_carrier_id,
  p_service_code => NULL, ---l_service_code,
  p_mode_code => NULL, ----l_mode_code,
  p_from => 'OM', x_transit_time => l_intransit_time,
  x_return_status => l_return_status);

  IF ( l_return_status <> wsh_util_core.g_ret_sts_success ) THEN
    dbms_output.Put_line('intransit time calculation error...'
                         ||l_return_status);

    dbms_output.Put_line('rsl_id:'
                         ||rsl_rec.shipment_line_id);

    l_insert_ms := 0; --don't create mtl_supply
  END IF;
ELSE
  l_intransit_time := 0;
END IF; --if ( l_ship_method_code is not null ) then
IF l_insert_ms = 1 THEN
  --Calculation of receipt date
  l_receipt_date := l_trx_date + Nvl(l_intransit_time, 0);

  IF Nvl(rsl_rec.item_id, -1) != -1 THEN
    BEGIN
        SELECT postprocessing_lead_time
        INTO   l_lead_time
        FROM   mtl_system_items
        WHERE  inventory_item_id = rsl_rec.item_id
               AND organization_id = rsl_rec.to_organization_id;
    EXCEPTION
        WHEN OTHERS THEN
          l_insert_ms := 0;

          dbms_output.Put_line('lead time calculation error...'
                               ||SQLERRM);

          dbms_output.Put_line('rsl_id:'
                               ||rsl_rec.shipment_line_id);
    END;
  ELSE--if  nvl(rsl_rec.item_id,-1) != -1 then
    l_lead_time := 0;
  END IF; --if  nvl(rsl_rec.item_id,-1) != -1 then
  --Calculation of expected delivery date
  l_expected_delivery_date := l_receipt_date + l_lead_time;
END IF; ----if l_insert_ms = 1 then
IF l_insert_ms = 1 THEN
  INSERT INTO mtl_supply
              (supply_type_code,
               supply_source_id,
               last_updated_by,
               last_update_date,
               last_update_login,
               created_by,
               creation_date,
               req_header_id,
               req_line_id,
               shipment_header_id,
               shipment_line_id,
               item_id,
               item_revision,
               quantity,
               unit_of_measure,
               receipt_date,
               need_by_date,
               destination_type_code,
               from_organization_id,
               from_subinventory,
               to_organization_id,
               to_subinventory,
               intransit_owning_org_id,
               cost_group_id,
               to_org_primary_quantity,
               to_org_primary_uom)
  SELECT 'SHIPMENT',
         rsl_rec.shipment_line_id,
         rsl_rec.last_updated_by,
         rsl_rec.last_update_date,
         Nvl(rsl_rec.last_update_login, -1),
         rsl_rec.created_by,
         rsl_rec.creation_date,
         prl.requisition_header_id,
         rsl_rec.requisition_line_id,
         rsl_rec.shipment_header_id,
         rsl_rec.shipment_line_id,
         rsl_rec.item_id,
         rsl_rec.item_revision,
         rsl_rec.quantity_shipped,
         rsl_rec.unit_of_measure,
         l_receipt_date,
         prl.need_by_date,
         rsl_rec.destination_type_code,
         rsl_rec.from_organization_id,
         l_from_sub,
         rsl_rec.to_organization_id,
         rsl_rec.to_subinventory,
         l_intransit_owning_org_id,
         rsl_rec.cost_group_id,
         l_to_org_prim_qty,
         rsl_rec.primary_unit_of_measure
  FROM   po_requisition_lines_all prl
  WHERE  prl.requisition_line_id = rsl_rec.requisition_line_id;

  dbms_output.Put_line('rsl_id:'
                       ||rsl_rec.shipment_line_id);
END IF; --if l_insert_ms = 1 then
END LOOP;

CLOSE c_rsl_with_no_ms;

dbms_output.Put_line('Please commit the script if satisfy the transaction...');

NULL;
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line('Exception Occurred'
                           ||SQLERRM);

      dbms_output.Put_line('rsl_id:'
                           ||rsl_rec.shipment_line_id);

      IF ( c_rsl_with_no_ms%isopen ) THEN
        CLOSE c_rsl_with_no_ms;
      END IF;

      ROLLBACK;

      dbms_output.Put_line('Rollbacked the transaction...');
END;