O2C Order Status in Lines & Shipping in Back End & Front End

Status	Lines-Back End	Front End	WSH-Back End	front end	Triggered Programs
Entered	ENTERED	ENTERED	-		-
Booked	AWAITING_SHIPPING	AWAITING_SHIPPING	R	Ready to Release
Pic Release	AWAITING_SHIPPING	Awaiting Shipping	S	Released to Warehouse	Pick Selection List Generation,Pick Slip Report
Pick Confirm	AWAITING_SHIPPING	Picked	Y	Staged/Pick Confirmed
Ship Confirm	AWAITING_SHIPPING	Picked	C	Shipped
After Runnning Trip Stop	SHIPPED	Shipped	C	Interfaced	run Interface Trip Stop - SRS
After Work flow Bgnd  Process	CLOSED	Closed	C	Interfaced	Workflow Background Process
Invoice	CLOSED	Closed	C	Interfaced Delivery Level='Closed' Trip Level='Closed'	Autoinvoice Master Program, Autoinvoice Import Program, Prepayment Matching Program (Prepayments Matching Program)

Oracle Applications : Opening Periods

What is Period:

1. An interval of time characterized by the occurrence of a certain condition, event, or phenomenon: a period of economic prosperity.

2 Geology A unit of time, longer than an epoch and shorter than an era.

Significance Of Opening Periods in Oracle apps:

TO OPEN MONTHLY PERIODS OF DIFFERENT MODULES OR NEXT YEAR AND FOLLOW THE BELOW STEPS

1. HOW TO OPEN INVENTORY PERIODS (INV) :

Follow the below given navigation path :

INVENTORY - > ORACLE INVENTORY - > ACCOUNTING CLOSE CYCLE - > INVENTORY ACCOUNTING PERIODS

2. HOW TO OPEN PAYABLE PERIODS (AP):

Follow the below given navigation path :

PAYABLES - > ORACLE PAYABLES - > ACCOUNTING - > CONTROL PAYABLES PERIODS

3. HOW TO OPEN RECEIVABLE PERIODS (AR) :

Follow the below given navigation path :

RECEIVABLES - > ORACLE RECEIVABLES - > CONTROL - > ACCOUNTING - > OPEN/CLOSE PERIODS

4. HOW TO OPEN GENERAL PERIODS (GL) :

Follow the below given navigation path :

GENERAL SUPER LEDGER - > SETUP - > OPEN/CLOSE

5 Navigate to Purchasing SuperUser->Setup->Financials->Accounting->Control Purchasing Periods , then enter the Fiscal year and click Go button.

(If it prompts for selecting Operating Unit,select it and click Go button.)

Query to get whether accounting period or not?

SELECT   application_id
into     g_application_id
  FROM   fnd_application_tl
 WHERE   application_name = 'Receivables'


SELECT   SET_OF_BOOKS_ID INTO P_SOB_ID FROM GL_SETS_OF_BOOKS

P_ACCOUNTING_DATE IS YOUR period

FUNCTION validate_gl_accounting_date (p_accounting_date   IN DATE,
                                      p_sob_id            IN NUMBER)
   RETURN BOOLEAN
IS
   v_count   NUMBER := 0;
BEGIN
   SELECT   COUNT ( * )
     INTO   v_count
     FROM   gl_period_statuses gps
    WHERE       gps.application_id = g_gl_application_id
            AND gps.set_of_books_id = p_sob_id
            AND gps.closing_status IN ('O', 'F')
            AND p_accounting_date BETWEEN NVL (gps.start_date,
                                               p_accounting_date)
                                      AND  NVL (gps.end_date,
                                                p_accounting_date);

   IF v_count > 0
   THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END validate_gl_accounting_date;

Run Concurrent Program from Form: Launch SRS Built In

Run Concurrent Program from Form: Launch SRS Built In

Form Personalization to run Concurrent Program from Form 

Built-in : Launch SRS Program.

Example :

Our Requirement is to Run Production Batch Sample Label from Production Batch Form.

We could completed this requirement through Form Personalization in below steps.

A)-Define 1 Menu : Special Menu for the Report, that shall show in tools Menu bar.

B)-Attach Concurrent Program menu with this Special Menu (defined in the above step).

C)- Store batch id ( For parameter to run your concurrent) in one Global variable.

D)- In Concurrent Definition window , Assign Global variable value to the Parameter for Batch id.

Here are the screen shots.

Requirement is like Below.

Goto Help --> Diagnostics --> Custom Code --> Personalize.

Add 1 New Sequence for You personalization, and Select Event as When-New-Form-Instance.

Goto Actions, and Choose any 1 special Menu.

Once menu is define, we need to attach Concurrent program to this menu.

Create new Sequence, for attaching The concurrent program with special menu defined in above step.

Goto Actions, here we need to define 2 Actions

1)- Global Variable to store Batch ID for Parameter.

2)- Attach Concurrent program to the Special Menu.

Global Variable name should be Logical and unique, as this needs to be pass to your concurrent program.

Save your work, Till here personalization is done for Concurrent program is attached and run, now Concurrent program will be run , but we have to assign Parameters manually, it will not pick any parameter automatically.

Step-3, To pass parameters from Batch form automatically.

GO TOà System adminà applicationà Concurrentà define

Now we have to attach Global variable from above steps to Concurrent Program.

Go to Parameters, and then pass global variable from the earlier form i.e, Batch Form.

Select Batch id parameter.

Its Default Type Should be SQL Statement.

Then Pass the 

“select :GLOBAL.XX_BATCH_ID from dual”

Now try to run report from Batch form,

How to call a Concurrent Program from a Special Menu Item?

Well, we can run our concurrent programs from a Special Menu Item, and if you have a requirement of this sort, you can use the steps below to use Form Personalization Builtin to achieve this task in couple of minutes.

1] First create the Special Menu Item wherever required through Form Personalization. Here I have added a Special Menu Item called ‘Assign Item to a Subinventory’ to the Form-‘INVIDITM’. For that assign the Trigger Event as ‘WHEN-NEW-FORM-INSTANCE’.

2] In Actions Tab, choose the type as ‘MENU’ and select the Menu Entry and give a Proper Menu Label.

3] Create the Concurrent Program which you want to attach to this custom menu item. Once created, assign the program to the Request Group of the Responsibility. Also it is required to add the ‘Lunch SRS Form’ Function (Requests: Submit) to the main menu of that responsibility. If you don’t do this step you may get Form Personalization error in later steps.

4] Create one more entry in Form Personalization window with Trigger Event as your custom menu item.

5] Here select the Actions Type as ‘Builtin’ and Builtin Type as ‘Lunch SRS Form’. In the program name give the name of your concurrent program.

6] Validate and Apply Now

Once done, you will be able to view the custom menu item in the Form and when you will click it, it opens the SRS Form with your concurrent program.

Basic Form Personalization steps in oracla apps

Basic Form Personalization steps in oracla apps

Oracle has provided a simple and easy feature to implement the customer specific requirements without modifying the underlying forms code or CUSTOM library. Although CUSTOM library still can be used for forms customization to implement the complex business logic, the personalization feature provided in the latest release is easy, faster and requires minimum development effort.

If user needs to personalize any details in a form like changing field names, adding some validations, etc, then Form Personalization can provide the way out in few simple steps.

Please follow here to go through Steps with example

Steps:

Step – 1:

Go to the specific form where you need the changes

Then we need to note down the block and field name which is needed in next steps.

So keep the cursor on the required field and then Go to Help --> Diagnostics -->Examine

Note down the Block and Field value.

Step -2:

Now the step to do the personalization

Follow this navigation

Help à Diagnostics à Custom Code à Personalize

This will show the Form Personalization window

Here you need to do the customization as per the requirement.

As you can see in this form, there are mainly 4 sections

1.       Rule

2.       Condition

3.       Actions

4.       Context

Step – 3:

Rule:

This is the section where you need to create the rules how your requirement is going to be executed. This section contains 4 segments, Seq, description, Level and Enabled flag.

Seq : Need to give the Sequence how the rule will execute (a number)

Description: Write a sort description of the rule which you are going to create

Level: Select the level where you want the rule to execute

Enabled: This is the flag which will enable or disable the rule.

Example:

Step – 4:

Condition:

Now need to set the condition.

This is the section where you will define when the rule will be executed.

This section contains 4 segments, Trigger Event, Trigger Object, Condition and Processing mode.

Trigger Event: This specifies where the rule will be executed. There are mainly 5 types of event which decided the trigger of the rule.

Trigger Object:  This is the Object in the form which decides at what level the rule will be executed.

The values can be “<Form Name>”, ‘<Block Name>” or “<Item Name>”

Condition: Here mention any SQL statement to control the execution of the rule when the mentioned criterion is met.

Processing Mode: This is the mode where you want the rules to be executed.

Example:

Step – 5:

Actions:

This decides what will happen when the rule will execute.

This contains 2 sections.

Left sections contains Seq, Type, Description, Language, Enabled

Seq: Mention the Action sequence which this rule will execute

Type: This is of 4 types, Property, Message, Builtin, Menu.

This decides which type of action, the rule will perform.

According to this the right section will come and need to fill the detailed actions.

Description: Write a description on the action which this will perform.

Language: Select the language

Enabled: You can enable or disable the actions here.

Right Section is a dynamic section; it appears depending on the Type field value in Left section.

Action Types:

Property

The action type “Property” is used to set the properties of the objects. The various objects include

“Item, Window, and Block etc.”.  Actual object name defined in the form should be entered after selecting the object type.

Property Name is the property to be changed

Value is the new desired value for the property.

The current value of the property will be displayed when clicked on the “Get Value” button.

Example:

Object Type = “Item”

Target Object = “:ORDER.ORDER_NUMBER”

Property Name = “PROMPT_TEXT”

Value = “Claim Number”

The value is interpreted at runtime, so you can use SQL functions and operators. Any value started with “=” operator will be interpreted at runtime; otherwise the value is treated as is entered in the value field

Message:

The action type “Message” is used to display custom messages during runtime. Message Type and Description should be entered after selecting the action type as “Message”. The available message types are “Error, Warning, Hint, Question and Debug”. Depending on the response expected from the user, the appropriate message type should be selected.

Example:

Message Type = “Hint”

Message Text = “Please Follow the Date format DD-MON-YYYY”

Builtin:

The action type “Builtin” is used to execute the form and AOL API’s. Depending on the API type selected, the parameters should be entered.

Example:

Builtin Type = FND_UTILITIES.OPEN_URL

Argument = http://www.oracle.com

Menu:

The action type “Menu” is used to activate the available special menus on the Tools menu.

Oracle Applications provide 45 special menus under Tools menu which can be used by customers based on their requirements.

Select the SPECIAL menu which is not used by the form. Menu label is the prompt which appears to the users when Tools menu is invoked, block specifies the blocks for which the special menu should be activated and Icon name is the .ico file name.

A separator can be created above the activated special menu by selecting the “Render line before menu” checkbox.

Example:

Menu Entry = SPECIAL4

Menu Label = Additional Order Header Information

Enabled in Blocks(s) = ORDER

Icon Name = Flower.ico

Step – 6:

Context:

Context manages to whom the personalization should apply. This is similar to the concept of using profile options in Oracle Applications. The various levels are Site, Responsibility, Industry and User. During runtime, the values provided in the context are evaluated and personalization rules will be applied. Usage of context is very vital in implementing the personalization to prevent the inappropriate users accessing these customizations of the form.

Example:

Context = Responsibility

Value = Order Management Super User

STEP BY STEP ANALYSIS

Requirement: Don’t allow the user to use more than 1 qty in Order line Qty field during creation of a sales order.

Solution:

Step -1:

Go to the specific form where you need the changes

Here in this case this will be the Sales Orders form and then Line Items tab.

Then we need to note down the block and field name which is needed in next steps.

So keep the cursor on the required field and then Go to Help à Diagnostics à Examine

Note down the Block and Field value.

Step -2:

Help --> Diagnostics --> Custom Code --> Personalize

Step – 3:

Fill the Rule section

Step – 4:

Fill the Condition section

Step – 5:

Fill the Actions section

Step – 6:

Fill the context As you need.

Save this.

Log out of the application and login again, then in Sales Order line item tab enter Qty field value as 1 and try to save, you can see the message.

Important Oracle Apps Tables

Trading Partners Tables
=======================
select * from ece_tp_headers;
select * from ece_tp_details;

db objects Tales
====================
select * from dba_objects;
select * from user_objects;
select * from all_source;

Server Directories Tables
===========================
select * from v$parameter where name like '%utl_file%';

URL table
===============
select * from icx_parameters;

Instance Name Table
===================
 select INSTANCE_NAME  from V$INSTANCE;

Interface Tables/Programs in Oracle Apps R12

Interface Tables/Programs in Oracle Apps R12

=======================================================================
PO Requisition Creation
=======================================================================
## Interface Tables:

PO_REQUISITIONS_INTERFACE_ALL

## Error Table
PO_INTERRFACE_ERRORS

## Base Tables:

PO_REQUISITIONS_HEADERS_ALL                      
PO_REQUISITION_LINES_ALL      
PO_REQ_DISTRIBUTIONS_ALL

## Oracle Seeded Concurrent Program

   Requisition Import
 
=======================================================================
Purchase Orders Creation
=======================================================================
## Interface Tables:

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE

## Error Table
PO_INTERRFACE_ERRORS

## Base Tables:

PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL

## Oracle Seeded Concurrent Program

Import Standard Purchase Orders

=======================================================================
Sales Orders Creation
=======================================================================
## Interface Tables:

OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL

## Base Tables:

OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL

## Oracle Seeded Concurrent Program

 Order Import

=======================================================================
Price List Creation
=======================================================================
## Interface Tables:

QP_INTERFACE_LIST_HEADERS
QP_INTERFACE_LIST_LINES
QP_INTERFACE_PRICING_ATTRIBS

QP_INTERFACE_ERRORS

## Base Tables:

QP_LIST_HEADERS
QP_LIST_LINES
QP_PRICING_ATTRIBUTES

## Oracle Seeded Concurrent Program

QP: Bulk Import of Price List

=======================================================================
Customers Creation
=======================================================================
## Interface Tables:

RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL


## Base Tables:

HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_CUST_ACCOUNTS_ALL
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUSTOMER_PROFILES
HZ_CUST_PROFILE_CLASSES
HZ_PARTY_RELATIONSHIPS
HZ_CONTACT_POINTS
HZ_ORG_CONTACTS

## Oracle Seeded Concurrent Program

Customer Interface

=======================================================================
Items Creation
=======================================================================
## Interface Tables:

MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
MTL_INTERFACE_ERRORS

## Base Tables:

MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES
   
## Oracle Seeded Concurrent Program

   Item Import
=======================================================================
Suppliers Creation
=======================================================================
## Interface Tables:

AP_SUPPILERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACTS_INT
AP_SUPPLIER_INT_REJECTIONS

## Base Tables:

AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

## Oracle Seeded Concurrent Program

Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import