Script to Clear Orders Stuck in Workflow @ Releasing

If you have some order lines in booked status and the ship line activity is in error instead of ‘Notified’ then use the following script (But check on Development server first). Pass the line_id for one of the line from problematic order and check if this will progress the line to ‘Awaiting Shipping’ and the work flow activity to Notified status.

set serveroutput on
Declare

l_line_id NUMBER := &line_id; /* Order Line Id*/
l_org_id NUMBER; /* Organization Id*/
l_count NUMBER;
l_activity_id NUMBER;
l_result VARCHAR2(30);

Begin

OE_Standard_WF.OEOL_SELECTOR
(p_itemtype => ‘OEOL’
,p_itemkey => to_char(l_line_id) /* Order Line Id*/
,p_actid => 12345
,p_funcmode => ‘SET_CTX’
,p_result => l_result
);

select activity_id
into l_activity_id
from wf_item_activity_statuses_v
where item_type = ‘OEOL’
and activity_name = ‘SHIP_LINE’
and item_key = to_char(l_line_id)
and activity_status_code = ‘ERROR’;

wf_item_activity_status.create_status(’OEOL’,to_char(l_line_id),l_activity_id,wf_engine.eng_notified,wf_engine.eng_null,SYSDATE,null);
commit;
End;
/

What Notification Types are available in Oracle Service

The following Notifications are available:”You can set up the application to automatically notify the service request owner and customer contacts associated with the service request whenever a certain event occurs, for example, whenever a service request is created, closed, or reassigned. Notifications can be sent via Oracle Workflow notifications or by e-mail.

Oracle TeleService includes a notification workflow and notification templates suitable for different types of events, but implementers must set up the rules that trigger the workflow. The rules make it possible to selectively notify users only about events of interest to the organization. This notification functionality enhances and replaces notifications through the Call Support Process workflow which can be associated with Service Request Types. The Call Support Process notifies users each and every time a service request is updated. This release includes one seeded notification rule: If service request status changes to closed, then notify the primary contacts of related service requests.

The following table describes the events that can trigger a notification message to be sent and the scope of the rule you can create.

1. Contact Added to Service Request

2. Published Solution Added to Service Request

3. Service Request Status Changed

4. Service Request Created

5. Service Request Owner Changed

6. Relationship Created

7. Relationship Removed

8. Service Request Updated

The Notifications that are available in Oracle Service can be set up via the Customer Support -> Setup -> Service Requests -> Notification Rules form.

Reference

Metalink

Oracle® TeleService Implementation Guide

How to start the Workflow Mailer Service Using Oracle Application Manager (OAM)

1.    Login to OAM

2.    Navigate to Workflow

3.    Click on Service Components you will find a new page with item name “Workflow Notification Mailer” and Container  “Workflow Mailer Service”

4.    Under column container click on Workflow Agent Listener Service

5.    From drop down list at the end of the line , choose start then click on Go button

6.    Once service container are activated , all service components with startup mode automatic will be up and running

How to Delete Stock Locators by Using API

Account Receivable to General Ledger Transfer

Account Receivable Transfer to General ledger

Source Oracle Account Receivable User Guide

Key ERP Leadership Positions and Pre-Requisite

How to enable multiple logins per Single user

Perform the following steps to enable the multiple logins per user id:

  1. Login as Workflow Web Administrator Responsibility –> Click Administration.
  2. Please note the user name in the “Workflow System Administrator” field.
  3. Logout from Oracle Applications.
  4. Now Login with the above mentioned user name.
  5. Select Workflow Web administrator Responsibility –> Click Business Events.
  6. Search for the Business event oracle.apps.icx.security.session.created
  7. Click on the icon from update column
  8. Set the status of the event to disable
  9. Retest the multiple logins per user id.

How to restrict single session per user in Oracle EBS

How to Apply Security on Form By Using Customized Profile Option and Forms Personalization.

how-to-apply-security-on-form-by-using-customized-profile-option-and-forms-personalization

By using This Document you can create your own profile options and apply security or fulfill other requirements as well

How to Create Stock Locators By using API

Calculate Price Flag in Oracle Order Management

Price Flag indicates the control for which the price is frozen

Calculate Price Flag in Oracle Order Management

CRM and Order Management Integration

Order Entry had a separate entity: SO_LINE_SERVICE_DETAILS to store install base information associated with product sales order lines. You had to run the Service Interface concurrent program to then communicate the install base information to Oracle Service. This program is now obsolete. Order Management integrates with the various CRM products (IStore, Telesales, Quotes etc) via Order Capture. Any changes to the Order Object are communicated on line to Order Capture via the ASO_ORDER_FEEDBACK_PUB.UPDATE_NOTICE API. Order Capture in turn publishes the information to a queue that all the interested CRM products poll.

An Oracle White Paper

How to add new Attachment Category to a Form (Sale Order)

How to Exract Service Activity LOV Detail

SELECT cs_txn_billing_oetxn_all.org_id, cs_transaction_types_vl.NAME,
cs_transaction_types_vl.description,
cs_txn_billing_types.billing_type,
cs_transaction_types_vl.revision_flag,
cs_transaction_types_vl.new_cp_return_required,
cs_transaction_types_vl.new_cp_status_code,
cs_transaction_types_vl.installed_cp_return_required,
cs_transaction_types_vl.installed_status_code,
cs_txn_billing_types.txn_billing_type_id,
cs_transaction_types_vl.no_charge_flag,
cs_txn_billing_oetxn_all.order_type_id, csd_repairs.repair_line_id,
cs_txn_billing_oetxn_all.line_type_id,
cs_transaction_types_vl.line_order_category_code line_category_code,
cs_bus_process_txns.transaction_type_id
FROM csd_repairs,
csd_repair_types_b,
cs_bus_process_txns,
cs_transaction_types_vl,
cs_txn_billing_types,
cs_txn_billing_oetxn_all
WHERE csd_repair_types_b.repair_type_id = csd_repairs.repair_type_id
AND cs_bus_process_txns.business_process_id =
csd_repair_types_b.business_process_id
AND cs_transaction_types_vl.transaction_type_id =
cs_bus_process_txns.transaction_type_id
AND (TO_DATE (SYSDATE) BETWEEN NVL
(cs_bus_process_txns.start_date_active,
TO_DATE (SYSDATE)
)
AND NVL (cs_bus_process_txns.end_date_active,
TO_DATE (SYSDATE)
)
)
AND cs_transaction_types_vl.transaction_type_id =
cs_txn_billing_types.transaction_type_id
AND cs_txn_billing_types.billing_type = ‘M’
AND cs_txn_billing_oetxn_all.txn_billing_type_id =
cs_txn_billing_types.txn_billing_type_id
– AND cs_transaction_types_vl.line_order_category_code = ‘RETURN’
AND NVL (cs_transaction_types_vl.depot_repair_flag, ‘N’) = ‘Y’
ORDER BY cs_transaction_types_vl.NAME

How to Check Order In Workflow Error and Not Bookable

By Using Following Query you can extract Order Number and Other Details which are in Work flow Error

SELECT h.order_number, h.org_id “Operating Unit ID”, h.ordered_date,
h.header_id, h.cancelled_flag, h.open_flag, h.flow_status_code,
h.creation_date, h.last_update_date,
(SELECT COUNT (1)
FROM oe_order_lines_all l
WHERE l.header_id = h.header_id AND open_flag = ‘Y’) “Order Open”
FROM oe_order_headers_all h, wf_items wi
WHERE wi.item_type = ‘OEOH’
AND wi.item_key = TO_CHAR (h.header_id)
AND h.open_flag = ‘Y’
AND NOT EXISTS (
SELECT ‘WF is pending’
FROM wf_item_activity_statuses wias
WHERE wias.item_key = TO_CHAR (h.header_id)
AND wias.item_type = ‘OEOH’)

How to Change LOV Using Forms Personalization

How to Get Detail of Locks with Object Locked

SELECT VLO.OS_USER_NAME “OS USERNAME”, VLO.ORACLE_USERNAME “DB USER”,
VP.SPID “SPID”, AO.OWNER “OWNER”, AO.OBJECT_NAME “OBJECT LOCKED”,AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL
) “MODE OF LOCK”,
VS.STATUS “CURRENT STATUS”
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

How to Call Customized Function in Personalization

Tables Used in Auto Invoice Master Program

When the Auto invoice program is run, data is populated into the tables listed below.

1. RA_BATCHES_ALL

2. RA_CUSTOMER_TRX_ALL

3. RA_CUSTOMER_TRX_LINES_ALL

4. RA_CUST_TRX_LINE-GL_DIST_ALL

5. RA_CUST_TRX_LINE_SALESREPS_ALL

6. AR_RECEIVABLE_APPLICATIONS_ALL

7. AR_PAYMENT_SCHEDULES_ALL

8. AR_PAYMENT_SCHEDULES_ALL

9. RA_INTERFACE_ERRORS_ALL

Query to check Invoices in Error

SELECT ria.line_type, ria.description, ria.currency_code, ria.amount,

ria.trx_date, ria.quantity, ria.quantity_ordered, ria.sales_order,

ria.sales_order_line, ria.sales_order_date, ria.inventory_item_id,

riea.interface_line_id, riea.MESSAGE_TEXT, riea.org_id

FROM ra_interface_errors_all riea, ra_interface_lines_all ria

WHERE riea.interface_line_id = ria.interface_line_id

AND riea.org_id = ria.org_id

Steps in ERP Gap Analysis

  • The primary step will be to make a note of the existing business system and list out the flaws and positive aspects. This is an attempt to have an idea of what is currently happening given the scenario in question so as to help in ERP implementation.
  • Evaluate and decide the additions that need to be made to the business in view of ERP implementation. The aim is to make sure that there is not even a thin line of difference between ERP and the organizations commercial activities. It will be oriented to ERP function.
  • Rating the existing level of performance to set a benchmark or standards for the business as on date. This will help in finding out the benefit of ERP.
  • Having an in-depth study of the regulations and statements in the organizations and suggesting modifications. This also will decide ERP implementation.
  • Clearly defining the roles of individuals in the organization so that the priorities are met and the structure remains undisturbed. This is to make things clear for ERP function.
  • Checking if the objective in discharging duties are met because it is the ultimate solution to any issue. If they are not met the gaps should be made known and corrected. Only then the organization can achieve the benefit of ERP.
  • Ensuring that functions are executed properly and if need be personnel can be rewarded to boost up and encourage performance.
  • Similarly comparisons are to be made for every other factor that draws relation in one way or other. These results are to be complied for ERP gap analysis.
  • The gap analysis takes into account all the factors of study and gives the results. It either recommends the implementation of an ERP system or rejects the idea in totality.

Source: - www.erpwire.com

Posted in General. 1 Comment »

How to list down Attribute Mapping Rules in Oracle Advanced Pricing

SELECT QPSEG.SEGMENT_MAPPING_COLUMN, QPSOUR.USER_SOURCING_TYPE,

QPSOUR.USER_VALUE_STRING, QPCON.PRC_CONTEXT_CODE,

QPSOUR.ATTRIBUTE_SOURCING_LEVEL, QPSOUR.REQUEST_TYPE_CODE,

QPREQ.PTE_CODE, QPCON.PRC_CONTEXT_TYPE, QPSEG.SEGMENT_CODE,

QPCON.SEEDED_FLAG CONTEXT_SEEDED_FLAG,

QPSEG.SEEDED_FLAG ATTRIBUTE_SEEDED_FLAG

FROM QP_SEGMENTS_B QPSEG,

QP_ATTRIBUTE_SOURCING QPSOUR,

QP_PRC_CONTEXTS_B QPCON,

QP_PTE_REQUEST_TYPES_B QPREQ,

QP_PTE_SEGMENTS QPPSEG

WHERE QPSOUR.SEGMENT_ID = QPSEG.SEGMENT_ID

AND QPPSEG.USER_SOURCING_METHOD = ‘ATTRIBUTE MAPPING’

AND QPSOUR.REQUEST_TYPE_CODE = ‘ONT’

AND QPSEG.PRC_CONTEXT_ID = QPCON.PRC_CONTEXT_ID

AND QPREQ.REQUEST_TYPE_CODE = QPSOUR.REQUEST_TYPE_CODE

AND QPPSEG.PTE_CODE = QPREQ.PTE_CODE

AND QPPSEG.SEGMENT_ID = QPSOUR.SEGMENT_ID

AND QPPSEG.SOURCING_ENABLED = ‘Y’

AND QPCON.PRC_CONTEXT_TYPE IN

(’PRICING_ATTRIBUTE’, ‘PRODUCT’, ‘QUALIFIER’)

Item Catalogs Overview

Oracle Inventory lets you use item catalogs to partition your items into groups that share common characteristics. You can determine in advance what is required to uniquely define an item in each group. You can define your catalogs by setting up as many distinct item catalog groups as you need to partition your items and use unique characteristics within your catalog group to completely describe the items you assign to the group.

Oracle Inventory provides item catalogs to help you quickly locate items based on key characteristics

Business Needs

With Oracle Inventory you can:

· Easily and intuitively search for items based on cataloged characteristics

· Find common existing items when developing an engineering prototype

· Use a standard language of item characteristics to describe your items

· Maintain detailed item descriptions without embedding the descriptions in your item numbers

· Implement group technology

Major Features

  1. Catalog Groups

Oracle Inventory lets you define catalog groups to match the natural groupings of your items. For example, you can define separate catalog groups for finished goods, sub-assemblies, and raw materials, or break it down further by defining catalog groups by commodity for raw materials.

  1. Item Characteristics

Each catalog group can have its own set of item characteristics, called descriptive elements. You can define as many descriptive elements as necessary to describe the characteristics of your items. For each descriptive element, you can specify whether an entry for the element is optional, giving you complete flexibility in defining your catalog.

  1. Item Description Default

You can use descriptive element values as a default item description. You can choose the descriptive elements you want Oracle Inventory to use to build your item descriptions. This gives you consistent descriptions for all your items, again helping you to quickly and easily identify any item.

  1. Item Aliases

You can specify any number of aliases for items that belong to a catalog group.

  1. Item Search

Once you have cataloged your items, you can use the item catalog to quickly and easily find any item. You can search by one or more descriptive elements to quickly retrieve all items with the specific characteristics you specify.

Example

Companies in the same industry can communicate effectively while referring to items without naming their items identically because item cataloging could provide a standard language in referring to items. For e.g. Companies that manufacture computer monitors communicate effectively (because the items they manufacture are in the computer monitor catalog group) but they do not need to name their items identically. E.g. in the manufacture of shoes, the different catalog groups could be leather shoes, synthetic rubber shoes, etc.

A detailed description of what an item is could be stored without embedding the meaning in the item number.

You can search for items by their description –

You can update the description of an item at any time with the values of the descriptive elements

You can find common existing types of items

You can provide prompt service for you customers if they dont know the names of the items but know how the items look

References

Oracle Metalink

Oracle.com

Inventory User Guide

Application Implementation Wizard

Application Implementation Wizard

Overview

Oracle Application Implementation Wizard is an innovative application tool to facilitate the implementation of Oracle Applications. The Wizard addresses the complexities and interdependencies of multiple Oracle Applications products, enabling low risk, high efficiency implementation of Oracle Applications product suite.

During an implementation, the Wizard may be used to configure one or several Oracle Applications products. The setup of each product is accomplished by the completion of implementation processes. An implementation process is a sequence of steps, which are related to Oracle Applications setup forms. You can initiate iteration of a process multiple times to complete your implementation task.

Application Implementation Wizard Structure

The Wizard automatically detects the products installed on your implementation site and displays all the processes you need to run in the correct order. You can locate specific processes and corresponding iterations using a variety of flexible selection criteria. Once you are in an iteration of a process, you are automatically routed from step to step throughout the process. Guidance is always available, providing instructions, options, warnings and alerts for key decision making.

Implementation Process Hierarchy

The most efficient implementation strategy is to perform the common tasks first. Therefore, implementation processes are grouped into these categories:

  • Common Applications

Common processes address all the shared setup steps across Oracle Applications product families, which include System Administration, HRMS, Financial, Manufacturing and Front-Office.

  • Common Process of Product Family (Financial, Manufacturing and Distribution)

These processes include shared setup steps across products, but within its own product family.

  • Products

The additional processes to implement products like HR, General Ledger, Accounts Receivable, Inventory, Services, etc.

Business Needs

With Application Implementation Wizard you can:

  • Dramatically speed up your implementation process. You can use the Wizard to achieve parallel setup within your implementation team.
  • Lower your implementation risk. The Wizard guides you through the complexity of interdependent tasks and activities performed during the setup of any combination of Oracle Applications products.
  • Effectively manage your implementation project. You can use the Wizard to assign tasks to different members of your team, set objectives and monitor their implementation status and progress.
  • Document your implementation issues and suggest potential resolutions.

Major Features

Effective Organization of Setup Steps

By effectively grouping and sequencing implementation processes together, redundant setup steps across products are eliminated whenever possible. This also enables a higher degree of collaboration among implementation teams for parallel setup. After completing the common processes, your implementation team can work completely in parallel on the product specific processes. Because the implementation processes are fully automated, you never need to worry about sequencing and the complexities of cross product interdependencies.

Progress Monitor

Equipped with the Implementation Monitor, the Wizard provides situational awareness throughout the implementation. You always see graphically where you have been and what steps are ahead. By clicking on the graphic icon of a step in the implementation process monitor, you can review or preview any of the setup step information in a process

Implementation Process Overview

Status Display

Identified by different graphic icons, you always know what processes have been completed and how many are still open or active. For each iteration of a process, you can see who is performing the iteration, how long it has lasted and what the current step is in this iteration. Based on your judgment, you can also abort, suspend or change priority of any iteration.

Team Collaboration

To effectively manage and delegate implementation tasks, the Wizard allows you to assign processes to different members in your implementation team. Additionally, if you need help about how to setup a particular step, you can always reassign the step with your comments to the specialist in your team to handle.

Self-Documentation

The self-documentation feature provides an audit trail for you to track the status and issues of your implementation. You are able to append comments or note issues at process level, iteration level and step level.

At process level, you can enter information to provide guidelines and set objectives for your implementation team.

For each iteration of a process, you can enter comments, reminders, and task objectives for the person assigned in the iteration.

For each step in your iteration, you are able to record any conflict, issue, reason or open question specific to that step.

You have the option to skip steps in an iteration or branch within an iteration. All the comments and user options are automatically recorded by the Wizard for your later recall.

Setup Guidance

Guidance is always available, providing instructions, options, warnings and alerts for key decision making during your implementation. These instructions provide information like whether the step is a mandatory or optional one, what is the current context of the step and what you are supposed to do in this step. If you need more detailed help information about how and what to input in a particular setup form, you can always get on-line documentation by a simple click.

Flexible Identification of Processes and Iterations

Flexible Identification of Key Processes

A large implementation involves a lot of processes and iterations. The Wizard provides very flexible selection criteria for you to identify the critical processes and iterations you need. You can find processes or iterations according to their status, assignment, priority, duration, etc. You can also view your result sorted by process hierarchy, process name, or process context.

Phased Implementation

The Wizard provides setup support to allow the option of phasing Oracle Applications into production. You can phase your implementation based on your implementation rollout plan for Oracle Applications Products. For example, you may want to implement Oracle General Ledger and Accounts Payable in the first phase and Sales and Marketing in the second phase. The Wizard will automatically show you the corresponding processes you need to run based on the products you specified for that phase.

Define Implementation Phases

Context Check

Oracle Applications use organization context to ensure data segregation and security. To prevent setup information from being entered into the wrong organization, the Wizard enforces a context check whenever you try to open a form through a reference button in any setup step.

Some processes (e.g., System Administration) do not have any context and are only performed once per implementation, regardless of how many business entities you have. Most processes in the Wizard need to be performed multiple times if you have multiple business entities, which are categorized by Oracle Applications into the following context types:

Business Group: Used to separate employee information in HR and Payroll products for different organizations or countries.

Set of Books: Used to separate information about financial reporting entities with different chart of accounts, functional currency, and accounting calendar.

Operating Unit: Any organization like a sales office, a division, or a department that uses Order Entry, Oracle Payables, Oracle Purchasing, or Oracle Receivables.

Inventory Organization: An organization for which you track inventory transaction and balances, and/or that manufactures or distributes products.

These features allow you to implement the various multiple business entities in parallel.

The context of your process must match the security and profile of your current responsibility. This prevents accidental modifications. For example, if you have two operation units, the Wizard ensures that you are setting up the data for the operating unit you originally intend to setup.

Implementation Process Flow

Automatic Detection of Installed Products

The Wizard can automatically detect what products are installed on your site. It shows you only the processes and steps relevant to your specific implementation.

Source Oracle Functional Overview

Add Responsibility to your USER without having Application System Administrator Rights

By following procedure you can add responsibility to your USER with out having Application System Administrator Rights

begin
fnd_user_pkg.addresp(
‘&User_Name’, /*Application User Name */
‘&Responsablity_Application_Short_Name’, /*get from Query Below */
‘&Responsibility_Key’,/*get from Query Below */
‘&Security_Group’, /* Most of cases it is ‘STANDARD’ so you can hard code it */
‘&Description’, /* Any comments you want */
‘&Start_Date’, /* Sysdate From Today */
‘&End_Date’ ); /* Sysdate + 365 Rights for Next One Year*/
commit;

dbms_output.put_line(’Responsibility Added Successfully’);
exception
when others then
dbms_output.put_line(’ Responsibility is not added due to ‘ || SQLCODE || substr(SQLERRM, 1, 100));
Rollback;

end;

To get value for ‘Responsablity_Application_Short_Name’ and ‘Responsibility_Key’ Parameters you need to run following sql Statement by Using APPS User Name

SELECT FAV.APPLICATION_SHORT_NAME, FAV.APPLICATION_NAME,FRV.RESPONSIBILITY_KEY, FRV.RESPONSIBILITY_NAME

FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID
ORDER BY FRV.RESPONSIBILITY_NAME

@Advanced Supply Chain Planner

EXAMPLE CODE 

To add Responsibility of @Advanced Supply Chain Planner to User Ahmad Bilal

BEGIN
fnd_user_pkg.addresp (’AHMADBILAL’,
‘MSC’,
‘ADV_SUPPLY_CHAIN_PLANNING’,
‘STANDARD’,
‘Test By Script’,
SYSDATE,
SYSDATE + 100
);
COMMIT;
DBMS_OUTPUT.put_line (’Responsibility Added Successfully’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (   ‘ Responsibility is not added due to ‘
|| SQLCODE
|| SUBSTR (SQLERRM, 1, 100)
);
ROLLBACK;
END;

Examples to use in setting up modifiers in Oracle Advance Pricing

Example 1: Line level discount of 8% discount on all products

File Path: $QP_TOP/patch/115/sql/QPXEXDS1.sql

Example 2: Buy more than 5 quantities of item 62081 , Get 8% discount

File Path: $QP_TOP/patch/115/sql/QPXEXDS2.sql

Example 3: For customer 1000, Buy more than 2 Units of item 62081, Get 10% discount

File Path: $QP_TOP/patch/115/sql/QPXEXDS2.sql

Example 4: Charge 2% surcharge on all products

File Path: $QP_TOP/patch/patch/115/sql/QPXEXSUR.sql

Example 5: For customer 1000, Buy more than 2 units of item 62081, Get a Payment Term 2/10 NET 30

File Path: $QP_TOP/patch/115/sql/QPXEXTSN.sql

RFID Enabled Shipping and Receiving

 

RFID Enabled Shipping & Receiving

Above diagram outlines the planned flow of an RFID Receipt. The huge potential of RFID is that data capture does not require user interaction or line of sight. So transaction can be processed without any manual user interaction with the system.

Here the action of a receiving dock worker driving through a doorway which is covered by an RFID reader will automatically trigger an ASN Receipt. Similar processes are possible for intra-warehouse moves, shipping, etc.

To make this work several components are required:

  1. Middle ware is required to interface with the RFID Reader hardware and to provide the appropriate filtering so that duplicate or erroneous reads are eliminated.
  2. The information from the read must be interpreted as a specific business event. This means that the ID of the tag must be resolved into some data on the system and based on the state of that data, the transaction to be processed can be deduced. For example, if you read an ID at a reader in Receiving, and that ID is associated with an LPN on an ASN that you have received from your supplier, then the transaction to be processed is an ASN Receipt of that LPN.
  3. The application must process that transaction using a API which does not require user input.
  4. Any results or exceptions must be processed and these made available (via the middle ware) to the light trees, buzzers, etc to single results to the user.

The above framework is currently being prototyped for possible inclusion in a future release.

Reference: - Oracle Apps World

Supply Chain Footprint in Oracle Application

1. Supply Chain Planning

MRP/Supply Chain Planning

Demand Planning

Advanced Supply Chain Planning

Constraint and Inventory Optimization

Global Order Promising

Manufacturing Scheduling

Inventory Optimization

Collaborative Planning

Transportation Planning

Supply Chain Intelligence as an independent module

2. Order Management

Order Management

Configurator

iStore

TeleSales

Receivables

Advanced Pricing

Quoting & Proposals

Trade Management

Release Management

Sales Contracts

3. Procurement

Purchasing

Payables

iProcurement

Sourcing

iSupplier Portal

Procurement Contracts

  1. Manufacturing

Discrete Manufacturing

- Project Manufacturing

- Flow Manufacturing

- Project Contracts

- Shop Floor Management

Process Manufacturing

  1. Logistics

Inventory Management

Mobile Supply Chain Apps

Warehouse Management

Transportation Execution

6. Service

Tele Service

Depot Repair

Field Service

- Spares Management

- Advanced Scheduler

- Mobile Field Service

iSupport

Service Contracts

7. Maintenance Management

Enterprise Asset Management

Property Manager

Sub Queries in Oracle SQL and their types

What is a Sub Query?

A query nested inside another query and it is used when a query is based on an unknown value. A sub query is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query the sub query must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum.

What are the types of Sub Queries?

Single Row Sub Query

Return the outer query one row of results that consists of one column

Multiple Row Sub Query

Return to the outer query more then one row of the results it Require use of IN, ANY, ALL, or EXISTS operators

Multiple Column Sub Query

Return to the outer query more then one row of the results in this type of query Column list on the left side of operator must be in parentheses it use the IN operator for WHERE and HAVING clauses

Correlated Sub Query

Reference a column in the outer query and execute the sub query once for every row in outer query

Uncorrelated Sub Query

Execute the sub query first and passes the value to outer query

 

Source Oracle Introduction to Database

Posted in SQL. No Comments »

What is Trading Community Architecture in Oracle Application 11i

Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. These applications, as well as TCA itself, provide user interfaces, batch data entry functionality, and other features for you to view, create, and update Registry information (Source Overview Oracle® Trading Community Architecture User Guide)

The parties in TCA could be one of following four types:

  1. Organization e.g. Dawlance
  2. Person e.g. Ahmad Bilal
  3. Group e.g. IEEE Consortium
  4. Relationship e.g. Ahmad Bilal at Dawlance.

Following are main components of Oracle TCA

  1. Contacts
  2. Locations
  3. Party Layer
    1. Sites
    2. Relationships
  4. Account Layer
  5. Customer Accounts

Main Tables in TCA

  1. HZ_PARTIES
  2. HZ_LOCATIONS
  3. HZ_PARTY_SITES
  4. HZ_CUST_ACCT_SITES_ALL
  5. HZ_CUST_SITE_USES_ALL
  6. HZ_CUST_CONTACT_POINTS

Oracle Application Forms Development / Modification

  1. Copy libraries.

For modification or development in oracle application, the following libraries are required to open or change application form and must be copied from $AU_TOP/resource to the local drive e.g. c:\APPS_LIBRARY where you are trying to open Form or custom.pll.

    1. APPCORE.pll
    2. APPCORE2.pll
    3. CUSTOM.pll
    4. APPFLDR.pll
    5. VERT.pll
    6. GLOBE.pll
    7. JA.pll
    8. JE.pll
    9. JL.pll
    10. GHR.pll
    11. FNDSQF.pll
    12. APPDAYPK.pll
    13. PSAC.pll
    14. PQH_GEN.pll
    15. OPM.pll

  1. Set The Forms Path.

Open Registry Editor by using regedit and edit HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME\FORMS60_PATH and point to the location where the libraries above where copied. “C:\APPS_LIBRARY” in this example, without quotes

  1. Add the FORMS60_PATH key to the environment path.

This is accessed by right-clicking “My Computer”, selecting properties, clicking on the advanced tab, selecting Environment Variables. Set value to C:\APPS_LIBRARY