Inventory - Onhand , Transaction Quantity , Reserve Quantity

-------------------------------------------------------------------------------------------------------------------

create table  xyka_test_todrop_bkp  as ( select * from xyka_test_todrop)

commit

drop table xyka_test_todrop


create table xyka_test_todrop
as
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM, sum(ON_HAND) ON_HAND
from mtl_onhand_total_v
where ORGANIZATION_ID=105
and SUBINVENTORY_CODE = 'BMD'
group by ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM



alter table xyka_test_todrop
add( qty_transact number,
    qty_reserve number );




select * from xyka_test_todrop

--where item = 'BH-28'

select * from hr_operating_units


DECLARE
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
a number := 0;
b number := 0;
BEGIN
inv_globals.set_org_id (85);
inv_quantity_tree_pub.clear_quantity_cache;
for x in (  
    select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM,ON_HAND
    from xyka_test_todrop
    --where INVENTORY_ITEM_ID = 8367
           --and LOCATOR_ID = 18471
           --and ORGANIZATION_ID = 112
           --and SUBINVENTORY_CODE = 'IBS'
     )
LOOP
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => x.ORGANIZATION_ID,
p_inventory_item_id => x.INVENTORY_ITEM_ID,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => x.SUBINVENTORY_CODE,
p_locator_id => x.LOCATOR_ID,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);
update  xyka_test_todrop
set QTY_TRANSACT = X_att,
    qty_reserve = x_atr
where INVENTORY_ITEM_ID = x.INVENTORY_ITEM_ID
           and LOCATOR_ID = x.LOCATOR_ID
           and ORGANIZATION_ID = 105
           and SUBINVENTORY_CODE = 'BMD';
a := sql%rowcount;
b := a + b;          
end loop;
dbms_output.put_line(to_char(b));
END;


select * from xyka_test_todrop

commit

XML Reports An invalid character was found in text content.

You need to update your profile in 11i / R12 .

change this to UTF8. We had this issue with our international documents .

Go To - System Administrator > Profiles > Find for below profile . change value to - UFT 8

FND: NATIVE CLIENT ENCODING

Before >> 

After >>

WMS - Pick Pending Summery Report

/* Formatted on 4/9/2012 3:12:18 PM (QP5 v5.114.809.3010) */
  SELECT   ROW_NUMBER ()
              OVER (
                 ORDER BY
                    oel.shipping_method_code, hp.party_name, oeh.order_number
              )
              AS sr_no,
           mmtt.transaction_temp_id task_no,
           wdt.task_id,
           hp.party_name customer,
           msi.DESCRIPTION,
           flv.meaning task_status,
           fu1.user_name USER_name,
           mmtt.transaction_quantity QUANTITY,
           oeh.order_number,
           mmtt.SUBINVENTORY_CODE,
           mmtt.TRANSACTION_SOURCE_NAME,
           wt.user_task_type_code EQUIPMENT,
           mmtt.LOCATOR_ID,
           oel.shipping_method_code route,
           ot.name order_type
    FROM   hz_parties hp,
           hz_cust_accounts hca,
           oe_order_headers_all oeh,
           mtl_material_transactions_temp mmtt,
           wms_dispatched_tasks wdt,
           oe_order_lines_all oel,
           fnd_lookup_values flv,
           fnd_user fu1,
           mtl_system_items_b msi,
           oe_transaction_types_tl ot,
           wms_tasks_v wt
   WHERE       msi.organization_id = mmtt.organization_id
           AND msi.inventory_item_id = mmtt.inventory_item_id
           AND oel.line_id = mmtt.trx_source_line_id
           AND flv.lookup_type = 'WMS_TASK_STATUS'
           AND flv.lookup_code =
                 NVL (wdt.status, NVL (mmtt.wms_task_status, 1))
           AND flv.language = 'US'
           AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
           AND oeh.header_id = oel.header_id
           AND fu1.employee_id(+) = wdt.person_id
           AND oeh.sold_to_org_id = hca.cust_account_id
           AND hca.party_id = hp.party_id
           AND flv.MEANING IN ('Pending', 'Unreleased')
           AND oeh.order_type_id = ot.transaction_type_id
           AND wt.transaction_temp_id = mmtt.transaction_temp_id
           AND hp.party_name = NVL (:P_CUST, hp.party_name)
           AND wt.user_task_type_code =
                 NVL (:P_EQUIPMENT, wt.user_task_type_code)
           AND oeh.order_number = NVL (:P_ORD_NO, oeh.order_number)
           AND ot.name = NVL (:P_ORD_TYP, ot.name)
           AND ot.language = 'US'
           AND NVL (oel.shipping_method_code, -99) =
                 NVL (:P_ROUTE, NVL (oel.shipping_method_code, -99))
           AND mmtt.SUBINVENTORY_CODE =
                 NVL (:P_SUB_INV, mmtt.SUBINVENTORY_CODE)
           AND TRUNC (mmtt.creation_date) BETWEEN TRUNC(NVL (
                                                           :P_FRM_DT,
                                                           mmtt.creation_date
                                                        ))
                                              AND  TRUNC(NVL (
                                                            :P_TO_DT,
                                                            mmtt.creation_date
                                                         ))
           AND mmtt.organization_id =
                 NVL (:P_ORGANIZATION_ID, mmtt.organization_id)
ORDER BY   oel.shipping_method_code, hp.party_name, oeh.order_number

WMS - LPN In Pre Stagging Report

/* Formatted on 4/9/2012 2:44:15 PM (QP5 v5.114.809.3010) */
  SELECT   ROW_NUMBER () OVER (ORDER BY wdd.ship_method_code) AS sr_no,
           COUNT (wlc.quantity) no_of_lines,
           SUM (wlc.quantity) transaction_quantity,
           mil.concatenated_segments concatenated_segments,
           wlpn.license_plate_number,
           wsc.parent_lpn,
           wlpn.lpn_id,
           wdd.ship_method_code,
           wdd.source_line_id,
           wdd.subinventory
    FROM   wms_license_plate_numbers wlpn,
           wms_lpn_contents wlc,
           mtl_item_locations_kfv mil,
           wsh_deliverables_v wdd,
           wsh_dsno_containers_v wsc
   WHERE       1 = 1
           AND wlpn.lpn_id = wlc.parent_lpn_id
           AND wdd.container_instance_id = wsc.container_instance_id
           AND wdd.lpn_id = wlpn.lpn_id
           AND mil.inventory_location_id = wdd.locator_id
           AND wdd.ship_method_code = NVL (:p_route, wdd.ship_method_code)
           AND mil.concatenated_segments =
                 NVL (:p_loc_segs, mil.concatenated_segments)
           AND wdd.organization_id =
                 NVL (:p_organization_id, wdd.organization_id)           --122
           AND mil.subinventory_code = 'STGN'
           AND UPPER (wdd.released_status_name) = 'PICKED'
           AND NOT EXISTS (SELECT   *
                             FROM   wsh_dsno_containers_v
                            WHERE   wlpn.license_plate_number = parent_lpn)
GROUP BY   mil.concatenated_segments,
           wlpn.license_plate_number,
           wsc.parent_lpn,
           wlpn.lpn_id,
           wdd.ship_method_code,
           wdd.source_line_id,
           wdd.subinventory;

WMS - Pending Task Report

SELECT   wt.transaction_temp_id task_no,
         hp.party_name customer,
         wt.item item_number,
         wt.ITEM_DESCRIPTION,
         wt.status_code,
         wt.status,
         wt.TRANSACTION_QUANTITY,
         oel.SHIPPING_METHOD_CODE,
         oeh.order_number,
         fu1.user_name,
         wt.user_task_type_code,
         wt.subinventory_code
  FROM   wms_tasks_v WT,
         apps.oe_order_lines_all oel,
         apps.oe_order_headers_all oeh,
         apps.fnd_user fu1,
         hz_parties hp,
         hz_cust_accounts hca
 WHERE       1 = 1                        --TRANSACTION_SOURCE_TYPE in ('RMA')
         AND TASK_TYPE_DESCRIPTION = 'Pick'
         AND STATUS_CODE <> 'Completed'
         AND oel.line_id = WT.TRANSACTION_SOURCE_LINE_ID
         AND oeh.header_id = oel.header_id
         AND wt.person_id = fu1.employee_id(+)
         AND oeh.sold_to_org_id = hca.cust_account_id
         AND hca.party_id = hp.party_id
         AND wt.status = NVL (:p_status, wt.status)
         AND wt.creation_date BETWEEN NVL (:p_from_date, wt.creation_date)
                                  AND  NVL (:p_to_date, wt.creation_date)