Query to Get Inventory Category Set Category Details for Item

SELECT DISTINCT msi.inventory_item_id inventory_item_id,
                msi.segment1 item_number,
                msi.description,
                msi.primary_uom_code primary_uom_code,
                msi.item_type,
                msi.organization_id organization_id,
                ood.organization_code organization_code,
                catsl.category_set_name,
                cat.segment1 family,
                cat.segment2 class,
                cat.segment3 TYPE
  FROM mtl_system_items msi,
       org_organization_definitions ood,
       inv.mtl_categories_b cat,
       inv.mtl_categories_tl cat1,
       inv.mtl_item_categories icat,
       inv.mtl_category_sets_tl catsl
 WHERE     msi.organization_id = ood.organization_id
       AND msi.inventory_item_id = icat.inventory_item_id
       AND icat.category_id = cat.category_id
       AND msi.organization_id = icat.organization_id
       AND icat.category_set_id = catsl.category_set_id
       AND catsl.category_set_id = 1
       AND cat.category_id = cat1.category_id
       -- AND msi.inventory_item_id = 662679
       AND msi.segment1 LIKE 'XRQLFB%'
       AND ood.organization_code = 'MAS';


Thanks
Amar Alam

Oracle Apps + Get On Hand Quantities through API

DECLARE

v_api_return_status                     VARCHAR2 (1);
v_qty_oh                                      NUMBER;
v_qty_res_oh                               NUMBER;
v_qty_res                                     NUMBER;
v_qty_sug                                    NUMBER;
v_qty_att                                      NUMBER;
v_qty_atr                                      NUMBER;
v_msg_count                                NUMBER;
v_msg_data                                  VARCHAR2(1000);
v_inventory_item_id                    VARCHAR2(250) := '376676';
v_organization_id                        VARCHAR2(10)  := '93';

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line ('Transaction Mode');
DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );
DBMS_OUTPUT.put_line ('Organization        :'|| v_organization_id);

apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
(p_api_version_number  => 1.0,
 p_init_msg_lst               =>     apps.fnd_api.g_false,
 x_return_status              =>     v_api_return_status,
 x_msg_count                 =>     v_msg_count,
 x_msg_data                   =>      v_msg_data,
 p_organization_id         =>      v_organization_id,
 p_inventory_item_id    =>      v_inventory_item_id,
 p_tree_mode                 =>      apps.inv_quantity_tree_pub.g_transaction_mode,
 p_onhand_source         =>      3,
 p_is_revision_control  =>      FALSE,
 p_is_lot_control           =>     FALSE,
 p_is_serial_control      =>     FALSE,
 p_revision                    =>     NULL,
 p_lot_number              =>     NULL,
 p_subinventory_code  =>     NULL,
 p_locator_id                =>     NULL,
 x_qoh                          =>     v_qty_oh,
 x_rqoh                         =>     v_qty_res_oh,
 x_qr                             =>     v_qty_res,
 x_qs                             =>     v_qty_sug,
 x_att                             =>     v_qty_att,
 x_atr                            =>      v_qty_atr);

DBMS_OUTPUT.put_line ('on hand Quantity                :'|| v_qty_oh);
DBMS_OUTPUT.put_line ('Reservable quantity on hand     :'|| v_qty_res_oh);
DBMS_OUTPUT.put_line ('Quantity reserved               :'|| v_qty_res);
DBMS_OUTPUT.put_line ('Quantity suggested              :'|| v_qty_sug);
DBMS_OUTPUT.put_line ('Quantity Available To Transact  :'|| v_qty_att);
DBMS_OUTPUT.put_line ('Quantity Available To Reserve   :'|| v_qty_atr);

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);

END;

Thanks
Amar Alam

Create View Using Execute Immediate

DECLARE
      l_string           VARCHAR2 (32767);
      viewname       VARCHAR2 (30);
      objectid           NUMBER;
      P_OBJ_ID      NUMBER:=100150;
BEGIN
      viewname := 'XX_REPORT_V_' || P_OBJ_ID;
      objectid := P_OBJ_ID;
      l_string :=
            'CREATE OR REPLACE VIEW '
         || viewname
         || ' AS SELECT (OBJ_OBJECT_SQL1||OBJ_OBJECT_SQL2||OBJ_OBJECT_SQL3) AS          SQL_QUERY '
         || ' FROM EUL5_OBJS WHERE obj_id ='''
         || objectid
         || '''';

      EXECUTE IMMEDIATE l_string;
COMMIT;
END;

Your's
Amar Alam

Trigger firing sequence in D2K (Oracle Forms)

Trigger Firing sequence:This is most important thing to understand in Oracle D2K Forms When you open a form following triggers are executed

First Logon Triggers are fired:1.PRE-LOGON
2.ON-LOGON
3.POST-LOGON

After that Pre Triggers:4. PRE-FORM
5. PRE-BLOCK
6. PRE-TEXT

After that WHEN-NEW Triggers:
7. WHEN-NEW-FORM-INSTANCE
8. WHEN-NEW-BLOCK-INSTANCE
9. WHEN-NEW-ITEM-INSTANCE

After that ITEM Level Triggers:After this focus is on the first item of the Block. If you type some data and press the tab key following trigger will fire in sequence
10.KEY-NEXT-ITEM (This trigger is present on the item level).
11.POST-CHANGE (This trigger is present on the item level).
12.WHEN-VALIDATE-ITEM (This trigger is present on the item level).
13.POST-TEXT-ITEM (This trigger is present on the item level).
14.WHEN-NEW-ITEM-INSTANCE (Block Level Trigger).

Now focus will go the next item present on the form.
If there are no further items present on the screen them if we enter data and press tab key then only KEY-NEXT-ITEM is fired. Now suppose we close the form then the item level triggers are fired.

POST TRIGGERS :

15.POST-BLOCK
16.POST-FORM

Thanks
Amar Alam