Order Line Status Awaiting Fulfillment

In Oracle we can have a scenario where for a multi line order one or more lines of the Order are in status Awaiting Fulfillment.
This scenario occurs in case the order lines have been assigned a Fulfillment set.

Fulfillment set are defined as a group of lines to be fulfilled together.

There can be different cases for these :

Eg : 1 , Book a sales order with 2 lines
     Line 1 --> shippable Item
     Line 2 --> Service / Bill Only item

In this scenario let us assume that business has created a Sales Order for Laptop and Warranty sell. Laptop is a shippable item and Warranty is a Bill Only item.

CASE 1 --> Order Booked without Fulfillment Set

when the order is Booked , Line 1 will go to awaiting shipping and Line 2 will close since it is a BILL Only item.

Once Line 1 is shipped  , it will be closed and invoiced. In the current scenario we will have 2 AR Invoices generated for same sales order --> One for the Warranty and one for the shipped products.

From business perspective it does not make sense to Bill a customer for Warranty till the Laptop is shipped .

In such a scenario business will follow the below case :

CASE 1 --> Order Booked with Fulfillment Set

when the order is Booked , Line 1 will go to awaiting shipping and Line 2 will proceed to Awaiting Fulfillment stage .

As Order Line 1 is not shipped : Line 2 will not be fulfilled (and therefore invoiced) until the shippable line is fulfilled

Once Line 1 is shipped  , Line 1 and Line 2 will move to status Fulfilled and then be closed simultaneously and invoices. In the current scenario we will have 1 AR Invoice generated for sales order --> for Warranty and Shipped goods.

The same approach can be applied even for multi-line order with different shippable items in case the lines need to be closed and invoiced together.

Thus when lines are in Awaiting Fulfillment status check if Fulfillment set is used on order line

Hopefully this clarifies the doubt on status Awaiting fulfillment and how it can be resolved , also providing details of usage of fulfillment set.

AR Invoice Override and Default Payment Term

When Creating an AR Invoice or Debit Memo , once the user enters the BILL TO Location of the invoice the payment term will be defaulted as per the setup on the Customer BILL TO or the Payment term maintained on the Profile maintained on the customer BILL TO.

Based on the needs business might want to update the defaulted value. In this scenario click on LOV and select the new Value.

The display of entries on the LOV depends on the setup on the customer profile file : Allow Override Terms

If the check box is un-checked then the LOV will be empty and Payment terms cannot be over-ridden and the LOV will be empty. If the check box is checked Oracle will populate the LOV with the eligible payment terms and user can select any

Delete an Unposted Journal

Sometimes when doing a Journal entry or an upload via Web ADI , as a result of Human Error or unexpected system behaviour
but if the Journals have not been posted we always have the option of Deleting the Journals from the system.

Below steps should be followed
1. Navigate to the GL Responsibility

Navigate to Journals   > Enter

Query the Journal by entering the Journal and Journal batch name :

Review the Journal being fetched and click on Delete and SAVE button as mentioned below

AR Receipt application - table changes

When a cash receipt is created , sooner or later the Cash receipt will be applied to an open AR transaction in the system.

In this post we will discuss the entries in the table  and how are the records affected during this process.

1. Receipt Creation : When cash receipt is created entry will be made in the system in the tables
   AR_CASH_RECEIPTS_ALL --> storing details of the Cash receipt
   AR_CASH_RECEIPT_HISTORY_AL --> Detail of the changes made to the receipt which need                tracking Eg : Creation , reversal etc
   AR_RECEIVABLE_APPLICATIONS_ALL --> Detail of the RECEIPT creation record with STATUS = UNAPP , Amount_applied as the Receipt amount

2. Receipt Application : When the Cashe Receipt is applied to an AR transaction the open amount of         the Receipt and the Ar invoice will decrease. Entry is made into the table AR_RECEIVABLE_APPLICATIONS_ALL with 2 records .one with Status = UNAPP and a negative amount of the amount applied debiting the acutal account affected when the receipt was created , since the Open balance in the account decreases.
Another entry is made with status = APP , Amount_applied is the actual amount applied to the receipt and the column applied_customer_trx_id , applied_payment_schedule_id all get populated with the details of the AR Invoice which has been impacted.  In the relevant columns the split of LINE and TAX amount is also visible.


Eg : Receipt crearted for amount : 100

entry is made in table AR_RECEIVABLE_APPLICATIONS_ALL with amount : 100 , STAUS = UNAPP
60 is applied to a transaction

2 entries are made in the table : AR_RECEIVABLE_APPLICATIONS_ALL

1. Amount : -60 , status = UNAPP debiting the actual account
2. Amount : 60 , status = APP , Crediting the receivale Account and also storing the details of the affected invoice in the columns applied_customer_trx_id , applied_payment_schedule_id.

Depending on the Receipt method used and the Receipt method Setup  , If the receipt is fully applied the status will be remitted or cleared

If Receipt is using a receipt method with Setup : By Matching and receipt is fully applied the status will be remitted . Once the receipt is matched with Bank statement the status will be cleared.

Credit Memo TAX Line for Applied Credit Memo

Oracle has provide the facility of Creating Credit memo with Invoice line Application Details i.e. when a Credit Note is imported into Oracle we can import it with the application details of an invoice or a Debit Memo.

The Application works for both LINE and TAX records but the way Oracle handles the Credit Memo application for TAX and LINE type records is different

For LINE type record AutoInvoice allows over application import even though the actual line amount is less that the CM line amount.

but for TAX line such case doesnt worl and the maximum amount permissible is the TAX line amount of the applied invoice

EG :

You have an invoice and a CN like below :

Now when we import the Credit Note into Oracle we will expect Oracle to import the Credit Note like the above scenario with Credit Lines having the amounts as expected with corresponsiding applications to invoice,

But when the import is done the credit Note gets Imported as below :

If you observe the TAX line 2 is imported with amount 75 and not 80 , but LINE 2 is imported with amount 180 as expected.

In this Case when Credit Note is imported LINE 1 will be applied to LINE 1 of invoice and will have 0 Open amount , Same for TAX line 1

For LINE 2 the amount 150 will be applied to Invoice LINE 2 and 30 will be open. For TAX line 2 the invoice will be imported with Amount 75 , same as the Invoice TAX line amount and the same will be fully applied 

This is how Autoinvoice is designed to work for Credit Note Applied TAX line import.

If you are faced with such a scenario to avoid in future you can follow the below approach :

1. Create On Account Credit Memo and perform the application manually in Oracle outside Auto Invoice import Process

SQL to check Order Line Pricing

When we do a pricing of a Sales Order  on a bulk level we may need to check the Re-Pricing Status to see if all the lines have been re-priced successfully or not.

In order to see the if the line pricing is successful or not we can use the below SQL

    select distinct ooh.org_id,ool.creation_date,ool.line_id,ooh.order_number,ool.line_number,ool.ordered_item,ool.unit_selling_price,qlh.name,qll.operand,qll.start_date_active,ool.flow_status_code
 ,ool.calculate_price_flag,ool.unit_list_price
 from oe_order_headers_all ooh,
      oe_order_lines_all ool,
      wsh_delivery_details wdd,
      qp_list_headers qlh,
       qp_list_lines qll,
       qp_pricing_attributes qpa,
       mtl_system_items_b msib
 where ooh.header_id = ool.header_id
 and ool.line_id = wdd.source_line_id
 and wdd.released_status in ('R','B')
 and qlh.list_header_id = qpa.list_header_id
 and qpa.product_attr_value = to_char(msib.inventory_item_id)
 and msib.organization_id = :warehouse_id
 and msib.segment1 = ool.ordered_item
 and qpa.list_line_id = qll.list_line_id
 and ool.price_list_id = qlh.list_header_id
and ooh.order_number = :order_number
 and ool.unit_selling_price <>qll.operand
;

For the rows returned by the SQL we need to check the value of calculate_price_flag.
If Calculate_price_flag = N , if we see on front end the value will be Freeze which means that the item re-pricing will not be allowed,

If there are some records where calculate_price_flag = Y and no re-pricing is done please check if any manual modifier is applied on order line, If any modifier is applied overrding the Price List Price then re-pricing will not take place

Journal Line Bank Statement Reconciliation

Many times business wants to reconcile a Journal with a bank statement line but it might happen that the Journal Line is not available in the list of Reconcilable lines  , Below are some of the simple checks you can perform to find the reason why the Journal is not available

• Is the Journal Line Posted
• Is the Journal line account same as the Cash Account on the bank account setup
• If you have entered any search criteria in the Find screen which might not be matching the Journal you are expecting to reconcile
• The Journal has not been already reconciled with a bank statement . In Order to check if a Journal line is reconciled against a bank statement  , use the below SQL

          SELECT Count(1)

              FROM ce_statement_lines sl, 

                          ce_statement_reconcils_all sr, 

                          gl_je_headers jh, 

                          gl_je_lines jl ,

                          ce_statement_lines sh

            WHERE 1=1

                 AND sr.statement_line_id=sl.statement_line_id

                 AND sr.je_header_id=jh.je_header_id

                 AND jh.je_header_id=jl.je_header_id

                 AND sr.reference_id=jl.je_line_num

                AND sr.reference_type='JE_LINE'

                AND sl.statement_header_id = sh.statement_header_id

                AND gjh.name = :journal_name

Hope this Helps

Thanks

Order Line Pricing Issue , Line Not Getting Priced using a Price List

Many times when Trying to create a Sales Order we enter an item on the Order Line but the Pricing of the line does not happen  , and in some cases when selecting the item on the  Order line for a specific Price List we get and error that " Item XXXX and UOM EA not available on the price list"

In such scenarios we have to check the Setup of the concerned price list to determine the root cause of the issue :

1. Go to the Price List Setup and see if the Item is actually Available on the price list or not by entering the Price List name and the Item details

If the item is not available on the Price list then maintain the item on the price list and re-try Order Line Creation
2. If the Item is available on the Price List , then scroll right on the Item Setup line and check the         Start Date and End Date on the price list line. Ensure that the line is not end dated and the start Date if available is less than the Order Pricing Date

If there is an issue with the pricing dates , correct it and re-try booking the order

3. If the price list Line setup is correct then check for the Qualifier Setup on the Price List by clicking on the Qualifier Tab

   

The Qualifiers are used to specify the conditions which Govern if this price list should be Selected or not for the Order Lines pricing

An important thing to know here is the Grouping number

Records of the Grouping number will be ANDed with each other and records of different Grouping  Number will follow an OR condition

An important thing to note here is about Grouping Number : -1 , a grouping number of -1 will always be included in the condition formation and will be ANDed with the conditions of other Grouping numbers

Many a times the Price List Qualifiers fail and give an issue in Order Line Pricing

Tables storing Price list information :

QP_LIST_HEADERS --> Price List Header information

QP_LIST_LINES --> Price details

QP_PRICING_ATRRIBUTES --> Item details

Below SQL can be used to check item availability on a price-list

select qlh.name,msib.segment1,qll.operand

  from apps.qp_list_headers qlh,

           apps.qp_list_lines qll,

           apps.qp_pricing_attributes qpa,

           apps.mtl_system_items_b msib

 where qlh.name = 'PRICE_LIST_NAME'

 and qlh.list_header_id = qpa.list_header_id

 and qpa.product_attr_value = msib.inventory_item_id

 and msib.organization_id = :WAREHOUSE_ID

 and msib.segment1 = 'ITEM_NUMBER'

 and qpa.list_line_id = qll.list_line_id;

Descriptive flexfields SQL to get DFF Details

Descriptive flex fields have always been used on Standard Oracle forms to provide the business additional fields for information representation.

From the front end information of the DFF can be seen from the Application Developer Responsibility

Flexfields --> Descriptive --> Register

For a technical developer if needed the DFF information can be checked from the Data base too via an SQL.

The below SQL gives the DFF details defined for AR Invoices :
Line transaction flexfield

select fdf.application_id,fdf.APPLICATION_TABLE_NAME,fdf.TITLE,fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE,fdfc.DESCRIPTION--fdfc.*
       ,col.APPLICATION_COLUMN_NAME,col.end_user_column_name,col.ENABLED_FLAG,col.FLEX_VALUE_SET_ID
from FND_DESCRIPTIVE_FLEXS_VL fdf,
     FND_DESCR_FLEX_CONTEXTS_VL fdfc ,
     FND_DESCR_FLEX_COL_USAGE_VL col
where fdfc.DESCRIPTIVE_FLEXFIELD_NAME =fdf.DESCRIPTIVE_FLEXFIELD_NAME
and upper(fdf.TITLE) like 'LINE TRANSACTION FLEXFIELD%'
and fdf.application_id = col.application_id
and col.DESCRIPTIVE_FLEXFIELD_NAME =fdf.DESCRIPTIVE_FLEXFIELD_NAME
and fdfc.descriptive_flex_context_code = col.descriptive_flex_context_code ;

Changing the Title we can get details of Order flexfield Structures too

AR Recipts to GL SQL including XLA

Many times we might need to get data for AR Receipts and link them to GL via the XLA

Below SQL can be used to get that information and also get details of Receipt applications too

select ard.source_id,gjh.period_name,gjh.name journal,gjh.JE_CATEGORY,gjh.JE_SOURCE,gcc.concatenated_segments--xdl.*

          ,gcc.SEGMENT2 sbu,gcc.segment5 natural_account,gcc.segment6 sub_account,gcc.SEGMENT8 region,

          gjl.accounted_dr Debit_amount,gjl.accounted_cr credit_amount

          ,xdl.SOURCE_DISTRIBUTION_TYPE,xal.ACCOUNTING_CLASS_CODE,

          ard.SOURCE_ID, ard.SOURCE_TABLE, ard.SOURCE_TYPE,

     gjl.DESCRIPTION,

     (select party_name 

        from apps.hz_cust_accounts hca,

             apps.hz_parties hp

       where hca.cust_account_id = acr.PAY_FROM_CUSTOMER      

         and hca.party_id = hp.party_id) customer_name,

        gjh.POSTED_DATE     ,

        null trx_number,

        arm.name receipt_method,

        null transaction_type,

        null purchase_order

from apps.gl_je_headers gjh,

     apps.gl_je_lines gjl,

     apps.xla_ae_lines xal,

     apps.xla_distribution_links xdl,

     apps.xla_ae_headers xah,

     apps.gl_code_combinations_kfv gcc,

     apps.AR_DISTRIBUTIONS_ALL ard,

     apps.AR_CASH_RECEIPT_HISTORY_ALL acrh,

     apps.ar_cash_receipts_all acr,

     apps.ar_receipt_methods arm

where 1=1

AND gjl.code_combination_id = gcc.code_combination_id

and gjh.ledger_id = gjl.ledger_id

and gjh.je_header_id = gjl.je_header_id

and gjl.gl_sl_link_id = xal.gl_sl_link_id

and xal.ae_header_id = xdl.ae_header_id

and xal.ae_line_num = xdl.ae_line_num

and xdl.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'

and xah.ae_header_id = xal.ae_header_id

and  ARD.LINE_ID = XDL.source_distribution_id_num_1

--AND ard.source_type = 'CRH'

and ard.SOURCE_ID  = acrh.CASH_RECEIPT_HISTORY_ID

and acr.cash_receipt_id = acrh.cash_receipt_id

and acr.RECEIPT_METHOD_ID = arm.RECEIPT_METHOD_ID(+)

UNION

select ard.source_id,gjh.period_name,gjh.name journal,gjh.JE_CATEGORY,gjh.JE_SOURCE,gcc.concatenated_segments--xdl.*

          ,gcc.SEGMENT2 sbu,gcc.segment5 natural_account,gcc.segment6 sub_account,gcc.SEGMENT8 region,

          gjl.accounted_dr Debit_amount,gjl.accounted_cr credit_amount

          ,xdl.SOURCE_DISTRIBUTION_TYPE,xal.ACCOUNTING_CLASS_CODE,

          ard.SOURCE_ID, ard.SOURCE_TABLE, ard.SOURCE_TYPE,

     gjl.DESCRIPTION,

     (select party_name 

        from apps.hz_cust_accounts hca,

             apps.hz_parties hp

       where hca.cust_account_id = acr.PAY_FROM_CUSTOMER      

         and hca.party_id = hp.party_id) customer_name,

        gjh.POSTED_DATE     ,

        rcta.trx_number,

        arm.name receipt_method,

        (select name from apps.ra_cust_trx_types_all where cust_trx_type_id = rcta.cust_trx_type_id) transaction_type,

        rcta.purchase_order

from apps.gl_je_headers gjh,

     apps.gl_je_lines gjl,

     apps.xla_ae_lines xal,

     apps.xla_distribution_links xdl,

     apps.xla_ae_headers xah,

     apps.gl_code_combinations_kfv gcc

     ,apps.AR_DISTRIBUTIONS_ALL ard,

     AR_RECEIVABLE_APPLICATIONS_ALL ara,

     apps.ar_cash_receipts_all acr,

     apps.ra_customer_trx_all rcta,

     apps.ar_receipt_methods arm

where 1=1

AND gjl.code_combination_id = gcc.code_combination_id

and gjh.ledger_id = gjl.ledger_id

and gjh.je_header_id = gjl.je_header_id

and gjl.gl_sl_link_id = xal.gl_sl_link_id

and xal.ae_header_id = xdl.ae_header_id

and xal.ae_line_num = xdl.ae_line_num

and xdl.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'

and xah.ae_header_id = xal.ae_header_id

and  ARD.LINE_ID = XDL.source_distribution_id_num_1

--AND ard.source_type = 'RA'

and ard.SOURCE_ID  = ara.RECEIVABLE_APPLICATION_ID

and ara.CASH_RECEIPT_ID = acr.CASH_RECEIPT_ID

and ara.APPLIED_CUSTOMER_TRX_ID = rcta.customer_trx_id(+)

and acr.RECEIPT_METHOD_ID = arm.RECEIPT_METHOD_ID(+)

Order Line Not Invoiced

When a Sales Order is Created It may happen that you expect to see and invoice for the Order once the lines are closed but Invoices dont appear there can be a number of reasons for it :

1. The item on the Order lines is not enabled for invoicing , check the Invoice Enabled Flag on the Shipping Warehouse , IVO and Item Master
2. The order is an Internal Sales Order , these orders are not invoiced
3.Configuration items are not invoiceable
4. If line flow does not invluce the Invoice Interface Activity then the invoice invoice_interface_status_code will not be set and remain null

One of the above scenario should be valid and your issue should get resolved 

Journal Batch Posting Error

When Posting a Journal Batch you might encounter an Error where the Posting program fails .
In such cases first thing is to check the OUTPUT and LOG file for potential messages displayed by Oracle.

One of the errors which can occur is : ORA-00001: unique constraint (GL.GL_JE_HEADERS_U2) violated

If you see the table gl_je_headers there is an unique index on Batch Id and Journal Name.

For the same batch you will see that the Journals have a unique name but when doing posting Oracle checks for the First 78 characters of the Journal Name and Batch Id Uniquenss , if the condition is violated we will get the above error.

To resolve the error open the Journals in the batch update the names by adding Unique numbers at the beinging , save your work and Post again

Issue will be resolved.

Multiple Organization Access Control (MOAC)

One of the biggest changes available to users in migrating from 11i to R12 is Oracle MOAC Structure,

In 11i in a given Legal entity is a Business had 2 Operating units A and B , then if a User wishes to see the information attached to Operating unit A and the he needs to use the responsibility associated to the Operating unit A , and similar is the case for B

In R12 the scenario is different , Oracle has made available the feature of Security Profiles based on which the user from one responsibility only can have access to data across Multiple Operating units in a given legal entity.

thus in the above Scenario the user can access the data of both the operating units using the same responsibility and will not need to change the responsibility for every operation,

This feature is enabled by the use of Security profiles which we will discuss soon