Friday 30 November 2018

Automatic receipt write-off program in Oracle Apps

Automatic receipt write-off program in Oracle Apps

In this post , We will be discuss about Automatic receipt write-off program in Oracle Apps. For Example , We have billed one Customer of Amount $489 but we have received the Payment against this customer of about  $490 now we have apply this Payment to the Customer Invoice but because of my payment was $1 more from Invoice my customer Invoice is totally closed but my receipt is still open for an amount of $1. We can also apply this payment from customer next invoices but if this is not the case then you can use the receipt write-off feature to write the overpaid payment of $1 to Write off account and can close this receipt in the system.
 
Oracle Automatic receipt write-off is a program which helps to automatically write off all the Over Payment Receipts. This Program is only for those receipts which are over paid.
 
This Program write-off receipts only on the basis of Designated un-applied amount or percentage of the Unapplied amount mentioned in the Automatic receipt write-off program parameters. This Program also check that , The user who is running this receipt write-off program should have approval limits for receipt write-off.
 
You can use the Create Automatic Receipt Write-offs program to:
·         Schedule periodic write-offs as receipt adjustments for small remaining balances.
·         Limit write-offs by a percentage of the original receipt amount and by the policy of your enterprise.
·         Create write-offs for specific currencies and customers.
 
Automatic receipt write-off program in Oracle Apps
 
 
Automatic receipt write-off program in Oracle Apps

How to check ar invoice interface errors in oracle apps r12

How to check ar invoice interface errors in oracle apps r12

Here we will be discuss about How to check ar invoice interface errors in oracle apps r12. Oracle apps r12 has given us the feasibility to check ar interface errors directly from the Applications. We don't need to go for backend to check this. Even with error checking we have the feasibility to correct the errors directly from Oracle application and then re submit the Auto Invoice Import program to process these corrected ar invoice interface records. Here below I will share the complete steps to check ar invoice interface errors in oracle apps r12.
 
 

Steps to check ar invoice interface errors in oracle apps r12

Step1:-  Go to receivable Manger.
 
Setup ==> Interfaces ==> Control ==> Auto Invoice ==> Interface Exceptions 
 
How to check ar invoice interface errors in oracle apps r12
 
Step2:-  As below Query this Form(F11 & ctrl F11) to find out all the ar invoice interface error out records in oracle apps r12.
 
click on the Detail Button to which you want to Correction in the Stuck error out record to re-process it.
 
How to check ar invoice interface errors in oracle apps r12
 

What is dqm in oracle apps r12 : Data Quality Management Tool

What is dqm in oracle apps r12

DQM is the Data Quality Management Tool available in the Oracle apps r12. This Tool was not available earlier in the Oracle 11i. This data tool come with the Oracle TCA (Trading Community architecture) in oracle apps r12. This is the very powerful Data tool which could be integrate with different Oracle Applications Module. This Tools is specifically deigned to increase the standard of Data Quality in Oracle. This Tool provide many mechanisms to enhance the standards of the data with their algorithms and analysis. DQM is not pre installed with Oracle applications , We need to put enabled in Oracle and then integrate with Different Modules as per our Requirment like If we want to enable the DQM in the Suppliers Master , or in Customer Master or in the Employees area so we can implement this any where in the Oracle Application to enhance our data standards.
 

Purpose and Working of DQM in oracle apps

 
As I said this is the Data Quality tool , which helps to increase the Standards in maintaining the Masters. This Tool has mechanism and many algorithms which helps to analyze and search the data in many forms so that it helps to find and search the duplicate records from system. DQM uses its internal Staging tables to validate the data. This First Migrate all the Data from TCA registry to its staged Schema with its own standard or set of methods. The staged schema is a mirror of a portion of the TCA Registry and contains data that transformations have cleansed and standardized. DQM stores the TCA party data in all prospects so that , there should not be an chance of duplication in the system.
 
DQM not only helps to find the Duplicate data but also helps to prevent the Duplication in the Oracle system with many other Data related features.

Searches through the staged schema are actually searches against the interMedia or B-Tree indexes that index the schema and speed up the search process. During the acquisition phase of matching, the indexes quickly limit the number of parties to evaluate for scoring.
You can run programs in DQM to generate or update the staged schema and the indexes. For accurate searches, the staged schema can be automatically synchronized with the TCA Registry when records in the Registry are created or updated through user interfaces or TCA import.

DQM Example to Store the Data in the Staging Schema

What is dqm in oracle apps r12
 

Oracle party merge api:Sample PLSQL code for Merging the Two Parties in Oracle Apps

Oracle party merge api

In this post , We will be discuss about Oracle party merge api. In Party Merge , there could be a merging of many other business level Entities Like as per my previous example Party A has both Supplier and Customer so if we will do the Merging of This Party A Both supplier A and Customer A will be merged to another Party B. To Learn more about Party Merge Click Here. Here below is the API to merge parties in oracle apps.
 
 
Oracle party merge api
 

Sample PLSQL code for Oracle party merge api

Declare
l_batch_party_id NUMBER;
l_batch_id NUMBER;
l_from_party_id NUMBER;
l_to_party_id NUMBER;
BEGIN
/* Here below we are extracting the Party informations for Merging these two Parties */
SELECT hz_merge_parties_s.nextval
INTO l_batch_party_id
FROM dual;
BEGIN
SELECT batch_id
INTO l_batch_id
FROM hz_merge_batch
WHERE orig_system_reference=p_batch_id;
SELECT party_id
INTO l_from_party_id
FROM hz_parties
WHERE orig_system_reference = p_from_party_num;
SELECT party_id
INTO l_to_party_id
FROM hz_parties
WHERE orig_system_reference = p_to_party_num;
EXCEPTION
WHEN OTHERS THEN
oracle_error('Party Information Extraction Error. ' || p_from_party_num
||' ' || p_to_party_num);
END;
INSERT INTO hz_merge_parties
( BATCH_PARTY_ID
,BATCH_ID
,MERGE_TYPE
,FROM_PARTY_ID
,TO_PARTY_ID
,MERGE_REASON_CODE
,MERGE_STATUS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,ORIG_SYSTEM_REFERENCE )
VALUES( l_batch_party_id
,l_batch_id
,p_merge_type
,l_from_party_id
,l_to_party_id
,p_merge_reason_code
,p_merge_status
,'0'
,SYSDATE
,'0'
,SYSDATE
,'0'
,p_batch_party_id );
COMMIT;
oracle_error('Party Merge success!', 'atch Party ID: ' || p_batch_party_id );
EXCEPTION
WHEN OTHERS THEN
oracle_error('Party Merge Error Out', 'Orig Batch Party ID: ' || p_batch_party_id || ' ' || sqlerrm);
END;
 

Difference between party merge and account merge


Difference between party merge and account merge

Here we are going to Discuss about the Difference between party merge and account merge. Account merge is the subset of Party Merge in Oracle apps r12. Party Merge is the Feature of Oracle TCA architecture. One Party Merge can involve many Account Merges but Account Merge is only specific to single Account. Account Merger is related to the Customer or Supplier but Party Merge could be link with BU, Supplier and Customers , Employees and many other entities. We need to perform Party merge and then perform the customer merge. It operates on the simple logic, First Parent records need to be merged before merging the child records. Here I will explain each Party merger and account merge differences in Detail.
 

Account Merge :-


When merging two different customers, you must merge all site uses associated with the customer being merged. For example, Customer A and customer B each has one Bill-To site and one Ship-To site. You can transfer activity from A to B by merging like site uses assigned to B(for example, Bill-To's merged with Bill-To's).When You merge one customer to Another , System Automatically Transferred all the Receipts ,AR Invoices and other Activities of the Old Customer to this New Customer so the Liability of the customer will be transferred to this New supplier too.

 

Party merge :-


We all know that TCA. TCA(Trading Community Architecture) comes for picture in Oracle apps r12. TCA more related to Party and more about Customer Oriented. In TCA , Oracle treats every thing in Oracle application as party , which could be become its potential customer in future. To more clarifying this Statement , I will share the Working Examples as below.


For Example I have create the Supplier A in Oracle apps but as per TCA architecture , Oracle will automatically create this Supplier A as an Party because according to TCA , Supplier can also be my Potential Customer in Future. So in Future If I need to sell some goods to this Supplier A , then I can directly create the Customer From this Party A So one Party will become my Supplier and Customer Both but with Different Relation Ship

In Party Merge , there could be a merging of many other business level Entities Like as per my previous example Party A has both Supplier and Customer so if we will do the Merging of This Party A Both supplier A and Customer A will be merged to another Party B.

Party merge in oracle apps r12 : How to perform Party Merge

Party merge in oracle apps r12 : How to perform Party Merge

Here we will be discuss about Party merge in oracle apps r12. There are two Merge options available in the Oracle Receivables and that is "Account Merge" and "Party Merge". Account Merger is purely related to the Customers when we are only doing the merger of the two customers but party merge is quite different in this aspect. Party represent one entity in the entire application and that could be supplier , customer  or both many other. So here in this post m We will be first try to understand about Party merge and then do perform Party merge in oracle apps r12.
 

Party Role in the Oracle Apps r12

We all know that TCA. TCA(Trading Community Architecture) comes for picture in Oracle apps r12. TCA more related to Party and more about Customer Oriented. In TCA , Oracle treats every thing in Oracle application as party , which could be become its potential customer in future. To more clarifying this Statement , I will share the Working Examples as below.
 
For Example I have create the Supplier A in Oracle apps but as per TCA architecture , Oracle will automatically create this Supplier A as an Party because according to TCA , Supplier can also be my Potential Customer in Future. So in Future If I need to sell some goods to this Supplier A , then I can directly create the Customer From this Party A So one Party will become my Supplier and Customer Both but with Different Relation Ship.
 
The same thing apply to employee , customer and my Business unit records too.
 

How to Perform Party merge in oracle apps r12

In Party Merge , there could be a merging of many other business level Entities Like as per my previous example Party A has both Supplier and Customer so if we will do the Merging of This Party A Both supplier A and Customer A will be merged to another Party B.
 
Party merge in oracle apps r12
 
 

Oracle Customer merge api : Sample PLSQL code of Oracle Customer merge api

Oracle Customer merge api

Customer Merge is the option in oracle in which we can merge the one customer to another customer.Use Customer Merge to consolidate any duplicate customers or transfer site use activity from a customer or site that is no longer active or has been taken over by another customer or site. After the merge completes successfully, all activity that was previously associated with the old customer or site is now associated with the new customer or site.  When You merge one customer to Another , System Automatically Transferred all the Receipts ,AR Invoices and other Activities of the Old Customer to this New Customer so the Liability of the customer will be transferred to this New supplier too. In this post , We will be discuss about the API to merge customers in Oracle Apps.
 
Oracle Customer merge api
 

Sample PLSQL code of Oracle Customer merge api

Declare
l_reqid NUMBER;
l_merge_id NUMBER;
l_merge_header_id NUMBER;
l_customer_id NUMBER;
l_customer_number VARCHAR2(50);
l_customer_ref VARCHAR2(50);
l_duplicate_id NUMBER;
l_duplicate_number VARCHAR2(50);
l_duplicate_ref VARCHAR2(50);
BEGIN
BEGIN
SELECT ra_customer_merges_s.nextval
INTO l_merge_id
FROM DUAL;
SELECT ra_customer_merge_headers_s.nextval
INTO l_merge_header_id
FROM DUAL;
SELECT hca.cust_account_id, hca.account_number, hca.orig_system_reference
INTO l_customer_id, l_customer_number, l_customer_ref
FROM hz_cust_accounts hca
WHERE hca.orig_system_reference = p_customer_number;
SELECT hca.cust_account_id, hca.account_number, hca.orig_system_reference
INTO l_duplicate_id, l_duplicate_number, l_duplicate_ref
FROM hz_cust_accounts hca
WHERE hca.orig_system_reference = p_duplicate_number;
EXCEPTION
WHEN OTHERS THEN
oracle_error('Erros in Extracting the Customer and Duplication Customer Informations');
RAISE;
END;
INSERT INTO ra_customer_merge_headers
( CUSTOMER_MERGE_HEADER_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,CUSTOMER_ID
,CUSTOMER_NAME
,CUSTOMER_NUMBER
,CUSTOMER_REF
,DUPLICATE_ID
,DUPLICATE_NAME
,DUPLICATE_NUMBER
,DUPLICATE_REF
,DELETE_DUPLICATE_FLAG
,PROCESS_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,CUSTOMER_FIRST_NAME
,CUSTOMER_LAST_NAME
,CUSTOMER_TYPE
,DUPLICATE_FIRST_NAME
,DUPLICATE_LAST_NAME
,DUPLICATE_TYPE
,MERGE_REASON_CODE
,MERGE_FAIL_MSG )
VALUES( l_merge_header_id
,SYSDATE
,'0' --created_by--
,SYSDATE
,'0'
,'0' --last_update_login--
,NULL
,NULL
,NULL
,NULL --request_id--
,l_customer_id --customer_id--
,p_customer_name
,l_customer_number
,l_customer_ref
,l_duplicate_id
,p_duplicate_name
,l_duplicate_number
,l_duplicate_ref
,p_delete_duplicate_flag
,p_process_flag
,NULL --DFF attribute_category--
,NULL
,p_customer_first_name
,p_customer_last_name
,p_customer_type
,p_duplicate_first_name
,p_duplicate_last_name
,p_duplicate_type
,p_merge_reason_code
,p_merge_fail_msg
);

l_reqid := FND_REQUEST.SUBMIT_REQUEST
( application => 'AR'
,program => 'RAXMRG'
,Description => 'Customer Merge'
,start_time => NULL
,sub_request => FALSE
);
END;
 

Customer merge in oracle r12 : How to perform Customer Merge

Customer merge in oracle r12 : How to perform Customer Merge

Here we are discussing about Customer merge in oracle r12. Customer Merger is the New Feature in Oracle apps r12 which is uses to merger two different customer in to one customer including merging its Bill to and Ship to sites too. Customer Merge is also the best option to consolidated the duplicate customers accounts in to one account or site that is no longer active or has been taken over by another customer or site. Merging is also come In place when two different customers merged with each other in business and now we need to work only with single customer so we can also do the merging of these customers in our Oracle system too. Merging is not only the merging to two different customers but it also involved many other the merging of other transactions too. Here below I will describe the complete scenario of Customer merge in oracle r12.
 

Important aspects of Customer merge in oracle r12

Step1:- When you go to Receivable's Manger then under Customers Navigation , you gets the Customer Merger Feature as below.
 
We need to provide the From Customer and To Customer Information like In From you will provide that customer which you wants to merge with TO Customer Name.
 
You also have the option to Delete the Merged customer after merging.
Customer merge in oracle r12
 
 
Step2:- When merging two different customers, you must merge all site uses associated with the customer being merged. For example, Customer A and customer B each has one Bill-To site and one Ship-To site. You can transfer activity from A to B by merging like site uses assigned to B(for example, Bill-To's merged with Bill-To's).
 
Step3:- When You merge one customer to Another , System Automatically Transferred all the Receipts ,AR Invoices and other Activities of the Old Customer to this New Customer so the Liability of the customer will be transferred to this New supplier too.
 
Step4:- The merge process also checks for records in the AutoInvoice interface tables.
 
 

Difference Between Account alias issue and Miscellaneous issue

Difference Between Account alias issue and Miscellaneous issue

Here we will be discussing about the Differences Between Account alias issue and Miscellaneous issue. Both Account alias Issue and miscellaneous Issue are Inventory Transaction Types. Both these receipts uses to Reduce the Stock of the Item in the system with out having Requirment to issue the Material against Inventory WIP jobs or wants to do some inventory adjustments related to Issue transaction but both are little bit different their operations or way of working. Here below I will explain in Detail the Difference Between Account alias issue and Miscellaneous issue.
 
 
Difference Between Account alias issue and Miscellaneous issue
 

Account alias issue 

 

Account alias issue is almost similar like the Miscellaneous issue in oracle. They also uses to reduce the Stock of the Item in Subinvnetory without having Requirment of to consume the stock again WIP JOBS. But the Only Difference between   Miscellaneous issue and Account alias issue is that , In Miscellaneous issue , We need to remember the complete GL Account Combination to which this Item Material consumption Should be charged but in the Account alias Issue we can use the GL Account Combination aliases which we have already created in the GL so that we don’t need to remember the complete account. Account alias is the user friendly name of the GL account combination that helps non finance user to easily remember the names instead of entire GL account combination to which material  issue should be charged.
 
 
 

Miscellaneous Issue

 
 This is one of the Transaction type of the Inventory. When we want to reduce the Stock of the Item in the Oracle inventory but we don't want to issue the material against WIP jobs then we can directly issue the Inventory items from some Subinvnetory using Inventory Open Miscellaneous Transactions form with using Transaction type of ‘Miscellaneous Issue’ .Transaction Type is very Important In this. We also need to enter the GL Account Combination to which this Item Material Should be charged.
 
 Less User-friendly as compered to  Account alias Issue.

Account alias issue in oracle apps r12

Account alias issue in oracle apps r12

Here we are going to discuss about Account alias issue in oracle apps r12. Account Alias Issue is one of the Transaction type of the Oracle Inventory. As its name represent , This is related to the Onhand Item Stok Issue or Consumptions. In others words , If we want to decrease the Stock of some Inventory Item or wants to do some inventory adjustments related to Issue transaction then we uses this Account alias issue transaction types in oracle apps but in Inventory we have two types to Issue transactions that is ' Miscellaneous Issue and Account alias issue " both uses to reduce the Onhand stock of the item but both are different in their way of working. Here I will share the complete details of Account alias issue in oracle apps r12
 
 
Account alias issue is almost similar like the Miscellaneous issue in oracle. They also uses to reduce the Stock of the Item in Subinvnetory without having Requirment of to consume the stock again WIP JOBS. But the Only Difference between   Miscellaneous issue and Account alias issue is that , In Miscellaneous issue , We need to remember the complete GL Account Combination to which this Item Material consumption Should be charged but in the Account alias Issue we can use the GL Account Combination aliases which we have already created in the GL so that we don’t need to remember the complete account. Account alias is the user friendly name of the GL account combination that helps non finance user to easily remember the names instead of entire GL account combination to which material  issue should be charged.
 
Here below is the Setup of the Account alias.
Account alias receipt in oracle

Account alias issue in oracle apps r12

 

Thursday 29 November 2018

Difference between account alias receipt and miscellaneous receipt

Difference between account alias receipt and miscellaneous receipt

Here we are going to Discuss about , difference between account alias receipt and miscellaneous receipt. Both Account alias and miscellaneous receipt are Inventory Transaction Types. Both these receipts uses to Increase the Stock of the Item in the system with out having Open Purchase Order or uses to do some stock Adjustments in the Oracle system but both are little bit different their operations or way of working. Here below I will explain in Detail the Complete Difference between account alias receipt and miscellaneous receipt.
 
Difference between account alias receipt and miscellaneous receipt
 

5 Differences between account alias receipt and miscellaneous receipt

Miscellaneous receipt:- This is one of the Transaction type of the Inventory. When we want to increase the Stock of the Item in the Oracle inventory but we have no purchase order against that then we can directly receive the Inventory items in some Subinvnetory using Inventory Open Miscellaneous Transactions form with using Transaction type of ‘Miscellaneous receipts’ .Transaction Type is very Important In this. We also need to enter the GL Account Combination to which this Item Material Should be charged.
 Less User-friendly as compered to  Account alias receipt
Account alias receipt: This is also one of the Transaction type of the Inventory. Account alias receipt is almost similar like the Miscellaneous receipt in oracle. They also uses to Increate the Stock of the Item in Subinvnetory without having Requirment of Open Purchase order. But the Only Difference between   Miscellaneous receipt and Account alias receipt is that , In Miscellaneous receipt , We need to remember the complete GL Account Combination to which this Item Material Should be charged but in the Account alias receipt we can use the GL Account Combination aliases which we have already created in the GL so that we don’t need to remember the complete account. Account alias is the user friendly name of the GL account combination that helps non finance user to easily remember the names instead of entire gl account combination to which material should be charged.
 
More User-friendly as compered to  Miscellaneous receipt.

Account alias receipt in oracle

Account alias receipt in oracle

Here we will be discussing about Account Alias receipt in Oracle. Account Alias receipt is related to the Oracle Inventory. This is more about same with Purchase Receipts and Miscellaneous receipts but different in the Functioning in oracle. These all the three receipts are Inventory Transactions have directly link with oracle Inventory Item Stock. All these three receipts increases the stock of the Item in Oracle Inventory. Here below I will discuss in detail about account alias receipt in oracle and in which cases we need this type of receipt.

Purchase Receipt: -

 When we do receiving against the Purchase orders then we do Purchase Receipts.

Miscellaneous receipt:-

 This is one of the Transaction type of the Inventory. When we want to increase the Stock of the Item in the Oracle inventory but we have no purchase order against that then we can directly receive the Inventory items in some Subinvnetory using Inventory Open Miscellaneous Transactions form with using Transaction type of ‘Miscellaneous receipts’ .Transaction Type is very Important In this. We also need to enter the GL Account Combination to which this Item Material Should be charged.

Account alias receipt:

This is also one of the Transaction type of the Inventory. Account alias receipt is almost similar like the Miscellaneous receipt in oracle. They also uses to Increate the Stock of the Item in Subinvnetory without having Requirment of Open Purchase order. But the Only Difference between   Miscellaneous receipt and Account alias receipt is that , In Miscellaneous receipt , We need to remember the complete GL Account Combination to which this Item Material Should be charged but in the Account alias receipt we can use the GL Account Combination aliases which we have already created in the GL so that we don’t need to remember the complete account. Account alias is the user friendly name of the GL account combination that helps non finance user to easily remember the names instead of entire gl account combination to which material should be charged.
 

Setting up the Account alias is the first thing to use before using the Account Alias receipt.

Here below is the Setup of the Account alias.

Account alias receipt in oracle

Account alias receipt in oracle

 

Account alias in oracle apps : How to Define the Account alias in oracle

Account alias in oracle apps : How to Define the Account alias in oracle

In this post , we will be discuss about Account alias in oracle apps. Account Alias is uses in the GL accounts Combination. When we enter the AP invoices , In the Distributions of the AP invoices we need to Enter the GL account to which we are booking this AP invoice Expense. This GL accounts combinations depends upon the Account KFF structure. Some companies use 5 segment GL account combination in KFF and some uses 6 and some users 7 segments too but in the GL account KFF. So every time user need to select the 5 or 6 GL accounts combinations in the Invoice distribution. This is time consuming also and also have an chance that user can select the wrong GL account combination for this We can define the GL Account combination alias so user no need to remember the all 6 segment GL code combination. They just need to remember the Account Alias for That GL account and enter in the Invoice and system will automatically enter the GL account combination for the Account Alias.
 
 

Example of Using Account alias in Oracle Apps

 
For Example I have 6 segment GL KFF and I have one GL account Combination "001-445-5555-677776-554-666" that I uses in the Invoice distribution for some specific supplier. Now I need to remember this GL account Combination every time to enter this in the Invoice Distribution or I can use the Account Alias feature to create the Account Alias with Name "ABC SUPPLIER ACCOUNT" for this GL Account combination "001-445-5555-677776-554-666" and when ever I need to enter this Account Combination I can use only this Alias "ABC SUPPLIER ACCOUNT".
 
 
How to Define the Account alias in oracle
Step1:-

 
Account alias in oracle apps

How to Define the Account alias in oracle

 
 

Open purchase order query in oracle apps

Open purchase order query in oracle apps

In this post , We will be discuss about Open purchase order query in oracle apps. Open Purchase order in oracle apps are those purchase orders which are still not received or some of the qty is still pending for receive from supplier side or in second case all the quantity has been received but the AP invoice is not created against all the quantity of the Purchase order. So when ever the complete billing of the Purchase order will not done in AP , oracle will keep that Purchase order still open in the system. Here below I will share the complete details and the tables used to design the Open purchase order query in oracle apps.
 
Open purchase order query in oracle apps

Important Table of Open purchase order query in oracle apps

1.PO_HEADERS_ALL
2.PO_LINES_ALL
3.PO_LINE_LOCATIONS_ALL
3.PO_DISTRIBUTIONS_ALL
 
 

Example of Open purchase order query in oracle apps

 SELECT
 ,poh.segment1 "PO NUM"
 ,poh.authorization_status "STATUS"
 ,pla.line_num "SEQ NUM"
 ,plla.line_location_id
 ,d.po_distribution_id
 ,poh.type_lookup_code "TYPE"
 FROM
 ,po.po_headers_all poh
 ,po.po_lines_all pla
 ,po.po_line_locations_all plla
 ,po.po_distributions_all d
 WHERE
 poh.po_header_id = pla.po_header_id
 and plla.po_line_id = pla.po_Line_id
 and plla.line_location_id = d.line_location_id
 AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
  and poh.closed_date is NULL

Oracle cross validation rules query

Oracle cross validation rules query

In this post , We will be discuss about Oracle cross validation rules query. We all know the We creates Cross validations rules in Oracle Chart of Account or in KFF. With the help on these rules we restrict the business users to enter the Specific GL accounts combinations in the Oracle Transactions. We can easily verify these rules from the Oracle application but here in the post , I will share the SQL query to extract the Cross Validations rules in the Oracle.  Please find below the complete details of the Oracle cross validation rules query.
 
 
 
Oracle Cross validation rules works on the basis of Two Principals and these are Include and Exclude.
 
Two Important Tables of Oracle cross validation rules query
 
1.fnd_flex_include_rule_lines
2.fnd_flex_exclude_rule_lines
 

 Sample of Oracle cross validation rules query

 
SELECT FST.ID_FLEX_STRUCTURE_NAME " Accounting Strucuture"
, R.FLEX_VALIDATION_RULE_NAME "Cross Validation Rule Name"
, L.INCLUDE_EXCLUDE_INDICATOR "Indicator Type"
, L.CONCATENATED_SEGMENTS_LOW "Accounts FROM"
, L.CONCATENATED_SEGMENTS_HIGH "Acccounts TO"
FROM FND_FLEX_VALIDATION_RULES R,
 FND_FLEX_VDATION_RULES_TL TL,
 FND_FLEX_VALIDATION_RULE_LINES L,
 FND_ID_FLEX_STRUCTURES_VL FST
WHERE R.APPLICATION_ID = TL.APPLICATION_ID
AND FST.ID_FLEX_NUM = R.ID_FLEX_NUM
AND R.ID_FLEX_CODE = L.ID_FLEX_CODE
AND R.ID_FLEX_NUM = L.ID_FLEX_NUM
AND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND R.APPLICATION_ID = 101
AND R.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND R.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND R.APPLICATION_ID = L.APPLICATION_ID
ORDER BY FST.ID_FLEX_STRUCTURE_NAME,R.FLEX_VALIDATION_RULE_NAME,L.CONCATENATED_SEGMENTS_LOW

Wednesday 28 November 2018

Cross validation rules include exclude

Cross validation rules include exclude

In this post , We will we discuss about Cross validation rules in oracle apps and how we can implement these rules. Cross validation implementation rule works on the principle of Include and Exclude. We first include all the DFF segment values and then Exclude those Values of the KFF segments which we want to restrict to stop entering by business user. Here below is the complete working example of Cross validation rules include exclude.
 
Cross validation rules include exclude
 

Working Example of Cross validation rules include exclude.

Example :- I am using the one Example in this for better understanding. Suppose , We have one Chart of Accounts having 4 segments.
Step1:- For Example these below are my Char of Accounts Name and Available Values .
 
Segment1 == > BU ==> VALUES (00,12,14,15,ZZ)
Segment2 ==> Location ==> Values( 000,123,124,ZZZ)
Segment3==> Department==> Values (0000,2345,5678,ZZZZ)
Segment4==>Account==> Values(00000,34567,35678,45667,46788,89999,87766,ZZZZZ)
 
Step2:- Now I have requirement , I want to put Restrictions in the system that , If Any user Will select the BU with Value (15) is should not able to Use the Location Value (123) So this restriction we can implement with the Help of cross validation rules in oracle r12.
 
Step3:- We will Design the cross validation rules for this above Requirment as below.
 
INCLUDE : For BU ==>  00 TO ZZ (00 to ZZ means all the values available in the BU will be included)
For Location ==> 000 TO ZZZ (000 to ZZZ means all the values available in the Location will be included)
For Department==> 0000 TO ZZZZ (0000 to ZZZZ means all the values available in the Department will be included)
For Account ==> 00000 TO ZZZZZ (00000 to ZZZZZ means all the values available in the Account will be included)
 
Now to Put Restriction We will be create another Line With 'EXCLUDE'
 
EXCLUDE : For BU ==>  15 TO 15
For Location ==> 123 TO 123
For Department==> 0000 TO ZZZZ (0000 to ZZZZ means all the values available in the Department will be included)
For Account ==> 00000 TO ZZZZZ (00000 to ZZZZZ means all the values available in the Account will be included)
 
 

Difference Between Cross validation rules and security rules in oracle apps

Difference Between Cross validation rules and security rules in oracle apps

In this Post, We will be discuss about the Difference Between Cross validation rules and security rules in oracle apps. Both these rules works for KFF or Chart of Accounts but both have different implementation steps and way of working. Here in this Post, We will try to learn more about the basic Difference Between Cross validation rules and security rules in oracle apps.
 
 

3 Major Differences Between Cross validation rules and security rules in oracle apps

1.Cross Validation Rules Apply to the Whole Chart of Accounts Segments or Structure but Security rule can apply for Specific Segment in the chart of account or for all also.
2. Cross Validation rules by default works for all responsibilities in oracle apps These Rules works Globally , We have no control to restrict these rolls on the basis of User , Responsibility and Business Units but Security rules can work for all and or for specific responsibility too in the oracle apps.
3.Cross validation rule to put some restrictions in the  Char of Accounts or in the Accounting Key flex fields using this Cross Validation rules but Security rules works to put security in the Accounting Key flex fields segments.
 

Cross validation rules and security rules in oracle apps

Cross validation rules and security rules in oracle apps

Here we will discuss about cross validation rules and security rules in oracle apps. These are two Security rules available in the Oracle application for Char of Accounts (KFF). We can put restrictions and security on Accounting KFF or chart of Accounts using these rules. Both rules have different way of Functioning and different set of features too. The common seminaries between cross validation rules and security rule is only that for works for KFF or Chart of accounts. Here in this Post, I will be explain in detail about cross validation rules and security rules in oracle apps.
 

Detail Explanation of cross validation rules and security rules in oracle apps

 
1.Security rules : -
 
This is the simple Security Concept in the KFF, We can Exclude some of the values of Chart of Accounts Segments to be used by business Users. The good part of this Rules is that , This rule does not apply to the whole KFF, We can restrict this rule to Specific segment of the KFF and also we can put this Security to the Specific responsibilities not to the Entire Responsibilities like Cross Validation Rule. This rule only work for responsibilities so we have control we can design the different -2 Security rules for different responsibilities based on our Requirment.

How to Define Security Rule in Oracle apps
 
Cross Validation Rules :-
 
The purpose of this rule is more about security and control in the application. We put some restrictions in the  Char of Accounts or in the Accounting Key flex fields using this Cross Validation rules. Cross validation rules we creates in the General Ledger responsibility. These rules works for all Business units based on the Which Chart of Accounts we have applied this cross Validation rule.

1.These Rules only apply to the Char of Account or Key Flex Field Structure.

2.These Rules works Globally , We have no control to restrict these rolls on the basis of User , Responsibility and Business Units.

3.We uses the Cross validation rules to restrict the Key Flex fields segment values so that no user can use these values in the Account KFF during entering the Transactions.
 
 
 

How to implement security in kff in oracle apps

How to implement security in kff in oracle apps

Here we will be discuss about Security in the KFF in oracle apps. KFF Full form is Key Flex Fields or We also says Char of Accounts. KFF is very important in the Oracle application and all the system accounting completely relies on this Accounting KFF so for this some times we need to put security in the Accounting KFF's so that System accounting should be happens in control way and user should be restrict to perform unauthorized accountings in the system. When user do transactions in the Oracle system , They can select any value in the KFF, and this KFF value determines the accounting of this Transaction so this is very crucial and we need to put security in that  so that user should not be able to select the Wrong accounts in the KFF. Here below I will share the complete steps to implement security in kff in oracle apps.
 

Types of Security in KFF

 
1.Cross Validation Rules :-
 
The purpose of this rule is more about security and control in the application. We put some restrictions in the  Char of Accounts or in the Accounting Key flex fields using this Cross Validation rules. Cross validation rules we creates in the General Ledger responsibility. These rules works for all Business units based on the Which Chart of Accounts we have applied this cross Validation rule
 
 
2.Disable some Values of the Char of Accounts to be use by Business User.:-
 
This is simple Security Concept in the KFF, We can Exclude some values of Chart of Accounts Segment to be used by business Users. The good part of this Rules is that , This rule does not apply to the whole KFF, We can restrict this rule to Specific segment of the KFF and also we can put this Security to the Specific responsibilities not to the Entire Responsibilities like Cross Validation Rule

Steps to Implement security in kff in oracle apps 

Here I am going to discuss second type of Security in oracle apps.
 
Step1:- Go to General Ledger
Setup==>Financials ==> Security ==>Define
 
How to implement security in kff in oracle apps
 
Step2:- We are putting Security in the Accounting Flex Fields so Select
Application : General Ledger
Title: Accounting Flex Field
Structure :- Name of the KFF.

How to implement security in kff in oracle apps
 
Step3:- Here I am putting Security for KFF Segment2 (Department). I want to Exclude the KFF Department Segment Values from 2000 to 4000 for some Specific responsibility so that Business Users for these Responsibilities will enter the Department Values between 2000 and 4000.
 
Then Click on the Assign Button to Which Oracle Responsibility you want to apply this Security Rule.
 
How to implement security in kff in oracle apps

Step:- Select Oracle Responsibility to which you want to apply this Security Rule.

How to implement security in kff in oracle apps
 
 
 
 

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *