, , , ,

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
, , ,

General Ledger Interview Questions

Q) What is Planning Budget?

A) The plan for the future expenses is planning budget. It is a paper work. There is no funds requirement. It does not require journals. There are no restrictions for estimating of funds. It is a budget through which you cannot excercise budgetary control . But you can compare your actual budgets through inquiry window.

Q) After creating Journal Source how do we approve to the specific set of Books?

A) To approve journals from specific source, while creating the source ‘Require Journal Approval’
check box should be enabled. To approve all the journals that come from different sources In the Set of Books window under ‘Journaling’ tab ‘journal approval’ should be enabled.

/by
, ,

ORACLE FUSION MIDDLEWARE

JDeveloper
Oracle JDeveloper is an integrated development environment (IDE) for building service oriented applications using the latest industry standards for JAVA,XML,Web Services and SQL.

Oracle J Developer supports the complete development life cycle with integrated features for modeling, coding, debugging, testing, profiling, tuning and deploying applications.
JDeveloper visual and declarative development approach and innovative Oracle Application Development Framework (Oracle ADF) work together to simplify application development and reduce nundane coding tasks, offering unparalleled productivity and a choice of technology stacks.

Although JDeveloper is mainly a Java Development tool it offers extensive support for development in related languages and environments as well. In addition to the Java capabilities ,JDeveloper enables XML based application development with features such as the XML Schema Modeler, XML code insight and visual editing, and XSLT debugging. Oracle JDeveloper also provides a full development and modeling environment for building database objects and stores procedures.

Applications developed with JDeveloper work with any data source and can be deployed on any J2EE compatible application server.

Oracle JDeveloper a 100% Java based tool is a cross platform IDE that runs on windows Linux, MAC and various unix based systems letting developers choose their preferred development platform.

/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
, , , ,

Oracle Fusion Receivables


Oracle Fusion Receivables provides most of the functionalities available in Oracle EBS 11i/R12. All the mandatory setups are also similiar to the setups available in 11i/R12.
OFR allows you to perform most of your day-to-day accounts receivable operations. Receivables lets you manage customer billing activities, process customer payments, manage accounts receivable balances, and perform tasks that are related to revenue recognition and revenue adjustments.
/by