, , , ,

On-hand Quatity of one Particular Item in whole Organization

The following Query will On-hand Quatity of particular item in the whole Organization

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = ‘Your Inventory Organization ID’;

Example:-

Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;

If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.

select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = ‘Your Inventory Organization ID’
group by organization_id;

/by
, , , ,

Summary Hold information based on the Invoice Number

When we Purchase some material/Goods/Items from Vendor/Supplier, after receiving the material. Vendor would send the INVOICE (In other words we receive BILL for the Items you have received). And payment will be done automatically. If there is some Discrepancy in the Items received and in the BILL/INVOICE you received, for them to hold the payment we normally set the HOLD rules. From the following Query you can know the Hold reason at the Summary level. 

select * from
(SELECT api.invoice_id, api.invoice_date AS invoice_date,
api.invoice_num AS invoice_num, pov.vendor_id AS vendor_id,
pov.vendor_name AS supplier_name, apd.inv_lines AS total_inv_lines,
NVL (hold_tab_info.hold_inv_lines, 0) AS total_line_holds,
NVL (CEIL ((hold_tab_info.hold_inv_lines * 100) / DECODE(apd.inv_lines,0,1,apd.inv_lines)),
0
) AS percentage_line_hold,
DECODE (hold_tab_info.hold_inv_lines,
NULL, ‘N’,
0, ‘N’,
‘Y’
) AS defect,
DECODE (hold_tab_info.hold_inv_lines,
NULL, 0,
0, 0,
1
) AS defect_count, 1 inv_count,
NVL (hold_count.hold_cnt, 0) AS total_inv_holds,
NVL (c.hold_os, 0) AS days_outstanding,
NVL (api.invoice_amount, 0) AS total_invoice_amount,
NVL (hold_tab_info.hold_amount, 0) AS total_hold_amount,
NVL (CEIL ((hold_tab_info.hold_amount * 100) / DECODE(api.invoice_amount,0,1,api.invoice_amount)),
0
) AS percentage_amount_hold
FROM APPS.ap_invoices_all api,
(SELECT invoice_id, COUNT (invoice_id) inv_lines
FROM APPS.ap_invoice_distributions_all
GROUP BY invoice_id) apd,
(SELECT invoice_id, COUNT (hold_lookup_code) hold_cnt
FROM APPS.ap_holds_all
WHERE 1 = 1 AND line_location_id IS NOT NULL
GROUP BY invoice_id) hold_count,
(SELECT invoice_id, COUNT (hold_tab.line_num) hold_inv_lines,
SUM (hold_tab.hold_amount) hold_amount
FROM (SELECT DISTINCT api.invoice_id invoice_id,
apd.distribution_line_number line_num,
apd.amount hold_amount
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.ap_holds_all aph
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND aph.line_location_id IS NOT NULL) hold_tab
GROUP BY invoice_id) hold_tab_info,
(SELECT invoice_id, MAX (b.hold_os) hold_os
FROM (SELECT invoice_id,
DECODE (status_flag,
‘R’, ( TRUNC (NVL (last_update_date,
SYSDATE)
)
– TRUNC (hold_date)
),
(TRUNC (SYSDATE) – TRUNC (hold_date))
) hold_os
FROM APPS.ap_holds_all
WHERE line_location_id IS NOT NULL) b
GROUP BY invoice_id) c,
APPS.po_vendors pov
WHERE 1 = 1
AND hold_tab_info.invoice_id(+) = api.invoice_id
AND c.invoice_id(+) = api.invoice_id
AND api.invoice_id = apd.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND api.invoice_id = hold_count.invoice_id(+))
where invoice_num=’Your Invoice number’;

/by
, , ,

Detail Hold information of the Invoice

When we Purchase some material/Goods/Items from Vendor/Supplier, after receiving the material. Vendor would send the INVOICE (In other words we receive BILL for the Items you have received). And payment will be done automatically. If there is some Discrepancy in the Items received and in the BILL/INVOICE you received, for them to hold the payment we normally set the HOLD rules. From the following Query you can know the Hold reason at the detail level.

Note:- Normally this kind of Information will be required for the Top level management for the decision making.

select * from
(SELECT ‘Holds – Source1’ AS SOURCE, 
api.invoice_date AS invoice_date,
api.invoice_num AS invoice_num, 
pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount,
DECODE (aph.hold_reason, NULL, ‘N’, ‘Y’) AS defect,
poh.segment1 AS po_number, 
por.release_num AS po_release_num,
pol.line_num AS po_line_num, 
aph.hold_date AS hold_date,
aph.hold_lookup_code AS hold_lookup_code,
aph.hold_reason AS hold_reason, 
aph.last_update_date AS release_date,
(TRUNC (NVL (aph.last_update_date, SYSDATE)) – TRUNC (aph.hold_date)) AS days_os,
pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
api.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer, 
povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor, 
rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty, 
rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.ap_holds_all aph,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id (+) = pod.po_header_id —
AND pol.po_line_id (+) = pod.po_line_id 
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = ‘RECEIVING’
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id (+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE
UNION
SELECT ‘Holds NotLinked To PO-Source2’ AS SOURCE,
api.invoice_date AS invoice_date, api.invoice_num AS invoice_num,
pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount,
DECODE (aph.hold_reason, NULL, ‘N’, ‘Y’) AS defect,
poh.segment1 AS po_number, por.release_num AS po_release_num,
pol.line_num AS po_line_num, aph.hold_date AS hold_date,
aph.hold_lookup_code AS hold_lookup_code,
aph.hold_reason AS hold_reason, aph.last_update_date AS release_date,
(TRUNC (NVL (aph.last_update_date, SYSDATE)) – TRUNC (aph.hold_date)
) AS days_os,
pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
api.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer, povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor, rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty, rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.ap_holds_all aph,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND aph.line_location_id IS NULL
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id(+) = pod.po_header_id
AND pol.po_line_id(+) = pod.po_line_id
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = ‘RECEIVING’
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id(+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE
UNION
SELECT ‘NON Holds – Source 3’ AS SOURCE, apii.invoice_date AS invoice_date,
apii.invoice_num AS invoice_num, pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount, ‘N’ AS defect,
poh.segment1 AS po_number, por.release_num AS po_release_num,
pol.line_num AS po_line_num, NULL AS hold_date,
NULL AS hold_lookup_code, NULL AS hold_reason, NULL AS release_date,
0 AS days_os, pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
apii.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer, povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor, rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty, rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all apii,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND apii.invoice_id = apd.invoice_id
AND apii.vendor_id = pov.vendor_id(+)
AND apii.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND apd.distribution_line_number NOT IN (
SELECT apd.distribution_line_number
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.ap_holds_all aph
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND api.invoice_id = apii.invoice_id)
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id(+) = pod.po_header_id
AND pol.po_line_id(+) = pod.po_line_id
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = ‘RECEIVING’
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id(+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE)
where invoice_num=’Your Invoice number’

/by
, ,

Retrive Oracle Apps R12 User Password

Step 1:
CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2;
END get_pwd;
/
CREATE OR REPLACE PACKAGE BODY get_pwd AS
FUNCTION decrypt (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt
(java.lang.String,java.lang.String) return java.lang.String’;
END get_pwd;
/
Step 2:

SELECT USER_ID,user_name,
       get_pwd.
        decrypt (
          (SELECT (SELECT get_pwd.
                           decrypt (FND_WEB_SEC.GET_GUEST_USERNAME_PWD,
usertable.encrypted_foundation_password)
                     FROM DUAL)
                   AS PASSWORD
             FROM fnd_user usertable
            WHERE usertable.user_name =
                     (SELECT SUBSTR (
                                FND_WEB_SEC.GET_GUEST_USERNAME_PWD,
                                1,
                                INSTR(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,
                                       ‘/’)
                                – 1)
                        FROM DUAL)),
          usr.ENCRYPTED_USER_PASSWORD)
          password,CREATION_DATE,LAST_LOGON_DATE
  FROM fnd_user usr
  ORDER BY CREATION_DATE DESC

/by
, , , , ,

Tree & Tree Structure


What is a tree?
A tree is hierarchical structures that enable several data management functions such as better access control, application of business rules at various levels of hierarchies, improved query performance, and so on.
This concept is similar to the organization hierarchy in 11i & R12.
For example, MI Corporation has two departments: Marketing and Finance.
The Finance department has two functional divisions: Receivables and Payables.
Defining a tree for the MI Corporation establishes a hierarchy between the organization and its departments, and between the departments and their respective functional divisions as shown in above pic.
What is a tree Structure?
A tree structure is configuration on the basis of which trees are created. It defines and restricts the tree. A tree is an instance of the hierarchy as defined in the tree structure. Tree structures enable you to enforce business rules to which the data must adhere.
The root node is the topmost node of a tree. Child nodes report to the root node. Child nodes at the same level, which report to a common parent node, are called siblings. Leaves are details branching off from a node but not extending further down the tree hierarchy.
Important Points

  • You can associate multiple data sources with a tree structure.
  • Every tree structure can contain one or more trees.
  • You can create tree structures specific to an application but you can share tree structures across applications.
  • If you apply version control to the tree structure, it is carried over to the trees that are based on the tree structure.
  • Each tree version contains at least one root node. Occasionally, a tree version may have more than one root node.
  • An administrator controls the access to tree structures through a set of rules that are periodically audited for validity.

What is a tree version
A tree is created having only one version. However, users can create more than one tree version depending on the need, and they can make changes to those versions. Depending on varying requirements, users can create one or more tree versions and publish all of them or some of them by making the versions active at the same time. Similar to any other version control system, versions of trees are maintained to keep track of all the changes that a tree undergoes in its life cycle.
What are tree labels
Tree labels are short names associated with trees and tree structures and point directly to the data source. Tree labels are automatically assigned to the tree nodes. You can store labels in any table and register the label data source with the tree structure.

/by