1.Overview
This document talks about getting the sales daily stock pick report in oracle apps r12
2.Technologies and Tools Used
The following technologies have been used to achieve the expected output.
- SQL Code
3.Use Case
This report is used on an everyday basis by the customer to check the checklist of items that is ready to dispatch on the corresponding date.
This document gives you the SQL query to fetch the details.
4.Architecture
Column Descriptions
Step 1:
Run this code package:
create or replace PACKAGE pkgutil AS
————————————————————
/*To get OnHand Qty */
————————————————————
FUNCTION sfgetonhandqty (
p_organization_id mtl_onhand_total_mwb_v.organization_id%TYPE,
p_subinventory_code mtl_onhand_total_mwb_v.subinventory_code%TYPE,
p_item_id mtl_onhand_total_mwb_v.inventory_item_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION sfgetsubinvcode (
p_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
END pkgutil;
/
create or replace PACKAGE BODY pkgutil AS
————————————————————
/*To get OnHand Qty */
————————————————————
FUNCTION sfgetonhandqty (
p_organization_id mtl_onhand_total_mwb_v.organization_id%TYPE,
p_subinventory_code mtl_onhand_total_mwb_v.subinventory_code%TYPE,
p_item_id mtl_onhand_total_mwb_v.inventory_item_id%TYPE
) RETURN NUMBER
RESULT_CACHE
AS
v_retvalue NUMBER;
BEGIN
SELECT
SUM(on_hand.on_hand) on_hand_qty
INTO v_retvalue
FROM
mtl_onhand_total_mwb_v on_hand
WHERE
on_hand.organization_id = nvl(p_organization_id, on_hand.organization_id) –ORG ID
AND on_hand.inventory_item_id = p_item_id
AND on_hand.subinventory_code = nvl(p_subinventory_code, on_hand.subinventory_code) –sub inventory
;
RETURN nvl(v_retvalue, 0);
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END sfgetonhandqty;
————————————————————
/*To get Sub invemtory Code */
————————————————————
FUNCTION sfgetsubinvcode (
p_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(30);
BEGIN
SELECT DISTINCT
moqd.subinventory_code
INTO lv_result
FROM
mtl_onhand_quantities moqd
WHERE
1 = 1
AND moqd.inventory_item_id = p_item_id
AND moqd.organization_id = p_organization_id;
RETURN nvl(lv_result, NULL);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetsubinvcode;
End pkgutil;
/
Step 2:
SQL Report Code:
SELECT
b.ordered_item,
a.order_number order_no,
pkgutil.sfgetwarehousename(b.ship_from_org_id) warehouse,
ordered_date,
b.ordered_item Cust_item_code,
c.description,
a.cust_po_number cust_po_number,
hzp.party_name customer_name,
b.schedule_ship_date pick_date,
b.request_date request_date,
c.primary_uom_code uom,
nvl(b.ordered_quantity, 0) – nvl(b.shipped_quantity, 0) qty_remaining,
pkgutil.sfgetonhandqty(c.organization_id, nvl(b.subinventory, pkgutil.sfgetsubinvcode(c.inventory_item_id, c.organization_id)), c.
inventory_item_id) AS on_hand,
hca.account_number cust_code,
pkgutil.sfgetcustomeritem(a.sold_to_org_id, b.ordered_item) customer_item_code,
nvl(b.subinventory, pkgutil.sfgetsubinvcode(c.inventory_item_id, c.organization_id)) secondary_inventory_name,
hzp.party_id
FROM
oe_order_headers_all a,
oe_order_lines_all b,
mtl_system_items_b c,
apps.hz_parties hzp,
hz_cust_accounts hca
WHERE
b.header_id = a.header_id
AND b.inventory_item_id = c.inventory_item_id
AND b.ship_from_org_id = c.organization_id
AND hca.cust_account_id = a.sold_to_org_id
AND hzp.party_id = hca.party_id
AND ( nvl(b.ordered_quantity, 0) – nvl(b.shipped_quantity, 0) ) > 0
ORDER BY
hca.account_number,
b.ordered_item,
b.ordered_item
5.Screen Shot
Output:
This will be the output that we can generate using this document.
