Wednesday, 24 September 2014

Oracle Apps BOM Tables and Queries


BOM_DEPARTMENTS Departments
BOM_DEPARTMENT_CLASSES Department classes
BOM_DEPARTMENT_RESOURCES Resources associated with departments
BOM_OPERATIONAL_ROUTINGS Routings
BOM_OPERATION_NETWORKS Routing operation networks
BOM_OPERATION_RESOURCES Resources on operations
BOM_OPERATION_SEQUENCES Routing operations
BOM_OPERATION_SKILLS
BOM_RESOURCES Resources, overheads, material cost codes, and material overheads
BOM_STANDARD_OPERATIONS Standard operations
BOM_ALTERNATE_DESIGNATORS Alternate designators
BOM_COMPONENTS_B Bill of material components
BOM_STRUCTURES_B Bills of material
BOM_STRUCTURE_TYPES_B Structure Type master table
BOM_BILL_OF_MATERIALS
BOM_PARAMETERS
BOM_DEPENDENT_DESC_ELEMENTS
BOM_REFERENCE_DESIGNATORS
BOM_SUBSTITUTE_COMPONENTS
FND_DOCUMENTS
FND_DOCUMENTS_TL
FND_DOCUMENTS_SHORT_TEXT
FND_ATTACHED_DOCUMENTS

Select * from MFG_LOOKUPS
WHERE
--LOOKUP_CODE =  'BOM_ITEM_TYPE'
--LOOKUP_CODE =  'WIP_SUPPLY_TYPE'
--LOOKUP_CODE =  'BOM_ASSEMBLY_TYPE'

-----------------------------------------------------------------
1) Query for BOM Details


SELECT d.inventory_item_id,
       d.concatenated_segments "Assembly_item",
       e.organization_code "Organization",
       nvl(a.ALTERNATE_BOM_DESIGNATOR,'10') "ALTERNATIVE BOM",
       b.ITEM_NUM "ITEM",
       c.concatenated_segments "COMPONENT",
       b.component_quantity "UNIT",
       c.primary_uom_code "QUANTITY",
       d.primary_uom_code "UNIT"
FROM   bom_bill_of_materials       a
      ,bom_inventory_components  b
      ,mtl_system_items_kfv  c
      ,mtl_system_items_kfv d
      ,org_organization_definitions e
WHERE  1=1
AND   a.organization_id in (:P_organization_id)
AND (a.BILL_SEQUENCE_ID  =  b.BILL_SEQUENCE_ID
          OR a.COMMON_BILL_SEQUENCE_ID  =  b.BILL_SEQUENCE_ID)
AND    b.implementation_date IS NOT NULL
AND    b.DISABLE_DATE    IS   NULL
AND  a.organization_id  =  c.organization_id
AND  b.COMPONENT_ITEM_ID  = c.inventory_ITEM_ID
AND  a.assembly_item_id = d.inventory_item_id
AND  a.organization_id = d.organization_id
AND   a.organization_id = e.organization_id
--AND    c.organization_id   = :P_organization_id
--AND  a.ASSEMBLY_ITEM_ID  = :P_assembly_item_id
-- AND  ALTERNATE_BOM_DESIGNATOR IS NULL
ORDER BY e.organization_code,d.inventory_item_id,ALTERNATE_BOM_DESIGNATOR,b.ITEM_NUM;



----------------------------------------------------------------
2) Query for Resources


SELECT  c.concatenated_segments "Assemly_Item",
        d.organization_code "Organization",
        a.alternate_routing_designator "Alternate_routing",
        b.operation_seq_num "OPERATION NUMBER",
        e.department_code "WORK CENTER",
        b.operation_description "DESCRIPTION(Operation)",
        c.primary_uom_code "OPERATION UNIT",
        G.uom "UNIT",
       f.operation_code,
        g.resource_code,
        g.USAGE_RATE_OR_AMOUNT
        h.DESCRIPTION
FROM    bom_operational_routings a
       ,bom_operation_sequences b
       ,mtl_system_items_kfv c
       ,org_organization_definitions d
       ,bom_departments e
       ,bom_standard_operations f
       ,bom_operation_resources_v g
       ,bom_resources h
WHERE a.organization_id in (:P_organization_id)
AND   a.organization_id = d.organization_id
AND   a.organization_id = c.organization_id
AND   a.assembly_item_id = c.inventory_item_id
AND   a.routing_sequence_id = b.routing_sequence_id
AND   b.department_id = e.department_id
AND   b.disable_date is null
--AND   a.assembly_item_id = :P_assembly_item_id
AND f.standard_operation_id(+) = b.standard_operation_id
AND g.operation_sequence_id = b.operation_sequence_id
AND h.resource_id = g.resource_id
order by d.organization_code,c.concatenated_segments,a.alternate_routing_designator, b.operation_seq_num


--------------------------------------------------------------------------------------------

3) Departments and Resources and Departments associated with Resources



select b.organization_code "Plant",
       a.DEPARTMENT_CODE "Department",
       a.DESCRIPTION "Department Description",
       a.DEPARTMENT_CLASS_CODE "Department Class",
       c.DESCRIPTION "Department Class Description",
       d.location_code "Department_location"
from   BOM_DEPARTMENTS a
      ,org_organization_definitions b
      ,BOM_DEPARTMENT_CLASSES c
      ,hr_locations d
where  a.organization_id in (:P_organization_id)
AND    a.organization_id  = b.organization_id
AND    a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE
AND    a.organization_id  = c.organization_id
AND    a.location_id = d.location_id(+)

select b.organization_code "Plant",
       a.RESOURCE_CODE "Resource Name",
       a.DESCRIPTION "Resource Description",
       a.UNIT_OF_MEASURE
from   BOM_RESOURCES a
      ,org_organization_definitions b
      --,mfg_lookups c
where  a.organization_id in (:P_organization_id)
AND    a.organization_id = b.organization_id
AND    a.Disable_date is Null
--AND    c.lookup_type = 'BOM_BASIS_TYPE'
--AND    c.LOOKUP_CODE = a.DEFAULT_BASIS_TYPE

exec mo_global.set_policy_context('S',:P_org_id);

--BOM_RESOURCE_TYPE
--BOM_BASIS_TYPE

select * from mfg_lookups where lookup_type like 'BOM_BASIS_TYPE'


select * from BOM_DEPARTMENT_RESOURCES

select * from BOM_RESOURCE_SHIFTS


select b.organization_code "Plant",
       a.DEPARTMENT_CODE "Department",
       a.DESCRIPTION "Department Description",
       a.DEPARTMENT_CLASS_CODE "Department Class",
       c.DESCRIPTION "Department Class Description",
       d.location_code "Department_location",
       f.RESOURCE_CODE "Resource Name",
       f.DESCRIPTION "Resource Description",
       f.UNIT_OF_MEASURE
from   BOM_DEPARTMENTS a
      ,org_organization_definitions b
      ,BOM_DEPARTMENT_CLASSES c
      ,hr_locations d
      ,BOM_DEPARTMENT_RESOURCES e
      ,BOM_RESOURCES f
     -- ,BOM_RESOURCE_SHIFTS g
where  a.organization_id in (:P_oranization_id)
AND    a.organization_id  = b.organization_id
AND    a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE
AND    a.organization_id  = c.organization_id
AND    a.location_id = d.location_id(+)
AND    a.DEPARTMENT_ID = e.DEPARTMENT_ID
AND    e.RESOURCE_ID = f.RESOURCE_ID
--AND    a.DEPARTMENT_ID = g.DEPARTMENT_ID
--AND    f.RESOURCE_ID = g.RESOURCE_ID

--------------------------------------------------------------------------------------------------------

Routing Open Interface

Concurrent: Bill and Routing Interface

-- Create Routing
    insert into bom_op_routings_interface
    (
    assembly_item_id,
    process_revision,
    process_flag,
    transaction_type,
    routing_type,
    organization_id
    )
    values
    (
     218967,
     Null,
     1, --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
     'CREATE',
     1,----1) manufacturing, 2) engineering
     204
    );


-- Create Routing Sequences
    insert into bom_op_sequences_interface
    (
    assembly_item_id,
    operation_seq_num,
    department_id,
    department_code,
    process_flag,
    transaction_type,
    organization_id,
    effectivity_date
    )
    values
    (
    218967,
    10,
    42877,
    'PD1',
    1,
    'CREATE',
    204,
    sysdate
    );


-- Create Operation Resources
    insert into bom_op_resources_interface
    (
    resource_seq_num,
    resource_id,
    resource_code,
    usage_rate_or_amount, --basis_type ,
    assigned_units,--schedule_flag,autocharge_type,
    assembly_item_id,
    operation_seq_num, --operation_sequence_id,
    process_flag,
    transaction_type,
    effectivity_date,
    organization_id ,
    schedule_flag,
    schedule_seq_num
    )
    values
    (
    10,
    90529,
    'PR1',
    1,
    1,
    218967,
    10, --2346216 ,
    1,
    'CREATE',
    sysdate,
    204,
    1,
    10
    );



         select * from mtl_system_items_kfv where inventory_item_id = 133 and organization_id = 204
 
 select * from bom_departments where department_id = 42877 and organization_id = 204

 select * from BOM_DEPARTMENT_RESOURCES where department_id = 42877

 select * from bom_resources  where resource_id = 90529

 select * from bom_op_resources_interface

BOM Routing Creation API

Creating Maintenance Routes with BOM_RTG_PUB API (You can use this API for discrete manufacturing)
Firstly I create a custom table then I insert the datas from excel sheet.


CREATE TABLE XXINN_MAINTENANCE_ROUTES(
ASSEMBLY_ITEM_NAME         VARCHAR2(240 BYTE),
ORGANIZATION_CODE          VARCHAR2(3 BYTE),
LONG_DESCRIPTION           VARCHAR2(4000 BYTE),
OPERATION_DESCRIPTION      VARCHAR2(400 BYTE),
OPERATION_SEQUENCE_NUMBER  NUMBER,
DEPARTMENT_CODE            VARCHAR2(10 BYTE),
RESOURCE_SEQUENCE_NUMBER   NUMBER,
RESOURCE_CODE              VARCHAR2(10 BYTE),
USAGE_RATE_OR_AMOUNT       NUMBER,
ASSIGNED_UNITS             NUMBER,
ERROR                      VARCHAR2(2000 BYTE)
)
Then I have written this procedure for creating the maintenance routes.

CREATE OR REPLACE PROCEDURE APPS.XXINN_CREATE_MAINTNCE_ROUTE(ERRBUF  OUT VARCHAR2,
RETCODE OUT NUMBER) IS
P_HEADER_REC       Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_OPERATION_REC    Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
P_RESOURCE_REC     Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
p_RETURN_STATUS    VARCHAR2(240);
p_MSG_COUNT        NUMBER;
l_ret_text         VARCHAR2(32000);
p_Message_List     Error_Handler.Error_Tbl_Type;
i                  NUMBER;
j                  NUMBER;
x_rtg_header_rec   Bom_Rtg_Pub.Rtg_Header_Rec_Type;
x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
x_operation_tbl    Bom_Rtg_Pub.Operation_Tbl_Type;
x_op_resource_tbl  Bom_Rtg_Pub.Op_Resource_Tbl_Type;
x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
x_op_network_tbl   Bom_Rtg_Pub.Op_Network_Tbl_Type;

CURSOR C1_ENG IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code
FROM  APPS.XXINN_MAINTENANCE_ROUTES t;

CURSOR C1_OPR(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
LTRIM (RTRIM(T.LONG_DESCRIPTION)) LONG_DESCRIPTION,
translate (LTRIM(RTRIM( t.OPERATION_DESCRIPTION)),'ÇĞŞİÜÖ','CGSIUO') OPERATION_DESCRIPTION
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
ORDER BY t.operation_sequence_number;

CURSOR C1_RES(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2, p_seq_number NUMBER) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
t.resource_sequence_number,
t.resource_code,
t.usage_rate_or_amount,
t.ASSIGNED_UNITS
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
AND t.operation_sequence_number = p_seq_number
ORDER BY t.resource_sequence_number;

BEGIN
FND_GLOBAL.APPS_INITIALIZE(1117, 23118, 426); --user_id, resp_id, resp_appl_id
FOR C_REC IN C1_ENG LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, C_REC.ASSEMBLY_ITEM_NAME);
DBMS_OUTPUT.PUT_LINE(C_REC.ASSEMBLY_ITEM_NAME);

P_HEADER_REC                    := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_HEADER_REC.ASSEMBLY_ITEM_NAME := C_REC.ASSEMBLY_ITEM_NAME;
P_HEADER_REC.ORGANIZATION_CODE  := C_REC.ORGANIZATION_CODE;
P_HEADER_REC.Eng_Routing_Flag   := 2;   -- 1 for manufacturing  2- for Engineering
P_HEADER_REC.Transaction_Type   := 'CREATE'; --
P_HEADER_REC.Return_Status      := NULL; --
i                               := 0;
j                               := 0;
P_OPERATION_REC.DELETE;
P_RESOURCE_REC.DELETE;

FOR c_rec2 IN C1_OPR(C_REC.ASSEMBLY_ITEM_NAME, C_REC.ORGANIZATION_CODE) LOOP
i := i + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
'            ' || C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE);
DBMS_OUTPUT.PUT_LINE('            ' ||
C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE||'-'||C_REC2.OPERATION_DESCRIPTION);
P_OPERATION_REC(i).ASSEMBLY_ITEM_NAME := C_REC2.ASSEMBLY_ITEM_NAME;
P_OPERATION_REC(i).ORGANIZATION_CODE := C_REC2.ORGANIZATION_CODE;
P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER := C_REC2.OPERATION_SEQUENCE_NUMBER;
P_OPERATION_REC(i).DEPARTMENT_CODE := C_REC2.DEPARTMENT_CODE;
P_OPERATION_REC(i).LONG_DESCRIPTION := C_REC2.LONG_DESCRIPTION; --Ana tabloda long description diye bir alan aç
P_OPERATION_REC(i).OPERATION_DESCRIPTION := C_REC2.OPERATION_DESCRIPTION;
P_OPERATION_REC(i).Operation_Type := 1; --
P_OPERATION_REC(i).Start_Effective_Date := SYSDATE; --
P_OPERATION_REC(i).Transaction_Type := 'CREATE'; --

FOR c_rec3 IN C1_RES(C_REC.ASSEMBLY_ITEM_NAME,
C_REC.ORGANIZATION_CODE,
C_REC2.OPERATION_SEQUENCE_NUMBER) LOOP
j := j + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
'                ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
DBMS_OUTPUT.PUT_LINE('                ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
P_RESOURCE_REC(j).ASSEMBLY_ITEM_NAME := C_REC3.ASSEMBLY_ITEM_NAME;
P_RESOURCE_REC(j).ORGANIZATION_CODE := C_REC3.ORGANIZATION_CODE;
P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER := C_REC3.OPERATION_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER := C_REC3.RESOURCE_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_CODE := C_REC3.RESOURCE_CODE;
P_RESOURCE_REC(j).USAGE_RATE_OR_AMOUNT := C_REC3.USAGE_RATE_OR_AMOUNT;
P_RESOURCE_REC(j).Operation_Type := 1; --
P_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE; --
P_RESOURCE_REC(j).Transaction_Type := 'CREATE'; --
P_RESOURCE_REC(j).Schedule_Flag := 1;
P_RESOURCE_REC(J).ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;

END LOOP; --res

END LOOP; -- opr

x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
x_rtg_revision_tbl.delete;
x_operation_tbl.delete;
x_op_resource_tbl.delete;
x_sub_resource_tbl.delete;
x_op_network_tbl.delete;

APPS.BOM_RTG_PUB.PROCESS_RTG(p_init_msg_list    => TRUE,
p_rtg_header_rec   => P_HEADER_REC,
p_operation_tbl    => P_OPERATION_REC,
p_op_resource_tbl  => P_RESOURCE_REC,
x_rtg_header_rec   => x_rtg_header_rec,
x_rtg_revision_tbl => x_rtg_revision_tbl,
x_operation_tbl    => x_operation_tbl,
x_op_resource_tbl  => x_op_resource_tbl,
x_sub_resource_tbl => x_sub_resource_tbl,
x_op_network_tbl   => x_op_network_tbl,
x_return_status    => p_RETURN_STATUS,
X_MSG_COUNT        => p_MSG_COUNT);

IF p_RETURN_STATUS <> 'S' THEN
error_handler.Get_Message_List(p_message_list);
FOR i IN 1 .. p_message_list.COUNT LOOP

IF i = 1 THEN
l_ret_text := p_Message_List(i).message_text;

ELSE
l_ret_text := l_ret_text || chr(10) || p_Message_List(i).message_text;
END IF;

END LOOP;

-- ROLLBACK;

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);

DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);

UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = l_ret_text
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;

ELSE

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,

P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');

DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');

UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = null
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;

END IF;

END LOOP;

END

;
/

BOM Creation API


===============================================================
bom_bo_pub.process_bom
(p_bo_identifier => 'BOM',
-- This parameter is required. It is used by the API to compare the version number of
-- incoming calls to its current version number.
p_api_version_number => 1.0 ,
-- This parameter is set to TRUE, allows callers to request that the API do the
--initialization of message list on their behalf.
p_init_msg_list => TRUE,
-- This is a set of data structures that represent the incoming business objects. This is a
--record that holds the Bill of Materials header for the BOM
p_bom_header_rec => v_bom_header_rec,
-- All the p*_tbl parameters are data structure that represent incoming business objects
-- They are PL/SQL tables of records that hold for each of the other entities.
p_bom_revision_tbl => v_bom_revision_tbl , p_bom_component_tbl => v_bom_component_tbl, p_bom_ref_designator_tbl => v_bom_ref_designator_tbl, p_bom_sub_component_tbl => v_bom_sub_component_tbl,
-- All the x*_tbl parameters are data structure that represent outgoing business objects
-- They are PL/SQL tables of records that hold records for each of the other entities except
-- now they have all the changes that the import program made to it through all the
-- steps.
x_bom_header_rec => v_bom_hdr_rec , x_bom_revision_tbl => v_bom_rev_tbl,
x_bom_component_tbl => v_bom_comp_tbl,
x_bom_ref_designator_tbl => v_bom_ref_desig_tbl, x_bom_sub_component_tbl => v_bom_sub_comp_tbl,
-- This is a flag that indicates the state of the whole business object after the
-- import. 'S' - Success, 'E' - Error, 'F' - Fatal Error, 'U' - Unexpected Error ,
x_return_status => v_return_status ,
-- This holds the number of messages in the API message stack after
--the import.
x_msg_count => v_msg_count
p_debug => 'N',
p_output_dir => '',
p_debug_filename => '' );
=========================================================

Saturday, 12 July 2014

Use of Fnd_log.STRING in PL/Sql code


Fnd_Log.STRING(log_level => Fnd_Log.LEVEL_STATEMENT,MODULE =>xxt.apps.custompackage name
                  ,message => 'your message');


FND: Debug Log Level   Codes

   LEVEL_UNEXPECTED     :  6
   LEVEL_ERROR          :  5
   LEVEL_EXCEPTION      :  4
   LEVEL_EVENT          :  3
   LEVEL_PROCEDURE      :  2
   LEVEL_STATEMENT      : 1
--------------------------------------------


FND: Debug Log Enabled : Yes

FND: Debug Log Module    Set this to %


Step 2
Login to the application and reproduce the problem.

Step 3
SELECT *
FROM fnd_log_messages
WHERE user_id = <id>
ORDER BY log_sequence DESC 





Why to set the profile option to statement level?
This profile option has following main levels.-
Error
Warning
Procedure
Statement

select * from fnd_log_messages where user_id = <id>and LOG_LEVEL =<log level>   



To purge data in production:

You can run concurrent program “Purge Debug Log and System Alerts”.


I have written a pl/sql concurrent process to interface Purchase Orders from 3rd Party System. How will add debug messages?
    fnd_log.STRING(log_level => fnd_log.level_statement
                  ,module    => '<module >'
                  ,message   => '<message >');

 Note:


fnd_log.string eventually calls procedure  FND_LOG.STRING_UNCHECKED_INTERNAL2. This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.

Thursday, 16 August 2012

R12 TCA (Trading Community Architecture):


R12 TCA (Trading Community Architecture):
What is TCA?
  • Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers.
  • Trading Community Architecture is the implementation of technology and applications to allow users to create and maintain relationships among entities. It is a way to understand who your customer interacts with inside and outside the enterprise.
Note: Customer, Vendor, Supplier are the terms that we come across very frequently.
For instance: We buy goods from a Shop Vendor so we are the Customer to that Vendor and the Vendor is our Supplier for that good. The Vendor gets goods from a merchant who actually produces it (Manufacturer), so now the vendor in-turn becomes a customer to the Manufacturer who is the Supplier.
The complete TCA can be related and runs around all these terms, these may be people we meet generally who might be a Supplier or Customer in our perspective.
Now moving ahead lets go in details about TCA.
How TCA Matters?
Before TCA:
  •  There are multiple customer definitions across the enterprise.
  •  It was very difficult to track current and historical information about the customers.
  •  There was a lack of support for mixed business.
  •  It was quite tough to understand relationships between customers and others (suppliers, partners, competitors)
After TCA:
  •   Create a central repository for the entire E-Business Suite to store information relating to all members of a trading community versus separate tables for each member-Prospects, Customers, Contacts, Employees, Partners, Distributors, Suppliers, Banks, etc.
  •   Record complex business relationships between Trading Community entities (including 3rd party relationships).
  •   Support all business models, industries, and geographies.
TCA Data Model Components:
PartyRepresents any entity that can enter into business relationships with your organization – Organization, Person, or Group
Party RelationshipA binary relationship between two parties such as a partnership. Example: Adam is a specific person party and Eve Inc. is an organization party. Adam at Eve Inc. is also treated as a party with the type relationship
LocationA Street Address
Party SiteAssociates a party with a location, indicating that party’s usage of the location
ContactA person related to an organization, this can be a relationship between an organization and a person as well as between two people
Contact PointYour avenue of contacting a party i.e. a phone number ,e-mail address or fax number
Customer AccountRepresents a customer relationship between your organization and a party
Customer Account SiteA party Site that you use in relation to a customer account, i.e, a bill-to or ship-to address
Customer Account ContactsA party contact that is used as a means of contacting the customer regarding his/her account

 TCA in 11i
  •   11i TCA already equipped to handle the following entities
–>Customer
–>Employee
–>Student
Here is Summarize information of the tables Pre and Post 11i:















TCA in R12 Includes:
  • New trading entities
–>Suppliers
–>Banks & Bank Branches
–>Legal Entity
TCA Data Model : Supplier & Customer
TCA in R12: Supplier Representation
  • Supplier organizations are in TCA
  • Terms of doing business with the supplier are in Purchasing / Payables
  • Supplier organization, address, contact, phone, email etc. are all in TCA
  • Employees are already in TCA, Payables using the same employee records in TCA
TCA – Suppliers:
  • Technical details for R12 Supplier in TCA











TCA in R12: Supplier Data Mapping
 
TCA in R12: Bank Account Model
TCA in R12: Bank Model
New Bank Account Model
  • Central place to define internal bank accounts
–>Keep track of all bank accounts in one place
–>Explicitly grant account access to multiple operating units/functions and users
  • Multi-Org Access
–>In the new model, bank accounts are owned by Legal Entities with the option to grant account use to Operating Unit (Payables, Receivables), Legal Entity (Treasury), Business Group (Payroll) .
R12 Multi-org access control
  • MOAC is new enhancement to Multiple Organization feature of Oracle Application
  • Enables Users to access the data from one or many Operating Units within a set of given responsibility
  • Data Security is maintained using Multiple Organization Security Profile, Define in Oracle HRMS.
Three key CE tables now as
1. CE_BANK_ACCOUNTS
 –>Stores bank account attributes
2. CE_BANK_ACCT_USES_ALL
–>Stores the bank account use attributes specific to Operating Unit (AR, AP) and Legal Entity (Treasury).
3.CE_GL_ACCOUNTS_CCID
–>Accounting data pertaining to the bank account use
  • All of the bank, branch and bank account related attributes in AP_BANK_BRANCHES and AP_BANK_ACCOUNTS_ALL tables will be upgraded to HZ_PARTIES and the new tables in Cash Management.
The Changing model for Bank in Global working model
–> Release 12, what’s New: 
TCA Bank Model in 11i and R12:
TCA in R12: Legal Entities
  • Legal entity is created as a party of party type ORGANIZATION or PERSON
  • An establishment is created as a party of party type ORGANIZATION.
  • TCA creates a new classification category called “Business Function”.  It is used mainly to model what business functions a party can perform in E-Business Suite
  • For modeling legal entities and establishments in TCA, classification code “Legal Entity” and “Establishment” are created under the “Business Function” class category.
  • An establishment is created as a party and always link to a party that is classified as a legal entity through the relationship model
TCA & Enhancement:
  • The Trading Community Manager Responsibility, allows users to create a setup or update data sharing and security
  • Customer Standard form that has been existing till R11i is finally gone.
  • Oracle Introduced a brand new HTML UI built using OA Frame works leveraging TCA that can be used to manage Customers, Accounts, etc.





TCA API’s List:

Order to Cash Technical Flow


Order Entry

Sales Order entry consists of two parts, Order header and Order lines.
Order Header
As order header information is entered and saved a record is created in OE_ORDER_HEADERS_ALL only and this record will be linked to many other tables and we shall discuss about those dependent tables now.
Transaction Type:
Transaction type is used to select the workflow path/process and it also defaults many other fields on the sales order. This transaction type is associated with a sequence which is responsible for providing the sales order number for the sales order.
--Transaction Type
SELECT oha.order_number ,
oha.order_type_id ,
ott.name ,
ott.description
FROM oe_order_headers_all oha,
oe_transaction_types_tl ott
WHERE 1 = 1
AND oha.order_number = '&ur_order#'
AND oha.order_type_id=ott.transaction_type_id
AND ott.LANGUAGE = 'US';
Actually order transaction type details are stored in OE_TRANSACTION_TYPES_ALL but in the above query we have used _TL table (multilingual table) as we want to display transaction name.
Price List:
Price list information is stored in QP_LIST_HEADERS_B table but we need to refer to its multilingual table QP_LIST_HEADERS_TL
--Price List
SELECT oha.order_number ,
oha.price_list_id ,
qlht.name ,
qlht.description
FROM oe_order_headers_all oha,
qp_list_headers_tl qlht
WHERE 1 = 1
AND oha.order_number = '&ur_order#'
AND oha.price_list_id=qlht.list_header_id
AND qlht.LANGUAGE = 'US';
Sales Person:
--Sales Person
SELECT oha.order_number ,
jrre.source_name ,
jrre.source_email
FROM oe_order_headers_all oha,
jtf_rs_salesreps jrs ,
jtf_rs_resource_extns jrre
WHERE 1 = 1
AND oha.order_number = '&ur_order#'
AND oha.salesrep_id =jrs.salesrep_id
AND jrs.resource_id = jrre.resource_id;
Order Header Status:
--Header Status
SELECT oha.order_number,
flv.lookup_type ,
flv.lookup_code ,
flv.meaning ,
flv.description
FROM fnd_lookup_values flv,
oe_order_headers_all oha
WHERE 1 = 1
AND oha.order_number = '&ur_order#'
AND oha.flow_status_code = flv.lookup_code
AND lookup_type = 'FLOW_STATUS'
AND LANGUAGE = 'US';
Payment Terms:
--Payment terms
SELECT oha.order_number,
name ,
description
FROM oe_order_headers_all oha,
ra_terms rt
WHERE 1 = 1
AND oha.order_number = '&ur_order#'
AND rt.term_id = oha.payment_term_id ;
Shipping Method:
--Shipping Method
SELECT oha.order_number,
wcsm.service_level
FROM oe_order_headers_all oha,
wsh_carrier_ship_methods wcsm
WHERE 1 = 1
AND oha.order_number = '&ur_order#'
AND oha.shipping_method_code= wcsm.ship_method_code
AND wcsm.organization_id = oha.ship_from_org_id;

Order Lines
Order lines consists of item ordered as part of the sales order.
On Hand Quantity of Item:
--On Hand Quantity
SELECT oha.order_number,
ool.line_number ,
ool.ordered_item ,
SUM(moq.transaction_quantity)
FROM oe_order_headers_all oha,
oe_order_lines_all ool ,
mtl_onhand_quantities moq
WHERE 1 = 1
AND oha.order_number = '&ur_order#'
AND oha.header_id = ool.header_id
AND ool.ship_from_org_id = moq.organization_id
GROUP BY oha.order_number,
ool.line_number ,
ool.ordered_item;
Though this is not an exact procedure to find out the on hand quantity, this is just for example.
Things to notice after entering the order
You can notice that the values of the following column in line and headers table as follows
 flow_status_code = ENTERED, booked_flag = N, open_flag = Y

Order Booking

Once the order is booked from the application, following are the changes occurred in the backed
  • OE_ORDER_HEADERS_ALL (flow_status_code as BOOKED, booked_flag updated to Y)
  • OE_ORDER_LINES_ALL (flow_status_code as AWAITING_SHIPPING, booked_flag updated Y)
  • Since the order is now booked, delivery details of the order are created in WSH_DELIVERY_DETAILS table. WSH_DELIVERY_DETAILS has delivery lines and LPNs.
  • OE_ORDER_LINES_ALL.line_id = WSH_DELIVERY_DETAILS.source_line_id
  • These delivery details information can been viewed form ‘Shipping Transactions Form’ and the delivery status in the application will be as ‘Ready to Release’
  • WSH_DELIVERY_DETAILS.released_status =’R’
  • WSH_DELIVERY_DETAILS.Release_Status can have any of the below valid values
  • Execute the below query to see lookup values
    SELECT lookup_type,
    lookup_code,
    meaning,
    description
    FROM fnd_lookup_values
    WHERE lookup_type = 'PICK_STATUS'
    AND LANGUAGE = 'US';
  • WSH_DELIVERY_ASSIGNMENTS assigns delivery details to a delivery and/or a parent delivery detail (LPN).
  • WSH_DELIVERY_ASSIGNMENTS.delivery_detail_id = WSH_DELIVERY_DETAILS.delivery_detail_id
  • WSH_DELIVERY_ASSIGNMENTS.delivery_id will be NULL as still pick release operation is not performed as final delivery is not yet created.
  • At the same time when order is booked ‘Demand interface program‘ is triggered in the background and demand of the item with specified quantity is created and these demand information is stored in MTL_DEMAND
  • MTL_DEMAND.demand_source_line = OE_ORDER_LINES_ALL.line_id

Reservations

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background(if scheduled) and quantities are reserved. Once this program get successfully get completed, the MTL_DEMAND and MTL_RESERVATIONS tables are updated.  OE_ORDER_LINES_ALL.line_id = MTL_RESERVATIONS.demand_source_line_id

Pick Release

Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.
Pick release can be done from ‘Release Sales Order’ form or ‘Pick Selection List Generation – SRS‘ program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from ‘Shipping Transaction form. For this case Pick Release is done from ‘Release Sales Order’ form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:
  • If material resveration is not done then MTL_RESERVATIONS gets updated now.
  • A new record is created in WSH_NEW_DELIVERIES with status_code = ‘OP’ (Open). WSH_NEW_DELIVERIES has the delivery records.
  • WSH_DELIVERY_ASSIGNMENTS.delivery_id = WSH_NEW_DELIVERIES.delivery_id
  • WSH_DELIVERY_DETAILS.released_status changes to ‘S’ (‘submitted for release’)
  • A move order is created in Pick Release process which is used to pick and move the goods to staging area (here move order is just created but not transacted). MTL_TXN_REQUEST_HEADERS, MTL_TXN_REQUEST_LINES  are move order tables
  • MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through move_order_header_id/line_id, this table holds the record temporally)
In shipping transaction form order status remains “Released to Warehouse” and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

Pick Confirm

Pick Confirm is to transact the move order created in Pick Release process. In move order transaction items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.
Order line status becomes ‘Picked’ on Sales Order and ‘Staged/Pick Confirmed’ on Shipping Transaction Form.
  • MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
  • OE_ORDER_LINES_ALL.flow_status_code =’PICKED’
  • MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Pick Transaciton
  • MTL_MATERIAL_TRANSACTIONS.trx_source_line_id  = OE_ORDER_LINES_ALL.line_id
  • MTL_TRANSACTION_ACCOUNTS is updated with accounting information
  • WSH_DELIVERY_DETAILS. released_status=‘Y’ (‘Released’)
  • WSH_DELIVERY_ASSIGNMENTS, MTL_ONHAND_QUANTITIES are updated accordingly
Note: Pick Confirm step can be eliminated if ‘Auto Pick Confirm’ is selected as ‘YES’ while performed manual pick release. If Pick release is done through shipping transactions form and if you want to perform auto pick confirm then picking rules setup has to be done.

Ship Confirm

Shipping execution of the order starts here. The goods are picked from staging area and given to shipping. “Interface Trip Stop” program runs in the backend.
  • OE_ORDER_LINES_ALL .flow_status_code =‘SHIPPED’
  • WSH_DELIVERY_DETAILS. released_status=‘C’ (‘Shipped’)
  • Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction.
  • Data is deleted from MTL_DEMAND, MTL_RESERVATIONS and WSH_NEW_DELIVERIES
  • Item deducted from MTL_ONHAND_QUANTITIES
  • MTL_TRANSACTION_ACCOUNTS is updated with accounting information.

Generating Invoice

After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.
OE_ORDER_LINES_ALL.invoice_interface_status_code = ‘YES’
ra_interface_lines_all (interface table into which the data is transferred from order management) Then Auto invoice programimports data from this table which get affected into this stage are receivables base table (especially ra_customer_trx_all and ra_customer_trx_lines_all).
RA_CUSTOMER_TRX_LINES_ALL.line_attribute_1 = OE_ORDER_HEADERS_ALL.order_number
RA_CUSTOMER_TRX_LINES_ALL.line_attribute_6 = OE_ORDER_LINES_ALL.line_id

Close Order

Last step of the process is to close the order which happens automatically once the goods are shipped
OE_ORDER_LINES_ALL.flow_status_code =’CLOSED’
OE_ORDER_LINES_ALL.open_flag = ‘N’
Header is closed in the month end, its  general oracle standard process

Wednesday, 15 August 2012

MOAC(Multi Org Access Control)



Oracle Apps Multi Org Access Control (MOAC) - A Synopsis


An improvement in R12 to facilitate cost savings and to cater to Shared Service Centers

Background of MOAC

In Release 11i Responsibilities have been tied to Operating units and each time a person needed to process transactions for a particular Operating Unit he/she had to switch to the responsibility created for the OU and thereby increasing transaction processing time(and cost), reducing efficienciences allround.

What is MOAC, What is the Change?

In R12 a Security Profile is created and all the Operating Units that need to be accessed are added. 
This is done from HR (either the Global security form- across Business units or security form- specific Business Unit)
Then Security List maintenance Program is run to make this Security Profile available for assignment to users and Responsibilities.

When it is assigned at User Level (MO: Security Profile) then the user is able to access all the OU's included in the profile.
When it is assigned at Responsibility Level (MO:Security Profile) from that responsibility it is possible to access all the OU's in the profile.

So in a shared service environment there is no need to log in and logout or change responsibilities.

Additional Features: Now say 10 OU's are included in a Security profile and assigned to a user, who needs to access only 5 daily, he can use the MO: Default Operating Unit and the User Preferences to restrict his access to the ones he really needs to use from amongst the 10.

When Migrating from R11i to R12 the existing Profile MO:Operating Unit is retained for backward compatibility whenever there is no intention to use the MOAC Feature.

So MOAC is as powerful if not more powerful than the proverbial Mother-in-Law!!!!!!!!!!!! (Think of her and her ability to gossip with multiple Neighbours (OU's) LOL. This is an attempt to help newbies to understand the concept in simple layman's language