On-hand inventory information

On-hand inventory information:-
--------------------------------------

The following select statement would extracts all on-hand inventory information from Oracle
Applications base tables.

Note:- You may need to modify the query to match with your instance conditions and your requirements.

SELECT
NVL(substr(org.organization_code, 1,3), ' ') orgcode
,NVL(substr(msi.segment1, 1, 8), ' ') seg11
,NVL(substr(msi.segment1, 9, 8), ' ') seg12
,NVL(substr(msi.segment1, 17, 4), ' ') seg13
,NVL(moq.subinventory_code, ' ') sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ' ') trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
-- AND hou.type = 'DC'
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

I hope the above information would be helpful to you.

Credit Card Number (of different format) from specific column

Credit Card Number (of different format) from specific column:-
--------------------------------------------------------------------------

Following query can be used to get the Credit Card Number (of different format) from specific column.

Column would have credit card Number in the text date. Date is not in any fixed format. And Credit Card Number would also not in any specific Format. In the following query, we have considered few credit card formats. In can include other formats accordingly as your requirement.

This query is contributed with Chandra Kadali. I thank him for this posting.

SELECT jtf_note_id, creation_date, LANGUAGE, notes,
CASE
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'99999999999999999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'99999999999999999'
),
LENGTH ('99999999999999999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'999999999999999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'999999999999999'
),
LENGTH ('999999999999999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'9999 9999 9999 9999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'9999 9999 9999 9999'
),
LENGTH ('9999 9999 9999 9999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'9999 999999 99999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'9999 999999 99999'
),
LENGTH ('9999 999999 99999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'9999-9999-9999-9999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'9999-9999-9999-9999'
),
LENGTH ('9999-9999-9999-9999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'9999-999999-99999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'9999-999999-99999'
),
LENGTH ('9999-999999-99999')
)
ELSE 'No Credit card Number'
END "Credit card Number"
FROM jtf_notes_tl
WHERE 1 = 1
AND (TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%999999999999999 %') ) -- 15 digit---
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999999999999999 %')
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999 9999 9999 9999 %')
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999 999999 99999 %')
--- below are different formats with '-' instead of ' ''
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999-9999-9999-9999 %')
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999-999999-99999 %')

I hope the above information would be helpful to you.

Initialize Apps

Initialize Apps:-
----------------

To run or execute the Oracle API from the back-end, we need to Initialize the Apps first.

We have API to Initialize the Apps.

To initialize apps using the API, we need to pass few IN parameter values.

The following is the script prepared to initialize apps based on the
Application, User-name and Responsibility name given.

Example:-

DECLARE
   l_appl_id             NUMBER;
   l_appl_name           VARCHAR2 (100) := 'PA';
   l_user_id             NUMBER;
   l_user_name           VARCHAR2 (100) := 'OPERATIONS';
   l_responsibility_id   NUMBER;
   l_resp_name           VARCHAR2 (200)
                                       := 'Projects, Vision Operations (USA)';
BEGIN

-- To get the Application ID of given Application.
   SELECT application_id
     INTO l_appl_id
     FROM fnd_application
    WHERE application_short_name = l_appl_name;

-- To get the User ID information of  given user
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

-- To get the Resp ID information of the given responsibility.
   SELECT responsibility_id
     INTO l_responsibility_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = l_resp_name AND application_id = l_appl_id;

--Initialixze the Application to use the API. 
   fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);
END;

I Hope the above script would help understanding about the Initializing the Apps API.

Close Notifications

Close Notifications:-
----------------------

Lot of time we want to close the Notification which has come to the user.

They are many ways to do it. The following script can be use to Close the Notification.

/*********************************************
-- Date 'Current Date'
-- Author JPREDDY
-- Purpose : Close Notifications
**********************************************/
BEGIN
wf_notification.RESPOND( 12504888, -- Notification ID.
'Close requested by on '||Sysdate, -- Comments
'IM12345' ); -- Incident Number.
COMMIT;
END;
/


You can know the status of the Notification from the following Query.

select * from wf_notifications
where NOTIFICATION_ID=12504888

There is some equal-vent API which does this work.

wf_notification.close(12280094 -- Notification ID
,'SYSADMIN');

Query to get the Number of Seconds or Minutes or Hours between 2 given days

Query to get the Number of Seconds or Minutes or Hours between 2 given days:-
----------------------------------------------------------------------------------------

Note:- I have prepared this query since I could not find any inbuilt functions or procedures in Oracle to get this. I had the requirement to treat 2 dates are same if differences is not more then 2 seconds.

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;

This is the simple decode function made for all the above 3 requirements. I hope this will help you.

Purchase Orders Updated on previous day

Purchase Orders Updated on previous day:-
--------------------------------------------------

You can use the following query to get all the Purchasing Orders upadetd on previous day in any Instance.

You can filter-out based on category set or Inventory Organization etc.
I have commented out category_set_id and Organization ID conditions in the where clause.
You can modify as per your requirement.

Note: - This kind of information would be required for the Organization (Management) for the tracking the PO's created on daily bases.

Note:- There are some values I am getting from attribute columns. This values would vary in your Instance. These values depends on the DFF setup in your Instance.

SELECT mcb.segment1 "GMA"
, mcb.segment2 "Group"
, mcb.segment3 "Dept"
, mcb.segment4 "Cat"
, poh.segment1 "PO"
, fu.user_name "PO Created by(User_id)"
, fu.description "PO Created by(Name)"
, fu2.user_name "Last Updated By(User id)"
, fu2.description "Last Updated By(Name)"
, vs.vendor_site_code
, vn.vendor_name
, hou1.name
, hrl1.location_code "Ship To"
, poh.authorization_status "PO status"
, msi.segment1 "SKU"
, msi.description "Item Description"
, pll.quantity
, pll.quantity_received
, pll.quantity_cancelled
, fu1.user_name "Cancelled by"
, pll.cancel_reason
, pll.cancel_date
, poh.creation_date
, pll.promised_date
, pll.need_by_date
, poh.last_update_date
,(SELECT DISTINCT rsh.attribute8
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) "Appointment Number"
,(SELECT DISTINCT rsh.attribute9
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) "Appointment Date"
,pol.unit_price "Unit Cost"
,(pol.unit_price*pol.quantity) "Ext Cost"
FROM
po_headers_all poh
, po_lines_all pol
, po_line_locations_all pll
, mtl_system_items_b msi
, inv.mtl_item_categories mic
, inv.mtl_categories_b mcb
, fnd_user fu
, fnd_user fu2
, po_vendors vn
, po_vendor_sites_all vs
, hr_locations_all_tl hrl1
, hr_organization_units hou1
, fnd_user fu1
WHERE TRUNC(poh.last_update_date)=TRUNC(SYSDATE-1)
AND poh.type_lookup_code='STANDARD'
--AND poh.attribute2 LIKE 'M%'
AND poh.po_header_id=pol.po_header_id
AND pol.po_line_id=pll.po_line_id
AND pol.po_header_id=pll.po_header_id
AND pol.item_id=msi.inventory_item_id
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
-- AND mic.category_set_id = 1
AND mcb.category_id = mic.category_id
--AND msi.organization_id=22
AND poh.created_by=fu.user_id
AND poh.vendor_id=vn.vendor_id
AND vs.vendor_id = poh.vendor_id
AND vs.vendor_site_id = poh.vendor_site_id
AND hrl1.location_id(+) = poh.ship_to_location_id
AND hrl1.LANGUAGE(+) = USERENV ('LANG')
AND hrl1.location_id=hou1.location_id
AND fu1.user_id(+)=pll.cancelled_by
AND poh.last_updated_by=fu2.user_id

Purchase Orders created on previous day

Purchase Orders created on previous day:-
-------------------------------------------------

You can use the following query to get all the Purchasing Orders created on previous day in any Instance.

You can filter-out based on category set or Inventory Organization etc.
I have commented out category_set_id and Organization ID conditions in the where clause.
You can modify as per your requirement.

Note: - This kind of information would be required for the Organization (Management) for the tracking the PO's created on daily bases.

Note:- There are some values I am getting from attribute columns. This values would vary in your Instance. These values depends on the DFF setup in your Instance.

SELECT mcb.segment1 "GMA"
, mcb.segment2 "Group"
, mcb.segment3 "Dept"
, mcb.segment4 "Cat"
, poh.segment1 "PO"
, fu.user_name "PO Created by(User_id)"
, fu.description "PO Created by(Name)"
, vs.vendor_site_code
, vn.vendor_name
, hou1.name
, hrl1.location_code "Ship To"
, poh.authorization_status "PO status"
, msi.segment1 "SKU"
, msi.description "Item Description"
, pll.quantity
, pll.quantity_received
, pll.quantity_cancelled
, fu1.user_name "Cancelled by"
, pll.cancel_reason
, poh.creation_date
, pll.promised_date
, pll.need_by_date
,(SELECT rsh.attribute8
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) "Appointment Number"
,(SELECT rsh.attribute9
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) "Appointment Date"
,pol.unit_price "Unit Cost"
,(pol.unit_price*pol.quantity) "Ext Cost"
FROM
po_headers_all poh
, po_lines_all pol
, po_line_locations_all pll
, mtl_system_items_b msi
, inv.mtl_item_categories mic
, inv.mtl_categories_b mcb
, fnd_user fu
, po_vendors vn
, po_vendor_sites_all vs
, hr_locations_all_tl hrl1
, hr_organization_units hou1
,fnd_user fu1
WHERE TRUNC(poh.creation_date)=TRUNC(SYSDATE-1)
AND poh.type_lookup_code='STANDARD'
--AND poh.attribute2 LIKE 'M%'
AND poh.po_header_id=pol.po_header_id
AND pol.po_line_id=pll.po_line_id
AND pol.po_header_id=pll.po_header_id
AND pol.item_id=msi.inventory_item_id
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
-- AND mic.category_set_id = 1
AND mcb.category_id = mic.category_id
-- AND msi.organization_id IN (Your Organization ID's)
AND poh.created_by=fu.user_id
AND poh.vendor_id=vn.vendor_id
AND vs.vendor_id = poh.vendor_id
AND vs.vendor_site_id = poh.vendor_site_id
AND hrl1.location_id(+) = poh.ship_to_location_id
AND hrl1.LANGUAGE(+) = USERENV ('LANG')
AND hrl1.location_id=hou1.location_id
AND fu1.user_id(+)=pll.cancelled_by

Hard Reservartion for the Stanard Sales Order Lines against Internal Sales Order

Hard Reservation for the Standard Sales order Line against Internal Sales order:-
----------------------------------------------------------------------------------------------

Note:- You need to have the ASCP Module Installed for the following script to work.

Note:- ASCP decide whether it have to create the PO or Internal sales order
based on the Sourcing Rules defined for a Item in the Inventory Organization.

When any sales Order created for any particular item for any Quantity and if that
quantity is not available then ASCP would created the PO (Purchase order to get the
Material required for third party or could create the Internal requisition which would
again creates the Internal sales order) and It has to reserve the quantity which
the Purchase Order or Internal sales order has.

Here, we are considering if ASCP has decided to create the Internal Sales Order.

You can use the following script.

This document is designed by Dhanunjaya Sadhu.
He is one is dedicated resource in ASCP module.

Many thanks to Dhanunjaya Sadhu

/************************************************************
Hard Reservation for Standard Sales Order line against Internal Sales Order


Created by : Dhanunjaya Sadhu
Creation Date :

Primarily applicable for oracle ASCP implemented projects
Can be customized based on the requirement change.

For others, the input values can be passed directly without CURSOR.

************************************************************/
PROCEDURE create_iso_hard_reservation
(p_line_id IN NUMBER,
p_delivery_date IN DATE,
p_ship_from_org_id IN NUMBER)

IS
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
l_inventory_item_id number;
l_source_header_id number;
l_reservation_qty number;
l_error_message VARCHAR2(300);
l_order_number NUMBER;


CURSOR C_ISO_RESV(c_line_id)
----- Cursor to select ISO pegged to the standard sales order line
IS
SELECT
DISTINCT ool.HEADER_ID,
ool.LINE_ID,
mfp.ALLOCATED_QUANTITY
FROM OE_ORDER_LINES_ALL ool,
OE_ORDER_HEADERS_ALL ooh ,
MSC_DEMANDS md,
MSC_SUPPLIES ms,
MSC_FULL_PEGGING mfp,
MSC_PLANS mp
WHERE mp.PLAN_ID=mfp.PLAN_ID
AND mp.plan_id=ms.plan_id
AND mp.plan_id=md.plan_id
AND ool.HEADER_ID=ooh.HEADER_ID
AND ooh.ORIG_SYS_DOCUMENT_REF = ms.ORDER_NUMBER
AND ool.SOURCE_DOCUMENT_LINE_ID=ms.PO_LINE_ID
AND ms.TRANSACTION_ID=mfp.TRANSACTION_ID
AND md.DEMAND_ID=mfp.DEMAND_ID
AND md.SALES_ORDER_LINE_ID=c_line_id;

BEGIN

--- initialize required input params to call reservation api--

--- get the item id--
SELECT DISTINCT INVENTORY_ITEM_ID
INTO l_inventory_item_id
FROM mtl_system_items
WHERE segment1=( SELECT ordered_item
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=p_line_id);


--- get the sales_order_id from mtl_sales_orders table
---which should be passed as one of the input params

SELECT SALES_ORDER_ID,segment1
INTO l_source_header_id,l_order_number
FROM mtl_sales_orders
WHERE SEGMENT1=(SELECT TO_CHAR(oh.order_number)
FROM OE_ORDER_HEADERS_ALL oh,OE_ORDER_LINES_ALL ol
WHERE oh.header_id=ol.header_id
AND ol.line_id=p_line_id);


FOR C_REC IN C_ISO_RESV(p_line_id)

----------- sending the sales order line to the cursor variable

LOOP


----- Initialize al required inputs to perform HARD RESERVATION------

-----fnd_global.APPS_Initialize(2572700,20559,300);
-----p_user_id, p_resp_id, p_resp_appl_id

p_rsv.reservation_id := NULL;
---- cannot know
---- will be generated once reservation is successful
p_rsv.requirement_date := p_delivery_date;
p_rsv.organization_id := p_ship_from_org_id; ---------------------------mtl_parameters.organization id
p_rsv.inventory_item_id := l_inventory_item_id;
---------------------------mtl_system_items.Inventory_item_id
p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe;
p_rsv.demand_source_name := NULL;
------------------------------------------also u can save it as 'SALES ORDER' ;
p_rsv.demand_source_header_id :=l_source_header_id;-----------------------------mtl_sales_orders.sales_order_id for ordernumber
p_rsv.demand_source_line_id := p_line_id;
------------------------------------ oe_order_lines.line_id
p_rsv.primary_uom_code := 'EA';
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := 'EA';
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.primary_reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.autodetail_group_id := NULL;
p_rsv.external_source_code := NULL;
p_rsv.external_source_line_id := NULL;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_internal_ord;
p_rsv.supply_source_header_id :=C_REC.HEADER_ID;
-------------------------------Header id of Internal sales order
p_rsv.supply_source_line_id :=C_REC.LINE_ID;
---------------------------------Line id of Internal sales order
p_rsv.supply_source_name := NULL;
p_rsv.supply_source_line_detail := NULL;
p_rsv.revision := NULL;
p_rsv.subinventory_code := NULL;
--------------------------subinventory code can be mentioned based on your customization.
p_rsv.subinventory_id := NULL;
p_rsv.locator_id := NULL;
--------------------------17930; -- A10-L2-B09
p_rsv.lot_number :=NULL;
---------------------------'200801225083 ';
p_rsv.lot_number_id := NULL;
p_rsv.pick_slip_number := NULL;
p_rsv.lpn_id := NULL;
p_rsv.attribute_category := NULL;
p_rsv.attribute1 := NULL;
p_rsv.attribute2 := NULL;
p_rsv.attribute3 := NULL;
p_rsv.attribute4 := NULL;
p_rsv.attribute5 := NULL;
p_rsv.attribute6 := NULL;
p_rsv.attribute7 := NULL;
p_rsv.attribute8 := NULL;
p_rsv.attribute9 := NULL;
p_rsv.attribute10 := NULL;
p_rsv.attribute11 := NULL;
p_rsv.attribute12 := NULL;
p_rsv.attribute13 := NULL;
p_rsv.attribute14 := NULL;
p_rsv.attribute15 := NULL;
p_rsv.ship_ready_flag := NULL;
p_rsv.demand_source_delivery := NULL;

--- Validating whether the pegging is done WITH AN ISO---
---- FInally performing Hard Reservation ---------

inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);


IF x_status='S' THEN
--- HARD RESERVATION IS DONE SUCCESFULLY -----
COMMIT;

dbms_output.put_line('reservation succesful');
dbms_output.put_line('reservation id: || x_rsv_id);

ELSE

------ getting error msgs from fnd_msg_pub------

if(nvl(x_msg_count,0) = 0) then
dbms_output.put_line('no message return');
else
for I in 1..x_msg_count LOOP
l_error_message := fnd_msg_pub.get(I, 'F');
end LOOP;
end if;

--- HARD RESERVATION API fails ---

dbms_output.put_line('Reservation API Error Message: '||l_error_message);

END IF;


EXCEPTION
WHEN OTHERS THEN --- In case of any pl/sql error
l_error_message := SQLERRM;
dbms_output.put_line('Plsql Error Message: '||l_error_message);

END create_iso_hard_reservation;

Hope you find the above document helpful in your work.

Hard Reservation for Standard Sales Order line against Purchase Order

Hard Reservation for Standard Sales Order line against Purchase Order:-
-------------------------------------------------------------------------------------

Note:- You need to have the ASCP Module Installed for the following script to work.

Note:- ASCP decide whether it have to create the PO or Internal sales order
based on the Sourcing Rules defined for a Item in the Inventory Organization.

When any sales Order created for any particular item for any Quantity and if that
quantity is not available then ASCP would created the PO (Purchase order to get the
Material required for third party or could create the Internal requisition which would
again creates the Internal sales order) and It has to reserve the quantity which
the Purchase Order or Internal sales order has.

Here, we are considering if ASCP has decided to create the Purchase Order.

For that you can use the following script.

This document is designed by Dhanunjaya Sadhu. He is one is dedicated resource in ASCP module.

Many thanks to Dhanunjaya Sadhu

/************************************************************
Hard Reservation for Standard Sales Order line against Purchase Order

Created by : Dhanunjaya Sadhu
Creation Date :

Primarily applicable for oracle ASCP implemented projects
Can be customized based on the requirement change.

For others, the input values can be passed directly without CURSOR.

************************************************************/
PROCEDURE create_po_hard_reservation
(p_line_id IN NUMBER,
p_delivery_date IN DATE,
p_ship_from_org_id IN NUMBER)
IS
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
l_inventory_item_id number;
l_source_header_id number;
l_reservation_qty number;
l_error_message VARCHAR2(300);
l_order_number NUMBER;

CURSOR C_PO_RESV(c_line_id) ----- Cursor to select Purchas Orders pegged to the standard sales order line
IS

SELECT
DISTINCT pla.PO_HEADER_ID,
pda.PO_DISTRIBUTION_ID,
mfp.ALLOCATED_QUANTITY
FROM PO_LINES_ALL pla,
PO_HEADERS_ALL pha,
PO_DISTRIBUTIONS_ALL pda,
MSC_DEMANDS md,
MSC_SUPPLIES ms,
MSC_FULL_PEGGING mfp,
MSC_PLANS mp
WHERE mp.PLAN_ID=mfp.PLAN_ID
AND mp.plan_id=ms.plan_id
AND mp.plan_id=md.plan_id
AND pla.PO_HEADER_ID=pha.PO_HEADER_ID
AND pla.PO_HEADER_ID=pda.PO_HEADER_ID
AND pla.PO_LINE_ID = pda.PO_LINE_ID
AND pla.PO_HEADER_ID=ms.DISPOSITION_ID
AND pla.PO_LINE_ID= ms.PO_LINE_ID
AND ms.TRANSACTION_ID=mfp.TRANSACTION_ID
AND md.DEMAND_ID=mfp.DEMAND_ID
AND md.SALES_ORDER_LINE_ID=c_line_id;

BEGIN

--- initialize required input params to call reservation api--

--- get the item id--

SELECT DISTINCT INVENTORY_ITEM_ID
INTO l_inventory_item_id
FROM mtl_system_items
WHERE segment1=( SELECT ordered_item
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=p_line_id);


--- get the sales_order_id from mtl_sales_orders
--- table which will be passed as one of the input params-

SELECT SALES_ORDER_ID,segment1
INTO l_source_header_id,l_order_number
FROM mtl_sales_orders
WHERE SEGMENT1=(SELECT TO_CHAR(oh.order_number)
FROM OE_ORDER_HEADERS_ALL oh,OE_ORDER_LINES_ALL ol
WHERE oh.header_id=ol.header_id
AND ol.line_id=p_line_id);

FOR C_REC IN C_PO_RESV(p_line_id)

-------------- sending the sales order line to the cursor variable

LOOP


----- Initialize al required inputs to perform HARD RESERVATION------

--fnd_global.APPS_Initialize(2572700,20559,300);
--p_user_id, p_resp_id, p_resp_appl_id

p_rsv.reservation_id := NULL; -- cannot know

------------will be generated once reservation is successful

p_rsv.requirement_date := p_delivery_date;
p_rsv.organization_id := p_ship_from_org_id;

---------------------------mtl_parameters.organization id

p_rsv.inventory_item_id := l_inventory_item_id;

---------------------------mtl_system_items.Inventory_item_id

p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe;
p_rsv.demand_source_name := NULL;-------'SALES ORDER';
p_rsv.demand_source_header_id :=l_source_header_id;

--------------------mtl_sales_orders.sales_order_id for order number

p_rsv.demand_source_line_id := p_line_id;

-------------------- oe_order_lines.line_id
p_rsv.primary_uom_code := 'EA';
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := 'EA';
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.primary_reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.autodetail_group_id := NULL;
p_rsv.external_source_code := NULL;
p_rsv.external_source_line_id := NULL;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_po;
p_rsv.supply_source_header_id :=C_REC.PO_HEADER_ID;

------------------------Header id of PO

p_rsv.supply_source_line_id :=C_REC.PO_DISTRIBUTION_ID;

------------------------Distribution id of PO

p_rsv.supply_source_name := NULL;
p_rsv.supply_source_line_detail := NULL;
p_rsv.revision := NULL;
p_rsv.subinventory_code := NULL;

-------------subinventory code can be mentioned

p_rsv.subinventory_id := NULL;
p_rsv.locator_id := NULL;--17930; -- A10-L2-B09
p_rsv.lot_number :=NULL;--'200801225083 ';
p_rsv.lot_number_id := NULL;
p_rsv.pick_slip_number := NULL;
p_rsv.lpn_id := NULL;
p_rsv.attribute_category := NULL;
p_rsv.attribute1 := NULL;
p_rsv.attribute2 := NULL;
p_rsv.attribute3 := NULL;
p_rsv.attribute4 := NULL;
p_rsv.attribute5 := NULL;
p_rsv.attribute6 := NULL;
p_rsv.attribute7 := NULL;
p_rsv.attribute8 := NULL;
p_rsv.attribute9 := NULL;
p_rsv.attribute10 := NULL;
p_rsv.attribute11 := NULL;
p_rsv.attribute12 := NULL;
p_rsv.attribute13 := NULL;
p_rsv.attribute14 := NULL;
p_rsv.attribute15 := NULL;
p_rsv.ship_ready_flag := NULL;
p_rsv.demand_source_delivery := NULL;


------------------------------ CASE II-------------------------------------------

--- Validating whether the pegging is done WITH AN EXTERNAL PURCHASE ORDER---
---- FInally performing Hard Reservation ---------


inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);


IF x_status='S' THEN --- HARD RESERVATION IS DONE SUCCESFULLY ---
COMMIT;

dbms_output.put_line('reservation succesful');
dbms_output.put_line('reservation id: || x_rsv_id);

ELSE

if(nvl(x_msg_count,0) = 0) then
dbms_output.put_line('no message return');
else
for I in 1..x_msg_count LOOP
l_error_message := fnd_msg_pub.get(I, 'F');
end LOOP;
end if;

--- HARD RESERVATION API fails ---

dbms_output.put_line('Reservation API Error Message: '||l_error_message);

END IF;


EXCEPTION
WHEN OTHERS THEN ------------------ In case of any pl/sql error
l_error_message := SQLERRM;
dbms_output.put_line('Plsql Error Message: '||l_error_message);

END create_po_hard_reservation;

Hope you find this document helpful in your work.

PO Requisition associated with PO Orders

PO Requisition associated with PO Orders:-
--------------------------------------------------

We Using following URL, we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.

They are 2 types of requisitions.

1) Purchasing Requisition

2) Internal Requisition.

Note:- Only Purchase Requisitions will be converted to the Purchase orders.

SELECT prha.segment1 "Requisition Number",
prha.type_lookup_code "Requisition Type",
pha.segment1 "Purchase Order Number",
pha.type_lookup_code "Purchase Order Type"
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id

Internal Requisitions has not converted to Internal Sales Order

Internal Requisitions has not converted to Internal Sales Order:-
-------------------------------------------------------------------------------

Requisitions (PO Module) would be 2 types.

1) Purchasing Requisitions (Handled by the PO Module)

2) Internal Requisitions (Handled by the OM Module).

We create the Purchase Orders for the Purchasing Requisitions and Internal Sales Orders will be created for the Internal Requisitions.

Note:- We create Internal Sales Order to transfer the material between the Inventories with-in the Organization.

We can use the following Query to find out all the Internal requisitions are created but not converted to the Internal Sales Orders.

SELECT rqha.segment1 "Requisition Number"
, rqla.line_num "Line Number"
, rqla.requisition_header_id "Requisition Header ID"
, rqla.requisition_line_id "Requisition Line ID"
, rqla.item_id "Inventory item ID"
, rqla.unit_meas_lookup_code "Unit Of Measure"
, rqla.unit_price "Unit Price"
, rqla.quantity "Quantity"
, rqla.quantity_cancelled "Quantity Cancelled"
, rqla.quantity_delivered "Quantity Delivered"
, rqla.cancel_flag "Cancelled"
, rqla.source_type_code "Source Type"
, rqla.source_organization_id "Source Organization ID"
, rqla.destination_organization_id "Destination Organization ID"
, rqha.transferred_to_oe_flag "Transferred to OE Flag"
FROM po_requisition_lines_all rqla
, po_requisition_headers_all rqha
WHERE rqla.requisition_header_id = rqha.requisition_header_id
AND rqla.source_type_code = 'INVENTORY'
AND rqla.source_organization_id IS NOT NULL
AND NOT EXISTS ( SELECT 'existing internal order'
FROM oe_order_lines_all line
WHERE line.source_document_line_id =
rqla.requisition_line_id
AND line.source_document_type_id = 10)
ORDER BY rqha.requisition_header_id
, rqla.line_num

Cancel Requisitions

Cancel Requisitions:-
------------------------

You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT
prha.requisition_header_id "requisition_header_id"
,prha.segment1 "Requisition Number"
,prha.preparer_id "preparer_id"
,TRUNC(prha.creation_date) "creation_date"
,prha.description "description"
,prha.note_to_authorizer "note_to_authorizer"
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code='CANCEL'
AND pah.object_type_code='REQUISITION'
AND pah.object_id=prha.requisition_header_id

SQL and PL/SQL Online

SQL and PL/SQL Online:-
---------------------------------------

You can use the following Oracle URL for practicing SQL and PL/SQL:

http://apex.appshosting.com/pls/apex/

Note:- You need to register for this (which is free) Once you get your Username, Password and Workplace then you can login from anywhere to practice the SQL and PL/SQL.

Display the Database Version and Server Operating System Name

Display the Database Version and Server Operating System Name:-
-----------------------------------------------------------------------------

The following query gives the version of the Oracle Database.

SELECT banner FROM v$version;

Example:-

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

From above information, we can say this is 10g Database and Server Operating system is Linux.

The following query can be used to get the version of the Server Operating system.

BEGIN
dbms_output.put_line('Port String: '||dbms_utility.port_string);
END;

Example:-

Port String: Linuxi386/Linux-2.0.34-8.1.0

From the above information, we can say this is Linux Operating system and version is 2.0.34-8.1.0

Note:- If you are login into the server with some ID then you can also know the name and version of the Server Operating system directly by typing

> uname

Duplicate rows in the table

Duplicate rows in the table:-
--------------------------------

The following query can be used to get the duplicate records from table.

SELECT * FROM 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');

Example:-

SELECT * FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY ename);

To eliminate/delete the duplicate rows from the table, you can use the following query.

DELETE 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');

Example:-

DELETE emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY
ename);

Display the number value in Words

Display the number value in Words:-
------------------------------------------

The following query can be used to display the number in the words.

select 'Your Number', (to_char(to_date('Your Number','j'), 'jsp')) from dual;

Example:-

select 211, (to_char(to_date(211,'j'), 'jsp')) from dual;

Thanks to Chandra k Kadali for posting this Article.

Install Base creation for all the Sales Order for which it is missing

Install Base creation for all the Sales Order for which it is missing:-
------------------------------------------------------------------------------

You can using the following package to create the IB (Install base) for the sales Order It has missed.

Normally, we enable the Install base option in the "Master Item" form for the Items for which we want to track. If we enable this option, then when we create the sales order with the Item for which Install base option is check then, you would see the IB got created.

CREATE OR REPLACE PACKAGE alloracletech_ib_pkg
IS
PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER);
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2);
END alloracletech_ib_pkg;
/

CREATE OR REPLACE PACKAGE BODY alloracletech_ib_pkg
IS

PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER)
IS
/************************************************************************
Purpose : This procedure creates IB for all the order which are missed.
*************************************************************************/
l_header_id NUMBER;
l_mtl_txn_id NUMBER;
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;

CURSOR c_ib
IS
SELECT -- oeh.order_number,
oel1.line_id
-- oeh.creation_date,
-- msib1.inventory_item_id,
-- msib1.segment1
FROM oe_order_lines_all oel1,
mtl_system_items_b msib1,
oe_order_headers_all oeh
WHERE oel1.ordered_item = msib1.segment1
AND msib1.comms_nl_trackable_flag = 'Y'
AND msib1.shippable_item_flag = 'Y'
AND msib1.organization_id = 22
AND oeh.header_id = oel1.header_id
AND oel1.flow_status_code = 'CLOSED'
AND oel1.line_category_code = 'ORDER'
AND oeh.order_type_id = 1008
-- AND oel1.line_id IN (5599900, 5742086)
-- AND oeh.order_number IN ( )
AND oeh.shipping_method_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM csi.csi_item_instances cii
WHERE cii.last_oe_order_line_id = oel1.line_id);
BEGIN
FOR i IN c_ib
LOOP
BEGIN
SELECT transaction_id
INTO l_mtl_txn_id
FROM mtl_material_transactions
WHERE trx_source_line_id = i.line_id
AND transaction_type_id = 33;

-- dbms_output.put_line(i.order_number||' '||i.line_id);
csi_inv_txn_hook_pkg.posttransaction
(p_header_id => l_header_id,
p_transaction_id => l_mtl_txn_id,
x_return_status => l_return_status
);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
WRITE ('L', 'No Transaction ID for the Line ID : ' || i.line_id );
WHEN TOO_MANY_ROWS THEN
WRITE ('L','More then one Transaction ID for the Line ID : ' || i.line_id );
WHEN OTHERS THEN
WRITE ('L',
'Error in LineID :' || i.line_id || CHR (10) || SQLCODE || ' : ' || SQLERRM
);
END;
END LOOP;
END create_install_base;

/************************************************************************/
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : This procedure writes to the output file or log file depending
on the parameter p_type passed.
*************************************************************************/
BEGIN
IF p_type = 'L'
THEN
fnd_file.put_line (fnd_file.LOG, p_message);
ELSIF p_type = 'O'
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;
END WRITE;
END alloracletech_ib_pkg;
/

Use the following to Execute the Procedure.

begin
alloracletech_ib_pkg.create_install_base;
end;
/

The following query can be used to find out whether Install Base (IB) created or not for the Order line.

select * from csi.csi_item_instances
where last_oe_order_line_id IN (Your Order Line ID);

Example:-

select * from csi.csi_item_instances
where last_oe_order_line_id IN (6912858, 6912859, 6912860);

I hope you find the above information help full.

Note:- There are few things which are hard-corded like order_type_id = 1008 etc, these are specific to my instance setup. It may vary for your Instance setup. Change the values accordingly.

Note:- I have tested above script in the 11i Instances.

Application Implementation Methodology

Application Implementation Methodology:-
--------------------------------------------------

I have received lot of request from many people asking for the AIM document information. For the best of my Knowledge, I have prepared the following information. If you have any more information related to AIM document, please share it across with Oracle Community.

Application Implementation Methodology in short referred as AIM.

AIM- Oracle’s Application Implementation Methodology is a proven approach to implement Oracle Applications.

AIM defines an organization’s business needs at the beginning of the project and maintains their visibility through out the implementation

AIM Phases-AIM project is conducted in phases. The phases provide quality and control checkpoints to coordinate project activities that have a common goal.

The phases are

1) Definition- During this phase, we plan the project ,review the organization’s business objectives ,understand the business processes and evaluate the feasibility of meeting those objectives under time, resources and budget constraints. The emphasis is on building an achievable work plan and introducing guidelines on how the organization will work to achieve the objectives.To achieve an early understanding of current business operations and future processes,the team also performs baseline and process modeling during this phase.If business change is required,then the high level future process designs are drawn.The goal is to identify the future business and system requirements,propose a future business model and determine the current application and information technology architecture.The information gathered provides input to the downstream activities in subsequent phases.All business requirements are associated with planned future business processes.So sharing an accurate understanding of the requirements is a critical success factor to the project.

2) Operations Analysis- The project team develops the business requirement scenarios based on deliverable drawn out of the definition stages.These business requirement scenarios are used assess the level of fit between the detailed business requirement and standard application functionality.Gaps are identified and new proposed solutions are developed.Proposed solutions for gaps evolve into detailed design during a solution design phase.

3) Solution Design- The purpose of the solution design is to develop the detailed design to meet the future business requirements.

4) Build-The coding and testing of all customizations,data conversions and interfaces are done in this phase.Business system testing is performed to validate that the functionality meets the business requirements.If customizations,extensions and conversions are not required ,build phase is still important because it includes business system test..In confererence room pilot(CRP),the business system test validates the new system in an environment that resembles the production.

5) Transition-During transition,the project team deploys the new system into the organization.All the elements must come together to tranisition successfully to actual production.During transition users perform an acceptance test of the new system.

6) Production-It marks the last phase of the implementation and the beginning of system support cycle.

AIM Processes

1) Business process architecture (BP)
Business process architecture addresses the organization’s business processes and aligns them with the business requirement and applications to be implemented. High level process designs (BP0.070) and future process model (BP0.080) are a part of this.

2) Business requirements Definition (RD)
Business Requirements Definition defines the business needs that must be met by the implementation project .This is RD 0.020 which is the current business needs.

3) Business Requirement Mapping (BR)
Business requirement mapping compares the future business requirements to standard oracle application functionality and identifies the gaps. Business requirements scenarios (RD0.050) are then mapped to oracle functionality.

4) Application and Technical Architecture (TA)
During Application and Technical Architecture, the information systems are designed to reflect the business vision

5) Module Design and Build (MD)
Module design and build produces custom application extensions for gaps in functionality identified during BR.Custom application extensions include program modules( forms, reports, alerts and database triggers) that must be designed, built and tested before being transitioned in the new system.

6) Data Conversions (CV)
Data conversion defines the tasks and deliverable to convert legacy data to oracle application base tables.

7) Documentation (DO)
It defines the documentation requirements for the project and establishes which of the documentation tasks are required.

8) Business System Testing (TE)
Early in the project life-cycle, Business System Testing focuses on
linking test requirements back to business requirements and securing
project resources needed for testing.
Business System Testing provides a formal integrated approach to
Testing

9) Performance Testing (PT)
Performance Testing enables you to define, build, and execute a
performance test. The Performance Testing team defines the scope of testing and relates it to point-in-time snapshots of the transactions expected in the real
production system

10) Adoption and Learning (AP)
Adoption and Learning establishes a measurement system that provides
an evaluation of organizational performance to help make sure that
expectations are met during implementation and after production
cut-over

11) Production Migration (PM)
Production Migration moves the company, system, and people to the
new enterprise system.

I hope you find this information helpful.

Permission names for specific User

Permission names for specific User:-
------------------------------------------

With the following query you can know what all the permissions you have for any Specific Application User.

I have used this query when working on some custom page. Based on the permissions given to the User, you can provide some additional functionality to the Users.

Note:- I have commented out the permission name. in the below query. You can use this condition if you want to search specific to the permission name.

SELECT fnd.user_id
, fnd.description
, p.permission_name
FROM jtf_auth_principals_b u
, jtf_auth_principal_maps pm
, jtf_auth_role_perms rp
, jtf_auth_permissions_b p
, fnd_user fnd
WHERE fnd.user_id='Your USER ID'
AND fnd.user_name=u.principal_name
-- AND p.permission_name ='CSI_SEARCH_PRODUCT_VIEW'
AND u.jtf_auth_principal_id = pm.jtf_auth_principal_id
AND pm.jtf_auth_parent_principal_id = rp.jtf_auth_principal_id
AND rp.jtf_auth_permission_id = p.jtf_auth_permission_id

Note:- You can know the USER ID of specific to some USER from the following Query.

select * from fnd_user where USER_NAME like 'User Name';

Get the Assembly Details

Get the Assembly Details:-
------------------------------

The following query would get all the details about the Assembly

SELECT a.assembly_item_id
, b.component_item_id
, c.segment1 component_item
, c.shelf_life_code
, c.shelf_life_days
, c.lot_control_code
,a.assembly_item_id
,a.organization_id
FROM bom_bill_of_materials_v a,
bom_inventory_components_v b,
mtl_system_items_b c
WHERE a.bill_sequence_id = b.bill_sequence_id
AND b.component_item_id = c.inventory_item_id
AND a.organization_id = c.organization_id
-- AND a.organization_id = l_organization_id
-- AND a.assembly_item_id = l_item_id;

Get all the Assembly completion Lot Numbers

Get all the Assembly completion Lot Numbers: -
-------------------------------------------------------

The following query gets the information about the Lot Numbers of Assembly Completion.

Note:- I have commented "mmt.organization_id = your_organization_id" condition. If you want to get the results specific to some inventory organization then, you can use this condition. Organization_id is representing the Inventory Organization.

SELECT mmt.transaction_id
, mmt.transaction_date
, mmt.inventory_item_id
, mmt.organization_id
, mtn.lot_number
, msi.shelf_life_days
, msi.shelf_life_code
, msi.segment1
FROM mtl_material_txns_val_v mmt
,mtl_transaction_lot_numbers mtn
,mtl_lot_numbers mln
, mtl_system_items_b msi
WHERE mmt.transaction_id = mtn.transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mtn.lot_number = mln.lot_number
AND mtn.organization_id = mln.organization_id
AND mmt.transaction_type_name = 'WIP Completion'
AND mmt.transaction_action = 'Assembly completion'
AND NVL (mln.attribute1, 'N') <> 'Y'
-- AND mmt.organization_id = your_organization_id
AND mmt.transaction_date >= mmt.transaction_date
AND mmt.transaction_date <= mmt.transaction_date
AND mmt.inventory_item_id >= mmt.inventory_item_id
AND mmt.inventory_item_id <= mmt.inventory_item_id;

Retrieves all PO transaction quantities

Retrieves all PO transaction quantities:-
----------------------------------------------

Following Query would get all the PO Numbers, Item ID, Quantity Order, Received, Canceled etc. The same information can be found in the PO order form from the Front end Application.

SELECT
POL.ITEM_ID "Inventory Item ID",
POLL.SHIP_TO_LOCATION_ID "Ship to Location ID",
poll.quantity "Quantity",
poll.Quantity_received "Quantity Received",
poll.QUANTITY_cancelled "Quantity Canceled",
POH.SEGMENT1 "PO Order Number"
FROM po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll
WHERE poh.po_header_id=pol.po_header_id
AND pol.po_line_id=poll.po_line_id
AND poll.po_header_id=pol.po_header_id
AND poh.authorization_status='APPROVED'
AND poh.type_lookup_code='STANDARD'
AND NVL(poh.cancel_flag,'N') ='N'
AND POL.ITEM_ID IS NOT NULL
AND NVL (poll.quantity, 0) > (NVL (Quantity_received,0) + NVL (Quantity_cancelled,0))