ERP Archives - Page 10 of 87 - Oracle ERP Apps Guide

API, Apps Technical, ERP, OPM, Oracle Apps, Oracle Manufacturing, PL SQL

OPM Recipe Header Uploading through Script using API

Recipe Header Uploading can be done using the following steps:

1. First Create a staging table say “MJIL_RCP_HDR_UPL_TBL” as shown below.

CREATE TABLE MJIL_RCP_HDR_UPL_TBL
(
RECIPE_ID NUMBER (15),
RECIPE_DESCRIPTION VARCHAR2 (70 BYTE),
RECIPE_NO VARCHAR2 (32 BYTE),
RECIPE_VERSION NUMBER (5),
USER_ID NUMBER (15),
USER_NAME VARCHAR2 (70 BYTE),
OWNER_ORGN_CODE VARCHAR2 (4 BYTE),
CREATION_ORGN_CODE VARCHAR2 (4 BYTE),
OWNER_ORGANIZATION_ID NUMBER,
CREATION_ORGANIZATION_ID NUMBER,
FORMULA_ID NUMBER (15),
FORMULA_NO VARCHAR2 (32 BYTE),
FORMULA_VERS NUMBER,
ROUTING_ID NUMBER,
ROUTING_NO VARCHAR2 (32 BYTE),
ROUTING_VERS NUMBER (5),
PROJECT_ID NUMBER (15),
RECIPE_STATUS VARCHAR2 (30 BYTE),
PLANNED_PROCESS_LOSS NUMBER,
TEXT_CODE NUMBER (10),
DELETE_MARK NUMBER (5),
CONTIGUOUS_IND NUMBER,
ENHANCED_PI_IND VARCHAR2 (1 BYTE),
RECIPE_TYPE NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER (15),
LAST_UPDATED_BY NUMBER (15),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_LOGIN NUMBER (15),
OWNER_ID NUMBER (15),
OWNER_LAB_TYPE VARCHAR2 (4 BYTE),
CALCULATE_STEP_QUANTITY NUMBER (5),
FIXED_PROCESS_LOSS NUMBER,
FIXED_PROCESS_LOSS_UOM VARCHAR2 (3 BYTE),
ATTRIBUTE_CATEGORY VARCHAR2 (30 BYTE),
ATTRIBUTE1 VARCHAR2 (240 BYTE),
ATTRIBUTE2 VARCHAR2 (240 BYTE),
ATTRIBUTE3 VARCHAR2 (240 BYTE),
ATTRIBUTE4 VARCHAR2 (240 BYTE),
ATTRIBUTE5 VARCHAR2 (240 BYTE),
ATTRIBUTE6 VARCHAR2 (240 BYTE),
ATTRIBUTE7 VARCHAR2 (240 BYTE),
ATTRIBUTE8 VARCHAR2 (240 BYTE),
ATTRIBUTE9 VARCHAR2 (240 BYTE),
ATTRIBUTE10 VARCHAR2 (240 BYTE),
ATTRIBUTE11 VARCHAR2 (240 BYTE),
ATTRIBUTE12 VARCHAR2 (240 BYTE),
ATTRIBUTE13 VARCHAR2 (240 BYTE),
ATTRIBUTE14 VARCHAR2 (240 BYTE),
ATTRIBUTE15 VARCHAR2 (240 BYTE),
ATTRIBUTE16 VARCHAR2 (240 BYTE),
ATTRIBUTE17 VARCHAR2 (240 BYTE),
ATTRIBUTE18 VARCHAR2 (240 BYTE),
ATTRIBUTE19 VARCHAR2 (240 BYTE),
ATTRIBUTE20 VARCHAR2 (240 BYTE),
ATTRIBUTE21 VARCHAR2 (240 BYTE),
ATTRIBUTE22 VARCHAR2 (240 BYTE),
ATTRIBUTE23 VARCHAR2 (240 BYTE),
ATTRIBUTE24 VARCHAR2 (240 BYTE),
ATTRIBUTE25 VARCHAR2 (240 BYTE),
ATTRIBUTE26 VARCHAR2 (240 BYTE),
ATTRIBUTE27 VARCHAR2 (240 BYTE),
ATTRIBUTE28 VARCHAR2 (240 BYTE),
ATTRIBUTE29 VARCHAR2 (240 BYTE),
ATTRIBUTE30 VARCHAR2 (240 BYTE)
);

2. Next create a procedure using the script as shown below.

CREATE OR REPLACE PROCEDURE CONA_RECIPE_UPLOAD_PD (ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
IS
/******************************************************************************
NAME: APPS.CONA_RECIPE_UPLOAD_PD
PURPOSE: Recipe Header Uploading
REVISIONS:
Ver Date Author Description
——— ———- ————— ————————————
1.0 8/16/2013 1. Oracle User Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: APPS.CONA_RECIPE_UPLOAD_PD
******************************************************************************/

mjil_rcp_hdr_tbl gmd_recipe_header.recipe_tbl;
mjil_rcp_hdr_flex_tbl gmd_recipe_header.recipe_flex;
X_status VARCHAR2 (1);
X_msg_cnt NUMBER;
X_msg_dat VARCHAR2 (1000);
X_row NUMBER := 1;
l_user_id NUMBER := 1114;
l_responsibility_id NUMBER := 22883;
l_out_index NUMBER;
l_responsibility_app_id NUMBER;

CURSOR c1
IS
SELECT * FROM mjil_rcp_hdr_upl_tbl;
BEGIN
FND_GLOBAL.
APPS_INITIALIZE (l_user_id, l_responsibility_id, l_responsibility_app_id);

FOR i IN c1
LOOP
mjil_rcp_hdr_tbl (X_row).recipe_no := i.recipe_no;
mjil_rcp_hdr_tbl (X_row).recipe_version := i.recipe_version;
mjil_rcp_hdr_tbl (X_row).recipe_description := i.recipe_description;
mjil_rcp_hdr_tbl (X_row).RECIPE_STATUS := i.RECIPE_STATUS;
mjil_rcp_hdr_tbl (X_row).RECIPE_TYPE := i.RECIPE_TYPE;
mjil_rcp_hdr_tbl (X_row).formula_no := i.formula_no;
mjil_rcp_hdr_tbl (X_row).formula_vers := i.formula_vers;
mjil_rcp_hdr_tbl (X_row).routing_no := i.routing_no;
mjil_rcp_hdr_tbl (X_row).routing_vers := i.routing_vers;

mjil_rcp_hdr_tbl (X_row).delete_mark := i.delete_mark;
mjil_rcp_hdr_tbl (X_row).creation_date := SYSDATE;
mjil_rcp_hdr_tbl (X_row).created_by := i.created_by;
mjil_rcp_hdr_tbl (X_row).last_updated_by := i.last_updated_by;
mjil_rcp_hdr_tbl (X_row).last_update_date := SYSDATE;
mjil_rcp_hdr_tbl (X_row).last_update_login := 1114;

mjil_rcp_hdr_tbl (X_row).user_name := i.user_name;
mjil_rcp_hdr_tbl (X_row).owner_orgn_code := i.owner_orgn_code;
mjil_rcp_hdr_tbl (X_row).OWNER_ORGANIZATION_ID :=
i.owner_organization_id;
mjil_rcp_hdr_tbl (X_row).creation_orgn_code := i.creation_orgn_code;
mjil_rcp_hdr_tbl (X_row).owner_id := i.owner_id;

mjil_rcp_hdr_flex_tbl (X_row).attribute1 := ‘FLEX1’;

X_row := X_row + 1;
END LOOP;

gmd_recipe_header.
create_recipe_header (p_api_version => 1,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_called_from_forms => ‘NO’,
x_return_status => X_status,
x_msg_count => X_msg_cnt,
x_msg_data => X_msg_dat,
p_recipe_header_tbl => mjil_rcp_hdr_tbl,
p_recipe_header_flex => mjil_rcp_hdr_flex_tbl);

DBMS_OUTPUT.put_line (‘Return status – ‘ || X_status);
DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);

FOR i IN 1 .. X_msg_cnt
LOOP
FND_MSG_PUB.get (p_msg_index => i,
p_encoded => ‘F’,
p_data => X_msg_dat,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || X_msg_dat);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Return status – ‘ || X_status);
DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);

FOR i IN 1 .. X_msg_cnt
LOOP
FND_MSG_PUB.get (p_msg_index => i,
p_encoded => ‘F’,
p_data => X_msg_dat,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || X_msg_dat);
END LOOP;
END;
/

3. Finally register the procedure and run it.

August 17, 2013/by Oracle ERP Apps Guide

Apps Technical, ERP, Oracle Apps, Scripts, SQL, Toad

Some Useful Queries in Oracle Apps R12

How to initialize session in Oracle ?
FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API’s in Oracle Ebusiness suite. Its not required for all the API’s but its recommended that you set this profile before making any calls to either private or public API.

Mentioned below is a sample call to FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
   resp_id=>l_resp_id,
   resp_appl_id=>l_resp_appl_id);

l_user_id is the fnd user ID which will be utilized during the call.
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.
You can use either sysadmin or use some user who has all the above listed responsibilities.

For SYSADMIN, utilize the following query to get the respective values

select fnd.user_id ,
   fresp.responsibility_id,
   fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = ‘SYSADMIN’
and fresp.responsibility_name = ‘Order Management Super User’;

Another option is Help > Diagnostics > Examine and get the values from $profile session values.

How to Find PO Requisitions associated with PO Orders?
By using below query , we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.

They are 2 types of requisitions.
1) Purchasing Requisition
2) Internal Requisition.
Note:- Only Purchase Requisitions will be converted to the Purchase orders.

SELECT prha.segment1 “Requisition Number”,
prha.type_lookup_code “Requisition Type”,
pha.segment1 “Purchase Order Number”,
pha.type_lookup_code “Purchase Order Type”
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id

How to get the Cancelled Requisitions?
You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT
prha.requisition_header_id “requisition_header_id”
,prha.segment1 “Requisition Number”
,prha.preparer_id “preparer_id”
,TRUNC(prha.creation_date) “creation_date”
,prha.description “description”
,prha.note_to_authorizer “note_to_authorizer”
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code=’CANCEL’
AND pah.object_type_code=’

REQUISITION’
AND pah.object_id=prha.requisition_header_id

How to find On-hand inventory information?

Execute below query to see the onhand inventory information in oracle applications.

SELECT
NVL(substr(org.organization_code, 1,3), ‘ ‘) orgcode
,NVL(substr(msi.segment1, 1, 8), ‘ ‘) seg11
,NVL(substr(msi.segment1, 9, 8), ‘ ‘) seg12
,NVL(substr(msi.segment1, 17, 4), ‘ ‘) seg13
,NVL(moq.subinventory_code, ‘ ‘) sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ‘ ‘) trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

How to know the version in oracle apps?

select * from v$version;

With the help of above query we can find out the oracle apps version.

How to know the application is multi-org or not?

By using below query we can get the multi org status.
 
SELECT multi_org_flag
FROM   fnd_product_groups

How to know Which User is Locked the table?

This Query will list the name of user who locked the table table. The object name is taken as an input parameter.

SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name = :P_TABLE_NAME
AND nvl(vs.status,'XX') != 'KILLED';

How to get Customer Address Details in Oracle apps?


The following Query will provide the customer address details in oracle applications.

SELECT  hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
  FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
  WHERE hp.party_id = hps.party_id(+)
       AND hp.party_id = hca.party_id(+)
       AND hcasa1.party_site_id(+) = hps.party_site_id
       AND hcasa2.party_site_id(+) = hps.party_site_id
       AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
       AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
       AND hcsua1.site_use_code(+) = 'bill_to'
       AND hcsua2.site_use_code(+) = 'ship_to'
       AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hps.location_id = hl.location_id
       AND hl.country = ftt.territory_code
       AND ftt.LANGUAGE = USERENV ('lang')
  ORDER BY customer_number;


Script to find Oracle API’s for any module
With the help of below query we can find the API for a module in Oracle Apps.


select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘AR_%API%’ –- Checking for AR Related APIs
order by
a.owner, a.name

June 7, 2013/by Oracle ERP Apps Guide

Apps Functional, Apps Technical, ERP, Oracle Apps, OUM

Oracle Unified Method (OUM) – Basics and Download

Since retirement for Oracle legacy methods, there was lot of queries on OUM vs AIM , therefore this post will help reader to know more about new method.

The OUM aka Oracle Unified Method supports the entire Enterprise IT Life cycle, including support for the successful implementation of every Oracle product.

It is a robust, technology agnostic methodology Oracle Methods …

Based on industry standards and field experience
Highly scalable to support project or program specific requirements
Iteratively developed through a continuous, evolutionary process

OUM Principal

This is based out of five main principles

Business: Focus on the business and assure stake holder acceptance and delivery of the development’s efforts.
Architecture: Define architecture before resources are committed for development and implementation.
Adaptability: Encourages adaptability for scalable delivery across small and large projects possessing disparate resources and skill levels.
Implementation: Provides rapid implementation techniques to ensure business solutions in short time frames
Standards: Leverage the Unified Modeling Language and the Unified Software Development Process

OUM is able to…

Support the complete range of Oracle technology projects including

Service-Oriented Architecture (SOA)
Enterprise Integration
Custom Software
Enterprise Performance Management (EPM)
Business Intelligence (BI)
Enterprise 2.0
Enterprise Application Implementation
Software Upgrade

Currently Oracle Consulting using this methods, tools support a complete range of technology engagements.

In Summary… OUM structure

is a comprehensive set of method materials that is applicable to any type of information technology project.
is a plan-based method – that includes overview material on various approaches and standards.
It includes base guidelines for focus areas, views, phases, activities, processes and tasks, as well as work product descriptions.

OUM basically have focus areas and there underline phases and processes , in summary

OUM FOCUS AREAS

These 3 Focus Areas provide a frameworks for enterprise level planning, project and program management and implementing .

Envision Focus Area deals with development and maintenance of enterprise level IT strategy, architecture, and governance. This also assists in the transition from enterprise-level planning and strategy activities to the identification and initiation of specific projects.
Manage Focus Area provides a framework in which all types of projects can be planned, estimated, controlled, and completed in a consistent manner.
Implement Focus Area This focus Area provides a framework to develop and implement Oracle-based business solutions .

Next let take a quick overview for Implement Focus area which got replacement of old methods like AIM Foundation, AIM for Business Flows, Compass, Results Roadmap, etc.

Understanding OUM’s Implement Focus

This focus area typically based out of milestone based approach.Typical Milestone are:

Life cycle objective milestone
Life cycle architecture milestone
Initial operation capability milestone
System in production milestone
Sign-off milestone

As mention earlier this comprises of 5 phases and 14 processes, details as follows:

OUM Project Phases

The Workflows within an OUM project are categorized into five phases: Inception, Elaboration, Construction,Transition, and Production. These phases are described below:

(A) Phase I: Inception :As the first phases in the project life cycle.Major goal of this phase is to achieve concurrence among all stakeholders on the lifecycle objectives for the project. Kick off’s, review , high-level business requirements along with initial project plan is being targeted in this phase only.

(B) Phase II: Elaboration :This is the second phases in the project life cycle, where major focus would be to develop the detailed requirements models, partition the solution, develop functional prototyping, and baseline the architecture of the system.

(C) Phase III: Construction :As the third phases in the project life cycle, Construction focuses on design, implementation, and testing of functions to develop a complete system.

(D) Phase IV: Transition :This typically consist from installation onto the production system through the UAT and launch of the live application, open and ready for business.

(E) Phase V: Production : The goal of the Production phase is to operate the newly developed system, assess the success of the system, and support the users. This includes: monitoring the system; acting appropriately to ensure continued operation; measuring system performance; operating and maintaining supporting systems; responding to help requests, error reports and feature requests by users; and managing the applicable change control process so that defects and new features are prioritized and assigned to future releases.

OUM Process – Typically OUM process defined as “A discipline or sub-project that defines a set of tasks related by subject matter, required skills, and common dependencies”. A process usually spans several phases in an approach.

All OUM tasks are also organized into processes that group related tasks together. Project team members are assigned to these groupings according to their specialization and background. OUM includes the following 14 processes

BUSINESS REQUIREMENTS [RD] – Objective of the Business Requirements process is to identify, refine, and prioritize the business requirements for the proposed system
REQUIREMENTS ANALYSIS [RA]– Objective of the Requirements Analysis process is to further analyze the requirements identified during the Business Requirements process as the basis for analysis and design.
ANALYSIS [AN]– Objective of the Analysis process is to analyze, refine, and structure the system requirements via the Analysis Model.
DESIGN [DS] – Objective of the Design process is to translate requirements into a system design that meets all functional and supplemental requirements.
IMPLEMENTATION [IM] – Objective of the Implementation process is to develop the final system, through a number of iterative steps.
TESTING [TE]– The Testing process is an integrated approach to testing the quality and conformance of all elements of the new system.
PERFORMANCE MANAGEMENT [PT]– Objective of the Performance Management process is to define, construct, and execute an effective approach to managing performance throughout the project life cycle.
TECHNICAL ARCHITECTURE [TA]– Objective of the Technical Architecture process is to design an information system architecture that realizes the business vision.
DATA ACQUISITION AND CONVERSION [CV]– Objective of the Data Acquisition and Conversion process is to convert all legacy data necessary for the operation of the new system.
DOCUMENTATION [DO]– Objective of the Documentation process is to develop documentation that augments product manuals with information about custom software and business procedures.
ORGANIZATIONAL CHANGE MANAGEMENT [OCM]– Objective of the Organizational Change Management process is to identify the human and organizational challenges of the project in order to mitigate risk.
TRAINING [TR]– Objective of the Training process is to adequately train the project team to begin the project and train the users to run the new system.
TRANSITION [TS]– Objective of the Transition process is to install the system and go production.
OPERATIONS AND SUPPORT [PS]– Objective of the Operations and Support process is to monitor and respond to system problems to fix errors and performance problems and plan enhancements.

Believe with this method companies get simplified project management, improved planning, reduced risk which make them manage project within time within Budget.Next post will more the details for other Focus area.Keep watching this space.

Download

May 15, 2013/by Oracle ERP Apps Guide

Apps Functional, Apps Technical, ERP, Oracle Apps, OUM

Retirement of Oracle AIM & AIM for Business Flows

Last week there was an email from Oracle for retirement for legacy Methods like AIM Advantage, AIM for Business Flows, EMM Advantage. These methods and Methodology been used by mostly Oracle Consulting,and there partners. The retirement date is January 31, 2011. Going Forward , OUM is going to be replaced by these methods, and not to forgot OUM version 5.3 was released a month back (November 13,2010).

OUM aka ORACLE UNIFIED METHOD is currently available to certified Oracle Partners(Platinum and Gold) and it is not freely downloadable publically. OUM supports the complete range of Oracle technology projects including:

Oracle Database
Oracle E-Business Suite
Oracle Enterprise 2.0
Oracle Fusion Middleware
Oracle Service-Oriented Architecture (SOA)
Oracle Business Intelligence – Applications
Oracle Business Intelligence – Technology
Oracle Data Warehousing
Governance, Risk, & Compliance (GRC)
Identity Management (IdM)
Oracle Transportation Management
Performance Management – Business Intelligence and Analytics
JD Edwards EnterpriseOne
PeopleSoft Enterprise
Siebel

Will keep you posted with some more updated on OUM time to time.

May 15, 2013/by Oracle ERP Apps Guide

AP, Apps Technical, AR, Basic Concepts, ERP, INV, Oracle Apps, SLA

R12 SLA Tables join conditions to AR, AP, INV,Payments and Receiving

Here I am trying to describe R12 SLA(Sub Ledger Accounting) Procedure.

1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL

2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process

– Applies accounting rules

– Loads SLA tables, GL tables
– Creates detailed data per accounting rules, stores in SLA “distribution links” table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah

XLA_AE_LINES xal

XLA_TRANSACTION_ENTITIES xte

XLA_DISTRIBUTION_LINKS xdl

GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id

xah.application_id = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xah.entity_id = xte.entity_id

xah.ae_header_id = xdl.ae_header_id

xah.event_id = xdl.event_id

xal.gl_sl_link_id = gir.gl_sl_link_id

xal.gl_sl_link_table = gir.gl_sl_link_table

xah.application_id = (Different value based on Module)

xte.entity_code =

‘TRANSACTIONS’ or

‘RECEIPTS’ or

‘ADJUSTMENTS’ or

‘PURCHASE_ORDER’ or

‘AP_INVOICES’ or

‘AP_PAYMENTS’ or

‘MTL_ACCOUNTING_EVENTS’ or

‘WIP_ACCOUNTING_EVENTS’

xte.source_id_int_1 =

‘INVOICE_ID’ or

‘CHECK_ID’ or

‘TRX_NUMBER’

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types

xdl.source_distribution_type = ‘AP_PMT_DIST’

and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id

—————

xdl.source_distribution_type = ‘AP_INV_DIST’

and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id

—————

xdl.source_distribution_type = ‘AR_DISTRIBUTIONS_ALL’

and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id

and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id

—————

xdl.source_distribution_type = ‘RA_CUST_TRX_LINE_GL_DIST_ALL’

and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id

—————

xdl.source_distribution_type = ‘MTL_TRANSACTION_ACCOUNTS’

and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id

—————

xdl.source_distribution_type = ‘WIP_TRANSACTION_ACCOUNTS’

and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id

—————

xdl.source_distribution_type = ‘RCV_RECEIVING_SUB_LEDGER’

and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id.

Hope this will help you.

February 1, 2013/by Oracle ERP Apps Guide

OPM Recipe Header Uploading through Script using API

Some Useful Queries in Oracle Apps R12

Oracle Unified Method (OUM) – Basics and Download

Retirement of Oracle AIM & AIM for Business Flows

R12 SLA Tables join conditions to AR, AP, INV,Payments and Receiving

Recent Comments

Latest Posts

Ads Blocker Detected!!!

Share this:

Share this:

Share this:

Share this:

Share this:

Recent Comments

Latest Posts

Ads Blocker Detected!!!