AR Receipt and Bank Statement Reconciliation

When the AR Receipt is Created and Applied to the Invoice based on the receipt class setup for the receipt method used in the AR transaction the Receipt status will change to Remitted or cleared.
If the Receipt class Matching setup is labelled as By Matching : In that case the Cash receipt status will change to Remitted,
The cash receipt needs to be reconciled against a bank statement to clear it off.

In order for this to happen we need to

• make an entry for the bank statement
• Select the receipt from the list of available receipts available for reconciliation
• Once the bank statement is reconciled with the receipt the receipt status changes to Cleared and the bank statement line is reconciled.

Below SQL can be used to find out details of a AR cash receipt being reconciled with a bank statement

select cba.BANK_ACCOUNT_NAME,cba.bank_account_num,csh.statement_number,csl.STATUS,csl.amount,csl.trx_type,acr.receipt_number,arm.name

from CE_STATEMENT_HEADERS csh,

     ce_statement_lines csl,

     ce_bank_accounts  cba,

     ce_statement_reconcils_all csr,

     apps.ar_cash_receipts_all acr,

     ar_receivable_applications_all ara,

     ar_receipt_methods arm,

     hr_operating_units hou,

     AR_CASH_RECEIPT_HISTORY_ALL ach

where csh.STATEMENT_HEADER_ID = csl.STATEMENT_HEADER_ID

and     csh.BANK_ACCOUNT_ID = cba.BANK_ACCOUNT_ID

and csl.STATEMENT_LINE_ID = csr.STATEMENT_LINE_ID

and csr.reference_id = ach.CASH_RECEIPT_HISTORY_ID

and csh.statement_number = :statement_number

and bank_account_num = :bank_account_num

and csr.REFERENCE_TYPE = 'RECEIPT'

and acr.cash_receipt_id = ara.cash_receipt_id

      and acr.receipt_method_id = arm.receipt_method_id

      and ara.org_id = hou.organization_id

      and hou.name  = :operating_unit

      and receipt_number = :receipt_number

      and acr.cash_receipt_id = ach.cash_receipt_id  

Pick Release Order with Container Item

In this article we will discuss on the Pick Release process and changes happening in the system when we do a pick release for a line attached to a container item.

Scenario : Suppose we are in a business where we sell LED lamps , since the end product is small generally the shipping units will be in hundreds or thousands. In the actual shipping

These small lights will be packed into large boxes/cartons/containers which will be shipped to the customer. Suppose we have a container with a capacity of 1000.

We create a sales order with a ordered quantity of 5000.

In this scenario when we do a shipping although we will be shipping 5000 units of product but in 5 containers as the business will pack them in 5 containers and ship those records.

If we have such a scenario first of all we need to do a setup of a container item and associate with the Actual inventory item. Here we will define how many units one container will hold which will be used in the stage of picking.

1.       It is assumed that a container item is already created in the system and we have defined a relationship between the actual item and the container item. This information is available in the table wsh_container_items

2.       In the Pick Release rule setup if Auto Pack is set to Yes : then the Pick release process will take care of packing the goods into the containers as per the setup in Step 1

Changes during Pick Release :

The Goods will be moved from the source sub inventory to the staging sub inventory

In wsh_delivery_details the delivery details will be split up in multiples of 1000 and balance quantity if it exists to match the container mapping and new lines will be created with source code ‘WSH’ to signify a container which has been associated to each line.

Below SQL can be used to get the details.

select lpn_id,source_code,split_from_delivery_detail_id,SRC_REQUESTED_QUANTITY,requested_quantity,wdd.cancelled_quantity,wdd.*

from wsh_delivery_Assignments wda,wsh_delivery_details wdd--,oe_order_lines_all ool

where 1=1--

and delivery_id = :delivery id 

and wda.DELIVERY_DETAIL_ID = wdd.DELIVERY_DETAIL_ID

order by wdd.DELIVERY_DETAIL_ID

here source code : OE is the actual order line and the source code ’WSH’ is for each container delivery details which has been created during the pick release process .Here the WSH records signify the number of cartons which have been packed the LPN ID for which the information can be found in table : WMS_LICENSE_PLATE_NUMBERS

The material transactions are created as below to track the transfer of goods from Source to Staging sub-invenotry

·         2  entries are created in mtl_material_transactions just like any other picking tracking the movement of goods from the source sub-inv to staging sub-inv

·         When the shipping is done then we will have 5 entries in mtl_material_transactions signifying the movement of goods outside the staging warehouse each with quantity 1000 reason being in physical shipment 5 cartons moved out of the warehouse

Order Import Error : VALIDATION FAILED FOR THE FIELD - ORDER TYPE

When creating Sales Order by populating the Order Interface , via Process Order API or entering the Sales Order manually , we get the Error : VALIDATION FAILED FOR THE FIELD - ORDER TYPE .
This issue is observed when the Order Date for the Sales Order is having a Value which is earlier to the Effective from date of the Sales Order Type,

Solution : Make the Effective to of the order type to a historical date earlier than the order date and re-rty