How to Query 'Total Onhand', 'Available To Reserve' & 'Available To Transact' Quantity of an ITEM in ORACLE APPS R21

/*Get ONHAND QUANTITIES through API in Oracle Apps R12 (INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES)*/

CREATE OR replace FUNCTION xx_get_ohqty_api(p_inv_item_id       IN VARCHAR2,

                                            p_org_id            NUMBER,
                                            p_subinventory_code VARCHAR2,
                                            p_lot_number        VARCHAR2,
                                            p_locator_id        NUMBER,
                                            p_qty_type          IN VARCHAR2)
RETURN NUMBER
IS
  x_return_status       VARCHAR2 (50);
  x_msg_count           VARCHAR2 (50);
  x_msg_data            VARCHAR2 (50);
  v_item_id             NUMBER;
  v_organization_id     NUMBER;
  v_qoh                 NUMBER;
  v_rqoh                NUMBER;
  v_atr                 NUMBER;
  v_att                 NUMBER;
  v_qr                  NUMBER;
  v_qs                  NUMBER;
  v_lot_control_code    BOOLEAN;
  v_serial_control_code BOOLEAN;
  l_qty                 NUMBER;
BEGIN
    SELECT inventory_item_id,
           mp.organization_id
    INTO   v_item_id, v_organization_id
    FROM   mtl_system_items_b msib,
           mtl_parameters mp
    WHERE  msib.inventory_item_id = p_inv_item_id
           AND msib.organization_id = mp.organization_id
           AND msib.organization_id = p_org_id; -- :organization_code;
    v_qoh := NULL;

    v_rqoh := NULL;

    v_atr := NULL;

    v_lot_control_code := FALSE;

    v_serial_control_code := FALSE;

    fnd_client_info.Set_org_context (1);

    inv_quantity_tree_grp.clear_quantity_cache;

    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_organization_id, 

    p_inventory_item_id => v_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_revision,
    p_lot_number => p_lot_number, 

    p_lot_expiration_date => SYSDATE,
    p_subinventory_code => p_subinventory_code, -- p_subinventory_code,
    p_locator_id => p_locator_id, -- p_locator_id,
    p_onhand_source => 3, 

    x_qoh => v_qoh, -- Quantity on-hand
    x_rqoh => v_rqoh, --reservable quantity on-hand
    x_qr => v_qr, 

    x_qs => v_qs, 

    x_att => v_att, -- available to transact
    x_atr => v_atr -- available to reserve
    );

    IF p_qty_type = 'OHQ' THEN --On Hand qty
      l_qty := v_qoh; --v_QuantityOnhand;
    ELSE
      IF p_qty_type = 'ATR' THEN --Available to Reserve
        l_qty := v_atr;
      ELSE
        IF p_qty_type = 'ATT' THEN --Available to Transact
          l_qty := v_att;
        END IF;
      END IF;
    END IF;

    RETURN l_qty;

    dbms_output.Put_line ('On-Hand Quantity: '|| v_qoh);

    dbms_output.Put_line ('Available to reserve: '|| v_atr);

    dbms_output.Put_line ('Quantity Reserved: '|| v_qr);

    dbms_output.Put_line ('Quantity Suggested: '|| v_qs);

    dbms_output.Put_line ('Available to Transact: '|| v_att);

    dbms_output.Put_line ('Available to Reserve: '|| v_atr);
EXCEPTION
  WHEN OTHERS THEN
             dbms_output.Put_line ('ERROR: '|| SQLERRM);
END xx_get_ohqty_api; 

/*Query to find Onhand Quantity of an ITEM in Oracle APPS R12*/
SELECT moqd.organization_id,
       moqd.inventory_item_id,
       msib.segment1                          item_code,
       moqd.subinventory_code                 ISSUE_SUBINV,
       moqd.revision,
       msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
       moqd.locator_id,
       moqd.lot_number,
       NULL                                   serial_number,
       SUM(moqd.primary_transaction_quantity) onhand_quantity,
       --SUM(moqd.transaction_quantity) onhand_quantity,
       milkfv.concatenated_segments           locators
FROM   apps.mtl_system_items_b msib,
       apps.mtl_onhand_quantities_detail moqd,
       apps.mtl_item_locations_kfv milkfv
WHERE  msib.organization_id = 105
       AND msib.inventory_item_id = moqd.inventory_item_id
       AND msib.organization_id = moqd.organization_id
       AND moqd.subinventory_code = 'S19'
       AND milkfv.inventory_location_id(+) = moqd.locator_id
       AND msib.inventory_item_status_code = 'Active'
       AND msib.mtl_transactions_enabled_flag = 'Y'
--AND msib.segment1 = :p_item
GROUP  BY moqd.organization_id,
          moqd.inventory_item_id,
          msib.segment1,
          moqd.subinventory_code,
          moqd.revision,
          msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
          moqd.locator_id,
          moqd.lot_number,
          milkfv.concatenated_segments
UNION
SELECT moqd.organization_id,
       moqd.inventory_item_id,
       msib.segment1                item_code,
       moqd.subinventory_code,
       moqd.revision,
       msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
       moqd.locator_id,
       moqd.lot_number,
       serial_number,
       1                            onhand_quantity,
       --SUM(moqd.transaction_quantity) onhand_quantity,
       milkfv.concatenated_segments locators
FROM   mtl_system_items_b msib,
       mtl_onhand_quantities_detail moqd,
       mtl_item_locations_kfv milkfv,
       mtl_serial_numbers msn
WHERE  1 = 1
       AND msib.organization_id = 105
       AND msib.inventory_item_id = moqd.inventory_item_id
       AND msib.organization_id = moqd.organization_id
       AND moqd.subinventory_code = 'S19'
       AND milkfv.inventory_location_id(+) = moqd.locator_id
       AND msib.inventory_item_status_code = 'Active'
       AND msib.mtl_transactions_enabled_flag = 'Y'
       AND msn.current_organization_id = moqd.organization_id
       AND moqd.subinventory_code = msn.current_subinventory_code
       AND msn.current_status = 3 --Resides in stores
       AND moqd.inventory_item_id = msn.inventory_item_id
       AND moqd.lot_number = msn.lot_number
GROUP  BY moqd.organization_id,
          moqd.inventory_item_id,
          msib.segment1,
          moqd.subinventory_code,
          moqd.revision,
          msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
          moqd.locator_id,
          moqd.lot_number,
          serial_number,
          milkfv.concatenated_segments; 

P.S.

Available to Reserve = (Quantity in on hand) – (Quantity already reserved for other orders)

i.e.

Available to Reserve = (Total Quantity in Reservable Subinventory) – (Total Quantity of the Item in the MTL_RESERVATIONS table)

Available to Transact = Quantity already reserved and ready to transact

SELECT SUM(transaction_quantity)
INTO   tr_qty
FROM   mtl_onhand_quantities_detail
WHERE  inventory_item_id = :INVENTORY_ITEM_ID
AND    organization_id =:P_ORG_ID ;


SELECT SUM(reservation_quantity)
INTO   tr_qty_b
FROM   mtl_reservations
WHERE  inventory_item_id = :INVENTORY_ITEM_ID
AND    organization_id = :P_ORG_ID ;


SELECT SUM(transaction_quantity)
INTO   tr_qty_c
FROM   mtl_onhand_quantities_detail
WHERE  inventory_item_id = :INVENTORY_ITEM_ID
AND    organization_id = :P_ORG_ID
AND    subinventory_code IN
       (
              SELECT secondary_inventory_name
              FROM   mtl_secondary_inventories
              WHERE  organization_id =:P_ORG_ID
              AND    reservable_type = '2');

Available_to_transact = nvl(tr_qty,0) – nvl(tr_qty_b,0);

Available_to_reserve = nvl(tr_qty,0) – (nvl(tr_qty_b,0)+ nvl(tr_qty_c,0));

Query for the Details Of Requisition In Oracle APPS R12

 

Interface Tables:

• PO_REQUISITIONS_INTERFACE_ALL
• PO_REQ_DIST_INTERFACE_ALL
• PO_INTERFACE_ERRORS where interface_type =’REQIMPORT’ (Error Table)

Base Tables:

• PO_REQUISITIONS_HEADERS_ALL
• PO_REQUISITION_LINES_ALL
• PO_REQ_DISTRIBUTIONS_ALL

Concurrent Program:

• Requisition Import

SELECT hou.name      operating_unit ,
       prh2.segment1 requisition_number ,
       prh2.description ,
       msi2.segment1              item_code ,
       prl2.line_num              pr_line_num ,
       prl2.item_description      req_line_desc ,
       prl2.unit_meas_lookup_code uom ,
       prh2.authorization_status  req_status ,
       ppf.full_name              preparer ,
       prh2.type_lookup_code      requisition_type ,
       hout.name                  organization ,
       hla.location_code          location ,
       ppf2.full_name             requisiter ,
       prl2.quantity ,
       prl2.unit_price unit_cost
FROM   apps.po_requisition_headers_all prh2 ,
       apps.po_requisition_lines_all prl2 ,
       apps.per_all_people_f ppf ,
       apps.per_all_people_f ppf2 ,
       apps.mtl_system_items_b msi2 ,
       apps.hr_locations_all hla ,
       apps.hr_all_organization_units_tl hout ,
       apps.mtl_categories mic ,
       apps.hr_operating_units hou
WHERE  1=1
AND    hou.organization_id=prh2.org_id
AND    Nvl(prh2.cancel_flag,'N')<>'Y'
AND    Nvl(prl2.cancel_flag,'N')<>'Y'
AND    hout.organization_id(+) = prl2.destination_organization_id
AND    hout.LANGUAGE(+) = Userenv ('LANG')
AND    ppf2.person_id(+)=prl2.to_person_id
AND    prh2.org_id=:P_ORG_ID
AND    hla.location_id(+) = prl2.deliver_to_location_id
AND    mic.category_id(+)=prl2.category_id
AND    msi2.inventory_item_id=prl2.item_id
AND    msi2.inventory_item_id(+) = prl2.item_id
AND    msi2.organization_id(+) = prl2.destination_organization_id
AND    prl2.requisition_header_id=prh2.requisition_header_id
AND    ppf.person_id=prh2.preparer_id
AND    Trunc(SYSDATE) BETWEEN ppf.effective_start_date AND    ppf.effective_end_date
AND    Trunc(SYSDATE) BETWEEN ppf2.effective_start_date(+) AND    ppf2.effective_end_date(+)
AND    prh2.segment1 : :P_Requisition_Number;

API to Assign Salesperson in Another Operating Unit in Oracle APPS R12

 

CREATE OR replace PROCEDURE xx_assign_salesrep_api(p_org_id      NUMBER,
                                                   p_salesrep_id NUMBER)
AS
  x_return_status VARCHAR2(30);
  x_msg_count     NUMBER;
  x_msg_data      VARCHAR2(2000);
  x_salesrep_id   jtf_rs_salesreps.salesrep_id%TYPE;
BEGIN
    fnd_global.Apps_initialize(0, <RESP_ID>, 222);

    mo_global.Set_policy_context('S', p_org_id);

    FOR c1 IN (SELECT *
               FROM   apps.jtf_rs_salesreps
               WHERE  salesrep_id = p_salesrep_id) LOOP
        jtf_rs_salesreps_pub.Create_salesrep_migrate 

        (p_api_version => 1.0,
        p_init_msg_list => fnd_api.g_true, 

        p_resource_id => c1.resource_id,
        p_sales_credit_type_id => c1.sales_credit_type_id,
        p_set_of_books_id => c1.set_of_books_id,
        p_start_date_active => c1.start_date_active,
        p_name => c1.name, 

        p_email_address => c1.email_address,
        p_status => c1.status,
        p_salesrep_number => c1.salesrep_number, 

        p_salesrep_id => c1.salesrep_id,
        p_org_id => p_org_id, 

        x_return_status => x_return_status,
        x_msg_count => x_msg_count, 

        x_msg_data => x_msg_data,
        x_salesrep_id => x_salesrep_id);

        COMMIT;

        dbms_output.Put_line('SALESREP ID->'||x_salesrep_id);
        dbms_output.Put_line(' RETURN STATUS->'||x_return_status);
        dbms_output.Put_line('MSG DATA->' ||x_msg_data);

    END LOOP;
END; 

API to Populate India GST Tax lines in Oracle APPS R12 Sales Order

 

Currently we have gone through a case where all sales order lines were not populated in India GST Tax form. To resolve this issue i.e. to populate remaining lines in India-GST-Tax form, we had to run below script:

DECLARE
    l_return_code     VARCHAR2(100);
    l_return_message  VARCHAR2(2000);
    l_event_class_rec zx_api_pub.event_class_rec_type;
    l_lines           oe_order_lines_all%ROWTYPE;
BEGIN
    fnd_global.Apps_initialize(fnd_global.user_id, 50278, 660);

    FOR c1 IN (SELECT *
               FROM   oe_order_lines_all A
               WHERE  header_id = 9348683
                      AND NOT EXISTS (SELECT 1
                                      FROM   jai_tax_det_factors X
                                      WHERE  X.trx_id = A.header_id
                                             AND X.trx_line_id = A.line_id
                                             AND X.application_id = 660)) LOOP
        l_event_class_rec.application_id := 660;

        l_event_class_rec.event_class_code := 'SALES_TRANSACTION_TAX_QUOTE';

        l_event_class_rec.event_type_code := 'CREATE';

        l_event_class_rec.entity_code := 'OE_ORDER_HEADERS';

        l_event_class_rec.trx_id := c1.header_id;

        l_event_class_rec.trx_date := SYSDATE;

        l_event_class_rec.tax_event_class_code := 'SALES_TRANSACTION';

        l_event_class_rec.tax_event_type_code := 'CREATE';

        l_event_class_rec.internal_organization_id := 105;

        -- l_event_class_rec.TAX_INVOICE_DATE :=SYSDATE;
        --l_event_class_rec.LINE_LEVEL_ACTION :='CREATE';
        l_event_class_rec.legal_entity_id := 101;

        l_event_class_rec.ledger_id := 1001;

        jai_om_tax_det_pkg.Determine_factors 

        (p_action_code => 'CREATE',
         p_line_id => c1.line_id, 

         p_event_class_rec => l_event_class_rec,
         x_return_code => l_return_code, 

         x_return_message => l_return_message,
         p_call_from => 'API');

        fnd_file.Put_line(fnd_file.log, l_return_code||'-'||l_return_message);
    END LOOP;

    COMMIT;
END; 

Oracle EBS Forms: How to know query behind LOV

 Find the SQL behind List of Values(LOV) of an EBS Form:

Step1 : Find the name of the form. (Help -> About Oracle Applications)

Step2 : Invoke the List of values. Enter the filter condition and Click Find Button.

Step3 : Keep the screen in the above state. Open the database session connecting to APPS schema and execute the below SQL.

SELECT (SELECT To_char (sql_fulltext)
        FROM   v$sqlarea
        WHERE  sql_id = ses.prev_sql_id) sql_behind_lov
FROM   v$session ses,
       v$sqlarea sq
WHERE  ses.MODULE LIKE '%&form_name%'
       AND client_identifier = '&user_name'
       AND sq.sql_id (+) = ses.sql_id;