Item Import Overview

Item Import Overview

Use the item import process to import items from your legacy system or PDM system. You can import items from any source into Oracle Inventory and Oracle Engineering. When you import items through the Item Interface, you create new items in your Item Master organization or assign existing items to additionalorganizations. You can specify values for all the item attributes, or you canspecify just a few attributes and let the remainder default or remain Null. You can also specify an item template for each item and inherit attribute values from the template. The Item Interface also lets you import revision details, including past and future revisions and effectivity dates. Validation of imported items is done using the same rules as the item definition forms, so you are insured of valid items.The Item Interface reads data from two tables for importing items and item details. You use the MTL_SYSTEMS_ITEM_INTERFACE table for new item numbers and all item attributes. This is the main item interface table, and can be the only table you choose to use. If you are importing revision details for new items, you can use the MTL_ITEM_REVISIONS_INTERFACE table. A third table, MTL_INTERFACE_ERRORS, is used for error tracking of all items that the Item Interface fails. You can import item categories using interface table called as MTL_ITEM_CATEGORIES_INTERFACE.Before you use the Item Interface, you must write and run a custom program that extracts item information from your source system and inserts it into the MTL_SYSTEM_ITEM_INTERFACE table, and (if revision detail is included) the MTL_ITEMS_REVISIONS_INTERFACE table. After you load the items into these interface tables, you run the Item Interface to import the data. The Item Interface assigns defaults, validates data you include, and then imports the new items. You may also specify an item template for each item being imported. You must import items into the Item Master organization before you import itemsinto additional organizations. You can accomplish this by specifying only your Item Master organization on first run of the Item Interface. Once this has completed, you can run the Item Interface again, this time specifying an additional or all organizations. You can also use the Item Interface to import a single item material cost and material overhead, and revision details.Below we will walk through few scenarios of item import. Scenario:

1Testing the basic item import with minimum columns populated.

First always try to populate the master organization with a sample record and test.1. How do I know my organization code and organization id ?Select organization_code , organization_id from org_organization_definitionsorder by organization_code;Sample out put is below.ORG ORGANIZATION_ID--- -------------------------------V1 204M1 207M2 209M3 606M4 1641M5 1642M6 1643M7 1644....2. How do I know which organization is master to a child organization?select a.organization_id ,a.organization_code,a.master_organization_id,b.organization_code from mtl_parameters a, org_organization_definitions bwhere a.master_organization_id = b.organization_idorder by a.organization_code;Sample out put is belowORGANIZATION_ID ORG MASTER_ORGANIZATION_ID ORG--------------------------- ------- ---------------------------------------- ------- 204 V1 204 V1 207 M1 204 V1 209 M2 204 V1 606 M3 204 V1 1641 M4 204 V1 1642 M5 204 V1 1643 M6 204 V1 1644 M7 204 V1 .....In the above example for organizations M1 to M7, V1 organization is the master organization.Populating V1 organization with one new item using just the basic columns.Please truncate all IOI interface tables before loading mtl_system_items_interface: ex. TRUNCATE TABLE TRUNCATE TABLE INV.MTL_SYSTEM_ITEMS_INTERFACE;TRUNCATE TABLE INV.MTL_INTERFACE_ERRORS;TRUNCATE TABLE INV.MTL_ITEM_REVISIONS_INTERFACE;TRUNCATE TABLE INV.MTL_ITEM_CATEGORIES_INTERFACE;Note: Truncate frees space, delete does not free space.insert into mtl_system_items_interface(process_flag,set_process_id,transaction_type,organization_id,segment1,description)values(1,1,'CREATE',204,'TESTITEMIMPORT', 'Testing Item Import');commit;In the above insert Process_Flag = 1. Only when process_flag = 1 will the item import program process the record.Set_Process_Id = 1. You can set which ever number you want for this column. This is basically used like a batch number. You can tell item import which set of records to process by entering a set_process_id.Transaction_Type is 'CREATE' when you are inserting new records and 'UDATE' when you are updating existing records.We are using organization_id 204 which is for master organization V1.Note: If you have more than one segment defined for items please populate all those segments.To check which segments to be populated please got to Setup > Flexfields > Key > Segments . Now query up 'System Items' by putting it in flexfield title.Select segments button, and now you should be able to see the different segments being defined.Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to master items screen and query on 'TESTITEMIMPORT' the item that we had populated and you should be able to see the item.Similarly you can import the same item for the child organization. Eg. we want item 'TESTITEMIMPORT' in organization M1 say Seattle manufacturing then all I have to do is replace the organization_id by M1 organization_id. Now the insert statement will look like this.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description)values(1,1,'CREATE',207,'TESTITEMIMPORT', 'Testing Item Import');commit;Run the item import with the same parameters and then check if the concurrent request has completed successfully.Now do Change Organization and go to Organization Item screen and query up TESTITEMIMPORT and you should be able to see the item in M1 organization.Scenario: 2~~~~~~~~~~~~~To import items and use item templates.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^To see all template names run the following sql.select template_id,template_name,description from MTL_ITEM_TEMPLATES;To see all the attributes related to template run the following sql.select template_id,attribute_name,enabled_flag,report_user_value from MTL_ITEM_TEMPL_ATTRIBUTES where template_id = &template_id;Please subsitute the template id from the 1st query in the second query to see the attribute values.In our example I am going to use Purchasing Template.select template_id,template_name,description from MTL_ITEM_TEMPLATESwhere template_id = 259;template_id template_name description----------- ------------- ---------------- 259 Purchased Item Purchased ItemWe are going to insert a new item in the master organization and use the purchasing template.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,TEMPLATE_ID)values(1,1,'CREATE',204,'TESTTEMPLATE', 'Testing Item Import With Template',259);commit;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to master items screen and query on 'TESTTEMPLATE' the item that we had populated and you should be able to see the item. Now you will see that the purchasing attributes are set because we used the purchasing template.Note: Instead of using template_id we can use template_name and you should see the same effect.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,TEMPLATE_NAME)values(1,1,'CREATE',204,'TESTTEMPLATE', 'Testing Item Import With Template','Purchased Item');commit;Scenario: 3~~~~~~~~~~~~To import items and material cost associated to it.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^First check which cost elements are defined for the organizaion.select cost_type_id,cost_type,description from CST_COST_TYPES;Output looks like this.cost_type_id cost_type description------------ ------------- ------------------------------------ 1 Frozen Frozen Standard Cost Type 2 Average Average Cost Type 3 Pending Pending Standard Cost Type.....To find the sub element name defined for your organization for a particular cost type please check bom resources table.In our example we are checking for material sub element in organization M1 with organization_id = 207 for cost_code type 1.select resource_code,description,cost_element_id,cost_code_type from bom_resources where organization_id = 207and cost_code_type= 1;resource_code description cost_element_id cost_code_type------------- ----------------- --------------- --------------Material Material Sub element 1 1Labor Labor Sub element 1 1Expense Expense Sub element 1 1In our scenario we want to create a new item in organization M1 along with its material cost (say $11) assuming the same item is already created in master organization V1.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,template_id,MATERIAL_COST,MATERIAL_SUB_ELEM)values(1,1,'CREATE',207,'TESTCOSTMATERIAL', 'Testing Item Material Cost Import',259,11,'Material');commit;In the above sql you might have observed we are populating MATERIAL_COST and MATERIAL_SUB_ELEM.Similary we can use MATERIAL_SUB_ELEM_ID, MATERIAL_OH_RATE, MATERIAL_OH_SUB_ELEM and MATERIAL_OH_SUB_ELEM_ID colums.To check the subelements you can go to Cost management responsibility for your organization and under Setup > Sub-Elements you will see the respective subelements being defined.Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTCOSTMATERIAL' the item that we had populated and you should be able to see the item. Go to Tools > Item Costs and then hit Open button from Item Cost Summary screenand you should see the details. In out example we see a material cost of $11 for the item in cost type 'Frozen'(Standard Costing).NOTE: Item import cannot be used to update item costs.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~You have to populate CST_ITEM_CST_DTLS_INTERFACE,CST_RESOURCE_COSTS_INTERFACE,CST_RES_OVERHEADS_INTERFACE and CST_DEPT_OVERHEADS_INTERFACE accordingly.The concurrent request can be launched by navigating to cost->cost mass edits->Import Cost Information.The parameters for the Cost Import concurrent program and their description isas follows : Import Cost Option : A LOV is provided from which the user can select one of the import options which may be either to import Only item costs , Only resource costs , Only overhead rates or all the cost information . Option Table from which data is processed ---------- -------------------------------------------- Only item cost cst_item_cst_dtls_interface Only resource costs cst_resource_costs_interface Only overhead rates cst_res_overheads_interface , cst_dept_overheads_interface All Cost Information From all the four interface tablesMode to run this request : A LOV is provided with possible two values , 'Insert new cost' or 'Remove and replace cost'. The 'Insert new cost' mode , is useful if you are importing large # of items and are not sure if that Item/Organization/Cost Type combination already exists in the production tables, if it does then the row in the interface table would be flaged as errored and not imported.This would prevent any accidental overwrite of already existing data. With 'Remove and replace cost' mode all the previous cost information for this item, cost_type and organization combination will be deleted from the production tables and the new information will overwrite (replace) the already existing one. Group Id Option : A LOV is provided from which the user can either select 'ALL' or 'Specific Group Id' . If the user wishes to submit multiple Cost Import process requests he can do so by submitting one request per group id. For doing so the data in the interface tables should be stamped with distinct group id value by using the NEXTVAL from the sequence generator CST_LISTS_S .The use of this sequence geneartor is a MUST for generating multiple groups or may lead to data corruption as these interface tables are used by other processes too. If the user selects "ALL" from the list then a group ID generated by a sequence will replace the group ID in the interface tables (if any) and all the unprocessed rows from the four interface table (viz. cst_item_cst_dtls_interface , cst_resource_costs_interface , cst_res_overheads_interface , cst_dept_overheads_interface ) will be processed in one run. Cost type to import to : The user is provided with a LOV from which he needs to select the cost type in which he wishes to import the cost information. Even if the user has populated a cost type or cost type ID in the interface tables, it would be overwritten with the one that is selected here. The cost types that the user can pick from is restricted to the multi-org, updateable cost types. Delete succesfull rows : This parameter decides whether the successfully processed rows should be deleted from the interface tables at the end of the run. If the user selects 'Yes' then all the successful rows be deleted, basically rows that do not have their error flag set to "E".Importing directly into Frozen/Average cost type (i.e non updateable cost types)and merging of new cost with existing costs is not supported at this time andwould still have to be processed by cost update routines. Also when importingthe costs from the interface table ,material overhead defaults (if any)specified for an Organization/Category would not be respected.Minimum columns in each table that the user needs to provide1. CST_ITEM_CST_DTLS_INTERFACE The columns that the user has to provide are a. Inventory_item_id b. organization_ID or organization_code. c. resource_ID or resource_code for cost elements other than 1(material). If we are importing cost into material cost element and default material subelement has been specified on 'Define Organization parameters' form then that would be respected unless the user overides it with a value in this column. d. usage_rate_or_amount e. cost_element_ID or cost_element f. Process Flag (must be set to 1) We default values for based_on_rollup, shrinkage_rate, inventory_asset_flag, lot_size from the row from CST_ITEM_COSTS for this item and the default cost type(default cost type of the cost type specified to import the costs into). If there is no such row already defined in CST_ITEM_COSTS, then, the values for these 4 columns also need to be specified and they have to be the same for all rows of this item, cost type and organization combination.CST_RESOURCE_COSTS_INTERFACE a. Resource_ID or resource_code b. organization_ID or organization_code c. resource_rate d. Process_flag (must be set to 1)CST_RES_OVERHEADS_INTERFACE a. Resource_ID or resource_code b. Overhead_ID or overhead c. Organization_ID or organization_code d. Process_flag(must be set to 1)CST_DEPT_OVERHEADS_INTERFACE a. Department_ID or department b. Overhead_ID or overhead c. Organization_ID d. Rate_or_amount e. Process_flag(must be set to 1)The other columns will be defaulted.This is available for only 11.5.9 and above customers.Customers on 11.5.8 can apply patch 2193391 to get this functionality.Please refer to costing Manuals for further details.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^In case you have any questions please log a tar with costing group.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Sample cost update statement is belowINSERT INTO CST_ITEM_CST_DTLS_INTERFACE(INVENTORY_ITEM_ID,ORGANIZATION_ID,COST_TYPE,RESOURCE_CODE,USAGE_RATE_OR_AMOUNT, COST_ELEMENT_ID,PROCESS_FLAG)VALUES(9935,207,'Pending','Material',17,1,1);commit;Using this I update the pending cost for the item from $11 to $17.Now once the pending cost is updated you will have to use 'Update Standard Cost'Concurrent program to update Frozen cost for the item from the pending cost.Scenario: 4~~~~~~~~~~~~~~To import item and revisions associated to it.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^When ever you import a new item based on the Starting revision specified under'Revison/Lot/Serial' tab in organization parameters screen the default revison gets assigned to the item. For eg. the default revision in V1 organization is 'A' when we do item import this revision gets associated to the item.Now lets import a new item with revision 'B' which is not the default revision.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,REVISION)values(1,1,'CREATE',204,'TESTREVB1', 'Testing Item Revision','B');Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTREVB1' the item that we had populated and you should be able to see the item. Go to Tools > Revisions and then you should be able to see two records one with revision 'A' which is the default revision and another with revision 'B' that we had populated.In case you want to import a new revision say revision 'C'. You directlypopulate the MTL_ITEM_REVISIONS_INTERFACE table and then run item import.insert into MTL_ITEM_REVISIONS_INTERFACE(item_number,revision, revision_label,implementation_date, effectivity_date, process_flag, organization_id ,set_process_id, transaction_type)values('TESTREVB1','C','C',sysdate,sysdate,1,204,1,'CREATE');Commit;Now run the item import.In Process Set enter 1 as we had used 1 in the set_process_id column.Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.When you query up the item 'TESTREVB1' You will see the new revision 'C'.Scenario: 5~~~~~~~~~~~~~~Lets take into consideration we have to import more than two revisions along ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^with item creation. We will be populating MTL_ITEM_REVISIONS_INTERFACE. The key point here is the IMPLEMENTATION_DATE should increment cronologically.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description)values(1,1,'CREATE',204,'TESTREVB2', 'Testing Item Revision');insert into MTL_ITEM_REVISIONS_INTERFACE(item_number,revision, revision_label,implementation_date, effectivity_date, process_flag, organization_id ,set_process_id, transaction_type)values('TESTREVB2','A','A',sysdate,sysdate,1,204,1,'CREATE');insert into MTL_ITEM_REVISIONS_INTERFACE(item_number,revision, revision_label,implementation_date, effectivity_date, process_flag, organization_id ,set_process_id, transaction_type)values('TESTREVB2','B','B',sysdate+.0003472,sysdate+.0003472,1,204,1,'CREATE');insert into MTL_ITEM_REVISIONS_INTERFACE(item_number,revision, revision_label,implementation_date, effectivity_date, process_flag, organization_id ,set_process_id, transaction_type)values('TESTREVB2','C','C',sysdate+.0023472,sysdate+.0023472,1,204,1,'CREATE');COMMIT;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTREVB2' the item that we had populated and you should be able to see the item. Go to Tools > Revisions and then you should be able to see three records one with revision 'A' , another with revision 'B' and the thrid one is 'C' that we had populated.Note: We have been inctementing the minutes section by sysdate+.0003472 and sysdate+.0023472 so that the implementation date is in cronological order.Scenario: 6~~~~~~~~~~~~~~~~Lets take into consideration we have to import item categories along with items.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^select category_id,structure_id,segment1,description,category_concat_segs,structure_name from mtl_categories_vwhere segment1 = 'DAIRY'order by category_id,structure_id;category_id structure_id segment1 description category_concat_segs structure_name --------------------------------------------------------------------------------------- 1440 51466 DAIRYDairy Products DAIRY Inventory Class 1861 51467 DAIRYDairy Products DAIRY Planning Class 1869 51470 DAIRYDairy Products DAIRY Sales Class 1886 51766 DAIRYDAIRY DAIRY GL Product Lineselect category_set_id,category_set_name,description,structure_name,structure_idfrom mtl_category_sets_v where category_set_name = 'Inventory Class';category_set_id category_set_name description structure_namestructure_id--------------------------------------------------------------------- 90 Inventory Class Inventory Class Inventory Class 51466In out example we want to use category 'DAIRY' which has a category_id of 1440 and category set 'Inventory Class' which has category_set_id of 90.You can go to the application Setup > Items > Categories > Category Codes and using examine get the category id.You can go to the application Setup > Items > Categories > Category Sets and using examine get the category set id.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description)values(1,1,'CREATE',204,'TESTCATG1', 'Testing Item Categories');insert into MTL_ITEM_CATEGORIES_INTERFACE(item_number,category_set_id, category_id,process_flag, organization_id ,set_process_id, transaction_type)values('TESTCATG1',90,1440,1,204,1,'CREATE');In the above insert statement you could use CATEGORY_SET_NAME and CATEGORY_NAME instead of CATEGORY_SET_ID and CATEGORY_ID.commit;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTCATG1' the item that we had populated and you should be able to see the item. Go to Tools > Categoriess and then you should be able to see category Dairy associated to the item.To verify you can run the following querySelect * from MTL_ITEM_CATEGORIES where inventory_item_id in (select distinct inventory_item_id from mtl_system_items_b where segment1 = 'TESTCATG1');It shows one record.NOTE: Populating set_process_id is mandatory for categories import. Please putthe same set process id when you run the import program.Updating exsisting item categories:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Updated transaction can only be performed as a combination of DELETE assignment, then CREATE.Lets take our above example of item 'TESTCATG1' which has now category 'DAIRY'associated with it. I want to update category 'DAIRY' with a new category 'BAKERY' whose category_id is 1436 for item 'TESTCATG1'.insert into MTL_ITEM_CATEGORIES_INTERFACE(item_number,category_set_id, category_id,process_flag, organization_id ,set_process_id, transaction_type)values('TESTCATG1',90,1440,1,204,1,'DELETE');insert into MTL_ITEM_CATEGORIES_INTERFACE(item_number,category_set_id, category_id,process_flag, organization_id ,set_process_id, transaction_type)values('TESTCATG1',90,1436,1,204,1,'CREATE');commit;Run item import.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 2 in create or update items field as we are updating the exsisting item and changing its category. Hit OK button.You can check view requests to check whether your request has completed successfully.When you query up item 'TESTCATG1' and select the categories from tools you will find that the category 'BAKERY' is now asscoiated with the item.Scenario: 7~~~~~~~~~~~~Lets take into consideration we have to import item catalogs along with items.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^In our example we want to asscoiate 'ADSL Modem' catalog to our item.select ITEM_CATALOG_GROUP_ID,SEGMENT1 from MTL_ITEM_CATALOG_GROUPS_Bwhere ITEM_CATALOG_GROUP_ID = 161;ITEM_CATALOG_GROUP_ID SEGMENT1--------------------- ---------------------------------------- 161 ADSL Modemselect ITEM_CATALOG_GROUP_ID,ELEMENT_NAME,ELEMENT_SEQUENCE,DESCRIPTION from mtl_descriptive_elementswhere ITEM_CATALOG_GROUP_ID = 161;ITEM_CATALOG_GROUP_ID ELEMENT_NAME ELEMENT_SEQUENCE DESCRIPTION--------------------- ------------ ---------------- ----------- 161 2wire 10 2wire 161 linksys 20 linksysdescinsert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,ITEM_CATALOG_GROUP_ID)values(1,1,'CREATE',204,'TESTCATA1', 'Testing Item Catalog',161);commit;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTCATA1' the item that we had populated and you should be able to see the item. Go to Tools > Catalogs and then you should be able to see 'ADSL Modem' along with descriptive elements '2wire' and 'linksys'. In 11.5.9 you have to manually enter the values for descriptive elements. In 11.5.10 API to import descriptive element values will be present.Scenario: 8~~~~~~~~~~~~Lets understand how to update existing items.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Lets say I have an item 'ODC' which has no buyer code in Seattle organization (M1 organization code). I want to update and asscoiate 'West, Rod' as buyer to the item 'ODC'.First I check under Setup > Items > Attribute Contol to check if it controlled at the organization level or master level. In our case the attribute is controlled at the Org Level.Now we check if Rod exists in the list of valid buyers.select agent_id,agent_name,start_date_active,end_date_activefrom PO_AGENTS_V where agent_name like '%Rod%';agent_id agent_name start_date_active end_date_active-------- ---------- ----------------- --------------- 10183 West, Rod 17-FEB-04select inventory_item_id from mtl_system_items_bwhere segment1 = 'ODC'and organization_id = 207;207 organization id is for M1(Seattle organization)INVENTORY_ITEM_ID----------------- 9372Now use the above values in the insert script.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type, organization_id, inventory_item_id, BUYER_ID)values (1,1,'UPDATE',207,9372,10183);commit;Now you will observe that the transaction type is 'UPDATE' because we are updating an existing record. I am also using inventory_item_id because when you use id's the performance is better. And also when running the item import I use value 2 in create or update items field.Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 2 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'ODC' item in M1 organization. Under purchasing tab you will see that the buyer 'West, Rod' populated.Note:INCOIN does not support updating item numbers. Enhancement request 6507704 already logged for this.Scenario: 9~~~~~~~~~~~~~In this scenario you will learn how to populate character and number columns^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ and make it null.^^^^^^^^^^^^^^^^^Just from out above example we have buyer 'West, Rod' populated for item 'ODC'. Now for some reason we want to make null for buyer for item ODC.Buyer id column is a number column. To make it null we have to populate -999999.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type, organization_id, inventory_item_id, BUYER_ID)values (1,1,'UPDATE',207,9372,-999999);commit; Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 2 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'ODC' item in M1 organization. Under purchasing tab you will see that the buyer field is updated to null.Suppose planner 'S.Peters' is asscoiated to item ODC. You want to make it null.PLANNER_CODE in mtl_system_items_interface is a charracter type. To make it null we have to use '!'.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type, organization_id, inventory_item_id, PLANNER_CODE)values (1,1,'UPDATE',207,9372,'!');commit;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 2 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'ODC' item in M1 organization. Under general planning tab you will see that the planner is updated to null.Note: To make number columns null use -999999 and to make character columns -----------------------------------------------------------------------------null use '!' exclamation.-------------------------Frequently Asked Questions (FAQ):~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~a. How do I know what error has occurred?^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Ans . The process_flag in mtl_system_items_interface will be 3. Now the error message will be in mtl_interface_errors table.The record in mtl_system_items_interface will have a transaction_id. There will be corresponding record with error code and explanation in mtl_interface_errors.select transaction_id from mtl_system_items_interface where process_flag = 3;Now substitue the &transaction_id value in the following sql by the value that you get in the ablove sql. You should be able to see the error.select message_name,column_name,error_message from mtl_interface_errors where transaction_id = &transaction_id;b. What things should I check if I getting errors?^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Ans. First check if you are populating the correct values in all the fields. Then check if there are any mandatory descriptive flexfield columns. You can go to application setup > flexfields > descriptive > segments and on query type ?Items? in title field and then check each segment whether it is mandatory and whether it is doing any validation against some pre-existing values in some value set. If so check if the corresponding attributes are populated properly. Many times user forgets to populate mandatory values and item import fails.Also check for invalid objectsSelect object_name,object_type from all_objects where status = 'INVALID';If any invalid objects please recompile those and retest item import.c. I run item import but records are not getting processed. The process_flag^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^is still 1.^^^^^^^^^^^Ans. Check under sysadmin> concurrent managers whether inventory namager is running. Then check if there is suffcient memory and extent spaces. Use notes 1019721.6 and 1020085.6 to determine if there is a memory or extents issue. Please check if there is sufficient roll back segments , system global area and tablespace.Please use the following truncate . This frees up space.TRUNCATE TABLE INV.MTL_SYSTEM_ITEMS_INTERFACE;TRUNCATE TABLE INV.MTL_INTERFACE_ERRORS;TRUNCATE TABLE INV.MTL_ITEM_REVISIONS_INTERFACE;TRUNCATE TABLE INV.MTL_ITEM_CATEGORIES_INTERFACE;Now try importing one record and see if you are able to import it successfully.If you see ORA-1654 happens while inserting records into mtl_pending_item_status table in the log file (01654, 00000, "unable to extend index %s.%s by %s in tablespace %s") please use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. d. What do I do to check more in to errors and see where the import is failing?^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Ans. From sysdamin please set the following profile options.INV: Debug Trace: set Yes to turn on the debug functionality. INV:Debug Level: set 10 , to print the detail debug messages.INV: Debug file (Including the complete path): The path is one of the directories from the valid directories defined in the obtained v$parameter through the following sql : select value from v$parameter where name = 'utl_file_dir'; Make sure to include the file name at the end of the path, e.g. '/<>/trx_mgr.log'MRP: Debug Mode 'Yes'2. After setting the above system profile values go to Sysadmin > Concurrent > Program > Define. Query on short name 'INCOIN'. Now check enable trace and save. Now run the item import the log file of item import will give more details. Note: A log file may not be generated in the directory, what developmentwants is the concurrent request log file after the above settings are done.To identify the correct trace file please run the following queryselect request_id,oracle_process_idfrom fnd_concurrent_requestswhere request_id = &request_id;Please substitute &request_id by the correct request id from the concurrent program.The trace file in the trace directory would have the number thatyou get for oracle_process_id from the above query as a part of the file name.eg: if oracle_process_id = 22771The file could be ora22771.trce. 11.5.9 Item import issues and patches to be applied.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^1. Cannot create items - Patch 32154732. INV_IOI_REV_BAD_ORDER error message for revisions - Patch 32263593. Item import fails with no error message - Patch 32263594. Item import completes but process_flag = 4 and set_process_id = -999 - Patch 32263595. If duplicate records for one master IOI does not import child recs for other master records. The error message that you will get is "This Child Item has no Master Item record in MTL_SYSTEM_ITEMS" -Patch 3474468f. List of documents required while logging a tar on item import.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^1. Dump of records in all 4 interface tables (in excel spread sheet includingcolumn headers) MTL_SYSTEM_ITEMS_INTERFACE,MTL_INTERFACE_ERRORS,MTL_ITEM_REVISIONS_INTERFACE and MTL_ITEM_CATEGORIES_INTERFACE before running import and after running import.2. Output of BOMCHECK.sql (See NOTE:244145.1)3. Raw Trace, Tkprof of trace and log files after doing settings recommended in section d(What do I do to check more in to errors and see where the import is failing?) above.4. Version of INCOIN from the server.G. Some examples of errors and what it might mean^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^a.Some times users are unable to import items to child organization. They mightget error DEFAULT_INCLUDE_IN_ROLLUP_FLAG,INVENTORY_ITEM_STATUS_CODE,INVENTORY_PLANNING_CODE,PLANNER_CODE,PLANNING_MAKE_BUY_CODE,FIXED_LOT_MULTIPLIER,ROUNDING_CONTROL_TYPE,CARRYING_COST Check the corresponding attribute values.If planner code is set at Master level please make it organization level.(Ref. Oracle Inventory User Guide pg 5-52 - General Planning Attribute Group ) "Planner Code This attribute is controlled at the Organization level only."b.Some times users might get MESSAGE NAME : BOM_PARSE_ITEM_ERROR or MESSAGE NAME : INV_IOI_ERRERROR MESSAGE : *** BAD RETURN CODE b ***Please do the following1)Please see that setup > Receiving > (Purchasing and Receiving) setups are complete.2)Please auto extend all MTL Tables and MTL Indexes.3)Please auto extend Table Space.4)Re-Link all Inventory libraries. Relink INCOIN.o.5)Compile all Inventory Key Flex Fields.6)Compile all invalid objects.7)Please check all INV profile options are set.8)Now truncate the interface tables and populate it with few records and run import. The error should go.H. Is there an API for Item creation and updation^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ INV_ITEM_GRP.Create_item - For creating the items INV_ITEM_GRP.Update_item - For updating the items (For details on calling on this API check file INVGITMB.pls)- file in /invdev/inv/11.5/patch/115/sqlOr useEGO_ITEM_PUB.Process_item - For creating/updating items (For details on calling on this API check file EGOPITMB.pls) - file in /egodev/ego/11.5/patch/115/sql

Increasing Performance in cursor

To increase the performance in the cursor use below statement in the cursor
/*+PARALLEL(OL, 10) */

CURSOR cur_month_usage
IS
SELECT /*+PARALLEL(OL, 10) */
ol.sold_to_org_id,
(SELECT hc.account_number
FROM apps.hz_cust_accounts hc
WHERE 1 = 1
AND hc.cust_account_id = ol.sold_to_org_id)

Examples of the to_date function

Examples of the to_date function might include:
to_date('10-12-06','MM-DD-YY')
to_date('jan 2007','MON YYYY')
to_date('2007/05/31','YYYY/MM/DD')
to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')
to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')
to_date('022002','mmyyyy')
to_date('12319999','MMDDYYYY')
to_date(substr( collection_started,1,12),'DD-MON-YY HH24')
to_date('2004/10/14 21', 'yyyy/mm/dd hh24')
TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)

To see the current system date and time with time zone use CURRENT_DATE function
select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53

Expense Report Restriction of Viewing from other users

Expense Report Restriction of Viewing from other users

For restricting the user to view other users expense report from his View Expense report tab we need to set up below

• @Japan Web Expenses

Navigation: sysadmin-->security-->responsibility-->Define (F11 query for above responsibility and add securing attribute(ICX_HR_PERSON_ID), save the changes)

How to Reverse the distribution Lines when entered wrongly

Reverse the distribution Lines when entered wrongly

Responsibility: GL Super User
Nav: Setup > Financials > Flexfields > Key > Security > Assign
Pull up COMPANY rule
Delete row for INR STAT AP Manager

Query to find on which Patch level we are for particular module

Query to find on which Patch level we are for particular module

select * from fnd_application
where APPLICATION_SHORT_NAME='PA'


select * from FND_PRODUCT_INSTALLATIONS
where APPLICATION_ID=275

Can cross check the PATCH_LEVEL column.

To Track Requisition Number from PO Number

If we have PO Number then use below query to get the Related Requisition Number

SELECT segment1
FROM po_requisition_headers_all
WHERE requisition_header_id IN
(SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id IN
(SELECT requisition_line_id
FROM po_req_distributions_all
WHERE distribution_id IN
(SELECT req_distribution_id
FROM po_distributions_all
WHERE po_header_id IN
(SELECT po_header_id
FROM po_headers_all
WHERE segment1 = Give Po Number)
)
)
)
;

How to block Auto Approval of PurchaseRequisition created by preparer, and it should route to his manager for approval,though preparer has approval

How to block Auto Approval of PurchaseRequisition created by preparer, and it should route to his manager for approval,though preparer has approval Authority

Navigation to change the setup as below

PO Super User--->Setup--->Purchasing--->Document Types (Select Purchase Requisition)

Now uncheck the Attribute (Owner Can Approve). Save the work and close the form.

Once the above setup is done, no preparer can approve the requisitions he created rather it will route to his manager, though he has Approval Authority.

steps are involved while making payments, checkrun and generation of payment notifications

The following steps are involved while making payments, checkrun and generation of payment notifications:

1. Create Invoices
Navigation: AP Super User -->Invoices-->Entry-->Invoice Batches
Validate the above Invoices Through 'Actions' / Release Holds if any exist.

2. Create Payment Batch
Navigation: AP Super User -->Payments-->Entry-->Payment Batches
Actions --> Select Invoices
Actions --> Build Payments
Once the above steps are done Requery the Payment Batch and Click 'Payment' Button to Build Payments

3. Requery Payment Batch in 'Payment Batch Window' and perform below steps one by one after completion of Concurrent requests generated by the same
Actions --> Build Payments
Actions --> Format Payments
Actions --> Confirm Payment Batch

The Payment Notifications are generated in the 'Actions --> Confirm Payment Batch' step, when the Payment Batch is confirmed.

A trigger is executed when 'AP_INV_SELECTION_CRITERIA_ALL' table is updated with STATUS = 'CONFIRMED' and if the PAYMENT_METHOD_LOOKUP_CODE = 'EFT'.

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

select * from V$SESSION
where OSUSER like 'c_sgoud' -- User name of the terminal ( may be your windows login name )

Alter system kill session '146,46619'


select sid, serial# from v$session where username = 'USER';

alter system kill session 'SID,SERIAL#';

Oracle Table to Find Approval Limits for any person

Oracle Table to Find Approval Limits for any person

ap_Web_signing_limits_all

query with person_id

Time Card Creation

Time Card Creation

Patcard Creation steps for i2 technologies client

As i2 do not have OTL implemented so all the time cards created will be saved in pa tables only.

Go to responsibility @webtimecards--- Create New Timecard
Select project and other details and enter the time details for the week and submit the time cards , you will get the Time card number once you submitted.

The above information is saves in Pa_expenditures_all table and the lines details are saved in the internediate interface tables once you go to the
PA Super User ---Expenditures---Transaction Import ---Import Transactions
here select 'PRC: Transaction Import' select Transaction source as "Oracle Self Service Time" and Btach Name we need to take from pa_expenditures_all table USER_BATCH_NAME and submit the program.

Once Submitted the records will be inserted into select * from pa_expenditure_items_all table

3 C's.(Set of books)

Set of books determines the functional Currency, Chart of account structure, and
accounting Calendar for each company or group of companies, which are known as the 3 C's.

Select data with a hierarchical (parent/child) relationship.

Select data with a hierarchical (parent/child) relationship.

Syntax:

SELECT...
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]

Key:

START WITH : The row(s) to be used as the root of the hierarchy

CONNECT BY : Condition that identifies the relationship between
parent and child rows of the hierarchy

NOCYCLE : Do not circle around loops (where the current row has
a child which is also its ancestor.)

ORDER SIBLINGS BY : Preserve ordering of the hierarchical query
then apply the order_by_clause to the sibling rows
Example:

create table test_connect_by (
Level1 varchar2(100),
Item varchar2(100)
);

Sample Data

LEVEL1 ITEM

0 123
1 345
2 678
1 987
0 666

Inserted above values in the table

insert into test_connect_by values(0,666);

select * from test_connect_by


LEVEL1 ITEM

0 123
1 345
2 678
1 987
0 666


select * from test_connect_by
connect by prior item=level1


LEVEL1 ITEM

0 123
0 666
1 345
1 987
2 678

=====================================================

Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities

Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities in oracle

SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'SAIRAM_GOUD' --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name

Script To find Oracle API's for any module

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PA_%API%'
order by
a.owner, a.name;

Project Expense Report Account Generator workflow process

If using a custom Project Expense Report Account Generator workflow process:

To implement the solution, please execute the following steps:

1. Set the profile option 'Account Generator:Run in Debug Mode' = 'Yes'. This will persist the
workflow data so that the custom Account Generator workflow can be debugged.

2. Bounce the Apache server.

3. Reproduce the error. The custom Project Expense Report Account Generator workflow process can now be viewed through the workflow monitor.

4. Resolve the issue with the custom Project Expense Report Account Generator

5. After the issue has been resolved, the employees whose expense reports have failed with this
error should withdraw and resubmit their expense reports.

Expense report workflow errors ( WFENG_NOTIFICATION_PERFORMER )

Expense report workflow errors ( WFENG_NOTIFICATION_PERFORMER )

Failed Activity Inform Individual of Expense Allocations Failure
Activity Type Notice
Error Name WFENG_NOTIFICATION_PERFORMER
Error Message 3120: Activity 'APEXP/301116' has no performer.
Error Stack Wf_Engine_Util.Notification_Send(APEXP, 550307, 301116, APEXP:OIE_AP_EXP_ALLOCATIONS_FAILURE) Wf_Engine_Util.Notification(APEXP, 550307, 301116, RUN)


For the above error we need to cross check the setup for that employee who has filed the expense report

select * from gl_sets_of_books
where CHART_OF_ACCOUNTS_ID=50184

select * from per_employees_x
where EMPLOYEE_ID=72546

select * from GL_CODE_COMBINATIONS_KFV
code_combination_id=298127

and from the above cross check concatnated segments all are defined correctly or not

and you can go to HR super user and find the employee and click on Assignment buttion and from their select purchase order information where you can find the segmnents change accordigly and now file new expense report which will be in success.

Oracle Workflow tables:

Oracle Workflow tables:

WF_ACTIVITIES table stores the definition of an activity. Activities
can be processes, notifications, functions or folders. A process activity
is a modelled workflow process, which can be included as an activity in
other processes to represent a subprocess. A notification activity
sends a message to a performer. A functions activity performs an
automated function that is written as a PL/SQL stored procedure.
A folder activity is not part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row
defines one work item within the system.

WF_ITEM_ATTRIBUTES table stores definitions of attributes
associated with a process. Each row includes the sequence in which
the attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time
a message is sent.

AP Standard Expense Report Process (Expense report workflow error)

AP Standard Expense Report Process

Request Approval from APPROVER Error

Failed Activity Request Approval from APPROVER
Activity Type Notice
Error Name WFENG_NOTIFICATION_PERFORMER
Error Message 3120: Activity 'APEXP/301347' has no performer.
Error Stack Wf_Engine_Util.Notification_Send(APEXP, 549406, 301347, APEXP:OIE_REQ_EXPENSE_REPORT_APPRVL) Wf_Engine_Util.Notification(APEXP, 549406, 301347, RUN)

Resolution

For the errors with 'no performer' for 'Request Approval from APPROVER' Activity we should not restart the process, first we need to update the attribute values and then we can restart the process.

Once we open the workflow notification click on 'Activities History' and below we will have "Update Attributes" Button click on that and we will have fields with

Approver ID
Approver Name
Approver Display Name

Update above fields with proper values and Click on Apply Button , it will prompt with message
"Confirmation The workflow-level attribute values have been updated."

Now go to "Monitor Activities History" window and cilck the " Request Approval from APPROVER" Button and Re-start the workflow now the workfow will route to correct person and will be in Active state.

setting the Password for PDF File sent through XML Publisher

setting the Password for PDF File sent through XML Publisher

Open the rtf for which you want to set password and do the following things

1) Open the .rtf
2) Go to File - > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true

Note : property name should always start with xdo- .

Generating Excel Outputs from existing standard Oracle Reports 6i Output

Generating Excel Outputs from existing standard Oracle Reports 6i Output

Most end-users use Excel as a very generic tool, because of their familiarity and the control in analysing data using the power of MS Excel.

There is a trick to capture Oracle Reports output (text output) into a excel sheet.

Basic Steps :
1) Set the output format for the report to XML
2) Run the report to generate output.
3) Save the output file locally as a XML file.
4) Open the file using MS Excel.
5) To make it more beautiful, you may use a MS Excel Template.

The Excel Templates can be used to do complex data analysis and formatting. Displayed example below is a simple excel template, to make the point.

Points be to Noted while doing AR Conversion

Conversion (Data Migration) of Invoices in Receivables

Whenever we are going in for implementation of Receivables module, we have to consider the necessity of bringing in customer open balances from the old system to Oracle Receivables.

Some of the key questions that needs to be addressed before we take up a conversion activity. This is just a sample list and not an exhaustive one:

1. What are the different types of invoices in existing system Provide invoice samples? (invoices, credit/ debit memos, commitments, chargebacks)
2. Do we need to migrate only open invoices?
3. Do we migrate closed invoices also, if yes, then for what time period?
4. Please explain the invoice numbering mechanism? Is it automatic?
5. What are the interfaces from/to your existing receivables system?
6. Will the old system still be in place for querying and reporting purpose?

One can adopt one of the following three strategies for conversion:

1. Consolidate all the open balances customer-wise and create a single open invoice for each customer in the new Oracle system. The advantage of this system is that it is quite easy and not data intensive and makes good business sense in case of small businesses with very few customers. The major demerit of this approach is that later on one cannot track the individual invoices which the customer had sent and can become an audit issue also. In case of dispute over payment, this invoice will remain open till the dispute is resolved. Also, aging of invoices and dunning history will be lost.

2. Bring in all the open and partially paid invoices, credit/debit memos into the new system. Migrate all the unapplied and partially applied receipts to the new system. The advantage of this process of conversion is that you can track all open invoices individually and apply the correct receipt to correct invoice. Also, the conversion effort will be moderately low compared to case if you migrate all open and closed invoices. The disadvantage of this approach is that you cannot have a track of closed invoices in the new system. Also, it would be tough to handle scenarios where there is a dispute regarding incorrect receipt application, etc. This is the most common approach taken for receivables invoice, credit/debit memo and receipt migration.

3. Migrate all open and closed invoices to the new system. Reapply the migrated receipts to invoices in the new system. This approach makes sense if your receivables data is quite small else the effort involved in migrating all closed invoices and credit memos to the new system does not make much business sense.

The next question that arises is how we should migrate the invoices, credit/debit memos and receipts to the new system. Oracle provides standard interfaces to load the same. We can also use tools like Dataloader or manually key in the data into Oracle.

In this article i will talk of invoice, credit/debit memo conversion only. Prior to invoice migration, customer migration should be over apart from other pre-requisites. Following is the list of pre-requisites that should be completed prior to invoice, credit/debit memo conversion:

•Set-up of Customer Payment Terms should be complete
•Set-up of Currencies should be complete (this is necessary in case you have foreign currency invoices also)
•Set-up of Transaction Types should be complete
•Set-up of Accounting Rules should be complete
•Set-up of Tax rates and Tax codes should be complete
•Set up for sales representative should be complete
•Set up for debtor area should be complete
•Set up for income category should be complete
•Automatic customer invoice numbering should be set to 'No'
•Customer and Customer address should be migrated in the system
•Disable the Invoice interface purge program so that the data successfully imported should not get purged in the interface table.
•Set up for invoice batch source name should be complete

In the next step extract Invoice data from the legacy files and using SQL loader populate the interface tables RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL. Submit the Auto Invoice open interface program. Data from the two interface tables will be uploaded to the following base tables using the Invoice open interface program:

•RA_CUSTOMER_TRX_ALL
•RA_CUSTOMER_TRX_LINES_ALL
•RA_CUST_TRX_LINE_GL_DIST_ALL
•RA_BATCHES_ALL
•RA_CUST_TRX_LINE_SALESREPS_ALL
•AR_RECEIVABLE_APPLICATIONS_ALL
•AR_PAYMENT_SCHEDULES_ALL
•RA_INTERFACE_ERRORS_ALL

Ensure that the Purge Interface check box is not checked when you submit the Autoinvoice program. In the Autoinvoice errors form you can see the error corresponding to failed records. Correct the errors in the interface table and rerun the Autoinvoice program. Submit the Autoinvoice Purge Program separately. Only records that have been successfully processed by Autoinvoice are purged.

Using autoinvoice you can migrate invoices, credit/debit memos and on-account credits into Oracle. However, you have to set grouping rules (Navigation > Setup > Transactions > Autoinvoice > Grouping Rule) to group lines to create one transaction and ordering rules (Navigation > Setup > Transactions > Autoinvoice > Line Ordering Rules) to determine the order of the transaction lines on a particular invoice.

Troubleshooting the Projects to Receivables Interface

Troubleshooting the Projects to Receivables Interface

This feature is for anyone using Oracle Projects and Receivables 11.5.10 and beyond. There were several new features introduced to Projects 10.5.10 (Family Pack M), which now require additional configurations for the Receivables interface to work.Here are few quick tipts consolidated from the Metalink notes and user guides to remember what to do

Open Periods

Make sure to open respective accounting periods in Receivables. As Receivables Manager, navigate to Control > Accounting > Open/Close Periods.

Retention Error

You may not be using retention or multi-currency in Project Billing, but you still receive the “Transaction Code: TRA OU Level Retention Accounting flag N… Some transactions are disabled. Please Check auto-accounting setup” error when running the PRC: Interface Invoices to Receivables program (PATTAR).

To resolve the error you need to enable the AutoAccounting Assignments for Unbilled Retention Account and Rounding Account.

As Projects Billing Super User:

Navigate to Setup > AutoAccounting > Assign
Query up function ‘Revenue and Invoice Accounts’
Define Segment Rule Pairings for the Unbilled Retention Account
Define Segment Rule Pairings for the Rounding Account
Make sure to enable the Function Transactions!
Again, you need to do this even if you do not intend to enable retention and multi-currency billing in your Implementation Options.

Sales Credit Type Rejection

You might get a ‘No sales credit type at Implementation or Projec Level’ rejection when running the PRC: Interface Invoices to Receivables program (PATTAR).

In order to resolve this error as Projects Billing Super User:

Navigate to Implementation Options
Select ‘Exchange Rate Type’ in the Currency tab
Select ‘Sales Credit Type’ in the Billing tab
Transaction Source and Transaction Type Errors

The seeded Project Transaction Source and Project Transaction Types might be incomplete. Make sure to review and update these for your setting you defined in the Implementation Options > Billing tab. The source It is going to be ‘PROJECTS INVOICES’ (if new 11i or R12 implementation, or ‘PA INVOICES’ if upgraded from 10.7) and Transaction Types are going to be ‘Projects Invoice’ and ‘Projects Credit Memo’ (11i and beyond) or ‘PA Invoice’ and ‘PA Credit Memo’ (10.7) respectively.

As Receivables Manager:

Navigate to Transaction Sources: Setup > Transactions > Sources
Query up your Transactions Source you defined in the Implementation Options > Billing tab
Make sure the Reference Field Default Value field = ‘interface_header_attribute1′
Make sure the Standard Transaction Type = ‘Projects Invoice’
Make sure the Credit Meno Batch Source = ‘PROJECTS INVOICES’
Also navigate to Transaction Types: Setup > Transactions > Types
Query up ‘Projects Invoice’ transaction type
Make sure the Credit memo Type = ‘Projects Credit Memo’

How to track Current Apps Versions

1)select product_version,patch_level from fnd_product_installations
Get current version ana Patch level information.

2)select * FROM V$VERSION
Database Version infomation.

3)select * from v$instance
Instance details

4)select WF_EVENT_XML.XMLVersion() XML_VERSION from sys.dual;
Current XML Parser Version info.

5)select TEXT from WF_RESOURCES where TYPE = 'WFTKN' and NAME = 'WF_VERSION'
Workflow version Number.

6)select home_url from icx_parameters
Oracle applications front end URL

7)SELECT VALUE FROM V$PARAMETER WHERE NAME=’USER_DUMP_DEST’
Get the Trace file location.

8) XML Publisher Vesion info.
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.

Query to Display Module Wise Reports

Display Module Wise Reports

SELECT fa.application_short_name, fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name, minimum_width,
minimum_length, concurrent_program_name, concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY 1



SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1

File Types and Extensions in APPS

File Types and Extensions in APPS


.a Library files for C language code
.c C lang source code
.ctl DataMerge control file (Sql loader)
.dat DataMerge import/export (Data file)
.Drv Driver (patch related files)
.env Unix environment
.exp DataMerge export
.fmb Binary forms
.fmx Execuatble forms
.h C lang header file
.jar java archive
.lc C lang source to be archived
.lct Data loader control
.ldt Data loader datafile
.log Concurrent request log
.lpc PRO*C source to be archived
.msb Binary message
.msg Readable message
.o C lang object module
.odf Object description
.out Concurrent request output
.plb PL/SQL package body
.pll PL/SQL shared library (reports)
.pls PL/SQL package specs
.rdf Oracle D2k Reports
.req Oracle reports Executable
.sql SQL*Plus scripts

Overview of Oracle HRMS

Overview of Oracle HRMS

Oracle HRMS enables you to enter and track people recors when they apply for a job or when they leave organization.HRMS allows you to enter , maintain,report of employess information.


Business Process in HRMS


The following modules are intergrated in Oracle HRMS


1.Hiring & Deployment
2.Recruitment
3.Career Management
4.Training & Development
5.Compensation & Benefits
6.Time & Labour
7.Payroll Processiong
8.Organization & Resource Planning


Work Structure
Work structure are independent of the people who work in enterprise. and it suppots Enterprise and employee structure such as Business groups and legal entities and also contains grade structure, pyroll groups,jobs and positions.


Location
Locations represents physical site where employees work.The location types could be


Global Availble across all BG

Business Group only availble in BG



Jobs and Positions
Jobs and positions to represents the different roles that a person can perform in enterprise.


Job is generic role within BG

They are independent of ant single organization.

Exists for all Organizations.



Positions
Positions is specific role or function exist in only one organization.Position includes Jobs and Organization.


Positions are used to show spcific posts within org.

Using position contril functionality can manage positions.



Grades
Grades indicates seniority level in an enterprise


Grades and Pay
HRMS supports direct and indirect relationship between grades and Pay






Person Types in HRMS
HRMS store information about different group of peoples such as employees,applicants and people external to your enterprise.Using person Types you can restrict access to records of certain group of people



HRMS uses a unique identifier called as Employee Number to identify every employee in BG.


Business Group
Business group defines a complete set of HR data and linked with one legislative processing like payrool and benefits . But oracle HRMS come with predefined Business groups which is useful who do not intedent to use HR in Apps.



Personal Information
Every enterprise must be able to record personal information for its employee,applicants and contacts,So HRMS allow you update and report employee information.


Addresses
Employee must have only one primary addres on record.But you can enter as many addresses ad mecessary for each person.Each Country has its own national address style.



Tracking
By using web-based Manager Self service to enter some information like employee history,roles and employee data etc...


Employee Assignment
The assignment represents the way employee work in enterprise.The assignment is the central concept that related to the structure in which employes works.


Each assignment has employess assignment number that uniqely identifies assignment. When employees change their locations or job these changes are dtaetracked.


Assignment stastuses


1.Active
2.Suspended
3.Terminated
4.Ended


Employee Development
You can Record work choice,relocation,preferred work hours and work schedule.


Person Types
HRMS supports with predefined set of system person types ease person typa has its own user name. These are 8 types.


Retiree

Former Spouse

Surviving Family Member

Surviving Spouse

Former Family Member

Beneficiary

Dependent

Partcipant
Terminate Employee
When employee leaves organization terminate the person record action changes the person type to Ex-Employee and automatically ends all assignments for the person.You cannot terminates an employee who has future-dated assignments.

Oracle HRMS Protected Descriptive Flexfields

Oracle HRMS Protected Descriptive Flexfields

In oracle HRMS the following DFF's not allows you to change the Definitions. These are used by product Development to deliver std functionality for HRMS.


Further Element information Pay_Element_types_f
Further Assignment Information Per_Assignment_Extra_info
Further Payment Method information Pay_org_payment_methods_f
Further Person Information Per_people_f
Further Job Information Per_jobs
Org Development DF Hr_organization_Information

Execution Sequences of sql clauses.

Execution Sequences of sql clauses.
a)Select…..
b)Group by…
c)Having…
d)Orderby..

Diff Between Implicit and Explicit Cursors

Diff Between Implicit and Explicit Cursors
1) Implicit: declared for all DML and pl/sql statements.
By default it selects one row only.

2) Explicit: Declared and named by the programmer.Use explicit cursor to individually process each row returned by a Multiple statements, is called ACTIVE SET. Allows the programmer to manually control explicit cursor in the
Pl/sql block

Flex mode and Confine mode

Flex mode and Confine mode

Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.

Flex mode:
On: parent borders "stretch" when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against
them.

BIND Vs LEXICAL

BIND Vs LEXICAL

BIND VARIABLE :
-- are used to replace a single value in sql, pl/sql
-- bind variable may be used to replace expressions in select, where, group, order
by, having, connect by, start with cause of queries.
-- bind reference may not be referenced in FROM clause (or) in place of
reserved words or clauses.
LEXICAL REFERENCE:
-- you can use lexical reference to replace the clauses appearing AFTER select,
from, group by, having, connect by, start with.
-- you can’t make lexical reference in a pl/sql statmetns.

Types of Reports in APPS

Types of Reports in APPS


Oracle Reports(D2k 6i): This is the most used tool for reporting on Oracle Applications. Most of reports customizations are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.

Oracle Discoverer:This is an intuitive tool for creating reports and performing on-line analysis.The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel

Oracle XML Publisher: This is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report, XML Publisher merges the custom templates with the concurrentrequest extracts data to generate output in RTF, PDF, HTML and EXCEL.


Business Intelligence System (BI): This tool provides helps managers to take the right decision with the daily data that is uploaded on their systems.
This tools to provide high level information to run their business such as the profitability of a particular business unit.


FSG Reports (Financial Statement Generator): Very powerful report building tool for Oracle General Ledger. Benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only Disadvantage of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.

RXi Report:(Variable reports) :With this tool a user has the ability to print the same report with multiple layouts.This tool is most used on Oracle Financials Applications>

FNDLOAD

FNDLOAD

when we are working in oracle application development/implementation project? The equally important AOL data Migration takes place necessary to synchronize the data across databases instance during installation and upgarde.

Using FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.The Conversion between database format and text file format is specified by a configuration file.

What can be Done?


·It can be done following list

· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Concurrent Manager Schedules

Advantages when using FNDLOAD
1. Because downloaded data is stored in a text file, version administration is possible
2. No learning curve. this is relief for developer/dbas
3. Fully supported and recommended by Oracle
4. Capture the migrations in a file and use it during installations(log file).
5. Pin-point when something happened and where (database) easily
6. AOL data migration process is now simplified!

Disadvantages
1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. No validation against migrating database/instance sensitive data

The Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1.....]
· The mode is either DOWNLOAD or UPLOAD.
· The configfile is the file that Fndload needs to download on upload data.
· T he data file is the output file, in which the downloaded data is written
· The entity is the entity you want to download,
·

Example of download:
FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=application_short_name

Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter

Where is Config File Located

· Configuration files with extension .lct
o On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
·Data files with extension .ldt
· The configfiles (.lct) are delivered and maintained by Oracle
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME
oDownloading a parent automatically downloads all children - (Example) Concurrent Program download

Sample Script Code for these Objects :

1 - Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=printer style name


2 - Lookups
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=prod LOOKUP_TYPE=lookup name


3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=prod DESCRIPTIVE_FLEXFIELD_NAME=desc flex name P_CONTEXT_CODE=context name


4 - Key Flexfield Structures
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME=prod ID_FLEX_CODE=key flex code P_STRUCTURE_CODE=structure name


5 - Concurrent Programs
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=prod CONCURRENT_PROGRAM_NAME=concurrent name



6 - Value Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=value set name


7 - Value Sets with values
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=value set name


8 - Profile Options
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=profile option APPLICATION_SHORT_NAME=prod


9 - Requset Group
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=request group APPLICATION_SHORT_NAME=prod


10 - Request Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=prod REQUEST_SET_NAME=request set


11 - Responsibilities
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=responsibility


12 - Menus
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=menu_name


13 Forms/Functions
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt



14. User/Responsibilities
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER Then UPLOAD FNDLOAD apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER []

How to Unlock the Objects

How to Unlock the Objects

The following query could be useful :


Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select SESSION_ID from V$LOCKED_OBJECT where OBJECT_ID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))

Get the Process id from the query. Login as unix user and run the following command to kill the process.



$kill -9 SPID

Locate Trace file and Enviroment files from Front End

Locate Trace file and Enviroment files from Front End

After completing traceinge, You can find the Trace file location path by using following methods

Method 1) select value from v$parameter
where name ='user_dump_dest'

The above Query returns Trace file location Path.

Method 2) Login As sysadmin
Click at Goto ->OAM->OAM Setup->Site MAP->Monitoring->System Configuration-> Databse Init.ora Setting.

Here You can find out Parameter name as user_dump_dest , utl_file_dir and corresponding values.

Concurrent Program output in MS Excel format

Concurrent Program output in MS Excel format

1)Login as sysadmin
2) switch to Sysadmin responsibility
3)go to Install->Viewer Options
4) Add entries as Below

File Format =Text Mime
Type=application/vnd.ms-excel
Description=Microsoft Excel


5) save the changes
6) Run any concurrent program
7) Goto-> tools and Copy file
Your able to see the output into Excel format

Diffierent types Apps Tables

Diffierent types Apps Tables
_ALL Conatains multi org data before using this table you need to set client_into to specific org , Org_id is common column.
_V view created on base table
_VL view created on multi language tables.
always use the condition LANGUAGE = USERENV(’LANG’).
_TL Tables support multi language.
_B These Main base tables
_F these date tracked tables for only in HRMS and these table contain 2 common columns effective_start_date and effective_end_date
_S sequence related tables
_AVN Audit view shadow tables contains data track information

OM Flow and table level Information

OM Flow and table level Information

Steps in Order Cycle:

1) Order Entry
2) Booking
3) Pick release :
For this we have to go to
Shipping Responsibilty Release sales order
Here In this form , In the ORDER tab, we have to enter ORDER Number
And delete the Scheduled shipped Dates To & Requested Dates To.
In SHIPPING tab, set AUTO CREATE DELIVERY to YES. In INVENTORY tab enter WAREHOUSE, set AUTO ALLOCATE to YES and AUTO PICK CONFIRM to YES. IF we set AUTO PICK CONFIRM to NO, then We have to go for the following steps

1. go to Inventory Resp
Move order à Transact Move Order then it will ask for
warehouse information. Give the same name as before [M2]
In this form, In the HEADER tab, enter the BATCH
NUMBER of the order that is picked .Then Click FIND
Button. Click on VIEW/UPDATE Allocation, then
Click TRANSACT button. Then Transact button will be
deactivated then just close it and go to next step.

4) Shipping :
For this we need to go to Shipping Transaction Give the order Number, and click find
Then we can see the order status.
Then we have to click DELIVERY Tab Button, in the Action LOV
We have to choose, SHIP CONFIRM.
Then four concurrent program will run in the background.
Such As::
1.) INTERFACE TRIP Stop
2.) Commercial Invoice
3.) Packing Slip Report
4.) Bill of Lading

After this concurrent program will complete successfully, we have to run
One more WORKFLOW BACKGROUND PROGRAM.
· If we don’t want to ship all the items, that are PICKED, then we have to click LINE/LPN tab , then click DETAIL button .
Now, in that form , in the SHIPPING field, we have to enter how Much quantity of items, we want to ship . The rest remain quantity, that are Ordered will become backorder quantity .

5) Interfacing with AR :

After WORKFLOW BACKGROUND PROGRAM
Concurrent program will complete successfully, we have to run
AUTO INVOICE MASTER PROGRAM from
RECEIVABLE RESPONSIBILTY. After this program
will complete successfully , we can the invoice details in
RECEIVALE à TRANSACTIONS à TRANSACTIONS. Here in
This Form, we have to give our order number in reference field
And query for the invoice details .Then we can see the invoice details.




Table Level Information:
==========================
Order Entry
• At the header level a record gets inserted into the header table
OE_ORDER_HEADERS_ALL.
• At the line level, record(s) get inserted into the Line table
OE_ORDER_LINES_ALL.

Order Booking
• This will update FLOW_STATUS_CODE value in the table
OE_ORDER_HEADERS_ALL to “BOOKED”
• The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will change to
AWAITING_SHIPPING.
• Record(s) will be created into the table WSH_DELIVERY_DETAILS with
RELEASED_STATUS=’R’ (Ready to Release)
OE_INTERFACED_FLAG=’N’ (Not interfaced to OM)
INV_INTERFACED_FLAG=’N’ (Not interfaced to Inv)
• Record(s) will be created into WSH_DELIVERY_ASSIGNMENTS but with
DELIVERY_ID null.

Pick Release
------------------
IF “Autocreate Delivery” option = “Yes” THEN
• ) Create a record into the table WSH_NEW_DELIVERIES
• ) Update WSH_DELIVERY_ASSIGNMENTS with DELIVERY_ID, thus
• ) Update WSH_DELIVERY_DETAILS with RELEASED_STATUS=’Y

Auto Invoicing
----------------------
Before running “Autoinvoice Program”, record(s) will exist into the table
RA_INTERFACE_LINES_ALL with

INTERFACE_LINE_CONTEXT = ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1 = &Order_number
INTERFACE_LINE_ATTRIBUTE3 = &Delivery_id
SALES_ORDER = &Order_number

After running the “Auto invoice Program” for the order:
Records will be deleted from the table RA_INTERFACE_LINES_ALL and new details will be created into the following RA transaction tables.
>RA_CUSTOMER_TRX_ALL with
INTERFACE_HEADER_ATTRIBUTE1=&Order_number
RA_CUSTOMER_TRX_LINES_ALL with
INTERFACE_LINE_ATTRIBUTE1 = &Order_number
SALES_ORDER = &Order_number

Oracle Purchasing Terminology

Oracle Purchasing Terminology

Terms used in the conventional purchasing system and as referred in Oracle Purchasing may be different. A brief explanation of the ‘Oracle Purchasing’ terms vis-à-vis the existing terminology is provided the following paragraphs. These terms are extensively used in documenting the ‘Purchasing - To Be’ flows and it is recommended that the various users of this system get acquainted with the same.

Requestor: Any Employee who requires specific items to meet his operational needs. Requestor may or may not have the system access to enter the on-line requisitions. Requestor with no computer access will give their requisitions manually on pre-printed requisition form to the requisition ‘Preparer’ within the concerned department.


Preparer: Employee in a department with the responsibility of entering Purchase Requisitions in the system.


Approver: Employee with the responsibility and authority of approving the requisition and / or the Quotation Analysis Document and / or the Purchase Orders.


Buyer: Employee in the Purchase Department with the responsibility of entering the Requests for Quotation, the Quotations received from the Supplier and the Purchase Orders in the new system.


Request For Quotation (RFQ): Synonym for ‘Inquiry’. RFQs are sent to the vendors, requesting them for their quotations for the items / services required to be purchased.

Quotation: Quotations are the ‘Vendor Offers’ for the required items / services.

Quotation Analysis: It is a ‘Comparative Statement of Quotations’.

Standard Purchase Order: Standard Purchase Orders are created for purchase of various items / services where details such as the quantity to be purchased and the negotiated costs.

Blanket Purchase Agreement: Blanket purchase agreements are signed with the vendor for the supply of goods / services over a period. Such agreements can be used when details of the goods / services you plan to buy from a specific supplier in a period are known, but you do not yet know the details of your delivery schedules. They can also be used to specify negotiated prices for your items before actually buying them.

Blanket Release: You can issue a Blanket Release against a Blanket Purchase Agreement to place the actual order with the vendor. The Releases should be within the Blanket Agreement effectivity dates.

Planned Purchase Order: It is a long term agreement committing to buy items / services from a single source. The tentative delivery schedule is given to the vendor along with other details like quantities and estimated costs.

Schedule Release: This document is issued to the vendor against a Planned Purchase Order to place the actual order. This document will confirm the specific quantity and delivery date to the vendor.

Contract Purchase Agreements: Contract Purchase Agreements may be entered into with suppliers to agree on specific terms and conditions without indicating the goods or services that will actually be purchased. Standard Purchase Orders can be issued at a later date referencing such Contracts.


Purchasing Cycle:

Requisitions: Requisitions generated by various departments of your organization. They basically say that ‘I WANT THIS ITEM’ mentioning the item quantity and required date

Request for Quotation: The purchasing person sends the requirement to various suppliers giving details of the item and can also specify own terms and conditions such as payment terms, price etc.

Quotations: The Supplier responds to the RFQ and sends their quotations mentioning details of the item and all their terms and conditions regarding payment terms, discount, delivery schedules, etc.

Purchase Orders: The quotations received from various suppliers are compared and PO made for them in which all the terms and conditions of the firm are mentioned as regards to Payment, price, etc.

Receiving Goods: Suppliers ship the goods, which are received in your organization

Payments: After clearance from the receiving department about the receipt of goods, payments are made to the suppliers.

Close the PO: After the items are received, payments made you can close the PO.


Purchasing Requirements
· Item
· Quantity
· Ship to Address
· Bill to Address
· Delivery Schedules
· Terms and Conditions
· PO Numbering
· Approval Authority
· Supplier
· Accounts

How to find Descriptive FlexFileds Segments defined for paticular Application

How to find Descriptive FlexFileds Segments defined for paticular Application

For Example if you want to know the Descriptive Flexfields defined for your Purchase Requisition Header level form,

Go to System Adminstrator Responsibility--->Application--->Flexfield--->Descriptive--->Segments--->
Here you can give F11 and query for Application 'Purchasing' and Title as 'Requisition Headers'
and here you can see how the segments attributes are defined.

AR Query to get open invoices for single/All customers

AR Query to get open invoice for single customer /for all customer from the table ar_payment_schedules_all , you can modify the query how you want to get the details

select aps.*
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE 1 = 1
AND ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
and hc.cust_account_id=21924 --- Here you can give ths customer for whom you want open invoices to be retrieved

How to Cancel PO Requisition created

Cancellation of PO Requisition which is created

Go to PO Super User responsibility --->Reqisition Summary ---> Here open the Requisition Number which you want to cancel and Go to Menu Bar TOOLS Option--->Control and Mention Proper reason and Cancel the Requisition.

Once the Requisition is cancelled it will also Cancel the workflow and the status of workflow will be in Canclled mode.

How To Identify All Requisitions And Purchase Orders With Errored Activities

The following scripts have been provided to return requisitions and purchase orders with erroredactivities:

SELECT prh.segment1, prh.org_id, prh.authorization_status,
ac.display_name activity, ias.activity_result_code RESULT,
ias.error_name error_name, ias.error_message error_message,
ias.error_stack error_stack
FROM po_requisition_headers_all prh,
wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'REQAPPRV'
AND ias.item_key = prh.wf_item_key
AND ias.item_type = prh.wf_item_type
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'REQAPPRV'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date
ORDER BY prh.segment1, ias.execution_time;

--- Purchase Orders

SELECT pha.segment1, pha.org_id, pha.authorization_status,
ac.display_name activity, ias.activity_result_code RESULT,
ias.error_name error_name, ias.error_message error_message,
ias.error_stack error_stackfrom
FROM po_headers_all pha,
wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'POAPPRV'
AND ias.item_key = pha.wf_item_key
AND ias.item_type = pha.wf_item_type
AND pha.authorization_status IN ('IN PROCESS', 'PRE-APPROVED')
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'POAPPRV'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date
ORDER BY pha.segment1, ias.execution_time;

Auto Invoice Script

--Just replace the constant values with variables and call this procedure in a valid cursor loop

CREATE OR REPLACE PROCEDURE inv_proc_v (
asqlcode OUT NUMBER,
asqlerrm OUT VARCHAR2
)
IS
v_batch_source_name VARCHAR2 (100);
v_currency_code VARCHAR2 (10);
v_cust_trx_type_id NUMBER;
v_term_id NUMBER;
BEGIN
BEGIN
SELECT NAME
INTO v_batch_source_name
FROM ra_batch_sources_all
WHERE batch_source_id = 1228 AND org_id = 204;
END;

BEGIN
SELECT currency_code
INTO v_currency_code
FROM fnd_currencies
WHERE issuing_territory_code = 'US' AND enabled_flag = 'Y';
END;

BEGIN
SELECT cust_trx_type_id
INTO v_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = 'Invoice' AND org_id = 204;
END;

BEGIN
SELECT term_id
INTO v_term_id
FROM ra_terms_tl
WHERE NAME = '30 Net';
END;

BEGIN
SELECT set_of_books_id
INTO v_sob_id
FROM gl.gl_sets_of_books
WHERE short_name = 'Vision Operations';
END;

BEGIN
INSERT INTO ra_interface_lines_all
(interface_line_id, interface_line_context,
--ra_batch_sources_all
interface_line_attribute1, interface_line_attribute2,
batch_source_name, --ra_batch_sources_all
set_of_books_id, --gl.gl_setsof_books
line_type, tax_code,
--FND_lookups.tax_type
description, --mtl_system_items
currency_code, --fnd_currencies
amount, cust_trx_type_id,
--ra_cust_trx_types_all
term_id, --ar_terms
conversion_type,
--GL_DAILY_CONVERSION_TYPES.CONVERSION_TYPE
conversion_rate, trx_date,
gl_date, quantity, quantity_ordered, unit_selling_price,
unit_standard_price, inventory_item_id, --MTL_SYSTEM_ITEMS
territory_id,
--ra_territories
uom_code, --MTL_UNITS_OF_MEASURE
created_by, --fnd_global.user_id
creation_date, last_update_date,
last_updated_by, org_id, --fnd_global.org_id
tax_exempt_flag,
--FND_lookups.tax_control_flag
orig_system_bill_customer_id, --hz_customer_accounts
orig_system_bill_address_id, --hz_cust_sites_USES_all
orig_system_sold_customer_id
) --hz_customer_accounts
VALUES (ra_customer_trx_lines_s.NEXTVAL, 'LEGACY',
'1101233', '11045',
'LEGACY', 1, 'LINE', 'Exempt',
'Paper Carrier', 'USD', '1000.00', 1,
1060, 'User', 1, SYSDATE,
SYSDATE, 12, 12, 50.00,
50.00, 6074, 1003,
'Ea', 1318, SYSDATE, SYSDATE,
1318, 204, 'S',
5789,
5808, 5789
);
END;
END inv_proc_v;
/

Importing Blanket Purchase Aggrements(BPA)

Importing Blanket Purchase Aggrements(BPA)
In this article we will see what a Blanket Purchase Agreement is and how we can import them along with the price breaks.

Overview of Blanket Purchase Agreements:You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.

Blanket Releases: You can issue a blanket release against a blanket purchase agreement to place the actual order (as long as the release is within the blanket agreement effectivity dates. If your purchase agreement has price breaks, the quantity entered on the release determines what break price is defaulted into the Price field.

Import Process: The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders.Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.

You need to populate PO_HEADERS_INTERFACE and PO_LINES_INTERFACE to import header and line information into Purchasing. PO_LINES_INTERFACE table contains both line and shipment information, and imports data into both the PO_LINES and PO_LINE_LOCATIONS. The below are the additional columns that are required in PO_LINES_INTERFACE if you want to import price break information:
LINE_NUM
SHIPMENT_NUM

QUANTITY

UNIT_PRIC



If you are importing price break information through catalog quotations, you can also, optionally, populate the following columns in the PO_LINES_INTERFACE table: MIN_ORDER_QUANTITY
MAX_ORDER_QUANTITY
Lets take an example to better understand.Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:
1)quantity = 500, price = 10, effective date from '01-JAN-2006' to '31-JUN-2006'
2)quantity = 500, price = 11, effective date from '01-JUL-2006' to '01-JAN-2007'

To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE

LINE1: It will have only the line information. LINE NUM would be 1.
LINE2: For the first Price Break details but the LINE NUM will be the same as above i.e 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’
LINE3: For the second Price Break details but the LINE NUM will be the same as above i.e 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’ All the line-level records above must have the same INTERFACE_HEADER_ID.
--Inserting Header Information
insert into po_headers_interface
(interface_header_id,
action,
org_id,
document_type_code,
vendor_id,
vendor_site_id,
effective_date,
expiration_date,
Vendor_doc_num)
values
(po_headers_interface_s.nextval,
'ORIGINAL',
204,
'BLANKET',
21,
41,
'01-JAN-2006',
'01-JAN-2007',
'VENDOR04302006');

--Inserting Line Information
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
unit_price,
unit_of_measure,
effective_date,
expiration_date,
ship_to_organization_id,
ship_to_location_id,
PRICE_BREAK_LOOKUP_CODE)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
20,
'Each',
'01-JAN-2006',
'01-JAN-2007',
207,
207,
'NON CUMULATIVE');

Note: Cumulative: Price breaks apply to the cumulative quantity on all release shipments for the item. Non–cumulative: Price breaks apply to quantities on individual release shipments for the item.

--Inserting First Price Break
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
shipment_num,
shipment_type,
quantity,
unit_price,
unit_of_measure,
ship_to_organization_id,
ship_to_location_id,
effective_date,
expiration_date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
1,
'PRICE BREAK',
500,
10,
'Each',
207,
207,
'01-JAN-2006',
'30-JUN-2006');

--Inserting Second Price Break
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
shipment_num,
shipment_type,
quantity,
unit_price,
unit_of_measure,
ship_to_organization_id,
ship_to_location_id,
effective_date,
expiration_date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
2,
'PRICE BREAK',
500,
11,
'Each',
207,
207,
'01-JUL-2006',
'01-JAN-2007');

Final Step:
Run Import Price Catalog Concurrent Program to create this Blanket Purchase Agreement.

How to assign Approval Limits to user

How to assign Approval Limits to user

PO Super User
Setup > Approvals > Approval Assignments
Select Job = .GCSM Delivery Manager. (User job title)
Document Type = Approve Purchase Requisitions
Approval Group = APPROVAL 5000


Once the above process is done then we should run the process 'i2 AP Maintain Web Signing Limits'. after this process the AP Signing Limits table was automatically updated.

Useful Shell Script for Oracle Apps

Shell Script Tutorial for Oracle Apps

Steps to Register Shell Script as a concurrent program
Sample Shell Script to copy the file from source to destination
Basic Shell Script Commands
Steps to Register Shell Script as a concurrent program

step 1:
=======
Place the .prog script under the bin directory for your
applications top directory.

For example, call the script ERPS_DEMO.prog and place it under
$CUSTOM_TOP/bin

step 2:
=======
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called ERPS_DEMO.prog use this:

ln -s $FND_TOP/bin/fndcpesr ERPS_DEMO

This link should be named the same as your script without the
.prog extension.

Put the link for your script in the same directory where the
script is located.

step 3:
=======
Register the concurrent program, using an execution method of
'Host'. Use the name of your script without the .prog extension
as the name of the executable.

For the example above:
Use ERPS_DEMO

step 4:
=======
Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id

Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.

Sample Shell Script to copy the file from source to destination

#Note: If you see # in front of any line it means that its a comment line not the actual code
#** ********************************************************************
# Created By :
# Creation Date :
# Script Name :
# Description : This Script accepts three parameters
# 1)Data File Name 2)Source Directory Path 3)Target Directory Path
# Then copy the file from source location to target location.
# If copy fails send the error status/message to concurrent program so that user can see status.
#
#
# ========
# History
# ========
#
#
#** ********************************************************************
#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters
# $1 : username/password of the database
# $2 : userid
# $3 : USERNAME
# $4 : Concurrent Request ID
DataFileName=$5
SourceDirectory=$6
TargetDirectory=$7
echo "--------------------------------------------------"
echo "Parameters received from concurrent program .."
echo " Time : "`date`
echo "--------------------------------------------------"
echo "Arguments : "
echo " Data File Name : "${DataFileName}
echo " SourceDirectory : "${SourceDirectory}
echo " TargetDirectory : "${TargetDirectory}
echo "--------------------------------------------------"
echo " Copying the file from source directory to target directory..."
cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}
if [ $? -ne 0 ]
# the $? will contain the result of previously executed statement.
#It will be 0 if success and 1 if fail in many cases
# -ne represents not "equal to"
then
echo "Entered Exception"
exit 1
# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success
else
echo "File Successfully copied from source to destination"
exit 0
fi
echo "****************************************************************"


Basic Shell Script Commands

# Create Directory
mkdir

# Remove Directory
rmdir

#remove folder with files
rm -r -f

# Change Directory
cd

# Create new file
vi

#insert data into file
vi
esc i

#Save file
esc :wq enter

# exit with out saving changes
esc :q! enter

# open existing file
vi

#remove file
rm

# copy file with same name
cp /

# copy file with new name
cp / /

# Move file with same name
mv /

# move file with data appended to filename in the front
mv / /`date+%H%M%d%m%y`

#print line
echo "your text here to print"

#print date
echo `date`

query gives all function names under a menu considering submenus's also.

-- Below query gives all function names under a menu considering submenus's also.
SELECT DISTINCT fmep.menu_id,
DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, 'No Func',
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
) funcid,
fff.user_function_name, fff.description
FROM fnd_form_functions_tl fff,
fnd_menu_entries fmec1,
fnd_menu_entries fmec,
fnd_menu_entries fmep
WHERE fmep.menu_id =
(SELECT menu_id
FROM fnd_menus
WHERE menu_name =
'INV_NAVIGATE'
--Change the menu according to your requirement
AND ROWNUM = 1)
AND fmep.sub_menu_id = fmec.menu_id(+)
AND fmec.sub_menu_id = fmec1.menu_id(+)
AND fff.function_id =
DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, -999,
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
)
ORDER BY DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, 'No Func',
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
)

Few Oracle Apps Technical Terms

Oracle Apps Technical Terms

Invoice
Receipt
Customer
Vendor
Buyer
Supplier
Purchase Order
Requisition
ACH: Account Clearance House


Sales Order
Pack Slip
Pick Slip
Drop Ship
Back Order
ASN: Advance Shipping Notice
ASBN: Advance Shipping Billing Notice
ATP: Available to Promise
Lot/Serial Number


DFF: Descriptive Flex Fields
KFF: Key Flex Fields
Value Sets
Organization
Business Unit
Multi Org
Folders
WHO Columns


Oracle Reports
Oracle Forms
Workflow Builder
Toad
SQL Developer
SQL Navigator
Discoverer Reports
XML/BI Publisher
ADI: Application Desktop Integrator
Winscp
Putty

How to RETRY , REWIND and UPDATE ATTRIBUTES for Expense Report Workflows

How to RETRY , REWIND and UPDATE ATTRIBUTES for Expense Report Workflows

RETRY Demo:

Log into Workflow Admin responsibility
Go to Administrator Workflow > Status Monitor
Key in the following values:
Type Internal Name: APEXP
User Key: Expense Report Number

Click Go
Select the Radio button and click on Activity History.


Select the Activity you want to RETRY say “Request Approval from APPROVER” and click on the Retry button on the top.

Click on Submit button in the next screen.

Confirm the details in the next page by clicking the OK button.


REWIND Demo:

Log into Workflow Admin responsibility
Go to Administrator Workflow > Status Monitor
Key in the following values:
Type Internal Name: APEXP
User Key: Expense Report Number

Click Go
Select the Radio button and click on Activity History.

Select the Activity which you want to REWIND for example say “Request Approval from APPROVER” and click on the Rewind button on the bottom.


Choose the activity to which you want the workflow to return in the next screen and select the "Apply" button. This would REWIND the activity.




UPDATE ATTRIBUTES Demo:

In the “Activity History” screen, click on the “Update Attributes” Button at the bottom. This opens up a page with all the attributes where you could update the necessary and hit “Apply” button to save the changes.