Julian to Gregorian Calender Switch impact on Oracle

Do you see something abnormal in following image?

Yes …….what’s this ……?

And same thing you will see on some other ERP solutions / software once you select September 1752.

Answer

The calendar snippet shows that there are no days between October 4 and October 15. This discontinuity is called the “cutover”.

What is Cutover

“This is a hybrid Julian/Gregorian calendar with a cutover on October 4/15 1582.” Any dates before a cutover are Julian, any dates during a cutover are nonexistent, and any dates after a cutover are Gregorian.

Mass Feature Available in Oracle Purchasing R12

Click Link To Download

How to Clear Application Cache by Using Funcational Administrator Responsiblity

1- Navigate to Functional Administrator Responsibility
2- Choose the “ Core Services” Tab
3- Choose “Caching Framework”
4- Click on “Global Configuration”
5- Click on “Clear All Cache”
6- The Click on yes on the display which will appear.

Meaningless Details (a story about project management)

I got this story from internet and I believe it’s worth sharing for Project Managers because most of us faced same situation during project management

This is a true story, although the details have been changed to protect the guilty parties.

I was hired as an outside consultant to manage a project for an international corporation. They were producing a new project – a multi-million dollar project that was supposed to go to market within a specific timeframe.

On the initial call with the new product team, I was introduced to the various people including the sponsor, the team leader, and the team. The sponsor turned the meeting over to the team leader and she introduced herself and then introduced the project.

“The codename for this project is ‘Ice’ because we’ll freeze out the competition when this product hits the market”. Okay, fair enough. I was the project manager of Project Ice.

We got started.

Less than a week later, we received a blanket email from the team leader. “We’ve changed the codename to ‘Project Snowstorm’ because we’re going to totally cover the market with this product.” Great! Now I was the project manager for Project Snowstorm.

We continued with the work.

Then, on our weekly team call, the team leader opened the call by saying “I’ve forgotten to tell most of you, but we’ve changed the codename to ‘Avalanche’ because we felt that it more accurately describes how we wanted to take our market by storm with this project.” So now I was the project manager for Project Avalanche.

We continued on the project.

Two days later, we got an email from the team lead: “The Senior V.P.’s don’t like the negative connotations of the term ‘avalanche’ so we’re going to give the codename a more positive spin, ‘Project Fire’ because we’re going to burn up the market!” So now I was the project manager for Project Fire.

And I continued to manage the project’s progress.

Then another email: “We’ve decided to change the codename again. ‘Project Fire’ was too general and didn’t seem like we had any focus, so we’re going to call it ‘Project Laser’.” So now I was the project manager for Project Laser.

… and on it went. The codename of the project changed 14 times. No, that is not an exaggeration or hyperbole. The project really did change codenames that many times.

If you’ve ever managed a project for another company, you know that it can be difficult to flex your project management muscle and say “enough is enough”. In the case of this project, I could have stepped in earlier but there were some political issues at play here and I needed to pick my battles (another lesson for another time).

In the end, the codenames didn’t extend the project timeline too much. But they did create a number of inefficiencies that could not be ignored. The timeline was impacted a little, and people were never sure who to talk to or where to file information or how to refer to the project because it was never clear.

To me, this is a perfect example of how a corporation can get caught up in a meaningless detail (a codename, of all things, and it had absolutely nothing to do with the final product name!) and forget the bigger picture: Completing a project successfully, on time and on budget.

Hyperion Startuppp (Appetizer for Hyperion Newbies)

Download Hyperion Startuppp (Appetizer for Hyperion Newbies)

How to Get Approval Group assigned to specific Job and Position in Oracle Purchasing

How to Get Approval Group assigned to specific Job and Position in Oracle Purchasing

Remittance Advice by Email to Supplier by Using UTL_SMTP

Click on Link to Read Article

Bank Account Transfers in Oracle Cash Management

Bank account transfers represent fund transfers between internal bank accounts.

Bank account transfers can be done either manually or automatically. The access to bank account transfer creation for a particular legal entity is subject to bank account use security. The bank account transfers are reflected in Cash Position automatically. Payment processing for bank account transfers is managed using Oracle Payments. Journal entries are created through a centralized sub ledger accounting engine. From the point of view of bank account ownership, you can either do an Intra-Company bank transfer or an Inter-Company bank transfer. Intra-company transfers represent cash transfers between two internal bank accounts that belong to the same legal entity, while Inter-company transfers represent cash transfers between two internal bank accounts that belong to two different legal entities. In case of such transfers, the system can produce balanced journal entries for both legal entities. And from the point of view of transfer currency, you can either do a Domestic bank transfer or an International bank transfer. In Domestic bank transfer, the currency of both bank accounts and the transfer amount is the same.

For example, you are transferring USD between two bank accounts that are denominated in USD. In International bank transfer, the currency of one bank account is the same as the currency of the transfer amount but the currency of the other bank account is different. For example, you are transferring USD from a bank account denominated in USD to the bank account denominated in AED.

Setting Up Bank Transfers

Before creating a bank transfer, complete the following steps:

Navigate to the Profile Options page using Otherà Profile menu.

1. Search for CE: Bank Account Transfers profile.
2. Select Cash Management and select OK.
3. Set up transaction subtypes.
4. Set up payment templates.

Creating a Bank Transfer

To create a bank transfer, complete the following setup steps:

Navigate to the Bank Account Transfers page under the Cash Management Super User responsibility.

1. Click Create.
2. Enter the bank account transfer information.

If you select a payment template, the bank account transfer attributes defined at the template level will default in automatically.

1. Click Apply.

If you would like to validate the bank account transfer at the same time as saving it, click Apply and Validate. The bank account transfer will be available for further processing such as manual authorization, payment and journal creation only after it has been validated.

Authorizing a Bank Transfer

To authorize a bank transfer, complete the following setup steps:

Navigate to the Bank Account Transfer Authorization page under the Cash Management Super User responsibility.

Select the bank transfers from the displayed list and click Authorize Transfers.

If the transfer requires payment processing, they will now be automatically sent to Oracle Payments. If they do not require payment processing, they will now be available for journal creation.

Strength and Weakness of Approval Hierarchies available in Oracle Purchasing

Strengths and Weaknesses of Approvals Using the Employee/Supervisor Relationship

Strengths

• Allows flexibility of establishing approval groups around either jobs or positions.

• Assuming Oracle Human Resources (HR) is installed and the data is being maintained, Purchasing uses the supervisor name on the employee record which should be entered and maintained for HR purposes already.

Weaknesses

• Documents may not travel by the employee/supervisor relationship for approval purposes, but take a different path in part or entirely.

• Documents can get hung up more frequently due to the volume of personnel changes that typically occur in companies.

• Higher volumes of documents need to be re-routed using the Forward-To settings. Recall that the first name alphabetically appears as the default.

Strengths and Weaknesses of Approvals Using Position Approval Hierarchies

Strengths

• Allows approval paths to be set up that follow the actual flow of documents for approval

• Assuming Human Resources is installed and the data is being maintained, Purchasing can make an employee representation of the position approval hierarchy by simply running the Fill Employee Hierarchy process. This will allow changes occurring in personnel assignments to be updated in the position approval hierarchy.

• Documents can get hung up less frequently due to the lower volume of structural hierarchy changes compared to the higher volume of personnel changes that typically occur in companies.

• Lower volumes of documents need to be re-routed using the Forward To settings, especially when there are multiple holders in a particular job or position. Recall that the first name alphabetically appears as the default when there are multiple holders.

Weaknesses

• Cannot be used with jobs, but must be used with positions.

Some Doubts………..In Oracle Purchasing

What if there is more than one holder in a job or position?

Multiple holders in the same job or position can only occur if Oracle Human Resources is installed. If only Oracle Purchasing is installed, you can assign only one employee to each job or position. If multiple holders exist when using position approval hierarchies, Oracle Workflow will route the document to the holder based on alphabetical order. You can see who the system will route the document to by clicking the Forward radio button, overriding the defaulted name if needed.

What if there are changes to the position approval hierarchy?

Personnel changes are updated in the position approval hierarchy by running the Fill Employee Hierarchy process which reviews the employee record to determine the current position assignment. You will probably want to schedule this process to run on a frequent basis to ensure smooth processing of all approval requests. Structural changes to the position approval hierarchy require the hierarchy to be rebuilt from the point where the change occurs to the bottom of that branch. Documents are routed according to the hierarchy in effect at the time they are submitted for approval.

What if Human Resources were already implemented with jobs set up, but no positions?

Unless the decision to set up jobs only and no positions can be revisited, you will have to route all documents by the employee/supervisor relationship and maintain this data on the employee records.

What if the approver is unavailable for an extended period?

Oracle Purchasing has the ability to automatically forward documents when users do not respond to notifications. This tool should be used when possible to prevent documents from holding up business productivity. Oracle Workflow manages this functionality, typically set up to send a first and second reminder after pre-determined time periods before forwarding the notification to the next approver.

What if the document has a status of Pre-Approved?

The status of Pre-Approved is the outcome of a person forwarding a document for approval even though the forwarding person has the necessary authority to approve it. The document may have been forwarded by mistake or for business reasons. Once the person it was forwarded to approves the document, the status will be changed to Approved and subsequent actions such as receiving and invoicing can be completed.

What if there is no account range on an approval group assigned to a job or position?

By default, all accounts are excluded from an approval group and therefore documents governed by this approval group cannot be approved. You avoid this situation by ensuring that there is at least one Account Range Include rule on every Approval Group.

What if Workflow can’t find a supervisor to approve the document?

If this occurs, the person submitting the request for approval must forward the document to a different person in the list of values for Forward To. If this isn’t done, the document will be returned to an Incomplete status and a notification will be sent stating No Approver Found – Please Select a Forward To Employee. If using employee/supervisor relationship to determine approval paths, the list of values will include all active employees. If using position approval hierarchies to determine approval paths, the list of values will include all employees in the hierarchy selected.

What if a job or position has different authority levels for different document types?

You may have jobs or positions that can approve one document up to a specified dollar amount, while they can approve another document at a lower dollar amount. If this occurs, simply set up multiple approval groups with the rules properly defined for the differences and ensure that the right approval group is assigned to the correct document type.

What if documents flow employee/supervisor, but approval rules are needed?

Approval Groups can be used whether approval paths are determined by employee/supervisor relationships or by position approval hierarchies.

Document Access and Security Level in Oracle Purchasing

Document Access Levels

Specify an access level to control what modification and control actions can take on a particular document once user gain access. Document owners always have full access to their documents.

Following are Access level options

• View Only – Accessing employees can only view the document. Only the document owner may modify or control the document.

• Modify – Accessing employees can view, modify, freeze and close the document.

• Full – Accessing employees can view, modify, freeze, close, cancel and finally close the document.

Document Security Levels

Anyone with menu access to the document entry window can create a document. These individuals then become the document owners.

Security levels can be set for each document type. Following are Security level options

• Private – Only the document owner and subsequent approvers can access the document.

• Purchasing – Only the document owner, subsequent approvers, and individuals defined as buyers can access the document.

• Hierarchy – Only the document owner, subsequent approvers, and individuals above the document owner in the security hierarchy can access the document.

• Public – All system users can access the document.

Accrual Accounting – Periodic Accrual on Period End

Accrual Accounting – Periodic Accrual on Period End in Oracle Purchasing

Debugging the Approval Workflow for Purchase Order or Purchase Requisition

Step 1

Review the action history of the document having a problem. This is done in the Purchasing Summary screens – either Requisitions –> Requisitions Summary or Purchase Orders –> Purchase Order Summary. If you attempt to view the action history and you receive the following error:

APP-14288 This document is either incomplete or you do not have access to it

Then this represents that Workflow has not yet performed a submit into the action history table. The document approval manager performs the action of submitting a record into the Action History and therefore is required to be running. It should be confirmed that the document approval manager is running.

Step 2

Notification Summary – review the Notification Summary for the user that submitted the document. Are there any notifications present? Because online form messaging alerts are now present in the form of notifications, it is imperative that the summary be reviewed for any informative notifications explaining in more detail as to the problem.

Step 3

Confirmation that the Workflow Background Process has been run via the System Administrator responsibility.

This is done via Requests –> Run within the System Administrator Responsibility. The parameters should be the Item Type – PO or Requisition Approval and then

Processed Deferred = Yes, Process Time Out = Yes. Process Stuck = Yes if the Process Stuck parameter is available.

Step 4

Ensure that all tables contain the necessary space to record the Workflow processing data. As users submit a document for Approval and the Workflow process is called, data is recorded into a series of Workflow tables. To quickly confirm whether the tables are having space limitations or not? Run the following in SQL:

select value

from v$parameter

where name like ‘background_dump_dest’;

The value returned by this SQL statement represents the location of the alert log on the database server,

For the instance in question. Please ask the Database Administrator to review the latest entries into the alert.log to confirm if any errors are being thrown with regards to table space.

The most common scenario that has been linked to table space, is when a user hits the ‘Approve’ button in the approval screen, only to have nothing happen. There is no message returned stating document has been submitted for Approval, nothing happens. The hourglass comes for 1 second, and then leaves, and the approval form is still present. This is most likely a table space issue.

Step 5

Workflow Monitor from the Purchase Summary screens. Utilize the Workflow monitor to track the path which the document has taken during its submission to Workflow. Some important milestones to look for in the Workflow monitor are ‘Can Owner Approve’ and ‘Does Approver have Approval Authority’. Are there any processes that contain errors? Or does it appear that a process has stopped at a point that is incorrect?

Example is if a process stopped at a point that is not referencing a notification or some other request point for user interaction

How can one recover ? If one the apps password changed through FNDCPASS when application and database is running

Nice Article Written and Shared by Fouad Hasan Bandarkar

Oppps Employee as Supplier in Oracle Payables R12. How Crazy….

How to enable creation of employee as supplier in oracle payable supplier entry form

How to Turn On “About This Page” in Oracle R12 Web Forms

Set Following Profile Options as Required at User or Site

Personalize Self-Service Defn = YES

FND: Personalization Region Link Enabled = YES

Disable Self-Service Personal = NO (Only at Site)

FND: Diagnostics = Yes

Bounce the Apache Server

Login to Application you can see the About This Page Link on all web pages

By Using About This Page link you can have almost all information of technology Stack

Early Payment Discount In Account Receivable

Early Payment Discount

Example
customer order received for item A and we have scheme for item A if user order item A we will give him discount of 200 $ on each items but only in case if he pay in 3 days how it will be handled ?

Solution Example:

Payment Terms
Receivables lets you define standard payment terms for your customers to specify the due date and discount date for their open items. Payment terms can include a discount percent for early payment and you can assign multiple discounts to each payment term line. For example, the payment term ‘2% 10, Net 30′ indicates that a customer is allowed a two percent discount if payment is received within 10 days, after 10 days, the entire balance is due within 30 days of the transaction date with no applicable discount. You can define proximal payment terms to pay regular expenses such as telephone bills and credit card bills that occur on the same day each month. You can also create split payment terms for invoice installments that have different due dates.

You can use payment terms to determine the amount of each installment. Receivable lets you either distributes tax and freight charges across all installments, or allocates all freight and tax amounts in the first installment of a split term invoice. You can use prepayment payment terms to indicate which business transactions require prepayment for goods and services. Receivables displays the active payment terms you define as list of values choices in the Customers, Customer Profile Classes, and Transactions windows.

To define a payment term:

1. Navigate to the Payment Terms window.
2. Enter the Name of this payment term.
3. Select the Prepayment check box if you are defining a prepayment payment term.

Receivables feeder systems, such as Oracle Order Management, can optionally implement business processes around prepayment payment terms to indicate that a particular business transaction requires the capture of funds before the delivery of a product or service.

4. To associate a credit check with this payment term, check the Credit Check box. Oracle Order Management uses this information to determine when to place an order on hold. In Oracle Order Management, if the profile for an address does not have credit checking limits defined in a particular currency but the customer does, then the order passes credit check. If the address does not have limits in the currency and neither does the customer, then the order is compared to the customer limit in that currency.

5. If you do not want to let your customers take discounts for partial payments on items associated with this payment term, then uncheck both the Allow Discount on Partial Payments check box
as well as the check box for the Discount on Partial Payment system option.

6. Enter the Installment Option for items assigned to this payment term. This indicates how Receivables will allocate the freight and tax charged to transactions using this payment term. Choose ‘Include tax and freight in first installment’ to include all tax and freight charges in the first installment. Choose ‘Allocate tax and freight’ to distribute tax and freight charges across all installments.

7. Enter the Base Amount for this payment term. The default is 100, but you can change it. The base amount is the denominator for the ratio Receivables uses to determine the amount due for installments of invoices to which you assign this payment term. The sum of the relative amounts for all of the payment schedules that you define for these payment terms must be equal to the value that you specify as a base amount.

8. If this payment term uses proximate terms, enter a Cutoff Day for inclusion in the monthly billing cycle. You can either enter a Day of the Month or select the Last Day of Month checkbox. Select the Last Day of Month check box to create a payment term to use with a consolidated billing invoice that includes all transactions created during the month that you specify.

If you are using the Consolidated Billing Invoices program, then you must enter values for the Day of Month and Months Ahead fields in the Due region of the Payment Schedule. Consolidated Billing does not use the Days or Date fields in the Due region. Customers who use payment terms where the value for the Cutoff Day is greater than the value in the Due: Day of Month field should not enter a value of 0 in the Months Ahead field. This will result in the following erroneous scenario:

Cutoff Day = 25
Day of the Month = 15
Months Ahead = 0
in this example, a consolidated billing invoice that is created on January 25 will be due before its creation date on January 15. To avoid this scenario, you must enter a value that is greater than 0 in the Months Ahead field.

9. If you want transactions assigned to this payment term to be printed before the due date, enter a number of Print Lead Days. Receivables will print this transaction x number of days before the due date, where x is the number of days you enter here.

10. Enter the Discount Basis you want Receivables to use when calculating discounts for your invoices.

Choose one of the following discount methods

Invoice Amount: Choose this option to calculate the discount amount based on the sum of the tax, freight charges, and line amounts of your invoices.

Lines Only: Choose this option to calculate the discount amount based on only the line amounts
of your invoices.

Lines, Freight Items and Tax: Choose this option to calculate the discount amount based on the amount of line items, freight, and tax of your invoices, but not freight and charges at the invoice header level.

Lines and Tax, not Freight Items and Tax: Choose this option to calculate the discount amount based on the line items and their tax amounts, but not the freight items and their tax lines, of your invoices.

11. Enter a range of Effective Dates for this payment term. If you do not enter an end date, this payment term will be active indefinitely.

12. Enter a line number for the installment term that you are defining in the ‘Seq’ field. Enter a higher number for each installment term with a later due date. For example, if you create terms with 50% due in 15 days and 50% in 30 days, enter ‘1′ in this field for the first line and ‘2′ for the second line.

13. Enter the Relative Amount for this payment term. This is the numerator of the ratio that Receivables uses to determine the amount due for this installment of these payment terms. The sum of the relative amounts for all of the payment schedules that you define for each payment term must be equal to the base amount for this term.

14. Enter the number of Days after the invoice date that payment is due for this installment term (optional). For split payment terms, this number indicates the number of days after the invoice date that an installment is due.

15. Enter the Date on which payment is due for this installment term (optional). If you do not complete this field, enter a value for either Due Days or both Day of Month and Months Ahead.

16. If you are defining proximate terms, enter the Day of Month that payment is due for this installment term. For example, if payment is due on the fifteenth of each month, enter ‘15.’

17. If you are defining proximate terms and you entered a value for Day of Month, enter the Months Ahead to which these installment terms of the proximate terms refer.

For example, if you entered ‘15′ for Day of Month and you enter ‘2′ here, an invoice dated in May will have a due date of July 15.

18. Save your work. To assign discounts to each payment schedule line of your payment term

Entering Discount Information
Receivables lets you assign discounts to your payment terms. You can also assign multiple discount line terms to each installment of your payment terms. For example, you might give your customer a 10% discount if they pay within 10 days, but only a 5% discount if they pay 11 to 20 days after the invoice date.
Discounts do not apply to Automatic Receipts. If you use the automatic receipts feature to create your Bills of Exchange and Direct Debits, Receivables will not calculate discounts, even if your customers pay before the due date.

Prerequisites
Define payment terms
To assign discount information to a payment term:
1. Navigate to the Payment Terms window.
2. Query or enter the payment term.
3. Choose Discounts.
4. Enter the discount percentage and number of Days for this payment term. For example, to give customers using this payment term a two percent discount if payment is received within ten days, enter ‘2′ and ‘10′ respectively.
5. To limit the period of time that the discount for this installment’s discount line will be active, enter the Date, day of the Month, and the number of Months Ahead for this discount’s expiration date. Receivables uses these values to calculate the discount date during invoice entry.
6. Save your work.

Source: – Metalink, Receivable User Guide

How to Get Supplier Bank Detail In R12

In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. After going thru the guides, it seems that the bank information (such as bank account numbers, etc) for suppliers may be in TCA or in Cash Management Bank Model. Please provide the tables names where bank account information and other bank related information is stored?

By Using Following Queries you can get Required Information on Basis on Supplier Site Name

/* For Supplier Bank Account Number Use This One */

SELECT bank_account_name, bank_account_num, branch_id

FROM iby_ext_bank_accounts

WHERE ext_bank_account_id IN (

SELECT ext_bank_account_id

FROM iby_account_owners

WHERE account_owner_party_id IN (

SELECT party_id

FROM hz_party_sites

WHERE party_site_name LIKE

‘%UPC%’))

/* USE THIS QUERY TO GET BANK NAME AND BANK BRANCH NAME OF SUPPLIER BANK*/

SELECT BANKORGPROFILE.HOME_COUNTRY BANK_HOME_COUNTRY,

BANKORGPROFILE.PARTY_ID BANK_PARTY_ID,

BANKORGPROFILE.ORGANIZATION_NAME BANK_NAME,

BANKORGPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER,

BRANCHPARTY.PARTY_ID BRANCH_PARTY_ID,

BRANCHPARTY.PARTY_NAME BANK_BRANCH_NAME,

BRANCHPARTY.PARTY_ID

FROM HZ_ORGANIZATION_PROFILES BANKORGPROFILE,

HZ_CODE_ASSIGNMENTS BANKCA,

HZ_PARTIES BRANCHPARTY,

HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE,

HZ_CODE_ASSIGNMENTS BRANCHCA,

HZ_RELATIONSHIPS BRREL,

HZ_CODE_ASSIGNMENTS BRANCHTYPECA,

HZ_CONTACT_POINTS BRANCHCP,

HZ_CONTACT_POINTS EDICP

WHERE SYSDATE BETWEEN TRUNC (BANKORGPROFILE.EFFECTIVE_START_DATE)

AND NVL (TRUNC (BANKORGPROFILE.EFFECTIVE_END_DATE),

SYSDATE + 1

)

AND BANKCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BANKCA.CLASS_CODE IN (’BANK’, ‘CLEARINGHOUSE’)

AND BANKCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BANKCA.STATUS = ‘A’ OR BANKCA.STATUS IS NULL)

AND BANKCA.OWNER_TABLE_ID = BANKORGPROFILE.PARTY_ID

AND BRANCHPARTY.PARTY_TYPE = ‘ORGANIZATION’

AND BRANCHPARTY.STATUS = ‘A’

AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID

AND SYSDATE BETWEEN TRUNC (BRANCHORGPROFILE.EFFECTIVE_START_DATE)

AND NVL (TRUNC (BRANCHORGPROFILE.EFFECTIVE_END_DATE),

SYSDATE + 1

)

AND BRANCHCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BRANCHCA.CLASS_CODE IN (’BANK_BRANCH’, ‘CLEARINGHOUSE_BRANCH’)

AND BRANCHCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BRANCHCA.STATUS = ‘A’ OR BRANCHCA.STATUS IS NULL)

AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID

AND BANKORGPROFILE.PARTY_ID = BRREL.OBJECT_ID

AND BRREL.RELATIONSHIP_TYPE = ‘BANK_AND_BRANCH’

AND BRREL.RELATIONSHIP_CODE = ‘BRANCH_OF’

AND BRREL.STATUS = ‘A’

AND BRREL.SUBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRREL.SUBJECT_TYPE = ‘ORGANIZATION’

AND BRREL.OBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRREL.OBJECT_TYPE = ‘ORGANIZATION’

AND BRREL.SUBJECT_ID = BRANCHPARTY.PARTY_ID

AND BRANCHTYPECA.CLASS_CATEGORY(+) = ‘BANK_BRANCH_TYPE’

AND BRANCHTYPECA.PRIMARY_FLAG(+) = ‘Y’

AND BRANCHTYPECA.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BRANCHTYPECA.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND BRANCHTYPECA.STATUS(+) = ‘A’

AND BRANCHCP.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BRANCHCP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND BRANCHCP.CONTACT_POINT_TYPE(+) = ‘EFT’

AND BRANCHCP.STATUS(+) = ‘A’

AND EDICP.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND EDICP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND EDICP.CONTACT_POINT_TYPE(+) = ‘EDI’

AND EDICP.STATUS(+) = ‘A’

AND BRANCHCA.OWNER_TABLE_ID = :IBY_BRANCH_ID /*USER BRANCH ID FROM ABOVE QUERY*/

If anyone have a better solution then please update

Join Processing in Oracle

In a query involving more than one table, join processing is involved to determine the most optimal plan to perform the join. This comprises:

1. Join cardinality
2. Enumerating join orders
3. Evaluating the costs associated with each join path under each join method.

Join Cardinality

Join cardinality is the determination of the number of rows that will make up the joined relation. In the worst case, we have no join predicates and the cardinality is the Cartesian product of the two or more tables involved. But typically when two or more tables participate in a join, the join is based on values of some columns. Using the statistical descriptions on the columns and the number of rows in each table, we calculate the cardinality of the joined form.

Join Orders

A “Join Order” is a particular permutation of ordering the access to tables participating in the join to complete the joined relation. Join orders depend on the type of join or the join topology. Depending on the structuring of the join predicates, joins can be classified into various types viz chain, star, cycle, complete graph, etc. For example, consider the query with a three table join -

SELECT … FROM t1,t2,t3 WHERE

t1.col1 = t2.col1 AND

t2.col1 = t3.col1 ;

Tables t1, t2 and t3 are joined in a chain order.

Tables t1,t2,t3 can be joined in n! i.e n factorial way .

where n is number of tables .

So in This case it will be 3*2*1=6

Possible join orders:

t1->t2->t3, t2->t1->t3, t2->t3->t1, t3->t2->t1

t1->t3->t2, t3->t1->t2

Note the Join orders such as t1->t3->t2, t3->t1->t2 are evaluated using

Cartesian product as there is no join predicate specified between t1 and t3.

Each of the join orders are possible and need to be evaluated for resource usage. As number of tables increase, depending on the join topology, the search space or the possible permutations go up steeply. Several techniques are used to prune the search space and reduce the work involved in identifying the best join order.

Evaluating Join Path Costs

For each join order, the optimizer evaluates the cost of performing the join by breaking the order into pairs. The first part is made of the relations already joined and the second part of the next table to be joined. Costs are considered using both join methods currently implemented in the kernel, the sort-merge join method and the nested-loops join method.

In the sort-merge method, the tables participating in the join are first sorted by the join columns. The sorted relations are then joined. The cost of performing join by this method includes the sorting costs and the cost of retrieving sorted records to perform the join.

In the nested-loops method, we scan the outer table row one at a time and for each row retrieved, we access the inner table based on the join columns. The outer table will actually be a joined relation where more than two tables are joined. The cost of performing the join in this method includes the cost of accessing the outer table and for each row retrieved and the cost of fetching the group of rows from the inner table based on the join columns.

For each table in a particular join order, there is a large number of possible access paths, the table can be accessed using a ROWID or could be accessed by doing a table scan. Alternatively, it may be accessed using a single index or a cluster scan or a merge of several single column no unique indexes.

Several rules are used to narrow down or prune the search space to speed up access path selection. For example, if there is a lookup by ROWID, it is considered the best and we don’t bother to evaluate other access paths open to the table.

Source := US Education Website

What is Content Set In Financial Statement Generator (FSG).

Content Set In FSG

A content set is used to override row set account assignments to generate different variations of the same report without defining separate reports. Most of the time, content sets are used to generate a given report for every value in a segment of the Chart of Accounts—for example, a Standard report that repeats for each company in the Chart of Accounts. To enter content sets, follow the navigation path Reports à Define à Content Set. Enter the name of the content set, an optional description, and the type. The type determines whether the run order of the reports will be sequential or parallel.

Next, define the Account Assignments for the content set. This is very similar to the Account Assignments form you saw in the Define Rows/Columns form. However, since content set overrides the row set, you do not need to fill in all segments. The segments without any value will fall back to the row set account assignments. Only need to define what display types to use to override the row set. Valid display types are listed in Below Table.

N	No override
CT	Summation of balances of all segment values within the account range.
PE	Expand the account range or parent value into detail segment values. In addition, it will page break after each segment value creating a different report.
PT	Override the account range but maintain the row set display types (E, T, B).
RE	Expand the account range or parent value into detail segment values. Same as E in defining rows.
RT	Total the segment values for the segment. Same as T in defining rows.
RB	Show both detail and total for the account range or the parent value. Same as B in defining rows.