Receiving in Oracle, receipt routing method

When a Purchase Order , Internal Requisition or Return Order is created in the system once the records are approved/processed and eligible for receiving then business can perform the receiving of the Transaction in Oracle from the Receiving Screen,

Depending on the System setup and the Receipt routing method on the transaction the receving of the record ca be performed in one of the below ways :
1. Direct Receiving
2. Standard Receiving
3. Inspected Receiving

Direct Receipt :
Direct Receipt or direct receiving is a form of receiving which has a single step of receiving. Once the goods a arrive at the warehouse the the business will perform a receiving in which the goods will directly be transferred to the destination sub-inventory and warehouse .this is a single point receving
, For such records we have 2 records in the are created in the table rcv_transactions with type as  : RECEIVE and DELIVER by a single act of receiving

Standard Receipt : 
This is 2 way/step receiving process . when the goods are received at the warehouse gate a Receiving will be performed at the warehouse gate. In this step an entry is made in the table rcv_transactions with the type : RECEIVE , Once the receipt of goods is completed here , the goods will be received in the destination sub-inventory then the goods are received once more in the system. In this stage the goods are moved to the sub-inventory in the system and the receving is completed, An entry with TYPE : DELIVER is created in the system

Inspected Receiving

This is 3 way/step receiving process . when the goods are received at the warehouse gate a Receiving will be performed at the warehouse gate. In this step an entry is made in the table rcv_transactions with the type : RECEIVE , Once the receipt of goods is completed here , the goods will be inspected and depending on that the goods either pass inspection or are rejected . Accordingly the an entry is made in the table with type : INSPECTED/REJECTED.
If the goods are Inspected then the goods can be received in the warehouse sub-inventory. the goods will be received in the destination sub-inventory then the goods are received once more in the system. In this stage the goods are moved to the sub-inventory in the system and the receving is completed, An entry with TYPE : DELIVER is created in the system
For successful receipt 3 entries are made in rcv transactions table with type : RECEIVE,INSPECTED and DELIVER
For failed Records we have 2 entires : RECEIVE and REJECTED
 

Released_status in wsh_delivery_details (wsh_delivery_details.released_status)

When a Sales Order line is booked , for Shippable item lines have an entry in the table wsh_delivery_Details. The column released status has status values which provide information of the Sales Order shipment status

B: Backordered- Line failed to be allocated in Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged- Line has been picked and staged by Inventory 

Detailed Explanation : 

B : Backordered : when we try to pick release a Sales Order but On-hand is not available for the order line , in such scenarios the order line is back ordered and the released status  = 'B'

C: Shipped :Once the Order line is shipped the associated delivery details Released Status is changed to 'C' .OM interface and Inventory interface have processed and the trip is closed.

D: Cancelled : When the Order line is cancelled the released status = 'D'

N: Not Ready for Release -Line is not ready to be released . this can happen when the information is available in wsh_delivery_details but order line has not reached the Awaiting Shipping status , might be due to some workflow customization's

R: Ready to Release: The Order line has been booked and the line is ready to be pick released . Line status is Awaiting Shipping under such scenarios

S: Released to Warehouse : Depending on the Pick release process in the business it moght be that Pick release is performed with Auto Pick confirm Set to No.

In such scenarios when we do pick release the Move Order will be created

Move Order allocation is done , but since Auto Pick Confirm is set to No the Mover Order will not be transacted.

The Delivery Detail will be in state : Pick Wave

X: Not Applicable- Line is not applicable for Pick Release , for the non-shippable item lines eg : Warranty orders

Y: Staged- Line has been picked and staged by Inventory .In this Case the line has been pick released. the Move Order Creation,Allocation and transaction is completed . Goods have be transferred from the Source Sub-invenotry to Staging area of the warehouse and the line is ready to be shipped

AR Invoice Dispute amount Update

In an AR Invoice we can update the Dispute Amount Information from a number of places :

1. More TAB on the AR Invoice Screen
2. Action --> Installments  from here we can update the Dispute amount and Dispute date details
3. Account Details Screen

From either of the 3 places when Dispute information is updated the information is reflected in the table ar_payment_schedules_all

However when the information is updated from the MORE TAB on the AR Invoice Screen in such a case the timestamp information od ar_payment_schedules_all is NOT Updated . 

In scenarios where we have a data warehouse which is based on the Timestamp update of WHO columns in such scenarios we need to ensure that Option 1 is not followed for updates and business follows Option 2/3.

Prevent Option 1 by using personalization or changes to Custom..pll

Item Categories -Category Set and Assignment

When a business maintains an inventory an essential requirement is the assignment of the items to specific categories

To understand this we need to have information of what these terminologies exactly represent

We will try to present here a brief overview of the item categories

Category Set : A category set can be said to a template or a schema on the basis of which we want to categorize items ,
Eg : A business may be involved in manufacturing of good which it wants to categorize on the basis of the countries where it wants to sell a product .In such a scenario the business will define a Category : SELL_COUNTRY and then assign items to this category set specifying to which Country the item is sold.

in order to perform this we need to perform some Steps :

1. Define Flex filed Structures for Item Category Sets : i.e,. If the business wishes to categrize items on the basis of SELL_COUNTRY , they need to create a KFF with specifying the details of the segments the combination of which will specify a valid value
2. Define Categories : Once the Flex-field  is defined we need to navigate to the item categories window and specify the values of categories which can be assigned under this category set
3. Once Item categories have been set up we need to navigate to the Category set window and create the Category set with details of the item categories
4. Once the setup is done the individual Items can be assigned to this categories

If we try to portray this scenario in the above sense take the example as below

Business decides to categorize items on the basis of the Regions :

1. Create a Flexfield with say 3 Segments : Continent - Country - State/Region
2. Specify the values for this Flexfield by Defining the Values Eg : Asia - India-Maharashtra 
3. Create a Category Set SELL_COUNTRY and then specify these values , default  values 
4. Assign the items to a valid category

Once this is done business can build reports to identify which goods are available for selling in Particular regions

One of the main uses of this is Item Assignment.

In a business we will have a number of warehouses , Depending on the Item categories we can run the warehouse assignment for a specific set of Category values thus assigning the items to Warehouses

In the above Example if a business has Multiple warehouses in India for which are under a hierarchy Eg : India-Mahrashtra

In this now if there are many items which need to be available to this warehouses , Item Assignment can be run for this hierarchy, Item Category Sell_COUNTRY with Item category as : Asia-India - Maharashtra. Thus all the items under this category will be picked and then assigned to the warehouses in the hierarchy

Going by business operations it is not possible to perform this activity manually for all items, such a categorization makes the business work efficiently and free from manual errors

API to Create External Bank Branch

When performing Master Data Migration for Supplier or Customer business also needs the migration of Associated Banks and Bank Branches .

In order to perform this we need to migrate the Banks , Bank Branches and associated Bank Branches

In R12 to create an external bank branch Oracle has given an API :

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

API Signature :

create_ext_bank_branch (
   p_api_version                IN   NUMBER,
   p_init_msg_list              IN   VARCHAR2,
   p_ext_bank_branch_rec        IN   ExtBankBranch_rec_type,
   x_branch_id                  OUT  NOCOPY  NUMBER,
   x_return_status              OUT  NOCOPY  VARCHAR2,
   x_msg_count                  OUT  NOCOPY  NUMBER,
   x_msg_data                   OUT  NOCOPY  VARCHAR2,
   x_response                   OUT  NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  )

p_ext_bank_branch_rec         Bank branch record type
x_branch_id                     Id of the branch created
x_return_status                Return status of API

Sample code :

                         IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
                            p_api_version           => 1.0,
                            p_init_msg_list         => FND_API.G_TRUE,
                            p_ext_bank_branch_rec   => l_bank_branch_rec,
                            x_branch_id             => l_num_branch_id,
                            x_return_status         => l_chr_return_status,
                            x_msg_count             => l_num_count,
                            x_msg_data              => l_msg_data,
                            x_response              => x_response_rec);


                         writelog (
                            'l_chr_return_status in bank branch '
                            || l_chr_return_status);
                         writelog ('Branch Id: ' || l_num_branch_id || SQLERRM);

                         --Check for Sucess API
                         IF (l_chr_return_status <> 'S')
                         THEN
                            FOR i IN 1 .. l_num_count
                            LOOP
                               fnd_msg_pub.get (
                                  p_msg_index       => -1,
                                  p_encoded         => 'F',
                                  p_data            => l_msg_data,
                                  p_msg_index_out   => l_msg_index_out);

                               writelog (
                                  'Error in Bank Branch Creation  ' || l_msg_data);
                               l_chr_val_ret_msg :=
                                  l_chr_val_ret_msg || '  ' || l_msg_data;
                               RAISE e_error;
                            END LOOP;
                         ELSE
                            writelog ('  Branch created successfully. ');
                         END IF;

Parameters to pass :

Bank Id
branch name
branch number
branch type
Swift code
alt_branch_name

Note : Branch can only be created if a bank exists make sure to use create bank before calling this API

A post for that too is available in the blog

Once the record is created information can be found in the view : iby_ext_bank_branches_v

API to Create External Bank

When performing Master Data Migration for Supplier or Customer business also needs the migration of Associated Banks and Bank Branches .

In order to perform this we need to migrate the Banks , Bank Branches and associated Bank Branches

In R12 to create an external bank Oracle has given an API :

IBY_EXT_BANKACCT_PUB.create_ext_bank

API signature :

PROCEDURE create_ext_bank (
        p_api_version              IN  NUMBER,
p_init_msg_list            IN  VARCHAR2,
p_ext_bank_rec             IN  ExtBank_rec_type,
x_bank_id                  OUT NOCOPY NUMBER,
x_return_status            OUT NOCOPY VARCHAR2,
x_msg_count                OUT NOCOPY NUMBER,
x_msg_data                 OUT NOCOPY VARCHAR2,
x_response                 OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  )

p_ext_bank_rec   is the record type variable with the infromation of the bank to be created in the system

x_bank_id                  is the id of the bank to be created
x_return_status           return status

Sample Code :

IBY_EXT_BANKACCT_PUB.create_ext_bank (
                            p_api_version     => 1.0,
                            p_init_msg_list   => FND_API.G_TRUE,
                            p_ext_bank_rec    => l_bank_rec,
                            x_bank_id         => l_num_bank_id,
                            x_return_status   => l_chr_return_status,
                            x_msg_count       => l_num_count,
                            x_msg_data        => l_msg_data,
                            x_response        => x_response_rec);

                         IF (l_chr_return_status <> 'S')
                         THEN
                            FOR i IN 1 .. l_num_count
                            LOOP
                               fnd_msg_pub.get (
                                  p_msg_index       => -1,
                                  p_encoded         => 'F',
                                  p_data            => l_msg_data,
                                  p_msg_index_out   => l_msg_index_out);
                               fnd_file.put_line (
                                  fnd_file.LOG,
                                  'Error in Bank  Creation  ' || l_msg_data);
                               l_chr_val_ret_msg :=
                                  l_chr_val_ret_msg || '  ' || l_msg_data;
                               RAISE e_error;
                            END LOOP;
                         ELSE
                            fnd_file.put_line (fnd_file.LOG,
                                               'Bank Created Sucessfully  ');
                         END IF;

Mandatory Parameters :
Bank Name
Bank Country
Bank Number
Currency Code

Combination of these is an Unique combination and API gives an error if we try to create duplicate records

Once the bank is created information can be searched in the view iby_ext_banks_v

IR-ISO cycle Techno Functional Overview

One of the cycles being setup and used in the business is the Internal Requisition and Internal Sales Order Cycle commonly known as IR-ISO Cycle.

The complete setup of the system needs to be done when doing the IR-ISO cycle . below we are highlighting some of the basic Setups to be done for an IR-ISO cycle, then we will give a brief overview of the Steps occurring in the IR-ISO Sycle
1. Item Setup : The items we wish to be ordered for IR-ISO need to have the Internal Order and Internal Order Enabled Flag checked for the Transaction , Item Validation and Item Master Warehouse
2.Shipping Networks need to be setup  between the Shipping and the Receiving Warehouse

3. The customer Setup should be completed : The customer for whom the internal Order is created the SHIP TO site must be setup with the Internal Location and Internal Warehouse of of the Shipping Warehouse

4. Purchasing Option Setup need to be updated with the Internal Order Type

Once the Setup is completed  we can perform the IR-ISO cycle

1. Create the Internal Requisition and save It
      > Entry is made in the table po_requistion_headers and po_requisition_lines table , Current Status          of the the Requisition is Incomplete
2. Approve the Requisition
     > The Requisition is now Approved
3. Run the Standard Oracle Program "Create Internal Order"
     > This program creates the Sales Order from the Internal Requisition , the program picks up the             information of the Requisition and Populates the information in the Order in the Order Interface         table oe_headers_iface and oe_lines_iface
4. Run the Program "Order Import"
    > This Program will complete the Order Import and Sales Order will be available in Oracle
5. Query the Sales Order using requisition information and Complete the Order cycle
   > From back end the column : source_document_line_id of oe_order_lines table will be have the po_requisition line id
6. Once the Order is picked and Ship the Internal Requisition is now eligible for receiving
7. Perform the Requisition receiving
   > Information comes in the Table : rcv_shipment_headers , rcv_shipment_lines, rcv_transactions

This is the total flow of information in IR-ISO and overview of the basic tables affected

Item Validation During Order Import

During Order Import many of us face Errors related to Item Validation on the Sales Order Line. This leads to  checks on the item master Setup. Listing down below some of the basic checks that should be done once any Item Related validation error is reported

1. Is the item assigned to the Warehouse specified on order line / the Warehouse defaulting via the defaulting rules
2. Is the Item assigned to the Item Validation Org , Item Master Org 
3. In case the Shipping OU and Selling OU are different the item should be assigned to the IVO of both the Operating Units
4. Order Enabled Flag , Customer Order Enabled Flag . this flag needs to setup for all the warehouses mentioned in Step 1,2,3
5. Item status needs to be checked for all the warehouses for steps 1,2,3

If the issue still persists check for all other item attributes which might be Impacting . For internal Sales Orders the Internal Order Enabled Flag needs to be set

Order Import Error : ORA-01403: no data found in Package OE_Holds_PUB Procedure evaluate_holds_post_write

When performing Order Import via the Standard oracle Order Import Process or Entering Orders manually from the Sales Order Screen we may get the Error Message ORA-01403: no data found in Package OE_Holds_PUB Procedure evaluate_holds_post_write .
This error may be because of the Actual reasons behind this issue , but one of the many scenarios when this order is raised by Oracle is when Defaulting rules fail and Oracle is not able to Default the value in the required fields which need to be maintained for Order Creation eg : Warehouse.

If you receive such and error please check the defaulting rules for these fields and try to find out which one of these rules is failing . Once corrected this error disappears in most of the scenarions faced.

BILL Only order , Item Flag Shippable

When Creating sales orders , we have many instances where we need to create a BILL Only Sales Order . There are situations when the company is creating an Order for invoicing but there will not be any shipments for this order ,
Eg : If you buy a Warranty for a product in this case there is not physical shipment needed
        If you have requested for a service of any product , in this case you are requesting the visit of a           technician to come and service , no physical shipments might be involved

In such scenarios when the order line is created it is in Entered Status
On booking the Order the line status changes to BOOKED Please make not that line does not go into Awaiting Shipping status
Once workflow background process runs the Order line is Closed

when entering the sales order for a BILL Only line , the item selected on the Sales order also needs to be setup in a particular way.
The SHIPPABLE ,flag on the Item needs to be unchecked , this needs to be ensured for the Item Master Organization , Item Validation Organization for this operating unit and also the warehouse selected on the order.

If this setup is not correct at all the levels the line will go into Awaiting Shipping status and you will not see the expected behaviour.

Customer Interface Error C5

Customer Interface Errors Out the data with the Error Code : C5

C5 --> Customer reference has two different customer class codes defined

This error occurs when we have multiple records for the same customer but different values of Customer Class code , there can be different scenarios for this error

• Creating a Site for Existing Customer : When we are creating a new site for an existing customer then the Customer Class code value for the data in the interface needs to be same as the Customer existing in Oracle , if there is any mismatch in the Customer Class code then Customer Interface will error out the record
• Creating a new customer with multiple sites  : In this case too all the records need to have the same Customer Class code , if the values mismatch then 1 record will be progressed to create the customer and others will error with this code

Update the data in Customer interface with the correct data and re-process the records

AutoInvoice Grouping Rules

When Data is populated into the AR interface and we run the Auto Invoice import Program , Oracle will process the data in the interface and Create invoices for them .

Eg : If we have 10 records in the Ar interface table and we know that all these records belong to 1 invoice then Oracle then when the AutoInvoice import program runs it should group these 10 lines to a Single invoice header . this grouping is done by Oracle on the basis of the Grouping Rules.

Grouping rules specify which columns of the AR Interface (ra_interface_lines_all) when grouped together will form 1 set of records , this Global set will become the invoice header and the individual lines will become the invoice lines.

Oracle has specified a set of Columns in Oracle R12 as Mandatory Grouping columns i.e. grouping will happen based on these columns and in addition users can specify additional Columns by creating different grouping rules and attaching it to the invoice batch source.

Below is the of columns that Oracle specifies as mandatory grouping columns

AGREEMENT_ID
APPLICATION_ID
BILLING_DATE
COMMENTS
CONS_BILLING_NUMBER
CONTRACT_ID
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DEFAULT_TAXATION_COUNTRY
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
DOCUMENT_SUB_TYPE
GL_DATE
HEADER_ATTRIBUTE1-15
HEADER_ATTRIBUTE_CATEGORY
HEADER_GDF_ATTRIBUTE1-30
HEADER_GDF_ATTR_CATEGORY
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
LEGAL_ENTITY_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
PAYMENT_ATTRIBUTES
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SET_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TAXED_UPSTREAM_FLAG
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER

when running Autoinvoice oracle will perform a grouping based on these columns to determine how many invoices need to be created , In addition if any custom Grouping Rules are specified those columns will also be included.

Many a times you might be facing the issue that you expected the lines to create 1 single invoice but multiple invoices got created , it is because of the Grouping rules and whenever you face such an issue check the Grouping Rules.

A common issue faced is the Error : Duplicate Invoice number , If you are supplying Invoice number in the AR interface and have not enabled Duplicate transactions Numbers in the setup then in case the grouping rules of an invoice face Auto Invoice will give the error Duplicate Invoice Number.

Make sure to check the grouping rules thoroughly

SQL to Fetch Profile Options

SELECT po.user_profile_option_name,
po.profile_option_name "NAME" ,
DECODE (TO_CHAR (pov.level_id), '10001', 'SITE' , '10002', 'APP', '10003', 'RESP', '10004', 'USER', '???') "LEV",
DECODE (TO_CHAR (pov.level_id) , '10001', '', '10002', app.application_short_name , '10003', rsp.responsibility_key, '10004', usr.user_name, '???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM apps.fnd_profile_options_vl po,
apps.fnd_profile_option_values pov,
apps.fnd_user usr,
apps.fnd_application app,
apps.fnd_responsibility rsp
WHERE 1=1
--AND (po.profile_option_name = 'ICX_NUMERIC_CHARACTERS')
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
ORDER BY "NAME", pov.level_id, "VALUE"

The above SQL can be used to get the profile option values at Different levels in the system

SQL for Material Transaction/Inventory to General Ledger cia SLA Journal check

select gjh.name,xah.EVENT_TYPE_CODE,xdl.SOURCE_DISTRIBUTION_TYPE,xdl.SOURCE_DISTRIBUTION_ID_NUM_1,xdl.ACCOUNTING_LINE_CODE,mmt.transaction_date,msib.segment1

  ,gcc.concatenated_segments gl_line_acct

  ,gcc_mta.concatenated_segments mtl_txn_acct

from gl_je_headers gjh,

     gl_je_lines gjl,

     xla_ae_lines xal,

     xla_ae_headers xah,

     apps.xla_distribution_links xdl,

     gl_code_combinations_kfv gcc,

     mtl_transaction_accounts mta,

     gl_code_combinations_kfv gcc_mta,

     apps.mtl_material_transactions mmt,

     apps.mtl_system_items_b msib

where gjh.je_header_id = gjl.je_header_id

AND gjh.ledger_id = gjl.ledger_id

and gjl.gl_sl_link_id = xal.gl_sl_link_id

and xal.ae_header_id = xah.ae_header_id

and xdl.ae_header_id = xal.ae_header_id

and xdl.ae_line_num = xal.ae_line_num

and gcc.code_combination_id = gjl.code_combination_id

 and xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id

and mta.REFERENCE_ACCOUNT = gcc_mta.code_combination_id

and mta.transaction_id = mmt.transaction_id

and msib.inventory_item_id = mmt.inventory_item_id

and msib.organization_id = mmt.organization_id

and gjh.name = ‘’

The above SQL gives details of material transactions and corresponding Journals created for them

customer interface Error code C1

Customer Interface Errors Out the data with the Error Code : C1

C1 --> This customer reference has two different customer names defined

This error occurs when we have multiple records for the same customer but different values of Customer Name , there can be different scenarios for this error

• Creating a Site for Existing Customer : When we are creating a new site for an existing customer then the CUSTOMER NAME value for the data in the interface needs to be same as the Customer existing in Oracle , if there is any mismatch in the name or case then Customer Interface will error out the record
• Creating a new customer with multiple sites  : In this case too all the records need to have the same CUSTOMER NAME , if the values mismatch then 1 record will be progressed to create the customer and others will error with this code

Update the data in Customer interface with the correct data and re-process the records

Journal Import in Oracle

For All the entries in the subledger modules , the data will be transferred to the GL and subsequently  journals will be created and posted.
In order to create journals in Oracle the data should be present in GL_INTERFACE , once the data is loaded into the GL_INTERFACE the Jounal Import programs "Import Journals" needs to be submitted.

Data can come into GL_INTERFACE either from the Subledgers eg :AP/AR/INV or uploaded by Web ADI.
Once the data is available in GL_INTERFACE , launching Jounral import will pick up data for the Ledger for which the program is run , validate the data and create journals.

Some of the important points to remember when running Journal Import :

• Group Id : When you are running Journal Import you can import data for multiple sources by specifying the Group Id Pramater. When specified journal import will process only the records of the specified Group Id. If the Group Id is not provided then Journal Import will only process the records for which Group Id is not specified ie Group ID NULL , records with group id will be left un-processed
• Chossing : Import Descriptive Flexfields, and whether to import them with validation : If we are not creating Summary Journals then we can choose to import the Descriptive Flexfields . Here we can choose to import DFF with our without validation . With Validation Imports journals only if the Descriptive Flexfields pass the validations or you can choose to import journals without Flexfiled journals  where Oracle will not validate the DFF and generate all the journals

SEPA Mandates

With the Introduction of SEPA , we can create Mandates in Oracle which will enable direct debit Payments from the customer,
Below we list the basic activities that need to be done for SEPA mandate creation and the Mandate detail population on the invoice.

1. Create Customers
2. Create Customer Banks with correct IBAN numbers
3. Create Madate for the Bank Account
4. Setup Direct Debit/Automatic Receipt Methods
5. Ensure that the Receipt method is assigned to the customer
6. Create AR invoice with the correct receipt method as setup in Step 4
7. The Invoices will get created with the Correct SEPA Mandates

When Creating the invoice in Oracle depending on the Receipt method specified for the direct debit receipt methods Oracle will automativall check ofr the presence of Mandates at the customer BILL TO and create the AR invoice with the Mandate information. Once created Direct Debits can be performed.
Post with Madate Creation Details coming soon.

External Bank and Bank Branches

When Creating Suppliers or Customers in Oracle , we might be associating bank accounts with them. A bank account can only be created if a bank and bank branch exists , thus the below sequence needs to be followed .

1. Check for an existing bank or Create a new bank
2. Check for an existing branch of the bank or create a new bank branch
3. Create the bank account for the bank and branch combination

Each bank and bank branch is created as a party in oracle.
There might be scenarios where we also create an address for the bank/ branch. When using the Oracle front end the details need to be entered from the OAF screen. In Order to achieve the same by program the below sequence needs to be followed .

1. Create the bank/ bank branch
2. Create a location in Oracle with the bank/branch address details using API : HZ_LOCATION_V2PUB.CREATE_LOCATION
3. Create a party site in Oracle using the party_id (bank id/bank_brach_id) of the bank/branch created in Step 1 and location id of the address created in Step 2. Use the API : HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE

 

Supplier Payment Method

In Oracle on the supplier/supplier site a payment method can be maintained. e.g.  CHECK or EFT.
When the Supplier is created the payment method information is populated in the table :
IBY_EXTERNAL_PAYEES_ALL and IBY_EXT_PARTY_PMT_MTHDS

Post creation when the payment method details are updated oracle will insert a new record in the table : IBY_EXT_PARTY_PMT_MTHDS , thus this table maitains the complete history of payment method details of the supplier/supplier site.

The current record is identified by PRIMARY_FLAG = 'Y' .

select

aps.vendor_name,aps.segment1 ,asl.vendor_site_code,pmt.PAYMENT_METHOD_CODE

from apps.ap_suppliers aps,
apps.ap_supplier_sites_all asl,
apps.IBY_EXTERNAL_PAYEES_ALL iep,
APPS.IBY_EXT_PARTY_PMT_MTHDS pmt
where aps.vendor_id = asl.vendor_id
and asl.vendor_site_id = iep.supplier_site_id
and asl.org_id = iep.org_id
and iep.ext_payee_id = pmt.ext_pmt_party_id
and pmt.primary_flag = 'Y'

Payables Import Error : Tax lines with the same Tax Regime and Tax cannot be allocated to the same transaction line more than once.

When importing an AP Invoice by Payables import program if you have TAX Lines where the same combination of the TAX REGIME and TAX appears more than once the PAyables import will give you an error : Tax lines with the same Tax Regime and Tax cannot be allocated to the same transaction line more than once .
If such error is received please check the invoice throwing the error and check the TAX lines perform a data fix by removing the un-necessary duplicate tax lines and re-import

Auto application of Credit Note to Invoice Using AutoInvoice

When Creating a Credit note by Auto Invoice import Oracle provides the option of auto application of a Credit Note to an invoice i.e. When you are creating a credit note as soon as a credit note is created it will be immediately applied to an AR invoice.
This functionality can be used by by populating the reference_line_attribute columns of ra_interface_lines_all tables By populating the reference line_id or the combination of reference line_context and reference line attributes which correspond to a given AR invoice line you can apply the CM line being created to an existing Invoice.
Ensure that the CM line which you want to apply has an amount less than or equal to the invoice line open amount else you will get an error by Auto Invoice if over application is not enabled.
This is an efficient way of Creating credit notes and also applying them to Open invoices/Debit notes in the system if the information of application is available beforehand

AutoInvoice Import Error : Tax lines with the same Tax Regime and Tax cannot be allocated to the same transaction line more than once.

When importing invoices via the AR Interfaces , in cases where 1 invoice line is associated to multiple tax lines Oracle can throw an error : Tax lines with the same Tax Regime and Tax cannot be allocated to the same transaction line more than once if the TAX setup is incorrect.

For 1 invoice lines we will be having 2 or more tax lines each having its own tax_rate_codes.

For each of these tax rates we cannot have the same combination of regime and Tax. Multiple tax allocations is only possible if tax lines belong to combination of different regimes and tax.

In case of such an issue the TAX setups need to be checked and corrective measures need to be taken

AutoInvoice Error : When the receipt method is of type Automatic, you must either supply a valid bank account or ensure that a primary bank account for the currency code of the transaction has been set up for the Bill To customer

When importing an invoice via the AutoInvoice Import Program we can get an error message : When the receipt method is of type Automatic, you must either supply a valid bank account or ensure that a primary bank account for the currency code of the transaction has been set up for the Bill To customer

This error occurs when the Invoice line in AR Interface has a receipt method id , but on the Customer Bill TO for which we wish to import the record there is no active bank record present.
If an Automatic receipt method is specified then the Customer BILL TO  needs to have an active bank account else this error will be thrown.

SQL :
select hcsu.location,ieb.bank_account_name,ieb.bank_account_num,hps.party_site_number
from apps.hz_cust_site_uses_all hcsu,
     apps.iby_external_payers_all iep,
     apps.iby_pmt_instr_uses_all pmt,
     apps.iby_ext_bank_accounts ieb,
     apps.hz_cust_acct_sites_All hcas,
     apps.hz_party_sites hps
where hcsu.site_use_id = iep.ACCT_SITE_USE_ID
and hcsu.org_id = iep.org_id
and iep.ext_payer_id = pmt.ext_pmt_party_id
and pmt.instrument_id = ieb.ext_bank_account_id
and hcsu.site_use_code = 'BILL_TO'
and hcsu.cust_acct_site_id = hcas.cust_Acct_site_id
and hcas.party_site_id = hps.party_site_id
and hcsu.org_id = hcas.org_id
and pmt.payment_function = 'CUSTOMER_PAYMENT'
order by 1

Date Issue in Oracle YY vs RR format

During my last data migration for customer banks we found an issue in the data post migration. The Customer Bank accounts got created with an incorrect start date.

For Eg : if the start date was 1-Jan-1970 , the bank account got  created with a start date 1-Jan-2070.

The reason for this was the date format being used :

We had used : 

TO_DATE ('01-JAN-70', 'DD/MON/YY') -->  this converts the date to 1-Jan-2070

The correct way to include the dates will be to use the typecast : TO_DATE ('01-JAN-70', 'DD/MON/RR')

 The RR format works as expected. For the dates between 00-49 , Oracle will translate them to year 2000 to 2049. for the values 50-99 Oracle will translate them to 1950 to 1999.

Depending on the business need the respective typecast can be used.

The basic difference between the YY and RR date formats is as  below :

In 'YY' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx00 and ending with the next xx99.
In 'RR' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx50 and ending with the next xx49. That is, the window of possible dates is shifted by 50 years.