Complete Oracle P2P cycle

Lets do a complete P2P Cycle

Step 1) Create Item
Step 2) Create Requisition and Approve it
Step 3) Auto Create RFQ
Step 4) Create Quotation
Step 5) Auto Create PO and Approve it
Step 6) Receipt
Step 7) Auto Invoice Generation (Remember Pay on Receipt should be checked on vendor site before creation of purchasing document and vendor site must be a pay site). Or you can manually create an invoice by using match option on Invoice entry form
Step 8) Validating Invoice and Accounting Creation
Step 9) Make Payment and Create accounting for payments
Step 10) Payables Transfer to General Ledger

Step 1) Lets Create an Item using Purchased template

Navigate to PO> Item> Master Item

Item Creation

Step 2) Create a Requisition for this Item and Approve the same

Requisition Creation

Tables Involved here. Also refer PO Data Flow 

PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

Step 3) Auto Create an RFQ from this requisition

Navigate to PO> Auto Create and query for the requisition # 14930.

Select Document type as RFQ

Auto Create RFQ

Select RFQ as Standard RFQ

Click Create

Click on Supplier and add suppliers who you want to send this RFQ

You can also specify Price breaks and terms

Now change the status of this RFQ active to send it to suppliers

Step 4) Create Quotation

Query RFQ > Tools> Copy Document

Create Quote

Click OK

Now please remember, standard quotation can only be converted to Blanket Agreement through Tools> Copy Document otherwise use AutoCreate form and Requisition to create standard PO.

Bid Quotations can be converted to Standard PO. You can even change the type of quotation here.

Step 5) Create PO

A new Standard PO # 6930 got generated. Approve it

Tables Involved.Also refer PO Data Flow
PO_HEADERS_ALL
PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

Step 6) Receive the PO

Tables Involved.
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
Also refer PO Data Flow

Step 7) Auto Invoice Generation

You can see the "ADS  (Pay On Receipt AutoInvoice)" run automatically because of supplier level setups.

Remember you pay on receipt should be checked on vendor site before creation of purchasing document.

Step 8) Go To Payable responsibilty and click on Invoice> inquiry> Invoice

Click on Action > Validate

Also Manually Approve the invoice

Now again click on Action > Create Accounting

 You will see a successful message

You can also see from tools>View Accounting events

Step 9) Make Payments and create Accounting for Payments

Now payment Click on action and select "Pay in Full"

Select Bank account, Payment Process Profile and Payment Date and save you work

Now create accounting for payments Click Actions > select Creating accounting

A confirmation message will be displayed

Step 10) Run  "Transfer Journal Entries to GL" request

Thanks
Mandeep

Read More»

SQL Query to find out Parameters and Value Sets associated with a Concurrent Program

SELECT
fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.enabled_flag " Enabled Flag",
fdfcuv.required_flag "Required Flag",
fdfcuv.display_flag "Display Flag",
fdfcuv.flex_value_set_id "Value Set Id",
ffvs.flex_value_set_name "Value Set Name",
flv.meaning "Default Type",
fdfcuv.DEFAULT_VALUE "Default Value"
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE
fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name like 'XX%'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = USERENV ('LANG')
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY fdfcuv.column_seq_num;

Read More»

How to make a Oracle form 'Query Only'

There are many ways in which we can make a form Query only. We are going to discuss two
1) Using parameter QUERY_ONLY=YES
2) using Form Personalization
Method 1: Using parameter QUERY_ONLY=YES
Lets take an example of Purchase order enter form

Step 1) Using Sysadmin Responsibility find out the Menu attached to Purchasing Super User responsibility for which you want to make Purchase Order form as read only.

Step 2) Now Navigate to Application>Menu and query for this form and find out the form function associate with enter purchase order form. In our case it is "Purchase Orders"

Step 3) Create a new form function name having logically similar name as that of original function and in the form tab make QUERY_ONLY=YES

Step 4) Attach this new function in the place of old function to the same menu

Save and let Compile Security Request complete

Test ResultNavigate to Purchasing Super User> Purchase Order

See now this form is read only

Method 2 : Using Form Personalization

Step 1) Navigate to PO form>Help>Diagnostic>custom Code>Personalize

Step 2) Now here you can disable Update, Delete and Insert

Test Case : Check PO form now

Thanks

Mandeep

Read More»

Important WF tables

WF_USER_ROLE_ASSIGNMENTS stores information about the direct and inherited assignments of users to roles in Oracle .

WF_USER_ROLES stores associations of users with the roles of which they are members.

WF_ROLES stores Roles, which can have one or more users as members.

WF_LOCAL_ROLES stores role information, including a user flag to mark those roles that also represent individual users. This table contains columns similar to those required in the WF_USERS and WF_ROLES views.

WF_LOCAL_USER_ROLES stores information about the associations of users with roles. This table contains columns similar to those required in the WF_USER_ROLES view.

WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modeled workflow process, which can be included as an activity in other processes to represent a sub process. 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_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.

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.

WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.

WF_MESSAGES contains the definitions of messages which may be sent out as notifications.

WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

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

WF_ITEM_TYPES: defines an item that is transitioning through a workflow process.

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_ITEM_ACTIVITY_STATUSES is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates.

WF_ITEM_ACTIVITY_STATUSES_H  stores the history of the WF_ITEM_ACTIVITY_STATUSES table.

WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process.

WF_ACTIVITY_TRANSITIONS defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window.

WF_ACTIVITY_ATTR_VALUES contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute.

Thanks
Mandeep

Read More»

R12 SQL Query to differentiate Customer and Suppliers Data

select * from (
select hp.party_number,hp.party_name,hp.status,decode(nvl(hpu.party_usage_code,hp.party_type),'ORGANIZATION','CUSTOMER',nvl(hpu.party_usage_code,hp.party_type)) party_type 
from  apps.HZ_PARTY_USG_ASSIGNMENTS hpu, hz_parties hp
where hp.party_id = hpu.party_id(+))
order by party_type

Read More»

Important HRMS Tables

• per_all_people_f 

This table is used to store employee related data.

• per_all_assignments_f

This table store employee assignment related information and connected to per_all_people_f with person_id and assignment number.

• per_addresses

This table store  information related to employee address form and  connected to per_all_people_f with person_id.

• per_pay_proposals

This table will store information related to employee salary form.

• per_person_types_tl

This table store type of the employee information and connected to per_all_people_f with the person_type_id.

• per_jobs_tl

This table is for JOBS in Oracle and can be linked to per_all_assignments_f.

• per_grades_tl

This table stores various types of GRADES and can be linked to per_all_assignments_f.

• hr_locations_all

Location table

• pay_all_payrolls_f

PAYROLLS Table and can be linked to per_all_assignments_f table

• per_pay_bases

Contain the PAY BASES can be linked to  per_all_assignments_f table.

• per_assignment_status_types_tl

Contain the assignment types and linked to per_all_assignments_f table

• per_person_type_usages_f

This table store the person type of the employee. 

Read More»

Responsibilites and their associated Menus

SELECT DISTINCT frt.responsibility_name, fmt.user_menu_name
  FROM apps.fnd_responsibility_tl frt,
       apps.fnd_responsibility fr,
       apps.fnd_menus_tl fmt,
       apps.fnd_menus fm,
       apps.fnd_application_tl fat,
       apps.fnd_application fa
 WHERE     frt.responsibility_id(+) = fr.responsibility_id
       AND fr.menu_id = fmt.menu_id
       AND fr.menu_id = fm.menu_id
       AND fat.application_id = fa.application_id
       AND fa.application_id = fr.application_id
       AND frt.LANGUAGE = 'US';

Read More»

Request Group assigned to Responsibilty

SELECT    responsibility_name responsibility,request_group_name,frg.description 
FROM       fnd_request_groups frg, fnd_responsibility_vl frv 
WHERE     frv.request_group_id = frg.request_group_id 
ORDER BY responsibility_name; 

Read More»

Responsibility Assigned to Users

SELECT  DISTINCT 
            u.user_id ,SUBSTR (u.user_name, 1, 30)  user_name 
           ,SUBSTR (r.responsibility_name, 1, 60)     responsibility 
           ,SUBSTR (a.application_name, 1, 50)       application 
FROM    fnd_user u, 
           fnd_user_resp_groups g, 
           fnd_application_tl a, 
           fnd_responsibility_tl r 
WHERE  g.user_id(+)                         =   u.user_id 
AND      g.responsibility_application_id  =   a.application_id 
AND      a.application_id                     =   r.application_id 
AND      g.responsibility_id                  =   r.responsibility_id 
ORDER BY SUBSTR (user_name, 1, 30), 
SUBSTR (a.application_name, 1, 50), 
SUBSTR (r.responsibility_name, 1, 60); 

Read More»

List of Responsibilities in an Instance

SELECT (SELECT application_short_name
FROM   fnd_application fa
WHERE fa.application_id = frt.application_id) application,
          frt.responsibility_id, frt.responsibility_name
FROM  apps.fnd_responsibility_tl frt;

Read More»

How to determine Oracle Database And OS version

You can use following two queries to find out the Apps/Database/OS version

select * from v$version;

select * from product_component_version;

Thanks
Mandeep

Read More»

Session Timeout in Oracle Apps

This post is about how to increase your session time out for oracle application. Sometime its very annoying that session time out is set at very low value. You can increase it for your login by setting profile option "ICX: Session Timeout" from sysadmin responsibility

Please navigate to Sysadmin > Profile > System and query for "ICX: Session Timeout" after checking both at Site and User checkbox and increase the value of User.

Thanks
Mandeep

Read More»

Interface Trip Stop Functionality

There are typically four interface program get triggered at the time of ship confirm. They are 

• Interface Trip stop
• Packing Slip Report
• Bill of Lading
• Invoice

Interface Trip Stop is one of the main interface programs which can be triggered at the time of ship confirm or later as Interface Trip stop - SRS concurrent request.

Interface trip stop has two parts

• Order Management Interface (First part)
• Inventory Interface (Second part)

Order Management Interface part update the order management tables like OE_ORDER_LINES_All (Update Shipped_Quantity, Shipping_Quantity, Actual_Shipment_Date) and also WSH_DELIVERY_DETAILS (make Released_status, OE_INTERFACED_FLAG = ‘Y’) table.

The Inv_Interfaced_Flag remains = X (Pending) or N which will changes to Y after execution of second part

The second part i.e. Inventory Interface will be triggered only if the value of OE_INTERFACED_FLAG = ‘Y’ otherwise second part will not be triggered. Even if you try to run this from SRS, these lines will not be picked for processing. 

Inventory Interface part move data from MTL_TRANSACTIONS_INTERFACE to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction. Also data is deleted from MTL_DEMAND, MTL_RESERVATIONS and WSH_NEW_DELIVERIES. Item quantity reduced from MTL_ONHAND_QUANTITIES and 

MTL_TRANSACTION_ACCOUNTS is updated with accounting information.

Both these parts are executed simultaneously.

Read More»

Shipping Parameters Quick Reference

Lets see Oracle Order Management Shipping parameter setups. Please note shipping parameters are inventory org specific where as Global parameters are not.

1) General Tab

#Weight UOM Class: Default Weight UOM class used in shipping from this Organization V1.
#Volume UOM Class : Default Volume UOM class used in shipping from this Organization V1.
#Percent Fill Basis : Select Percent fill basis from Quantity, Volume or Weight. This is the used to determine whether or not container met there minimum fill percentage.
For Weight or Volume, the calculation uses the physical attribute of item and container in Inventory and for Quantity, the calculation uses the Container Load Relationship details to determine the maximum number of items that will fit into the container.

#Export Compliance Screening: is used to enforce export compliance screening at Delivery Creation, Ship Confirm, or At Delivery creation and Ship Confirm
#Enable Workflow : This is used to enable workflow for delivery. You can configure workflow to achieve specific task like sending notification for over ship or backorder to a user
#Raise Business Events: This check box is used to enable business events.
#Enable Ship to Deliver Workflow: If you want to use ship to deliver workflow then enable this box here

2) Pick Release

# Release Sequence Rule : You can define release sequence rule at OM>Setups>Shipping>Picking>Define Release Sequence Rule. This is used to specify the Order in which delivery lines are allocated inventory.
# Pick Slip Grouping Rule: You can define release sequence rule at OM>Setups>Shipping> Pick Slip Grouping Rule. This rule specify how release delivery lines are grouped on Pick slip. Both above rules are default only and you can change them during pick release
#Print Pick Slip:  This specify when you want to print pick slip.
At the End: Pick slip will be printed at the end of the pick release process.
Immediate : In this case you must specify the "Number of Pick Slip lines". Once the threshold is reached it will start printing
# Number of Pick Slip lines:  This is used specify the number of lines will print on each pick slip and is mandatory when Print Pick slip is set to Immediate.
# Default Pick Release Document set: This is the default document set which is printed for pick release. You can change its value at the time of pick release.
# Default Stage Subinventory/Locator: Pick Confirm (Transact move orders) transfer material to this subinventory/Locator.
# Auto Create Deliveries: If this is selected then pick release auto create deliveries based on delivery grouping rules. You can override this option at the time of pick release.
# Auto Allocate: If this is selected then pick release automatically create and transact move orders. You can override this option at the time of pick release
# Enforce Ship Set and Ship Model: If this is selected then all ship lines in ship set or ship model will be released nor back ordered together.
#Plan Task: this is enabled if Warehouse management is used.
#Pull Replenishment: This is used to fill up a locator up to maximum capacity when there is a shortfall. this is also used with WMS.
# Retain Unstated Quantity during over pick:This is used to retain unstaged quantity during over shipment. We will see the effected of this in another post.

3) Shipping Transactions

# Default Ship Confirm Document Set: This is a document which are printed at ship confirm.
# Ship Confirm Rule: This is default auto ship confirm rule.
#Auto pack Option: If this is Yes then delivery lines are packed based on item container relationship
and if this is set as Autopack master then container further packed in another container such as frieght container for transport.
#Good Dispatch Account : The sales order issue transaction uses this account if the Oracle Order Management workflow cannot determine one.
#Enforce Packing in Containers: if yes, ship confirm displays a warning when confirming a delivery or trip with unpacked delivery line items.

4) Delivery

# Delivery  Grouping Attribute: This is used to select attributes for grouping of delivery lines.
#Autocreate Delivery Criteria: If you have enabled auto create delivery in pick release tab then thi sis used specify the criteria for that. Across Order means all selected delivery lines that match on the Delivery Grouping Attributes across orders are eligible to appear on one delivery.
#Append Limit: This is used to indicate the point at which you want to stop the system from adding lines to a delivery.

If you are using Oracle transportation Management then use other parameters

Thanks
Mandeep

Read More»

Copy Inventory Organization

Copy Inventory Organization in Oracle Apps is used to create a new inventory organization from exiting model organization. You can create multiple Inventory organizations with very little effort which is otherwise very tedious process during implementation.

You can optionally copy Organization Definition, Shipping network, Item and its attributes, Department, Resources, Routings, BOM.

Please note Subinventories, Locators, WIP Parameters, WIP accounting classes, Planning Parameters, and Shipping Parameters data from your model organization are always copied.

Copy Inventory Organization works only within same operating unit and new org created under same Business group, Operating unit, Legal entity and Ledger as that of model organization.

Pre-requisites 

1) Please define at least one inventory organization and all its steps. Please note you cannot use master organization as Model organization.
2) It is recommended to complete BOM setups if you want to copy that also
3) Location for new inventory organization. We will use "Test Location" in our demonstration.

Steps
1) Maintain Interface
2) Launch Organization Copy Program


Step 1) Maintain Interface step is used to put the data in Copy Inventory Organization interface tables.

Navigate to Inventory>Setup>Organization>Organization Copy>Maintain Interface

# Group Code: This is used to identify new org code and is one of the mandatory parameter Copy Organization Program. You can define multiple records under this Group Code.
# Organization Name: Please enter a name for new organization which will be created after running Copy Organization Program
# Organization Code: This is used to identify new org
# Location Name: Location for the new org. As mentioned before, we will use "Test Location" in our demonstration.

Now click on Populate Interface Table. A Confirmation Message will be displayed.

After Clicking ok, you can enter another inventory org which you want to create and use this form as many times as you want. You can also use this form to find previous records entered e.g,

The Status field here shows the status after running the program (Whether it is successful or failed). Blank mean the records are yet to be processed.

Step 2) Launch Organization Copy Program

Next step is to populate base tables by running Launch Organization Copy Program.

#Take model Organization as M1
#Group Code: use "Copy Test"
#Assign to Exiting Organization:  If yes, then new org will be assigned to same hierarchy as model org.
#Copy Shipping Networks: If yes the model org shipping network setup copied to new org.
#Copy Item: If yes, all item assigned to model org copied to new org
# Validate Item: Used to check inconsistencies before copied to new org.
# Copy BOM: BOM and its parameters will get copied to new org
# Copy Routings: If defined in model org, all departments, Resources and Routings will be copied to new org
# Purge: If yes then group code will be purged after running this program.

Click OK and Submit

Thanks
Mandeep

Read More»