Monday 12 October 2015

How to find the version of a file?


SELECT a.app_short_name, a.subdir, a.filename, b.version
FROM   apps.ad_files a,
       apps.ad_file_versions b
WHERE  a.FILENAME like '%ibyxmlv%'
AND    a.file_id = b.file_id
AND    a.app_short_name = 'IBY' --- e.g. : ASF, AMS....
AND    b.creation_date = (SELECT MAX (creation_date)
                          FROM apps.ad_file_versions ver
                          WHERE ver.file_id = a.file_id)

Monday 21 September 2015

create stock for exists LOT or increase onhand quantity for existing LOT

DECLARE
   v_object_id       NUMBER;
   v_return_status   VARCHAR2 (1)    := NULL;
   v_msg_count       NUMBER          := 0;
   v_msg_data        VARCHAR2 (2000);
   l_org_code        VARCHAR2 (240);
   retval            NUMBER;
   ln_interface_id   NUMBER;
   l_return_status   VARCHAR2 (100);
   l_msg_cnt         NUMBER;
   l_msg_data        VARCHAR2 (500);
   l_trans_count     NUMBER;
   v_header_id       NUMBER;
   ln_out            NUMBER;
   ln_quantity       NUMBER;
   ln_trn_type_id    NUMBER;
   l_error           VARCHAR2 (240);
   l_locator_id      NUMBER;
   l_uom             VARCHAR2 (240);
   p_sub_inv         VARCHAR2 (240)  := 'HIMPRD';
   l_revision        VARCHAR2 (240);

   CURSOR c1
   IS
      SELECT *
        FROM mtl_lot_numbers
       WHERE lot_number IN
                ('035636');
BEGIN
   FOR i IN c1
   LOOP
      BEGIN
         SELECT inventory_location_id
           INTO l_locator_id
           FROM mtl_item_locations
          WHERE segment1 = 'W300';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT primary_uom_code
           INTO l_uom
           FROM mtl_system_items_b
          WHERE organization_id = i.organization_id
            AND inventory_item_id = i.inventory_item_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT MAX (revision)
           INTO l_revision
           FROM mtl_item_revisions
          WHERE inventory_item_id IN (
                   SELECT inventory_item_id
                     FROM mtl_lot_numbers
                    WHERE lot_number = i.lot_number
                      AND organization_id = i.organization_id)
            AND organization_id = i.organization_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT mtl_material_transactions_s.NEXTVAL
           INTO ln_interface_id
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            ln_interface_id := 0;
      END;

      BEGIN
         SELECT transaction_type_id
           INTO ln_trn_type_id
           FROM mtl_transaction_types
          WHERE transaction_type_name LIKE 'Miscellaneous receipt%';
      EXCEPTION
         WHEN OTHERS
         THEN
            ln_trn_type_id := 0;
      END;

      BEGIN
         INSERT INTO mtl_transactions_interface
                     (source_code, source_line_id, source_header_id,
                      process_flag, transaction_mode, inventory_item_id,
                      organization_id, subinventory_code, locator_id,
                      transaction_quantity, transaction_uom,
                      transaction_date, transaction_type_id,
                      transfer_subinventory, lock_flag, last_update_date,
                      last_updated_by, created_by, creation_date,
                      transaction_interface_id, transfer_locator, revision,
                      distribution_account_id, primary_quantity
                     )
              VALUES ('Miscellaneous receipt', 99, 99,
                      1, 3, i.inventory_item_id,
                      i.organization_id, p_sub_inv, l_locator_id,
                      10000, l_uom,
                      SYSDATE, ln_trn_type_id,
                      NULL, 2, SYSDATE,
                      3842, 3842, SYSDATE,
                      ln_interface_id, NULL, l_revision,
                      35449, 10000
                     );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
                   (   'Error while inserting into MTL_TRANSACTION_INTERFACE'
                    || SQLERRM
                    || SQLCODE
                   );
      END;

      ln_quantity := 10000;

      BEGIN
         INSERT INTO mtl_transaction_lots_interface
                     (transaction_interface_id, lot_number,
                      lot_expiration_date, transaction_quantity,
                      last_update_date, last_updated_by, creation_date,
                      created_by, primary_quantity
                     )
              VALUES (ln_interface_id, i.lot_number,
                      i.expiration_date, 10000,
                      SYSDATE, 3842, SYSDATE,
                      3842, 10000
                     );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               (   'Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE'
                || SQLERRM
                || SQLCODE
               );
      END;

      BEGIN
         SELECT mtl_material_transactions_s.NEXTVAL
           INTO v_header_id
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            v_header_id := 0;
      END;

      BEGIN
         UPDATE mtl_transactions_interface
            SET transaction_header_id = v_header_id,
                transaction_date = SYSDATE
          WHERE source_code = 'Miscellaneous receipt'
            AND transaction_header_id IS NULL;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
                           ('Error while updating MTL_TRANSACTIONS_INTERFACE');
      END;

      COMMIT;

      BEGIN
         retval :=
            apps.inv_txn_manager_pub.process_transactions
                                         (p_api_version           => 1.0,
                                          p_init_msg_list         => 'T',
                                          p_commit                => 'T',
                                          p_validation_level      => 100,
                                          x_return_status         => l_return_status,
                                          x_msg_count             => l_msg_cnt,
                                          x_msg_data              => l_msg_data,
                                          x_trans_count           => l_trans_count,
                                          p_table                 => 1,
                                          p_header_id             => v_header_id
                                         );
         DBMS_OUTPUT.put_line ('l_msg_data:' || l_msg_data);
         DBMS_OUTPUT.put_line ('l_return_status:' || l_return_status);
         DBMS_OUTPUT.put_line ('v_header_id:' || v_header_id);
         DBMS_OUTPUT.put_line ('retval:' || retval);
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error := SQLERRM || SQLCODE;
            DBMS_OUTPUT.put_line ('ERROR occur:' || l_error);
      END;
   END LOOP;
END;

LOT creation using api


DECLARE
   v_object_id           NUMBER;
   v_return_status       VARCHAR2 (1)    := NULL;
   v_msg_count           NUMBER          := 0;
   v_msg_data            VARCHAR2 (2000);
   v_lot_number          VARCHAR2 (50);
   v_expire_date         DATE            := SYSDATE + 180;
   v_organization_id     NUMBER          := 122;
   v_inventory_item_id   NUMBER          := 19160;
BEGIN
   fnd_global.apps_initialize (3842, 51327, 385);
   DBMS_OUTPUT.put_line ('Calling API to Create Lot Number');
   v_lot_number :=
      inv_lot_api_pub.auto_gen_lot
                           (p_org_id                 => v_organization_id,
                            p_inventory_item_id      => v_inventory_item_id,
                            p_parent_lot_number      => NULL,
                            p_subinventory_code      => NULL,
                            p_locator_id             => NULL,
                            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          => v_return_status,
                            x_msg_count              => v_msg_count,
                            x_msg_data               => v_msg_data
                           );

   IF v_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line
                       (   'The Auto generation of Lot Number is Sucessful: '
                        || v_lot_number
                       );
   ELSE
      DBMS_OUTPUT.put_line ('The Auto generation of Lot Number Failed');
      ROLLBACK;

      FOR i IN 1 .. v_msg_count
      LOOP
         v_msg_data := fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F');
         DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
      END LOOP;
   END IF;

   IF v_lot_number IS NOT NULL
   THEN
      DBMS_OUTPUT.put_line ('Calling API to Create/Insert a Lot Numbers');
      inv_lot_api_pub.insertlot
                           (p_api_version            => 1,
                            p_init_msg_list          => fnd_api.g_false,
                            p_commit                 => fnd_api.g_false,
                            p_validation_level       => fnd_api.g_valid_level_full,
                            p_inventory_item_id      => v_inventory_item_id,
                            p_organization_id        => v_organization_id,
                            p_lot_number             => v_lot_number,
                            p_expiration_date        => v_expire_date,
                            x_object_id              => v_object_id,
                            x_return_status          => v_return_status,
                            x_msg_count              => v_msg_count,
                            x_msg_data               => v_msg_data
                           );

      IF v_return_status = fnd_api.g_ret_sts_success
      THEN
         COMMIT;
         DBMS_OUTPUT.put_line (   'The Creation of Lot Number is Sucessful: '
                               || v_object_id
                              );
      ELSE
         DBMS_OUTPUT.put_line ('The Creation of Lot Number Failed');
         ROLLBACK;

         FOR i IN 1 .. v_msg_count
         LOOP
            v_msg_data :=
                         fnd_msg_pub.get (p_msg_index      => i,
                                          p_encoded        => 'F');
            DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
         END LOOP;
      END IF;
   END IF;
END;


Wednesday 9 September 2015

API to Add and Delete Values in Value Set



DECLARE

   v_enabled_flag        VARCHAR2 (2)                  := 'Y';

   v_summary_flag        VARCHAR2 (2)                  := 'Y';

   v_start_date_active   DATE                          := SYSDATE;

   v_error_msg           VARCHAR2 (1000)               := NULL;

   v_who_type            fnd_flex_loader_apis.who_type;

   v_request_id          NUMBER;

   v_rec_success         NUMBER;

   v_rec_error           NUMBER;

   v_rec_cnt             NUMBER                        := 0;

   v_user_id             NUMBER                        := fnd_global.user_id;

   v_login_id            NUMBER                        := fnd_global.login_id;

   v_req_id              NUMBER                 := fnd_global.conc_request_id;

   v_value_set_name      VARCHAR2 (50)                 := 'TESTVALUESET';

   v_value_set_value     VARCHAR2 (50)                 := 'SAMPLEVALUE';

BEGIN

   v_who_type.created_by := v_user_id;

   v_who_type.creation_date := SYSDATE;

   v_who_type.last_updated_by := v_user_id;

   v_who_type.last_update_date := SYSDATE;

   v_who_type.last_update_login := v_login_id;


   BEGIN

      fnd_flex_values_pkg.load_row

                                 (x_flex_value_set_name             => v_value_set_name,

                                  x_parent_flex_value_low           => NULL,

                                  x_flex_value                      => v_value_set_value,

                                  x_who                             => v_who_type,

                                  x_enabled_flag                    => v_enabled_flag,

                                  x_summary_flag                    => v_summary_flag,

                                  x_start_date_active               => v_start_date_active,

                                  x_end_date_active                 => NULL,

                                  x_parent_flex_value_high          => NULL,

                                  x_structured_hierarchy_level      => NULL,

                                  x_hierarchy_level                 => NULL,

                                  x_compiled_value_attributes       => NULL,

                                  x_value_category                  => NULL,

                                  x_attribute1                      => NULL,

                                  x_attribute2                      => NULL,

                                  x_attribute3                      => NULL,

                                  x_attribute4                      => NULL,

                                  x_attribute5                      => NULL,

                                  x_attribute6                      => NULL,

                                  x_attribute7                      => NULL,

                                  x_attribute8                      => NULL,

                                  x_attribute9                      => NULL,

                                  x_attribute10                     => NULL,

                                  x_attribute11                     => NULL,

                                  x_attribute12                     => NULL,

                                  x_attribute13                     => NULL,

                                  x_attribute14                     => NULL,

                                  x_attribute15                     => NULL,

                                  x_attribute16                     => NULL,

                                  x_attribute17                     => NULL,

                                  x_attribute18                     => NULL,

                                  x_attribute19                     => NULL,

                                  x_attribute20                     => NULL,

                                  x_attribute21                     => NULL,

                                  x_attribute22                     => NULL,

                                  x_attribute23                     => NULL,

                                  x_attribute24                     => NULL,

                                  x_attribute25                     => NULL,

                                  x_attribute26                     => NULL,

                                  x_attribute27                     => NULL,

                                  x_attribute28                     => NULL,

                                  x_attribute29                     => NULL,

                                  x_attribute30                     => NULL,

                                  x_attribute31                     => NULL,

                                  x_attribute32                     => NULL,

                                  x_attribute33                     => NULL,

                                  x_attribute34                     => NULL,

                                  x_attribute35                     => NULL,

                                  x_attribute36                     => NULL,

                                  x_attribute37                     => NULL,

                                  x_attribute38                     => NULL,

                                  x_attribute39                     => NULL,

                                  x_attribute40                     => NULL,

                                  x_attribute41                     => NULL,

                                  x_attribute42                     => NULL,

                                  x_attribute43                     => NULL,

                                  x_attribute44                     => NULL,

                                  x_attribute45                     => NULL,

                                  x_attribute46                     => NULL,

                                  x_attribute47                     => NULL,

                                  x_attribute48                     => NULL,

                                  x_attribute49                     => NULL,

                                  x_attribute50                     => NULL,

                                  x_attribute_sort_order            => NULL,

                                  x_flex_value_meaning              => v_value_set_value,

                                  x_description                     => v_value_set_value

                                 );

      COMMIT;

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Error is ' || SUBSTR (SQLERRM, 1, 1000));

   END;

END;


API to Delete Values in Value Set:



DECLARE
   l_err_msg   VARCHAR2 (500) := NULL;

   CURSOR c1
   IS
      SELECT ffv.flex_value_id, ffv.flex_value
        FROM fnd_flex_value_sets ffvs,
             fnd_flex_values ffv,
             fnd_flex_values_tl ffvt
       WHERE flex_value_set_name = 'XXHL_DATE_FORMAT'          -- Value Set Name
         AND ffv.flex_value_set_id = ffvs.flex_value_set_id
         AND ffvt.flex_value_id = ffv.flex_value_id
         AND ffvs.flex_value_set_id = ffv.flex_value_set_id
         AND ffvt.LANGUAGE = 'US'
         AND ffv.enabled_flag = 'Y'
         AND ffv.summary_flag = 'N'
         AND ffv.flex_value_id = 309564;
BEGIN
   FOR i IN c1
   LOOP
      fnd_flex_values_pkg.delete_row (i.flex_value_id);
      COMMIT;
      DBMS_OUTPUT.put_line (i.flex_value_id || ' Deleted  Successfully !!!!');
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Exception: ' || l_err_msg);
END;

Tuesday 8 September 2015

value set tables in oracle apps r12

  SELECT v.*
  FROM fnd_flex_value_sets s, fnd_flex_validation_tables v
 WHERE s.flex_value_set_name = 'XHL_ORG_CODE'
   AND s.flex_value_set_id = v.flex_value_set_id

Wednesday 29 July 2015

How to Kill the session

select * from v$session where sid in (select session_id from dba_ddl_locks where owner='APPS' and name='XHL_IBOM_REPORT_PRC');

owner=instance user name
name=object name

alter system kill session '984,45554';

984=sid and 45554=SERIAL#

Friday 10 July 2015

Item Category Creation using API

DECLARE
    l_category_rec    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
    l_return_status   VARCHAR2(80);
    l_error_code      NUMBER;
    l_msg_count       NUMBER;
    l_msg_data        VARCHAR2(80);
    l_out_category_id NUMBER;
    BEGIN
      l_category_rec.segment1 := 'RED';
    
      SELECT f.ID_FLEX_NUM
        INTO l_category_rec.structure_id
        FROM FND_ID_FLEX_STRUCTURES f
       WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS';
    
      l_category_rec.description := 'Red';
    
      INV_ITEM_CATEGORY_PUB.Create_Category
              (
              p_api_version   => 1.0,
              p_init_msg_list => FND_API.G_FALSE,
              p_commit        => FND_API.G_TRUE,
              x_return_status => l_return_status,
              x_errorcode     => l_error_code,
              x_msg_count     => l_msg_count,
              x_msg_data      => l_msg_data,
              p_category_rec  => l_category_rec,
              x_category_id   => l_out_category_id
              );
      IF l_return_status = fnd_api.g_ret_sts_success THEN
        COMMIT;
        DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
      ELSE
        DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
        ROLLBACK;
      END IF;
    END ;

Thursday 18 June 2015

Update On hand Status using API

CREATE OR REPLACE PROCEDURE XX_UPDATE_MTL_STS
AS
   -- Common Declarations
   l_api_version      NUMBER := 1.0;
   l_init_msg_list    VARCHAR2 (2) := FND_API.G_TRUE;
   l_commit           VARCHAR2 (2) := FND_API.G_FALSE;
   x_return_status    VARCHAR2 (2);
   x_msg_count        NUMBER := 0;
   x_msg_data         VARCHAR2 (255);

   -- WHO columns
   l_user_id          NUMBER := -1;
   l_resp_id          NUMBER := -1;
   l_application_id   NUMBER := -1;
   l_row_cnt          NUMBER := 1;
   l_user_name        VARCHAR2 (30) := 'MFG';
   l_resp_name        VARCHAR2 (50)
                         := 'Manufacturing and Distribution Manager';

   -- API specific declarations
   l_object_type      VARCHAR2 (20);
   l_status_rec       INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
BEGIN
   -- Initialize variables
   l_object_type := 'H'; -- 'O' = Lot , 'S' = Serial, 'Z' = Subinventory, 'L' = Locator, 'H' = Onhand

   l_status_rec.organization_id := 209;
   l_status_rec.inventory_item_id := 516963;
   l_status_rec.lot_number := 'EXPLOT200';
   l_status_rec.zone_code := 'RIP';
   l_status_rec.locator_id := NULL;
   l_status_rec.status_id := 1; -- select status_id, status_code from mtl_material_statuses_vl;
   l_status_rec.update_reason_id := 305; --'Reviewed';  -- select reason_id, reason_name from mtl_transaction_reasons where reason_type_display = 'Update Status';
   l_status_rec.update_method := 2;

   /*
       l_status_rec.serial_number         := fnd_api.g_miss_char;
       l_status_rec.to_serial_number      := fnd_api.g_miss_char;
       l_status_rec.lpn_id                := fnd_api.g_miss_num;
       l_status_rec.initial_status_flag   := fnd_api.g_miss_char;
       l_status_rec.from_mobile_apps_flag := fnd_api.g_miss_char;
       l_status_rec.grade_code            := fnd_api.g_miss_char;
       l_status_rec.primary_onhand        := fnd_api.g_miss_num;
       l_status_rec.secondary_onhand      := fnd_api.g_miss_num;
       l_status_rec.group_id              := fnd_api.g_miss_num;
       l_status_rec.pending_status        := fnd_api.g_miss_num;
   */
   -- Get the user_id
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

   -- Get the application_id and responsibility_id
   SELECT application_id, responsibility_id
     INTO l_application_id, l_resp_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name = l_resp_name;

   FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_application_id);
   DBMS_OUTPUT.put_line (
         'Initialized applications context: '
      || l_user_id
      || ' '
      || l_resp_id
      || ' '
      || l_application_id);

   -- call API to update material status
   DBMS_OUTPUT.PUT_LINE (
      '=======================================================');
   DBMS_OUTPUT.PUT_LINE ('Calling INV_MATERIAL_STATUS_PUB.Update_Status');

   INV_MATERIAL_STATUS_PUB.update_status (
      p_api_version_number   => l_api_version,
      p_init_msg_lst         => l_init_msg_list,
      p_commit               => l_commit,
      x_return_status        => x_return_status,
      x_msg_count            => x_msg_count,
      x_msg_data             => x_msg_data,
      p_object_type          => l_object_type,
      p_status_rec           => l_status_rec);

   DBMS_OUTPUT.PUT_LINE (
      '=======================================================');
   DBMS_OUTPUT.PUT_LINE ('Return Status: ' || x_return_status);

   IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error Message :' || x_msg_data);
   END IF;

   DBMS_OUTPUT.PUT_LINE (
      '=======================================================');

END XX_UPDATE_MTL_STS;