Creating lookup as a Function

LOOKUP_TYPE="XXAT_CMIR_BOX_EMAIL"

Oracle_CRM

account_alias_definition_issue

1. What is account alias
2. how to define account alias
3. Steps missed in Odev


--> Error --> Invalid transaction source segments..

Records Stuck in Mtl_transactions_interface, Invalid Transaction Source Segment. [ID 98358.1]

Cause

The imported record was missing the transaction source segment

Solution

You have to have an account alias defined before you can use it as a transaction source in a transaction.

Determine the type of transaction. Run this select statement to find out the type of transaction:

select transaction_type_id
from mtl_transactions_interface
where process_flag = 3;

If it returns transaction_type_id = 31, it is an account alias issue transaction.

Navigate to the define account aliases screen: Inv/setup/Account Aliases
Verify the account aliases value populated in the Mtl_Transactions_interface table
against the account alias combinations existing in the account alias screen.

For additional information on defining account aliases, refer to the Oracle Inventory User's Guide > Dening Account Aliases. Go to My Oracle Support | Knowledge | Online Documentation | Release 11.5.10 Documentation | Documentation | Logistics | Inventory Management.

Stuck Sales Orders in Mtl_material_transactions - Invalid Source Segment [ID 100567.1]

To Bottom

---

Modified:Feb 12, 2011Type:PROBLEMStatus:ARCHIVEDPriority:3

Comments (0)

Problem Description
-------------------

Sales order transactions are stuck in the mtl_transactions_interface with the following
error:

error_explanation: APP-010756 - vlaue 11865 is not defined

error_code: app-05337 - invalid transaction source segment

The AR:substitute Balancing segment had been modified using Flex Builder to add a
segment to the Accounting Flexfield.
       
Solution Description
--------------------

The solution comes from an old bug that was almost disregarded. This entry is to 
update the solution for a newer release. 

Recompile the accounting flexfield from the General Ledger responsibility.

Setup/Financials/flexfields/key/segments  query up the General Ledger application and
recompile the flexfield from within the application.

       


References
----------

        Bug 294497

Additional Search Words
-----------------------

        app-05337 app-01756, stuck sales order transactions, mtl_transanctions_interface,
invalid transaction source segment, segment, transaction source, transaction, sales orders,
Flex Builder, flexfield, value not defined.

Queries

1. Getting Responsibility from Function Name
--> for one level
  select frt.responsibility_id, frt.responsibility_name from fnd_responsibility_tl frt,
                fnd_responsibility fr,
                fnd_form_functions_tl fff,
                fnd_menu_entries fme
         where frt.language = 'US'
         and frt.responsibility_id  = fr.responsibility_id
         and  fff.function_id = fme.function_id
         and fme.menu_id = fr.menu_id
         and fme.sub_menu_id is NULL
         and fff.user_function_name like '%Organization%Access%';

2. From Sub Menus also

SELECT frt.*
  FROM (SELECT     LEVEL, menu_id, sub_menu_id
              FROM fnd_menu_entries
             WHERE sub_menu_id IS NOT NULL AND function_id IS NULL
        START WITH sub_menu_id IN (
                      SELECT menu_id
                        FROM fnd_menu_entries fme, fnd_form_functions_tl fff
                       WHERE fme.function_id = fff.function_id
                         AND fff.user_function_name LIKE
                                                       '%Organization%Access%')
        CONNECT BY PRIOR sub_menu_id = menu_id) a,
       fnd_responsibility fr,
       fnd_responsibility_tl frt
 WHERE a.menu_id = fr.menu_id
   AND frt.responsibility_id = fr.responsibility_id
   AND frt.LANGUAGE = 'US';


http://oracle.anilpassi.com/some-scripts-contributed-by-ahmad-bilal-16.html

-- Query to inventory org access responsibility wise
SELECT   row_id, organization_code, application_name, responsibility_name,
         comments, organization_name, disable_date, organization_id,
         resp_application_id, responsibility_id, last_update_date,
         last_updated_by, creation_date, created_by, last_update_login,
         request_id, program_application_id, program_id, program_update_date
    FROM org_access_v
ORDER BY organization_code
;

--> query to concurrent prg info
SELECT fcpl.user_concurrent_program_name, fcpl.concurrent_program_id,
       fcp.concurrent_program_name, fe.executable_id, fe.execution_file_name,
       fe.executable_name ,  fa.application_short_name
  FROM fnd_concurrent_programs_tl fcpl,
       fnd_concurrent_programs fcp,
       fnd_executables fe,
       fnd_application fa
 WHERE user_concurrent_program_name LIKE '%XXAT%Inv%Adj%'
   AND LANGUAGE = 'US'
   AND fcp.concurrent_program_id = fcpl.concurrent_program_id
   AND fe.executable_id = fcp.executable_id
   and fa.application_id = fe.application_id;

-->Query to get the conc prg default parameteres


 SELECT       MAX(DECODE(application_column_name, 'ATTRIBUTE1',default_value, NULL))  argument1,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE2',default_value, NULL))  argument2,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE3',default_value, NULL))  argument3,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE4',default_value, NULL))  argument4,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE5',default_value, NULL))  argument5,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE6',default_value, NULL))  argument6,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE7',default_value, NULL))  argument7,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE8',default_value, NULL))  argument8,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE9',default_value, NULL))  argument9
             INTO         l_pr_argu_1, l_pr_argu_2, l_pr_argu_3, l_pr_argu_4,
                          l_pr_argu_5, l_pr_argu_6, l_pr_argu_7, l_pr_argu_8,
                          l_pr_argu_9
             FROM   FND_DESCR_FLEX_COL_USAGE_VL
             WHERE (APPLICATION_ID=20004)
             AND  (DESCRIPTIVE_FLEXFIELD_NAME='$SRS$.XXAT_PRICAT_OUTBOUND_CP') order by column_seq_num;

--  query to get fnd_conc_req_schedule

SELECT fcpl.user_concurrent_program_name, fcpl.concurrent_program_id,
       fcp.concurrent_program_name, fe.executable_id, fe.execution_file_name,
       fe.executable_name ,  fa.application_short_name
  FROM fnd_concurrent_programs_tl fcpl,
       fnd_concurrent_programs fcp,
       fnd_executables fe,
       fnd_application fa
 WHERE user_concurrent_program_name LIKE '%%'
   AND LANGUAGE = 'US'
   AND fcp.concurrent_program_id = fcpl.concurrent_program_id
   AND fe.executable_id = fcp.executable_id
   and fa.application_id = fe.application_id
   ;

---
Value set query (table type)

select v.*
  from FND_FLEX_VALUE_SETS s, FND_FLEX_VALIDATION_TABLES v
 where s.flex_value_set_name = 'YOUR_VS_NAME'
   and s.flex_value_set_id = v.flex_value_set_id

==================
Query to get Profile values at all levels
SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT",
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;

Seiban Manufacturing

Copied from http://www.infosysblogs.com/oracle/2011/05/seiban_manufacturing_-_enable.html

Seiban Manufacturing - Enable Trackability in your Supply Chain!

Continuing on the ETO Series, I will discuss about the concept of Seiban, How it works and the key business requirements in Build To Order (BTO) Industry that can be handled in Oracle EBS Seiban solution. Another prominent ERP product that addresses Seiban requirements in the Industry is Glovia from Fujitsu.

 What is Seiban?
The Japanese methodology of separation of inventories using an identifying number where the number is attached to on-hand Inventory, purchase orders and manufacturing jobs on supply side to identify them as belonging to a particular customer order, contract or project. This identifying number is called Seiban derived from Sei meaning "Manufacturing" (or Production) and Ban meaning "Number."
Why Seiban?
In most of the manufacturing scenarios, materials and resources are allocated to production orders based on their urgency of the customer orders which does not work well for Build To Order manufacturers. In Build-to-order manufacturing, the key requirement is to track and segregate materials and activities for specific customer orders which enable visibility and trackability of the customer order throughout the manufacturing and supply chain cycle.
How Seiban works?
A unique identification number, the Seiban number is allotted to a particular customer order and subsequent supply orders, purchased materials, activities and services performed for that customer order inherit the Seiban number to enable trackability.
The key features that Oracle offers for implementing a Seiban manufacturing system are,
1.Ability to link and track Demand and supply for Customer Order/Project/Contract (Seiban)
2.Tracking Seiban Execution in shop floor and supply chain.
3.Providing Identification and allocation of Inventory for Seiban;
4.Cost tracking for specific Seiban
5.Ability to view, track, report Seiban specific activities and detailed drill-downs
6.Ability to handle material transfers across Seibans including borrow/pay back and mass transfers
7.Ability to support Lean practices and ATO/PTO business scenarios
8.Visibility to Seiban profitability
9.Integration with Quality systems - To track back defects in a customer order (Seiban).
Here are the few limitations in Oracle EBS in supporting a Seiban system,
1. No single view for all Seiban costs and revenues without Oracle projects installation
2. Ability to use WBS/ Tasks not possible.
3. Oracle does not support reservations at Seiban level.

 http://www.glovia.com/solution/manufacturing-management/seiban.aspx

PDF Download Data Sheet

Trace activities and costs for any product line, batch, product(s), production run, or other individual item/grouping

Traditional MRP II system logic assumes that inventory is available to satisfy any customer requirement. As a result, it does not allow for the separation or allocation of certain materials, parts, or works-in-process for customer-specific use.
The ability to maintain separate customer identification or customer lots within the general population of parts and orders has traditionally been used in contract and project manufacturing environments. However, in today's customer-oriented world, this identification approach is very useful in providing clear identification and availability of materials for specific customer demand- even in an Assemble-to-Order, Make-to-Stock or Repetitive Manufacturing environment.

What is Seiban?

Separation of inventories is not uncommon in Japan where an identifying number is attached to all parts, materials, purchase orders and manufacturing orders (all supply orders) that identify them as belonging to a particular customer, job, project or product line. This identifying number is called Seiban, from the Japanese work Sei meaning "manufacturing" (or production) and Ban meaning "number."
The result is like having separate Material Requirements Planning (MRP) runs within the overall MRP process. In actual practice, there are separate MRPs for each designated Seiban requirement performed right along with the "regular" MRP for non-Seiban requirements. Glovia simplifies and enables this process by providing the interactive glovia G2 Seiban workbench and glovia G2 Project Resource Planning.

Why use Seiban?

Why would a company want or need to pre-designate specifically how and where the inventory will be used?

• Due to customer requirements, as in a built-to-order environment, manufacturers must be more responsive in order to remain competitive
• Because stocks and activities are linked through a common designator, visibility is enhanced, making the retrieval of Seiban/job-specific information instantaneous
• Information on all activities related to Seiban/job-related activities is highly accessible, thus improving coordination and synchronization
• Quick and easy retrieval of job-specific supply orders
• Customer-order-line level visibility throughout the entire manufacturing process
• Synchronized production scheduling, assembly, and delivery of products to customers as promised
• Customer-order-line profitability analysis
• Superior cost accounting through positive identification and tracking of all Seiban/job-related purchases, materials and routing activities

The Glovia Seiban Solution

glovia G2 incorporates the Seiban principle within a larger resource planning application that can coexist and is fully integrated with traditional Master Production Schedule (MPS) and MRP approaches. The Seiban workbench resides in glovia G2 Project Resource Planning (PRP), which is designed to track Seibans, jobs, projects or contracts; gather all related costs in a defined manner; and make status, progress and projected customer delivery information readily available.
glovia G2 Seiban Workbench facilitates all management and maintenance of Seiban-related parts, orders and activities. The solution provides:

• The ability to plan and re-plan material items and service items and to manage these items through the use of Seiban identification numbers
• The ability to perform impact analysis to see the effect of a change or potential change, because all other activities (both up and down the Bill of Material) are tightly linked to all others
• The ability to perform advanced capacity resource planning for an order
• The ability to change the schedule for any item within the Seiban group and optionally have the system adjust the schedules for all other items, activities and schedules by the same amount and keep them in synchronization. This also applies to changes in the customer requirement or master schedule
• Full integration with MPS and MRP, meaning that Seiban planning and management work independently of, but in full coordination with, the planning and management of non-Seiban items
• The ability to track all costs associated with the Seiban and value-added activities including direct-purchased materials, work orders, services and indirect costs
• Improved inventory management through the ability to peg replenishments to requirements
• Reduced inventory requirements because acquisition and replenishment can be tied directly to specific needs
• The ability to perform multi-level planning. For material and service items, PRP explodes the associated Bill of Material and plans the PRP items. Lower level MPS and MRP items are sent to the appropriate application for planning generated purchase requisitions.

Subcontracting For Seiban-Based Manufacturing

This chapter covers the following topics:

• Overview of Seiban-Based Manufacturing
• Seiban-Based Manufacturing Setup
• Organization Setup
• Defining Cost Groups for MP Organizations
• Defining Seiban Numbers
• Organization Items
• Seiban-Based Subcontracting Planning
• Seiban-Based Subcontracting Execution

Overview of Seiban-Based Manufacturing

Oracle Subcontracting supports subcontracting in Seiban-Based Manufacturing. In Seiban-Based Manufacturing, outsourced assemblies and components are planned based on Seiban numbers (project numbers) using Advanced Supply Chain Planning (ASCP) and hard pegging. The subcontracting process is also executed using locator-controlled OEM and MP organizations and Seiban numbering.
The Subcontracting feature can be used in both discrete and Seiban-Based Manufacturing environments. The following sections describe the additional setup and execution steps required to use subcontracting in Seiban-Based Manufacturing.

Seiban-Based Manufacturing Setup

The setup steps described in the chapter Setting Up Subcontracting, apply to Seiban-Based Manufacturing. The sections that follow are for planning components and executing the subcontracting process using Seiban (project) numbers.

Organization Setup

Inventory Parameters
For Seiban-Based Manufacturing, Locator Control should be defined as dynamic entry allowed in Inventory parameters for both the OEM and MP organizations.
To set up Seiban inventory parameters

1. From the Inventory responsibility, navigate to the Organization Parameters window.
   
   You use locator control to identify project inventory.
   
2. Save your work.
   

Project Manufacturing Parameters
Define the OEM and MP organizations as Project Manufacturing Organizations by adding Organization Classification as Project Manufacturing for both.

1. From the Inventory responsibility, navigate to the Organization window.
   
   
2. Navigate to the Project Manufacturing Parameters window, and click the General tab.
   
3. Select the Enable Project References indicator.
   
4. Set Project Control Level to Project.
   
   
5. Save your work.
   

To define WIP parameters
Seiban-Based Manufacturing uses locator-controlled subinventories for inventory transactions. To support the simulation of discrete jobs in the MP organization, supply subinventory should be a locator-controlled subinventory. Interlock Manager creates WIP jobs with this subinventory as component subinventory for back flushing. The Auto Receive concurrent program also receives components into this subinventory for simulation.

1. Navigate to the Work in Process Parameters window.
   
2. Select the Material tab and associate Locator Controlled Subinventory as Supply Subinventory. In addition, associate Supply Locator leaving Project/Task as blank. (For details about Locators in PJM, refer to the Oracle Project Manufacturing User's Guide.)
   
   
3. Save your work.
   

Defining Cost Groups for MP Organizations

You must define Cost groups and associate a WIP accounting class (defined at the time of creating the WIP parameters for MP organizations). This action is required for simulating WIP jobs by project numbers, and it is part of the Project Manufacturing (PJM) setup.
See: Oracle Project Manufacturing User's Guide
The MP organization is a zero-cost organization, and transfer to general ledger is set to No. Cost group setup is required to facilitate the simulation of assembly manufacturing in the MP organization using discrete jobs and project numbers. This setup does not have financial implications.
To define cost groups for MP organizations

1. From the Cost Management responsibility, navigate to the Cost Groups window.
   
2. Define the cost group with Type set to Inventory. You can use any existing cost group.
   
   
3. Click WIP Accounting Classes to define WIP Accounting Classes for this cost group (use the same WIP accounting class defined at the time of defining WIP Parameters of MP Organization). The WIP Account Classes for Cost Group window appears.
   
   
4. Save your work.
   

Defining Seiban Numbers

Before you plan and execute subcontracting in Seiban-Based Manufacturing environments, you define project numbers.
To define Seiban numbers

1. From the Project Manufacturing responsibility, navigate to the Seiban Wizard.
   
   
2. Select the Using Seiban Number indicator and click Next. The Seiban window appears.
   
   
3. Enter Seiban (project) Numbers.
   
   
4. Save your work.
   
5. Click Parameters to define the parameters for the Seiban Number (project). The Project Parameters window appears.
   
   
6. Select the General tab and enter Inventory Organizations that use the project numbers. In this scenario, the OEM and MP organizations should be included.
   
7. Associate a cost group, which was defined in the previous procedure.
   
8. Associate a WIP Accounting Class. This value should be same as the WIP accounting class associated with the WIP parameters of the MP organization.
   
9. Save your work.
   

Organization Items

In Seiban-Based Manufacturing, outsourced assemblies and components must be defined as hard pegged items for planning and execution using Seiban numbers.
To define items as hard pegged

1. From the Inventory responsibility, navigate to the Organization Item window.
   
2. Select the MPS/MRP Planning tab, and set the attribute Pegging to Hard Pegging. This information is used to for planning the demand and supply by project numbers.
   
   
3. Save your work.
   All outsourced assemblies and subcontracting components should be defined as hard pegged in both OEM and MP organizations.
   

Seiban-Based Subcontracting Planning

The steps and processes of planning are similar to those of discrete manufacturing.
For Seiban-Based Manufacturing, the differences are:

• Forecast must be defined by project.
  
• ASCP plans the components and assemblies by project. Demand and supply are considered and calculated by project.
  
• Planned orders are created by project.
  
• Planned orders are outsourced as assemblies and pre-positioned components. They are transferred to purchasing as purchase requisitions. The purchase orders are created with project as shipment lines.
  
• For outsourced assemblies, the purchase order is called as a subcontracting order, and the project number is stamped in the purchase order line shipment distributions.

Note: Subcontracting Orders and Replenishment Purchase Orders of the pre-positioned components should always have one Shipment and Distribution for every purchase order line. Multiple shipments and distributions are not supported. When you create subcontracting orders and replenishment purchase orders manually, you can create multiple purchase order lines. However, only one shipment and distribution is allowed for each purchase order line. Interlock Manager discards purchase order lines if it has more than one shipment or distribution for each PO line, and it will not process them.

Seiban-Based Subcontracting Execution

The process for executing Subcontracting in the Seiban-Based Manufacturing environment is the same as for discrete manufacturing.
Interlock Manager
The Interlock Manager concurrent request picks up subcontracting orders and processes them by creating WIP jobs in the MP organization. Interlock Manager also creates replenishment purchase and sales orders, and it allocates the replenishment sales orders according to subcontracting order requirements in the same way described in the Subcontracting Process chapter of this guide.

• Work In Process (WIP) jobs are created with Seiban (project) references
  
• Replenishment purchase orders and replenishment sales orders are created with Seiban (project) references
  
• Allocations are made with the same conditions of shipment dates and price of replenishment order, and the project number must be the same for subcontracting orders and replenishment orders for allocation in the Seiban environment.

Auto Receive Components
Same as Discrete Manufacturing
Reconciliation Manager
Same as Discrete Manufacturing
Process Receiving Transactions
Same as Discrete Manufacturing
Subcontracting Workbench
Workbench functionality is the same as for Discrete Manufacturing. All search options can be executed with a Seiban (project) number. The project number is available in additional search options, and you can personalize the search and results to view them by project number.
Subcontracting Accounting
In Seiban-Based Manufacturing, accounting is the same as discrete manufacturing. Concepts and posting of accounts remain the same.
Reports
Seiban (project) numbers are printed with all the reports, and the remaining features and functions are the same as for Discrete Manufacturing.