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