Saturday, January 21, 2017

Query Get Details of Different Journals Transferred To General Ledger (GL)

SELECT
  gjh.period_name "Period name"
  ,gjb.name "Batch name"
  ,gjjlv.header_name "Journal entry"
  ,gjjlv.je_source "Source"
  ,glcc.concatenated_segments "Accounts"
  ,mmt.subinventory_code "Subinventory"
  ,glcc3.segment4 "Costcenter"
  ,gjjlv.line_entered_dr "Entered debit"
  ,gjjlv.line_entered_cr "Entered credit"
  ,gjjlv.line_accounted_dr "Accounted debit"
  ,gjjlv.line_accounted_cr "Accounted credit"
  ,gjjlv.currency_code "Currency"
  ,mtt.transaction_type_name "Transaction type"
  ,TO_CHAR(mta.transaction_id)"Transaction_number"
  ,mta.transaction_date "Transaction_date"
  ,msi.segment1 "Reference"
FROM
  apps.gl_je_journal_lines_v gjjlv,
  gl_je_lines gje,
  mtl_transaction_accounts mta,
  mtl_material_transactions mmt,
  mtl_system_items_b msi,
  gl_je_headers gjh,
  gl_je_batches gjb,
  apps.gl_code_combinations_kfv glcc,
  apps.gl_code_combinations_kfv glcc2,
  mtl_secondary_inventories msin,
  mtl_transaction_types mtt,
  MTL_SECONDARY_INVENTORIES cost,
  gl_code_combinations glcc3
WHERE 1=1
  AND gjjlv.period_name BETWEEN 'JAN2015' AND 'DEC2016'
  AND gje.code_combination_id = gje.code_combination_id
  AND gjjlv.line_je_line_num = gje.je_line_num
  AND gl_sl_link_table = 'MTA'
  AND gjjlv.je_header_id = gje.je_header_id
  AND mmt.inventory_item_id = msi.inventory_item_id
  AND gje.je_header_id = gjh.je_header_id
  AND gjh.je_batch_id = gjb.je_batch_id
  AND mmt.organization_id = msi.organization_id
  AND mmt.organization_id = msin.organization_id
  AND mmt.subinventory_code= msin.secondary_inventory_name
  AND mta.gl_sl_link_id= gje.gl_sl_link_id
  AND mta.reference_account = glcc.code_combination_id
  AND msin.expense_account = glcc2.code_combination_id
  AND mmt.transaction_id = mta.transaction_id
  AND mtt.transaction_type_id = mmt.transaction_type_id
  AND cost.organization_id(+) = mmt.organization_id
  AND cost.secondary_inventory_name(+) = mmt.subinventory_code
  AND glcc3.code_combination_id(+) = cost.expense_account


Oracle Advanced Pricing Key Functionalities

The key functionalities of Oracle Advanced Pricing include the following:
· Defining and assigning rules for pricing products.
· Applying different types of discounts and surcharges to pricing.
· Creating a price list for different pricing criteria.
· Creating formulas to calculate pricing.
· Creating conversion rates for the usage of multiple currencies.
· Integration with different EBS modules for optimized pricing
· Supporting TCA party hierarchy for price list.
· Using Oracle Advanced Pricing, with the efficient use of qualifiers, modifiers, and formulas, we can efficiently manage all business scenarios.
· Targeting the specific item definition with the help of the pricing attribute.
· Making our own rules using the qualifier. For example, if today is Saturday then there will be 15 percent discount on the product.
· Multi-level responsibility available, such as pricing administrator, manager, and pricing user.

Oracle Advanced Pricing Process
The Oracle Advanced Pricing process normally initiates when a price for an item is created in the price list; the price for the item is called by the application. The qualifier and pricing attribute are used to select the eligible price or modifier. The price or the modified price adjustment, in the form of discount or surcharge, will be applied and final price is obtained. This final price is then applied against the item on the requested application.

Price list
The price list is the list of prices for different items and products. Each price list can have one or more price lines for an item. It contains the qualifier and pricing attributes. The prices of items in a price list can be constant values that can be picked up at the time of ordering. These prices can also be derived using formulas and percentages.

Qualifier
Qualifiers are rules that control who will be priced. Qualifiers contain the qualifier context and qualifier attribute that creates a logical grouping and explains who is eligible for these prices. Qualifier attributes can be order type, source type, order category, customer PO, and so on. In qualifiers we have operators that can create a condition such as equal to, between, not equal to, and so on.

Modifiers
Modifiers allow us to adjust the prices. Using a modifier, we can either increase or decrease the current price list for price adjustment surcharges, promotions, and discounts that are available to us these values are from list. Type code with a system access level.

Formulas
In Oracle Advanced Pricing, formulas are used to price items. These formulas actually contain the arithmetic and mathematical expressions used by the pricing process. Using these formulas, arithmetic equations provide us with the final price of items. If a formula is associated with any price list then we cannot use the constant and absolute values for that particular item.

Integration of Oracle Advanced Pricing with other modules
Oracle Advanced Pricing is fully integrated with other Oracle E-Business Suite modules. The following are the modules that are integrated with Oracle Advanced Pricing:
·         Oracle Purchasing
·         Oracle Order Management
·         Oracle Service Contract
·         Oracle Sales Contract
·         Oracle iStore
·         Oracle Transportation

Pricing concept of Oracle Advanced Pricing
There are four major concepts of pricing, that should be understood in order to achieve the proper pricing. This gives us an understanding of the limitations and flexibilities of the product, and how certain business scenarios should be catered to when using Oracle Advanced Pricing.

Pricing rules
Pricing rules show us who is eligible and to whom this price will be applied. Using this pricing rule, we can get the final price of the item including discounts and surcharges. Mostly, the pricing rules we create are according to the customers in Oracle Advanced Pricing. We can also create a pricing rule for a group of customers. Using the pricing rules, discounts and surcharges are also catered for. We can create numerous modifiers to which we can give different types of sales promotions, discounts, and surcharges.

Pricing action
Pricing action refers to the function that is performed in response to the request from the application. It consists of pricing that is applied to the transaction to be processed. Pricing actions can be the selection of the price list and further use of the formula and modifier, from which accurate and conditional pricing will take place against the business scenarios. The adjustment in the price according to discounts, offers, or additional surcharges applied on the price will take place using the modifiers.

Pricing control
Pricing control is another very important part of the pricing process. At the pricing control level, the controls on the pricing actions are determined and applied. Pricing control gives the control to validate and verify events, against which pricing action takes place. A common pricing control is the validity date. If an offer contains a discount, which is available for the product within a specific date range, pricing control will take care of that.

Price Extensibility
Oracle Advanced Pricing facilitates us with extensibility features so that we can properly map our business scenarios in Oracle. Price extensibility empowers us with various features such as APIs and attribute mapping.

Setting up Oracle Advanced Pricing
The steps required to set up Oracle Advanced Pricing are shown in the following figure:

Profile options
In order to set up Oracle Advanced Pricing, there are many profile options that are required to enable the product to work properly. An important profile is QP: Multi Currency Installed. When this profile is enabled we can use a price list in multiple currency rather than online in functional currency.
QP: Multi Currency Usage, which elaborates the application that calls the pricelist, can also have multiple currencies available in it.
To set up the profile options, navigate to System Administration | Profile | System.

We can also use other profile options such as "allow duplicate modifiers", "size of bulk import", and so on. There are many other profile options that are available in the system in order to give a better solution for our business scenarios.

Price Transaction Entity (PTE)
PTE stands for Price Transaction Entity. PTE is the required setup for Oracle Advanced Pricing. However, there is a default available for order fulfillment, logistics, procurement, and so on. PTE is the combination of a request type and source system. To query the PTE, navigate to Setup | Attribute Management | Price Transaction Entity.

If we select Order Fulfillment PTE in the Source System, there are default codes such as AMS, QP, OKS, and so on; against them we have Request Types that detail the header and line structure.

In the same manner, we have Functional Areas for Source System. For example, in the previous screenshot, the Advanced Pricing functional areas are highlighted along with the Category Set associated with them.

Qualifier
As we have already defined, qualifiers are the set of rules and conditions that actually help us analyze the eligibility of the price list that should be applied, and the modifier to be selected. We can select a different qualifier context such as order, customer, and so on. The qualifier attribute is dependent on the qualifier context. Therefore, we will be only offered relevant information. To create a qualifier for a specific product context and product attribute, we need to navigate to Price List | Price List Setup.

Now we will query the price at which we need to apply the qualifier. We will navigate to the Qualifiers tab and select the Qualifier Context and Qualifier Attribute as per our requirement. We can select Customer, Territories, and other values at the qualifier context.

Pricing, product, and qualifier attributes
To define the attributes, we need to navigate to Setup | Attribute Management | Context & Attribute

In Context & Attribute, we can create context for the qualifier, product, and price. They should have a unique code and description to distinguish them from the other contexts. Each context has its attributes. These attributes can be altered but if they are attached to a price list or modifier, they cannot be altered or deleted.

Unit of Measure (UOM)
Unit of Measure (UOM) is a shared application setup. If Oracle Inventory is already set up, we do not need to perform this setup again. UOMs are used in Oracle Advanced Pricing for the calculation and pricing unit. Navigate to Setup | Unit of Measure | Unit of Measure Classes.

Advanced Pricing lookups
Like other modules, Oracle Advanced Pricing also uses lookup codes. We can use and create these lookup codes as per our business needs and requirements. To create a lookup, navigate to Setup | Lookups.

For example, here we can create a modifier list type, which we can use to create the price adjustments and amendments.

Oracle Advanced Pricing in sales order entry process
Oracle Advanced Pricing intervenes in the sales order entry process. At the time of order booking, the price for the item is required. At the time of booking the order, the list price of the item is picked by order entry from Advanced Pricing. When the order entry calls the price, the pricing engine calculates the price according to the pricing setup of the qualifier and modifier, and gives the final price of the item for the booking order, as in the following screenshot. We can see the unit selling price of 1 quantity that has been picked from the predefined pricelist.

Summary

In this article, we have seen the functionality of Oracle Advanced Pricing and why it is used. We have also seen how to set up the Oracle Advanced Pricing, and have learnt the different terminologies, capabilities, and limitations of the product. Moreover, we have seen how to use Qualifiers, Attributes, Contexts, UOMs, lookups, and PTE, and how they work. We have also seen how an item is priced for sales orders from a predefined price list.

Oracle R12 Receivables Auto Lockbox (Inbound) Process Details

Using Auto Lockbox
Auto Lockbox (or Lockbox) is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing. A lockbox operation can process millions of transactions a month.

Auto Lockbox eliminates manual data entry by automatically processing receipts that are sent directly to your bank. You specify how you want this information transmitted and Receivables ensures that the data is valid before creating QuickCash receipt batches. You can automatically identify the customer who remitted the receipt and optionally use Auto Cash rules to determine how to apply the receipts to your customer's outstanding debit items.
You can also use Auto Lockbox for historical data conversion. For example, you can use Auto Lockbox to transfer receipts from your previous accounting system into Receivables. Auto Lockbox ensures that the receipts are accurate and valid before transferring them into Receivables. Auto Lockbox is a three step process:

1.      Submit Import:
During this step, Lockbox reads and formats the data from your bank file into Auto Lockbox tables using an SQL *Loader script.

2.      Submit Validation:
The validation program checks data in the Auto Lockbox tables for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables. At this point, you can optionally query your receipts in the QuickCash window and change how they will be applied before submitting the final step, Post QuickCash.

3.      Submit Post QuickCash:
This step applies the receipts and updates your customer's balances. See: Post QuickCash. 

These steps can be submitted individually or at the same time from the submit Lockbox Processing window. After you run Post QuickCash, Receivables treats the receipts like any other receipts; you can reverse and reapply them and apply any unapplied, unidentified, or on-account amounts.

Auto Lockbox Validation
Receivables validates the data you receive from the bank to ensure that the entire file was received, there are no duplicate receipts within a batch, and that customers and invoices are valid.

Auto Lockbox also validates all of your data for compatibility with Receivables. Auto Lockbox validates your data by ensuring that the columns in AR_PAYMENTS_INTERFACE reference the appropriate values and columns in Receivables.

Transmission Level Validation:
Auto Lockbox validates your lockbox transmission to ensure that transmission information corresponds to your transmission format. The following attributes are validated:

1.      Transmission format contains receipt records
2.      Lockbox number is part of the transmission format or you specify it when you submit Auto Lockbox from the Submit Lockbox window
3.      GL date is in an open accounting period
4.      Total transmission record count and amount that you supply must match the actual receipt count and amount that is determined by Auto Lockbox
5.      Origination number is valid if it is provided

Lockbox Level Validation:
Auto Lockbox validates your lockbox records to ensure that lockbox information corresponds to your transmission format. The following attributes are validated:
1.      Lockbox number is specified in either the Lockbox Header or the Lockbox Trailer, and is valid
2.      Lockbox batch count is correct if it is provided 
3.      Lockbox amount is correct if it is provided 
4.      Lockbox record count is correct if it is provided 
5.      Origination number is valid if it is provided
6.      No duplicate lockbox numbers

Batch Level Validation:
Auto Lockbox validates your batch records to ensure that batch information corresponds to your transmission format. The following attributes are validated:
Batch name exists on batch records
Batch name is unique within the transmission
Batch amount is correct
Batch record count is correct
Lockbox number exists on batch records if this number is part of the transmission format

Receipt Level Validation:
Auto Lockbox validates your receipt records to ensure that receipt information corresponds to your transmission format. The following attributes are validated:
Remittance amount is specified
Check number is specified
Item number is specified and is unique within a batch, a lockbox, or the transmission, depending on the transmission format
Lockbox number is specified (if this number is not part of the Lockbox Header or the Lockbox Trailer of the transmission format) and batches are not imported
Batch name is specified (if either Batch Headers or Batch Trailers are part of the transmission format)
Account number is specified (if Transit Routing Number is part of the transmission format)
Invoice1-8 are either valid or are left blank

Attention:
If you are using matching numbers and a receipt record indicates that multiple transactions will be paid by this receipt, Lockbox assumes that all of the transactions are the same type (e.g. invoices, sales orders, purchase orders, etc.). For example, if the first 2 transactions are invoices, Lockbox will successfully match them with this receipt. However, if the next transaction is not an invoice, Lockbox will either import the remaining receipt amount as unidentified or reject the entire receipt (depending your Lockbox definition).
Installment1-8 are either valid installment numbers or are left blank
Invoice, debit memo, credit memo, deposit, on-account credit, or chargeback number derived from the matching number does not belong to a guarantee or receipt
 Transaction number is entered where an application amount is specified
Sum of all of the Amount Applied columns for a receipt does not exceed the remittance amount
Customer number is valid (refer to Customer Validation below)
Customer number and MICR number both reference the same customer (if both are provided)
Receipt date is specified
Payment method is valid
Currency is valid (refer to Currency Validation below

Overflow Level Validation:
Auto Lockbox validates your overflow records to ensure that overflow information corresponds to your transmission format. The following attributes are validated:
Batch name is specified (if either Batch Headers or Batch Trailers are part of the transmission format)
Lockbox number is specified (if either the Batch Header or the Batch Trailer are not specified and the transmission format includes lockbox number)
Item number is specified and matches a receipt record
Overflow indicator is specified (unless it is the last overflow record)
Overflow sequence is specified
Invoice1-8 are valid invoice numbers (these numbers are optional, and can be left blank)

Attention:
If you are using matching numbers and a receipt record indicates that multiple transactions will be paid by this receipt, Lockbox assumes that all of the transactions are the same type (e.g. invoices, sales orders, purchase orders, etc.). For example, if the first 2 transactions are invoices, Lockbox will successfully match them with this receipt. However, if the next transaction is not an invoice, Lockbox will either import the remaining receipt amount as unidentified or reject the entire receipt (depending your Lockbox definition).
Installment1-8 are either valid installment numbers or are left blank
Transaction number derived is entered where an application amount is specified

Customer Validation:
Auto Lockbox can either validate your customer data based on the following attributes, or mark the receipt as ‘Unidentified' if no match is found:
Customer number is valid
MICR number is valid
Bill-To customer is from an Auto Associated invoice (if Auto Associate is enabled)

Currency Validation:
Receivables lets you process receipts in multiple currencies. If you pass the currency code, exchange rate type, and receipt date, Auto Lockbox will try to determine the exchange rate. If itis unable to determine the exchange rate, the receipt will fail validation. Receivables also supports cross currency deposits. This implies that receipts in your lockbox can be either in the same currency as that of the bank account, or in any other currency, provided the bank account is in your functional currency and its Multiple Currency Receipts field is set to Yes (Bank Accounts window, Receivables Options alternative region).

Running Auto Lockbox 
Run Auto Lockbox to submit your lockbox transmission processes and transfer payment information from your bank files into Receivables. Submit Auto Lockbox from the Submit Lockbox Processing window.

Use Auto Lockbox to import your invoice-related receipts. You must process non-invoice related receipts (such as investment income) through the Receipts window using a receipt type of 'Miscellaneous'. You can import, validate, and run Auto Lockbox all in one step, or perform the steps separately using the same window. For example, you can import data into Receivables and review it before validating it within Receivables. Upon examination and approval, you can submit the validation step and Receivables will automatically validate your data and create QuickCash receipt batches.

Caution:
When you receive your bank file, be sure to name the file and move it to the appropriate directory. You will need to specify the location of your bank file when you submit Auto Lockbox. If you receive daily files from your bank, be careful not to overwrite the files from the previous day.
Receivables uses SQL*Loader to load information from your bank files into Auto Lockbox tables. For SQL*Loader to load your bank file properly, each logical record that your bank sends to you must end with a carriage return; otherwise, SQL*Loader displays an error message when you initiate Auto Lockbox.

Attention:
If you are using the automatic receipts feature, Auto Lockbox ignores all transactions in this transmission that are selected for automatic receipt (i.e. transactions assigned to a payment method whose associated receipt class has Creation Method set to 'Automatic').

QuickCash lets you enter and apply receipts quickly by only requiring you to provide minimal information. QuickCash also provides an extra level of control for entering high volume receipts because it does not immediately affect your customer's account. When you enter receipts and applications in a QuickCash batch, Receivables stores them in an interim table. After reviewing a QuickCash batch for accuracy, you run Post QuickCash to update your customer's account balances.

QuickCash lets you apply your receipts to one or many transactions, use Auto Cash rules, place receipts on-account, or enter them as unidentified or unapplied. After your n Post QuickCash, Receivables treats the receipts like any other receipts; you can reverse and reapply them and apply any unapplied, unidentified, or on-account amounts. You must batch QuickCash receipts. For receipt batches you enter in the QuickCash window, Receivables does not update the status, applied, on account, unapplied, and unidentified fields until you save the batch. If you do not identify the customer for a receipt, Receivables automatically assigns the receipt a status of Unidentified.

Additional Information:
You cannot add miscellaneous receipts to a QuickCash batch

Post Quick Cash
When you enter receipts in the QuickCash window or import them using Auto Lockbox, Receivables stores them in interim tables. You can then use the QuickCash window to review each receipt and use the Applications window to ensure that the application information is correct. After you approve the receipts and their applications, run Post QuickCash to update your customer's account balances.

You can choose which QuickCash or Lockbox batches to review. For example, you may want to review only the receipts entered by your data entry clerks or the data files sent by your bank. The following diagram summarizes how Post QuickCash transfers receipts and applications from interim tables into Receivables.


Best Blogger TipsGet Flower Effect