*Wishing you a New Year filled with good health, new hope and new beginnings!
* May Y ear 2009 open up for you more opportunities, lead you onto the path of continued success, happiness and prosperity!
* I wish ... that the coming year showers on you all that you ever wished for!
The Payables and Receivables Netting feature enables the automatic netting of Payable
and Receivable transactions within a business enterprise. You can predefine a netting
agreement that incorporates the netting business rules and transaction criteria needed
to run your tailored netting process. The netting process automatically creates the
Payables payments and Receivables receipts required to clear a selected number of
Payables and Receivables transactions.
You can view the receipts that the Netting process creates by querying the netted
receipts in the Receipts workbench. To view additional details about the netting batch,
select AP/AR Netting from the Actions menu.
| Table Name | Feature Area |
| FUN_NET_AR_TRX_TYPES_ALL | Netting Agreements |
| FUN_NET_AP_INV_TYPES_ALL | Netting Agreements |
| FUN_NET_AGREEMENTS_ALL | Netting Agreements |
| FUN_NET_SUPPLIERS_ALL | Netting Agreements |
| FUN_NET_CUSTOMERS_ALL | Netting Agreements |
| FUN_NET_BATCHES_ALL | Netting Batches |
| FUN_NET_AR_TXNS_ALL | Netting Batches |
| FUN_NET_AP_INVS_ALL | Netting Batches |
| View Name | Feature Area |
| FUN_NET_RELATED_CUSTOMERS_V | Netting Related Customers |
This section identifies the new/changed setup steps for Oracle Netting that were introduced in Release 12. For detailed step-by-step implementation instructions, please refer to one of the Oracle Finanicials Implementation Guide, Oracle Financials User Guide or the Oracle Applications Upgrade Guide.
Set up a Bank Account in Oracle Cash Management which can be used by the Netting process. Set the "Netting Account" flag to "Yes" for the bank account. Assign this bank account to the seeded Receivables Receipt Class "AP/AR Netting".
Check the 'Allow payment of Unrelated Transactions' checkbox in Receivables System Options if you want to create a netting agreement across multiple unrelated customers.
If approval is required for a netting agreement, the approver must be set up as a contact for the trading partner (customer or supplier). The contact must be set up with an email address.
Setting the profile "Chargeable Subcontracting Enabled" to "Yes" would mean only invoices that were matched to purchase orders with outsourced assemblies would be selected by the netting process. If the profile option is set to "No", only invoices that were matched to purchase orders without outsourced assemblies would be selected by the netting process. If the profile option is set to "D", all eligible invoices will be selected for netting irrespective of whether or not they are matched to purchase orders.
Netting agreements control how payables and receivables transactions are netted for a group of trading partners. Create netting agreements to set up the rules that will decide which transactions are selected for netting and how they are ordered and processed once selected.
Netting does not upgrade any transactional data. It upgrades only setup data which will not be of high volume. Therefore no special consideration needs to be given for the table sizes during upgrade.
The minimum technical requirements for Netting are included in the Release 12 Rapid Install.
To check log messages added by the Netting processes, set the following profile options at user level
FND: Debug Log Enabled – Yes
FND: Debug Log Level – Statement
FND: Debug Log Module –FUN%NET
Then execute query
SELECT *
FROM fnd_log_messages
WHERE module like 'FUN%NET%'
AND trunc(timestamp) = trunc(sysdate)
Following is the sequence of events and the corresponding status for the netting batch.
| Event | Netting Batch Status |
| User creates the netting batch | RUNNING |
| "Review Batch" option is set to "Yes" | SELECTED |
| Error encountered while validating, selecting transactions | ERROR |
| Current date has gone past batch settlement date | SUSPENDED |
| "Review Batch" option is set to "No" but no approval is required | CLEARING |
| "Review Batch" option is set to "No" and approval is required | SUBMITTED |
| User rejects batch requiring approval | REJECTED |
| User approves batch requiring approval | APPROVED |
| Netting process completed for transactions | COMPLETE |
| User chooses to reverse batch | REVERSING |
| Batch has been reversed | REVERSED |
Criteria used for selecting AP invoices
Dates
AP Payment schedule due date < = FUN_NET_BATCHES.transaction_due_date
AP Payment schedule due date between Agreement Start Date and Agreement End Date ( if null ,Sysdate)
Only Invoices for supplier and supplier sites ( if specified) in the agreement are selected.
Only invoices with the selected invoice types in the agreement are included.
Operating Unit of the Invoices must equal Operating Unit of the batch
Invoices must be approved.
Invoices must not be on hold ( Payment schedule.hold_flag = ‘N’)
Invoices that have already been included in a batch that is not yet in status ‘COMPLETE’ are not included.
Shikyu Processing :
Additional processing is done for Shikyu Invoices based on the Shikyu Rule setup in the Netting Agreement.
Check Shikyu rule code on the Agreement
Y - "Yes" - Only invoices lines that were matched to PO lines with the OSA flag checked are selected for Netting
N - "No" -Only invoices lines that were matched to PO lines with the OSA flag not checked are selected for Netting
D - "Disregard"- All AP invoices selected for Netting; no filtering, therefore all eligible invoices are selected, as if the profile is "Off"
Lock AP Invoices
Selected AP invoices are locked by populating AP_PAYMENT_SCHEDULES.checkrun_id column for the
selected invoices.
The same checkrun_id value is populated in FUN_NET_BATCHES.CHECKRUN_ID for the given batch.
Criteria used for selecting AR Transactions
Dates
AR Payment Schedule due date <= FUN_NET_BATCHES.transaction_due_date
AR Payment Schedule date should be between Agreement Start Date and end date ( if null,Sysdate)
Transactions must be Complete.
Only transactions for customer and customer sites ( if specified) in the agreement are selected.
Only transactions with the selected transaction types in the agreement are included.
Operating Unit of the Transactions must be equal to the Operating Unit of the batch
Transactions that have already been included in a batch that is not yet in status ‘COMPLETE’ will not be included in the new batch.
The Receipt method associated with the transaction should not have a Payment_type_code = ‘CREDIT_CARD’
Transaction must not be in dispute.
Payment schedule status must be open (payment schedule.status = ‘OP’)
Transaction must not be a prepayment (ra_customer_trx.prepayment_flag = ‘N’)
Criteria for ordering the transactions
Invoices and transactions are first ordered by supplier and customer priority associated in the agreement.They are then ordered by the Netting Order Rule selected in the agreement. The priority is important as this decides which transaction / invoice will be netted first.
Netting Currency Rule
The Netting Currency Rule is checked to see if multiple batches need to be created.
If NET_CURRENCY_RULE_CODE = ‘SINGLE_CURRENCY’ then Invoices & transactions are filtered based on Invoice/transaction currency code = FUN_NET_AGREEMENTS.CURRENCY_CODE
If NET_CURRENCY_RULE_CODE = ‘WITHIN_CURRENCY’ then the invoices/transactions are grouped based on their invoice/transaction currency code .
For every group of transactions a new batch is created.
The netting currency of the batch = invoice currency code of the grouped transactions.
IF NET_CURRENCY_CODE = ‘CONVERT_TO_ACCOUNTING’ then the open balance on each transaction is converted to the the Accounting currency.
The netting currency of the batch = Accounting currency
Validate AP and AR Balances
For every batch that has been created.
AP balance is calculated as (sum of the OPEN_AMT in FUN_NET_AP_INVS_ALL for the given batch)
AR balance is calculated as (sum of the OPEN_AMT in FUN_NET_AR_TXNS_ALL for the given batch) .
When the Netting balance Rule = NET_PAYABLES ,
AP balance should be > the AR balance to continue the netting process. If not the current batch
status is set to ‘ERROR’.
If the AP balance or the AR balance <= 0 then the current batch is set to ‘ERROR’. If AP balance < amount =" AP" amount =" AR">
The following validations are performed when a netting batch as submitted as there might be a time lag between when the batch is created and when the batch is submitted. It is also possible that the batch might have been modified.
If the current date has gone past the batch settlement date ( sysdate > settlement_date) then the batch is set to status ‘SUSPENDED’ and the AP invoices are unlocked.
The AP and AR balances for the transactions are validated again. If the validation fails the batch is set status ‘ERROR’and the AP invoices are unlocked.
If the batch has passed all validations, it is sent for approval if approval is required else the Settle Netting Batch process is initiated.
Validation
The process starts off with the validation of the AR transactions. This is necessary as the AR transactions are not locked when the netting batch is created and therefore there is a possibility that the AR transasction may be have been modified since it was included in the netting batch.
If any of the validations fail , then the batch is set to status ‘CANCELLED’.
Validations are also performed to check whether the GL, AP and AR periods are open for the batch settlement date. If not the batch is set to status ‘ERROR’ and an error message is displayed in the Report Log.
Derive Netting Bank Details
The netting bank details like bank account name, bank account owner and number derived based on the FUN_AGREEMENTS.bank_account_id for the given batch.
Settle AP Invoices
Invoices in each batch are grouped by vendor , vendor site and invoice currency code. The AP Payment APIs are called to process the payments. These API return the check id which is updated in FUN_NET_AP_INVS_ALL.CHECK_ID.
Settle AR Transactions
AR Receipt APIs are called to create and apply the receipts. These APIs return the receipt id which is updated in FUN_NET_AR_TXNS.RECEIPT_ID
If all the processing goes through successfully, the batch status is set to COMPLETE and the AP invoices and netting agreements are unlocked.
The TCA Account Merge process calls a netting API to propogate the customer merge updates to the Netting tables.
If the merge results in duplicate accounts and /or account site uses for a netting agreement , then the priority of these records is updated to the highest priority among the records.
All duplicate records for a given agreement are deleted except for one record.
The Payables Supplier Merge process calls a netting API to propagate the supplier merge updates to the Netting tables.
If the merge results in duplicate supplier and /or supplier site for a netting agreement, then the priority of these records is updated to the highest priority among the records.
All duplicate records for a given agreement are deleted except for one record
More on netting in future posts.
Your comments and queries are most welcome.
Receipts:
Receipts in a broader term can be defined as The amount the customer pays to the organization in lieu of an invoice or a transaction that he has performed with the organization.
There are two types of receipts which are in use and they are
Standard Receipt: It is the payment that the organization receives either in cash or cheques for the services or goods provided. This type of receipt is also called as Cash Receipt.
Miscellaneous Receipt: These kinds of receipts are issued whenever there is no direct customer involved for the money coming into the organization. Money received in the form of Refunds from IT dept. investments, interest and stock sales and many more other sources are normally accounted by creating a Miscellaneous receipt.
Lets have some more details about receipts like the receipt Status.
Receipt Status:
A receipt can have the following status:
Approved: This receipt has been approved for automatic receipt creation. This status is only valid for Automatic Receipts.
Confirmed: The customer has approved the application of this receipt and their account balances have been updated within Receivables. This status is only valid for automatic receipts.
Remitted: This receipt has been remitted. This status is valid for both automatic and manually entered receipts.
Cleared: The payment of this receipt was transferred to your bank account and the bank statement has been reconciled within Receivables. This status is valid for both automatic and manually entered receipts.
Reversed: This receipt has been reversed. You can reverse a receipt when your customer stops payment on a receipt, if a receipt comes from an account with non-sufficient funds or if you want to re-enter and reapply it in Receivables. You can reverse cash/Standard receipts and miscellaneous transactions.
More updates on Accounts Receivables to follow. Please feel free to post your comments and queries if any.Feel free to post your comments and your queries.
Use of :$FLEX$ in the default type option of a Parameter form
Steps: 
The query used in the Default Value test field is as below: Select dname from dept where deptno =:$FLEX$.XX_SCENARIO_1
Ensure that the default value for the field Department Number is given as SQL Statement.
Select deptno from dept where deptno=20
Working:
Lets submit the concurrent program and check.
Since the default value was given as a SQL statement, the result of that is shown in the first parameter and the second parameter has the value based on the first parameter as specified in the SQL statement.
Scenario 3:
Use of $PROFILES$ in where clause and Default Value. It is done in the same way as is used in $FLEX$.
Modifying the where clause using $PROFILES$ returns Successful upon validation.
Scenario 4:
Use of Parameter name instead of a value set name in the $FLEX$ Special Variable.
Where Clause of the value set of table type using the parameter name.
Reason:
When we provide the name of the value set in $FLEX$ then we will be able to change the value of the parameter which is dependent on it. If the value set is used by more than one parameter then it becomes difficult to get the exact value in the dependent parameter as it will fetch the values based on the last used value of the value set. It could have been last used by the first parameter or any other parameter in the form. To avoid this kind of confusion, the alternative is to provide the name of the Parameter itself in the $FLEX$ variable. No matter how many times the value set is used, if we provide the parameter name, we will get the dependent parameter value based on its parent parameter only. Let me explain this using the pictures below:
Where Clause of the value set of table type using the parameter
name.
The first and third parameter are based on value set names and the second and fourth parameter are based on parameter name. Since the second parameter is not initialized, the third parameter (value set based )is also not initialized. 
Since the latest value is from Second parameter, hence the value in third is based on the second parameter. The third value is based on the value of the second parameter and is irrespective of the value set. Shown in the next picture.
Here the third parameter takes the latest value of the value set, hence shows the department name corresponding to that and the fourth parameter values are dependent on the value of the second parameter.
These were some of the basic uses of $FLEX$ and $PROFILES$. Feel free to post your queries and comments on this topic.
What is a Flexfield?
As the name suggests, a Flexfield is a ‘flexible field’. A flexfield is a field made up of segments. Segments are nothing but columns of the base table. Each segment has a name that can be assigned and a set of valid values. There are two types of Flexfields in Oracle Applications and they are called as
• Key Flexfield
• Descriptive Flexfield
With both of these flexfields, users get the ability to customize their application to match their business needs without programming.
When and where are these flexfields used?
Descriptive Flexfields are used in places whenever the user feels a need of extra information apart from the Oracle supplied standard information in the forms.
Key Flexfields are used whenever there is a need for storing Key information related to the organization.
What is the difference between Key and Descriptive flexfields?
What are the Base Tables involved in Flexfields creation?
The tables involved are
FND_FLEX_VALUES_TL, FND_ID_FLEXS, FND_FLEX_VALUES, FND_FLEX_VALUE_SETS, FND_ID_FLEX_STRUCTURES
Query to find the number of oracle Supplied key flexfields in each application
SELECT fa.application_name application, COUNT (fif.application_id) flexfield_count
FROM fnd_id_flexs fif, fnd_application_tl fa
WHERE fif.application_id = fa.application_id
GROUP BY fa.application_name
A sample output of this query is as below:
These were some of the basics involved with Flexfields in Oracle Applications. The next post will be on how to enable a Descriptive Flexfield in Oracle Applications.
Please feel free to post your comments or queries
Syntax:
Analytic-Function(Argument,Argument,...)
OVER (
Query-Partition-Clause
Order-By-Clause
Windowing-Clause )
Some of the Analytical functions are as below:
AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
Some examples of Analytical functions in SQL Queries
a) Calculating a Running total:
Query:
SELECT ename, deptno, sal, SUM (sal) OVER (ORDER BY sal) "Running Total"
FROM emp
Output: 
Note: One thing to note here is that, whenever ‘SAL’ is same for the same dept, the running total displayed here is a sum of both the salaries.
b) Retrieving the first and Last values in a group:
Query:
SELECT ename, sal, deptno, first_value(ename) over(Partition by deptno) FIRST, Last_value(ename) over(Partition by deptno) Last FROM emp
Output:
The above query gives the first and last values in a group.
c) Calculating a running average:
Query:
SELECT deptno, sal, AVG (sal) OVER (ORDER BY sal DESC) "Avg Sal"
FROM emp
Output:
d) TOP ‘n’ Analysis:
Query:
SELECT * FROM (SELECT ename, sal, ROW_NUMBER () OVER (ORDER BY sal DESC) RANK FROM emp) WHERE RANK <= 3
Output:
e) Accessing rows around a current row:
Query:
SELECT deptno, ename, sal, LAG (sal, 1, 0) OVER (ORDER BY hiredate, ename) "LAG",
LEAD (sal, 1, 0) OVER (ORDER BY hiredate, ename) "LEAD"
FROM emp
Output:
This query gives the LAG and LEAD values of an organization. LAG means the value previous to the current value and LEAD means the value next to the current value.
The syntax can be explained as below:
LAG(Column_name, Offset, Default_value)
LEAD(Column_name, Offset, Default_value)
Where:
Column_name is the name of the column
Offset defines on how many rows need to be skipped
Default value places a user defined value when there are now values in the offset range.
Windowing Clauses:
The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. The default window is an anchored window that simply starts at the first row of a group and continues to the current row.
The various keywords used here are:
ROWS n PRECEDING, RANGE n PRECEDING
Note: RANGE option can only be used for number and Date datatypes.
Example:
1) RANGE window clause:
Query:
SELECT empno, sal, COUNT (*) OVER (ORDER BY sal ASC RANGE 100 PRECEDING) "counter"
FROM emp
ORDER BY empno
Explanation:
To understand the above query, we need to first look at the clause ‘RANGE 100 PRECEDING’, This creates a range from the current row for sal which is in the range of current value to (current value-100) and counts the salaries which falls within this range.
If we take the first row(1600) the count for this is 2. This is because there exists a value 1500, which is exactly at the start of the range and the number itself (1600), hence the count 2. Similarly, 1250 has a count 2 because of the existence of another value 1250 and 2975 has a count of only 1 because the value prior to this is 2850 which is more than 100 points below it and doesn’t fall in the range created. The other counts and the values follow the same criterion.
2) ROW window Clauses:
Query:
SELECT deptno "Deptno", ename "Ename", sal "Sal",
SUM (sal) OVER (ORDER BY ename ROWS 2 PRECEDING) "Sliding Total"
FROM emp
Output:
