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

Query To Get The Number of Seconds or Minutes or Hours Between Two Given Dates

For the Seconds:-
-------------------
SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-
SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For Minutes:-
---------------
SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-
SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For the Hours:-
-----------------
SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-
SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Thanks
Amar Alam

Log Message Short Cut for Back End and Front End

/****************
Amar Alam
****************/
PROCEDURE display_log (p_message in varchar2)
is
BEGIN
fnd_file.put_line (fnd_file.log, p_message);
dbms_output.put_line(p_message);
END display_log;

Ex:
BEGIN
display_log('Oracle Applications Blog');
END;

Thanks
Amar Alam

Query to find BOM Components

/*********************************************************
*PURPOSE: Query to find BOM Components *
 *AUTHOR: Amar Alam *
**********************************************************/

SELECT msi.segment1 "Parent material coding",
       msi.description "Parent item description",
       msi1.segment1 "Sub item code",
       msi1.description "Sub item description"
  FROM mtl_system_items_b msi,
       bom_bill_of_materials bom,
       bom_inventory_components bic,
       mtl_system_items_b msi1
 WHERE     1 = 1
       AND msi.organization_id = 91
       AND msi.inventory_item_id = bom.assembly_item_id
       AND msi.organization_id = bom.organization_id
       AND bom.organization_id = 91
       AND bom.alternate_bom_designator IS NULL
       AND bom.bill_sequence_id = bic.bill_sequence_id
       AND bic.disable_date IS NULL
       AND bic.component_item_id = msi1.inventory_item_id
       AND msi1.organization_id = 91
       AND msi.segment1 IN ('00603141A48R')         -- Parent Item
--   AND msi1.segment1 in ('MD150') -- The child

Thanks
Amar Alam

Query to find Item Cost in Oracle Apps

/*********************************************************
*PURPOSE: Query to find Item Cost *
*AUTHOR: Amar Alam *
 **********************************************************/

SELECT msi.segment1 "ITEM_NAME",
       msi.inventory_item_id,
       cic.item_cost,
       mp.organization_code,
       mp.organization_id,
       cct.cost_type,
       cct.description,
       cic.tl_material,
       cic.tl_material_overhead,
       cic.material_cost,
       cic.material_overhead_cost,
       cic.tl_item_cost,
       cic.unburdened_cost,
       cic.burden_cost
  FROM cst_cost_types cct,
       cst_item_costs cic,
       mtl_system_items_b msi,
       mtl_parameters mp
 WHERE     cct.cost_type_id = cic.cost_type_id
       AND cic.inventory_item_id = msi.inventory_item_id
       AND cic.organization_id = msi.organization_id
       AND msi.organization_id = mp.organization_id
       AND msi.inventory_item_id = 5014
       AND mp.organization_id = 93
       AND cct.cost_type IN  ('Frozen','Pending','C16')

Thanks
Amar Alam