, , ,

Payment Due Calculation on AP invoice

Payables calculates payment Due Date using following values:

– Goods Received Date + Receipt Acceptance Days
– Invoice Date
– Terms Date

Payables populates the Payment Due Date with the most recent date among the above dates.

The logic is:
Most Recent( Goods Received Date + Receipt Acceptance Days, Invoice Date, Terms Date )

Goods Received Date:
            Goods Received Date gets populated on the invoice based on Terms Date Basis. We can find Terms Date Basis at following levels.
– Supplier Site Level
– Supplier level
– Payables Options

Payables first looks for Terms Date Basis at Supplier Site Level. If there is no value exist here, it will look at Supplier Level. If there is no value here as well, then Payables takes this value from Payables Options.

Receipt Acceptance Days:
         We can find Receipt Acceptance Days under Invoice Tab in Payables Options.

Invoice Date:
         We can find the Invoice Date in Invoice Date field on Payables Invoice.

Terms Date:
          Terms Date is the beginning date from which Payment Terms start when Payables calculates the scheduled payment(s) for an invoice. The invoice Terms Date defaults based on Terms Date Basis option you select:

• System: System date on day of invoice entry.
• Goods Received: The date you receive goods for invoices you match to purchase orders.
• Invoice: Invoice date.
• Invoice Received: Date you receive an invoice.


Payment Due Date calculation in case of a matched Invoice:

In case of a Matched invoice, Payables consider Receipt Transaction Date along with Goods Received Date + Receipt Acceptance Days, Invoice Date, and Terms Date.

The process includes two steps:

1. The system first checks for recent date of Goods Received Date+Receipt Acceptance Days and Receipt Transaction Date

The logic is:
Recent (GOODS RECEIVED DATE+RECEIPT ACCEPTANCE DAYS, RECEIPT TRANSACTION DATE)

2. After step1, the system uses the following logic again

Recent ( Recent Date from step1, Invoice Date, Terms Date)

Payables consider Receipt Date based on the “Recalculate Scheduled Payment” check box under Invoice Tab in Payables Options. If this check box is enabled, Payables consider the Receipt Date for the calculation of Payment Due Date. If you don’t want the system to use Receipt Date in the calculation of Payment Due Date, then disable “Recalculate Scheduled Payment”.

If you want recalculation, then system considers Receipt date for a matched invoice.

Navigation to enable/disable “Recalculate Scheduled Payment”:
Responsibility:    Payables Responsibility which has the access to setups

Navigation:        Setup > Options > Payables Options
Tab:                   Invoice
                                                                       
 Enable/Disable Recalculate Scheduled Payment based on business requirements.
/by
, , , , ,

Enable/Disable Concurrent program parameter based on Other Parameter

Based on a request, here is the details on how parameter can be enabled/disabled based another parameter value.
Below is the requirement
There are 2 valuesets parameter. If for parameter 1 user selects Yes then the other paramter should be enabled whereas if No is selected then the parameter should remain disabled. This can be achieved by using a hidden parameter as explained below.

Step1: Need 3 value sets for 3 parameter.
Value set1 = BOM_SRS_YES_NO (Oracle Defined)
Value Set2 = AMS_SRS_CHAR1 (Oracle Defined)
Value Set3 = SV_DEPENDENT_VS (User Defined)


Step2: Create Concurrent program as displayed in the screenshot below
Parameter1: Main Parameter

Parameter2: Hidden PArameter

Parameter3: Dependent Parameter

Step3: Assign concurrent program to a request group and test your program.

/by
, , ,

FNDLOAD Syntax in R12

In the following links I have discussed on how to migrate
1) Concurrent Programs
2) Value sets
from one instance to another using FNDLOAD.

Now I will try to cover several other objects that can be migrated using FNDLOAD.
The syntax for moving any objects using FNDLOAD is almost the same except few changes. Following is the list of .lct files that are used for different objects
1) Concurrent Program –> afcpprog.lct
2) Value Sets –> afffload.lct
3) Menus –> afsload.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=’XXXX’
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
4) Lookups –> aflvmlu.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=’XXXX’ LOOKUP_TYPE=’XXXX’
UploadFNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt
5) Flexfield –> afffload.lct
Descriptive FlexfieldDownload
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX APPLICATION_SHORT_NAME=’XXXX’ DESCRIPTIVE_FLEXFIELD_NAME=’XXXX’
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
Key FlexfieldDownload
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX APPLICATION_SHORT_NAME=’XXXX’ DESCRIPTIVE_FLEXFIELD_NAME=’XXXX’
UploadFNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
6) Profile Options –> afscprof.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=’XXXX’ APPLICATION_SHORT_NAME=’XXXX’
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt
7) Responsibility –> afscursp.lct
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=’XXXX’
UploadFNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt
8) Request Groups –> afcpreqg.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=’XXXX’ APPLICATION_SHORT_NAME=’XXXX’ REQUEST_GROUP_UNIT UNIT_NAME=’XXXX’
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt
9) Menus –> afsload.lct
Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=’XXXX’
Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
10) Forms Personalization –> affrmcus.lct
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME=’XXXX’
UploadFNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

/by
, , , , , ,

API to create Item Category

The item categories can be created using item import when Items are created. They can also be created using APIs discussed below:
Following are the steps to Check and/or Create Item Categories
1) Create Category Segment combinations
2) If the enforce list is checked for category then insert category combinations in the enforce list
3) Create/Update/Delete Category Assignments on Item

In Detail

Create Category Segment combinations
Following APIs can be used to create/update/delete category combinations.
INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
  X_CATEGORY_ID  OUT    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER
)
Use following API for assigning a category to a category set. 
A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.
INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_CATEGORY_ID  IN    NUMBER,
  P_PARENT_CATEGORY_ID  IN    NUMBER,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2
)
Following APIs can be used to create/update/delete Item category assignments.
INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_DESCRIPTION  IN    VARCHAR2
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)
/by
, , , , , ,

Item Import (Item Conversion)

The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.
The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.

Required columns in MTL_SYSTEM_ITEMS_INTERFACE
PROCESS_FLAG = 1 (The column is used to identify status of record)
TRANSACTION_TYPE = ‘CREATE’ or ‘UPDATE’
SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
DESCRIPTION = ‘Description of the item’
ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated.
LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import .

Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.
PROCESS_FLAG = 1
TRANSACTION_TYPE = ‘CREATE’
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = Same as item_number in mtl_system_items_interface table.
Each row in the mtl_item_revisions_interface table must have the REVISION and
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Required columns for MTL_ITEM_CATEGORIES_INTERFACE table.
TRANSACTION_TYPE = ‘CREATE’
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org
ITEM_NUMBER/INVENTORY_ITEM_ID or both
CATEGORY_SET_NAME or CATEGORY_SET_NAME or both
CATEGORY_ID or CATEGORY_NAME or both

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import(IOI) program can be run in parallel if seperate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and
processes Master records first. However, as one IOI process is not aware of
other IOI processes running in parallel, do not split a given item’s separate
Organization records into two different SET_PROCESS_IDs that are being run in
parallel.

Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
· for Numeric fields: insert -999999
· for Character fields: insert ‘!’

/by