Search This Blog

Thursday, March 29, 2012

Order Management(OM) Integration Options

OM is one of the most complex modules in EBS, and in typical complex business model, the integration of other product or third party can't be denied. Processing an Order requires integration with many other business areas. Most integration points with other Oracle products are implemented via PL/SQL-based APIs.Here is brief discussion for Order Management EBS Integration points.

OM Integration

double-arrowHow Order Management integrated with iStore?

OM records customer orders placed via iStore, Order Capture and other CRM applications. It validates setup for shipping and payment options along with providing order status and the shipping information to customers. Once the quote is converted into an order, you can only make changes to the order through Order Management, prior to booking.

double-arrowHow Order Management integrated with Telesales?

Telesales' eBusiness Center has several integrations with Order Management. There is an Order tab to view order history and create new orders.

double-arrowHow Order Management integrated with Cost Management?

OM call the the Cost Management CST_COST_API to obtain cost from cst_item_costs or cst_quantity_layers when the Gross Margin feature of OM is enabled.

double-arrowHow Order Management integrated with Field Service?

Field Service Report requires specifically that you setup Price Lists, Units of Measure (UOM), and two Inventory Item Attributes in Order Management. Price Lists contain the list price for an item. Items could be material, but also labor and expenses like units of driving distance. Once material, expense and labor transactions for a task have been taken down on the Field Service Debrief, this information is updated to Charges. In Charges the list price for the item is received from Order Management and is used to generate an invoice for a customer.

double-arrowHow Order Management integrated with Depot Repair?

This is used by Depot Repair to create RMA and Sales Orders, validate customer accounts, and invoice customers for repairs.

double-arrowHow Order Management integrated with Install Base?

Information about Install Base trackable items is interfaced to Install Base in the following ways:

  • Shippable Items: For both orders and returns, information is interfaced to Install Base via Inventory Interface.
  • Non-Shippable Items: For both order and returns, information is interfaced to Install Base via the Order Management Fulfillment workflow activity Install Base also supports Internal Sales Order transactions by appropriately creating /updating item instances as a result of transactions between internal organizations such as pick transactions, shipments, and receipts.

double-arrowHow Order Management integrated with Service Contracts?

Service Contracts need to pulls information from the Install Base newly created customer records and creates an ownership record.

  • Warranty: A Warranty contract is created when a Serviceable product is shipped.
  • Extended Warranty: An Extended Warranty contract is created when an Extended Warranty is sold on a sales order. Oracle EAM & OM integration
  • Subscription: Fulfillment starts after the contract approval process.
  • RMA: Service Contracts sends Order Management RMA information

Monday, March 26, 2012

Descriptive Flexfield Basics in Oracle Apps

Here are the screenshots......The descriptions are embedded within the screenshots.

We are in "Bank Branches screen" below, that is available in Payables responsibility. We need to add a new field as below.
Image

Once having noted down the table, we try to find the Title of the DFF for that Table. We go to Flexfield/Register
Image

Here we pick the Title of the respective DFF
Image

Query on that DFF Title from Descriptive Flexfield Segment Screen
Image

Add a new segment under "Global Data Elements"
Image

The options for making mandatory or enabling validations for the new field.
Image

Once you finalize the changes, you will be prompted to Freeze the DFF definition. Click on OK
Image

Now, we see the fruits of our configuration
Image

Monday, March 19, 2012

Understanding “Retainage” from Techies Mind

This kind of business normally you will find in Project intensive companies, where retainage refers to a portion of the payment that is withheld until the completion of a project.

In that case the client doesn't pay the contractor/party the retainage until all work on the project is complete.

Retainage is negotiated upfront and is stated as a percentage or amount of the overall cost of the project.

There is no limit for retainage.

dgreybarrow Example

Example 1

For example, a lets you company may hire a IT contractor for a $100,000 project. The contract stipulates 10% retainage. Over the course of the project, the client pays the contractor $90,000 for the work. But 10% of the total cost, $10,000, is withheld until the completion of the project. Once the project is completed, all the final details have been wrapped up, and the client is satisfied with the results, the client will pay the contractor the retainage amount.

Example 2

There may be other case you can have this way , the contract can specify that you will retain 20 percent from all payments until 25 percent of work is complete. Therefore, whenever the contractor sends you an invoice, you retain 20 percent of each payment until the overall progress reaches 25 percent.

dgreybarrowAccounting Treatment

Retainage is recorded on the balance sheet.

When the voucher is processed, the following accounting transaction is generated:
DR) Expenditure(Expense)
CR) Voucher payable (Liability)
CR) Retainage payable (Retainage)

When the voucher is released by audit for payment, the following transaction is generated:
DR) Vouchers payable(Liability)
CR) Cash

To release the retainage after successful completion of the contract the following transaction is generated:
DR) Retainage payable (Retainage)
CR) Cash

dgreybarrow Payables retainage @Oracle R12

These are two mandatory set up required in R12 to get retainage Function.

1)Retainage Account Setup
You need to first define the retainage account for the operating unit in the GL Accounts region on the Accounting tab of the Financials Options.

Setup > Options > Financials Options> Accounting tab

Oracle Payables uses this account to record the distribution on progress invoices and subsequent retainage release invoices for funds withheld from suppliers.

2)Supplier Terms and Controls Setup
You need to do Set up for your supplier's terms and control defaults using the Suppliers page.

.

Posted in Oracle Payable | No Comments »

Page 1 of 41234»

How conversion and interface differ?

There are good numbers of parameter on which they can be categorize. Take few of them:

  • Frequency
    • conversions are a one time event
    • interfaces are ongoing
  • Occurrence in the project timeline
    • conversions executed before production
    • interfaces executed during production
  • Manner of execution
    • conversions are batch
    • interfaces may be batch or real time
  • Complexity
    • Conversion does have very complex, its totally depends upon the data mapping activity.
    • coordinating with other systems make interfaces more complex
  • Maintenance
    • Maintence of interface is bit cost intensive task.

Interface Type
You have learned how interface is differ from Conversion/Migration. Now lets take few types of interfaces:
Normally in any system , there are two kinds of interface as:

Inbound Interfaces

  • An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables.
  • A typical inbound interface would follow these steps:
    1. Extract data from legacy system into a flat file.
    2. Use SQL*Loader or equivalent tool to upload information into a temporary table.
    3. Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables.
    4. Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.

inbound

Outbound Interfaces

o An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file).
o A typical outbound interface would follow these steps:
- Write a PL/SQL program to extract data from Oracle base tables into a flat file.
- Use a custom program to read that data and post it into the legacy system

outbound

Do we have some other standard way to do interface?

  • Open Interface is a table based interface registered as a concurrent program
    • process records in batches.
    • spawned(Pro-C) or PL/SQL based programs.
  • API (Application Program Interface) is a parameter based stored procedure
    • directly impacts base database tables.
    • may be called from Oracle open interfaces,Forms, Reports.
  • EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO's, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.(
  • Enterprise Application Integration (EAI) solutions are often used when complex integration requirements exist.

What Is An Open Interface Table (OIT)?

  • For inbound interfaces, the interface table is the intermediary table where data from the source application temporarily resides until it is validated and processed into an Oracle base table through a standard import concurrent program.
  • Open Interface Tables are standard Oracle tables.
    • Oracle uses OITs to provide a simple interface to Oracle base tables.
    • Oracle has list of all the open interface that oracle offered in there product.

Oracle Interface Program

  • Most Oracle modules have standard import programs (concurrent processes) to facilitate custom inbound interfaces. The specific processing performed varies by application.
  • These programs pull data from the open interface tables, validate the data, and then insert into one or more Oracle base tables.
  • Upon successful completion of processing, the program deletes the processed rows from the interface table or marks them as completed.
  • Depending on the import, errors can be viewed in various ways (exception reports, error tables, forms, etc…).

Examples of standard import programs:

  • GL: Journal Import
  • AP: Payables Open Interface
  • AR: Customer Interface
  • INV : Item Import
  • AR - Autoinvoice
Ok, thats is all about Conversion and Interfaces briefing. I will write some more for Tools used for Conversion/Interface and will discuss some granular details about a conversion/migration project and share some information about how and where AIM's documents fits into conversion and Migration projects. So watch out this space for some more stuff for conversions..

Sunday, March 18, 2012

What is In Requisition import

This interface lets you integrate Oracle Purchasing quickly with new or existing applications such as material requirements planning, inventory management, and production control systems and also helps to enter requisitions from external sources.

dgreybarrow First Timer, know these first

Before you start, you need to understand the database objects which play critical role.

Table Name Description Type
PO_REQUISITIONS_INTERFACE_ALL This is the main Requisition Import interface table Interface table
PO_REQ_DIST_INTERFACE_ALL

The PO_REQ_DIST_INTERFACE_ALL table was used in Release 11, for Self-
Service Purchasing

Interface table
PO_INTERFACE_ERRORS This table stores all errors resulted from import process. Errors table
PO_REQUISITION_HEADERS_ALL Base table that stores requisition headers REQ Base table
PO_REQUISITION_LINES_ALL Base table that stores requisition lines REQ Base table
PO_REQ_DISTRIBUTIONS_ALL This table stores requisition distributions REQ Base table

dgreybarrow Steps by Steps

These are the basic steps for the import process :

  • Insert data into interface table
  • Run import program to insert data into base tables
  • Review imported data from the front end.

First steps start with Inserting data into the Requisitions Interface Tables

You can inserts a single row into the PO_REQUISITIONS_INTERFACE_ALL and/or the PO_REQ_DIST_INTERFACE_ALL table for each requisition line that you want to import.

You identify the set of rows you want to import with each other by setting the INTERFACE_SOURCE_CODE and BATCH_ID columns appropriately in the PO_REQUISITIONS_INTERFACE_ALL table. You then pass these values as parameters to the Requisition Import program. If you do not specify any values for these parameters, the program imports all the requisition lines in the PO_REQUISITIONS_INTERFACE_ALL table.

Typically while inserting you might notice as per documentation, there are three types of columns exist in the in interface tables, as

  • Required Data
    • Required : That mean, You must provide values for all columns that are required.No choice.
    • Conditionally required :You may also have to provide values for columns that are conditionally required. Providing a CURRENCY_CODE, the RATE, RATE_DATE, and RATE_TYPE accordingly to Rate fields are conditionally required.
  • Derived Data : System will default those columns using logic similar to that used by the Requisitions form.
  • Optional Data : These are optional to be filled and will not stop the import process.

Review data before calling Import program.

when you submit the import program, third parameter is approval status , which altogether have different logic, which you need to understand the impact.

dgreybarrow How the Initiate Approval After Requisition Import parameter works and is the relationship between it and AUTHORIZATION_STATUS field?

Yes, this is important to understand the significance and importance of this field .

Req Import

Look at the table below , helps you to understand the value of parameter.

Value in table Parameter
value
Result
Approved YES Req is approved, no call to workflow
Incomplete YES Req is created, workflow is called. Result could be Incomplete, Approved, or In-Process
Approved NO Has no effect, record in the Interface states
Approved, no call to req approval workflow. Req is created as Approved.
Incomplete NO Req is created, and is Incomplete. No call to the workflow for req approval

dgreybarrow Take Away

If the records got imported successfully without issues, the records will stay in the interface tables.

  • You can notice ,successful records get PROCESS_CODE as “ACCEPTED”
  • It is good practice and important to check the Purchasing Interface Errors report always.
  • This Error report you can submit after your import completed.
  • Because the Purchasing Documents Open Interface saves or errors out line by line, it can accept partial documents. So that you may find a document has been accepted although some lines from it has been rejected . Therefore, to see which document lines were not submitted because of errors, you must check the Purchasing Interface Errors report.

dgreybarrow What happen when IMPORT run

This seems sound intresting to you.

when you run Requisition Import concurrent request , program enforced to complete in three steps phases.

The Requisition Import program operates in three phases.

Phase 1 :Validation

The first phase, where the program validates your data and derives or defaults additional information.

  1. The program generates an error message for every validation that fails and accordingly, it will create a row in the PO_INTERFACE_ERRORS table with detailed information about each error.
  2. Program will then check for the column MULTI_DISTRIBUTIONS in the PO_REQUISITIONS_INTERFACE_ALL table, if it is set to yes ( Y ) , Requisition Import will then check for corresponding distributions in the PO_REQ_DIST_INTERFACE_ALL table and if it did not find any corresponding distribution information, it will loads these as errors in the PO_INTERFACE_ERRORS table.

Phase 2 :Grouping

In the second phase, the program groups and numbers the validated requisition lines according to the following criteria:

  1. If you specify a value in the REQ_NUMBER_SEGMENT1 column of the PO_REQUISITIONS_INTERFACE_ALL table, all lines with the same value for this column are grouped together under a requisition header.
  2. If you provide a value in the GROUP_CODE column, all lines with the same value in this column are grouped together under a requisition header.
  3. If you do not provide values in either of these columns, the Requisition Import program uses the Group By parameter to group lines together.
  4. If you do not provide a value for this parameter, the program uses the default Group By that you set up to group requisition lines.

Phase 3 :Deletion

In the third phase, the program deletes all the successfully processed rows in the interface tables, and creates an output which lists the following :

  1. Number of interface records that were successfully imported.
  2. Number of interface records that were not imported.

This output can be viewed by choosing View Output for the Requisition Import concurrent Request ID in the Requests window.

dgreybarrow What Can , what Cann't

You can import approved or unapproved requisitions using the Requisitions Open Interface.

If you are using requisition encumbrance, approved requisitions that you import automatically become pre-approved.

In case there are some records not imported appears in the output from the report, then you can launch the Requisition Import Exceptions Report to view the rows that were not imported by the Requisition Import program along with the failure reason(s) for each row.

dgreybarrow A very common Observation while Requisition import

When you trying to submit a request to run the Requisition Import and when you try to select from the list of values in the parameter field for Import Source, you do not find any value and you receive the error message: "FRM-41830 List of Values contains No Entries "

Therefore you need to understand the List of Values for the Import Source parameter is dynamically determined by the INTERFACE_SOURCE_CODE column value in the PO_REQUISITIONS_INTERFACE_ALL table, So that if there are no values for the LOV, then there are no records in the table for the current organization.

In that case you just have to ensure that there are records in the interface and that the field INTERFACE_SOURCE_CODE is already populated.

What is In “Purchase Order import”

You can use Purchase Document Open Interface which allows you to quickly import a large volume of Standard Purchase Orders into Oracle Purchasing.

The Import process involves populating the PO interface tables with the document information to be imported and then running the Import
Standard Purchase Orders concurrent program which will validate the data and create the PO in the application and return an error message if something fail.

dgreybarrow First Timer, know these first

Before you start, you need to understand the database objects which play critical role.

Table Name Description Type
PO_HEADERS_INTERFACE This is the table where to insert PO headers data in interface table. Interface table
PO_LINES_INTERFACE This is where we insert PO lines information to be imported ( it is used also for Shipments details ) Interface table
PO_DISTRIBUTIONS_INTERFACE This is where we insert PO distribution details before import Interface table
PO_INTERFACE_ERRORS Stores all errors resulted from import process. Errors table
PO_HEADERS_ALL Stores document headers for purchase orders, purchase agreements,quotations, and RFQs PO Base table
PO_LINES_ALL Stores purchase document lines for purchase orders, purchase agreements, quotations, and RFQs PO Base table
PO_LINE_LOCATIONS_ALL Stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs PO Base table
PO_DISTRIBUTIONS_ALL Stores purchase order distributions PO Base table

dgreybarrow Steps by Steps

Know what is getting ining Data into Purchase Order Interface Tables

  1. Load PO header, lines, shipments and distributions data from your source system into the following interface tables
    • PO_HEADERS_INTERFACE
    • PO_LINES_INTERFACE
    • PO_DISTRIBUTIONS_INTERFACE
  2. Once the data has been inserted into the interface tables, a queries like the following can be used to review the information before running the import program :
    • Select * from PO_HEADERS_INTERFACE where INTERFACE_HEADER_ID=
    • Select * from PO_LINES_INTERFACE where INTERFACE_HEADER_ID=&headerid
    • Select * from PO_DISTRIBUTIONS_INTERFACE where INTERFACE_HEADER_ID=&headerid

Review data before calling Import Standard Purchase Orders program.

when you submit the import program, third parameter is approval status , which altogether have different logic, which you need to understand the impact.

Purchase Order import

dgreybarrow Understanding approval status in parameter

Significant impact is there on Approval Status parameter and have import logic which is as below:

Status in
Interface Table
Imporft Program Approval Status Parameter Resulting Document Status
NULL Incomplete Incomplete
NULL Approved Approved
NULL Initiate Approval Initiate Approval
Incomplete Incomplete Incomplete
Incomplete Approved Incomplete
Incomplete Initiate Approval Initiate Approval
Approved Incomplete Approved
Approved Approved Approved
Approved Initiate Approval Approved

dgreybarrow Take Away

If the records got imported successfully without issues, the records will stay in the interface tables.

  • You can notice , successful records get PROCESS_CODE as “ACCEPTED”
  • It is good practice and important to check the Purchasing Interface Errors report always.
  • This Error report you can submit after your import completed.
  • Because the Purchasing Documents Open Interface saves or errors out line by line, it can accept partial documents. So that you may find a document has been accepted although some lines from it has been rejected . Therefore, to see which document lines were not submitted because of errors, you must check the Purchasing Interface Errors report.

dgreybarrow What happen with IMPORT

This seems sound intresting to you.

  • The Purchasing Documents Open Interface (PDOI) programs first process a record from the PO_HEADERS_INTERFACE table.
  • Then, the program processes the child records in the PO_LINES_INTERFACE table then process the PO_DISTRIBUTIONS_INTERFACE table, before going on to the next PO represented by a record in PO_HEADERS_INTERFACE. Make sense.
  • In between , If the program gets an error while processing a record, the program writes the error details to the PO_INTERFACE_ERRORS table and increments the record's error counter.
  • Therefore, the Purchasing Documents Open Interface saves or errors out on a line-byline basis.
  • This means that if an error is found in a document line, only that line is rolled back (not submitted to Purchasing), and we will be able to find the error in the PO_INTERFACE_ERRORS table.
  • You should be aware , because the Purchasing Documents Open Interface can accept partial documents as it saves or errors out line by line.
  • If an error is found in a header, none of its lines are processed.
  • The Purchasing Documents Open Interface rolls back the header, does not process its lines, and does the following:
    • Sets the PROCESS_CODE column value to REJECTED in the PO_HEADERS_INTERFACE table.
    • Writes out the record identification number and the details of the error to the PO_INTERFACE_ERRORS table.
    • Begins processing the next header record.
  • If no processing errors are found during processing, the header record and all successfully submitted child records are loaded into Purchasing, and then flagged as processed by setting the PROCESS_CODE column to ACCEPTED.

As mention earlier, To check for records in error, the Purchasing Interface Errors Report can be run to provide information as to the cause of the error.

dgreybarrow Other tools

This is most acceptable interface and widly used every where, therefore Oracle have Diagnostics tool for this keeping developer in mind.

Oracle Diagnostics tool name is Oracle Purchasing Documents Open Interface Data Collection Test.

This diagnostic test will verify the data in the interface tables used by the purchasing documents open interface (PDOI) so that it can be used proactively or reactively to resolve or prevent issues in the purchasing documents open interface (PDOI).

Hope you find this is very useful and productive tool.should you need any input send me offline.

A single Query covering P2P life Cycle

In Finance, transaction management processing is one of labor intensive task in ERP, as it requires extensive data entry , chance are very very high for duplication/re-entry. As we know Procure to Pay life cycle start itself from contract management till making payment.

As we know the efficient Procure to pay process have these sub processes;

  • Contract Management
  • Purchase Requisitions
  • Purchase Orders
  • Accounts Payable - Managing invoice
  • Supplier Payment

p2p

In real business world, many time when system is running external/internal auditor are more interested in scrutiny of:

  • Goods received / invoices received
  • Inaccurate or duplicate vendor & material master records
  • Discrepancies in payment terms
  • Delays / long processing times
  • Detect duplicate vendor
  • Unusually large or small payments
  • Unauthorized changes made to invoices
  • Detect Duplicate invoice
  • Detect Duplicate payment
  • Approval status

Therefore, it is Inhouse ISD/Finance IT or implementing company responsibility is to provide such kind of adhoc reporting for auditor so that they can satisfy the audit requirement.

A 'P2P' query that made Auditors happy

It was brought by ISD team , as part year end audit for a ERP system which went live 3 month back. It was a one of requirement to display data for a particular PO which covers data from there all 5 five phases, means a particular PO line consist of:

  1. Requisition Detail
  2. Purchase Order Details
  3. Receiving Details
  4. Invoicing Detail
  5. Payment Details

Therefore thought to share this query, hope this would be great help who have such kind of adhoc requirement from daily life.

Here is query:

SELECT
A.ORG_ID "ORG ID",
E.VENDOR_NAME "VENDOR NAME",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
F.VENDOR_SITE_CODE "VENDOR SITE",
F.ADDRESS_LINE1 "ADDRESS",
F.CITY "CITY",
F.COUNTRY "COUNTRY",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
D.SEGMENT1 "PO NUMBER",
D.TYPE_LOOKUP_CODE "PO TYPE",
C.QUANTITY_ORDERED "QTY ORDERED",
C.QUANTITY_CANCELLED "QTY CANCALLED",
G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
G.UNIT_PRICE "UNIT PRICE",
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount",
(SELECT
DECODE(PH.APPROVED_FLAG, 'Y', 'Approved')
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) "PO STATUS",
A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
A.INVOICE_AMOUNT "INVOICE AMOUNT",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
A.INVOICE_NUM "INVOICE NUMBER",
(SELECT
DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER "CHEQUE NUMBER",
TO_CHAR(TRUNC(I.CHECK_DATE)) "PAYMENT DATE"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = 'Y'
AND D.TYPE_LOOKUP_CODE != 'BLANKET';

The important section which cover in the query output is as:

1. Information for Supplier

1

2.Purchase Order details

2

3. Receiving Items Details

3

4.Invoice Details

4

5.Payment Details

5

You download the query and details here.download btn

PO: Tips and useful Query

The consultant life while working at client site is not easy during ERP transformation projects, many times it's required to provide some adhoc query for extract to ends users, therefore it is important to have a cheat sheet so that such untimely things can be easily handled in sort span. Hope these query and tips useful to all Inhouse IT personals who is part of Implementation Project team.

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.

Internal Requisitions without Sales order

2. You want to display what requisition and PO are linked(Relation with Requisition and PO )

Requisition and PO

3. You need to list out all cancel Requisitions

Cancel Requisition

4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order)

PR without PO

5. You need to list all information form PR to PO ...as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.

PR to PO

6.Identifying all PO's which does not have any PR's

PO without Requisition

7. Relation between Requisition and PO tables

Here is link:

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

8.You need to find table which hold PO Approval path...

These two table keeps the data:

  • PO_APPROVAL_LIST_HEADERS
  • PO_APPROVAL_LIST_LINES

9. List all the PO's with there approval ,invoice and Payment Details

List PO's with Approval , invoice and Payment info

Wednesday, March 14, 2012

Where to start for Customer Interface

1.The first steps would be your is preparing Receivables setup activity

  • Be sure to set up new data in Receivables that the Customer Interface should import. For example:
    • AutoCash Rule Sets
    • AutoInvoice Grouping Rules
    • Collectors
    • Customer Addresses
    • Customer Bank Information
    • Customer Exemptions
    • Customer Profile Classes
    • Demand Classes
    • Dunning Letter Sets
    • Freight Carriers
    • Payment Methods
    • Payment Terms
    • Statement Cycles
    • Tax Codes
  • Be sure to also set up Lookups in Receivables that the Customer Interface should import. These are the lookups:
    • Countries
    • Site Use Codes
    • Credit Ratings
    • Risk Codes
    • Account Statuses
    • Communication Types
    • Customer Classes

2. Next is to map the Interface Tables

  • RA_CUSTOMER_INTERFACE_ALL
    • ORIG_SYSTEM_CUSTOMER_REF
    • CUSTOMER_NAME
    • CUSTOMER_STATUS
    • INSERT_UPDATE_FLAG
    • CUSTOMER_NUMBER
    • ORIG_SYSTEM_ADDRESS_REF
    • PRIMARY_SITE_USE_FLAG
    • SITE_USE_CODE
    • ADDRESS1
    • COUNTRY
    • LOCATION
  • RA_CUSTOMER_PROFILES_INT_ALL
    • CUSTOMER_PROFILE_CLASS_NAME
    • ORIG_SYSTEM_CUSTOMER_REF
    • INSERT_UPDATE_FLAG
    • CREDIT_HOLD
    • ORIG_SYSTEM_ADDRESS_REF
  • RA_CONTACT_PHONES_INT_ALL
    • ORIG_SYSTEM_CUSTOMER_REF
    • ORIG_SYSTEM_TELEPHONE_REF
    • TELEPHONE
    • TELEPHONE_TYPE
    • INSERT_UPDATE_FLAG
    • ORIG_SYSTEM_ADDRESS_REF
    • ORIG_SYSTEM_CONTACT_REF
    • CONTACT_LAST_NAME
  • RA_BANKS_INTERFACE
    • ORIG_SYSTEM_CUSTOMER_REF
    • PRIMARY_FLAG
    • START_DATE
    • BANK_ACCOUNT_NAME
    • BANK_ACCOUNT_CURRENCY_CODE
    • BANK_ACCOUNT_NUM
    • BANK_BRANCH_NAME
    • ORIG_SYSTEM_ADDRESS_REF
  • RA_CUST_PAY_METHOD_INTERFACE
    • ORIG_SYSTEM_CUSTOMER_REF
    • START_DATE
    • PAYMENT_METHOD_NAME
    • PRIMARY_FLAG
    • ORIG_SYSTEM_ADDRESS_REF

3. RUN the Import Program

  • Run Import after AR Customer Interface tables have been populated
  • Program will validate the data in the interface table before creating records in Receivables
  • Run the Customer Interface process through the Submit Request window
  • But, a separate navigational path is also provided
    Interfaces -> Customer
  • Check output file for errors

erroroutput

  • Make corrections and repeat import process

Not Surprise , if you get these....Common Errors..very common

  • a3: Bill_To_Orig_Address_Ref is not a valid bill-to address
    • Verify the Bill-To address reference is valid. Keep in mind that when using the bill-to reference with a ship-to address record... the bill-to must already exist in Receivables.
    • Note: Ran into this issue. Try running bill-to records through the interface first and ship-to records as second batch - this will resolve the error. Do not Interface with both in the same batch.
  • a1:Customer record for insert must have validated profile record defined
    • New customers and each Bill-To record must have a customer level profile in the RA_CUSTOMER_PROFILES_INT_ALL table.
  • a8: Conflicting profile classes specified for this customer/site
    • Profile classes for customer and bill-to must be the same. Sites cannot have a profile class different from the customer.
  • J1: Site_USE_CODE is not updateable.
  • J3: LOCATION is not updateable.
  • J2: PRIMARY_SITE_USE_FLAG is not updateable.
    • Keep in mind that site_use_code, primary_use_flag, and location may not be updateable through the Customer Interface
  • A3: Customer reference for insert is already defined.
  • A5: Customer Number already assigned to a different customer.
    • Customer reference and Customer number are values that must be unique. Verify the customer reference or customer number does not already exist for another customer.

Tips and Technique

1. Check out some of the Profile Options hitting Customer Import

  • HZ: Generate Party Number
    • This the profile option can be updated at Site, Application, Responsibility and User levels.This profile option determines whether party number should be auto-generated. If value is 'No',means party number must be passed in by the user else if 'Yes' or if the value is not set, party number will be auto-generated.
  • HZ: Generate Party Site Number
    • same as above for party site number set at all leval.
  • HZ: Internal Party
    • This profile option is used as a part of CRM setup. This must be set if CRM is installed. It is used for data migration purpose.
  • HZ: Generate Contact Number
    • This profile option determines whether contact number should be auto-generated.If the value is 'No', contact number must be passed in by the user. If the value is 'Yes' or if the value is not set, contact number will be auto-generated.

2. Automatic sequence number for customer number

Many times AR department is not like oracle seeded number which start by default 1000.Options are there:

From R11 and 11i, you cannot change the sequence via the forms and therefore any change that you make to the sequence would have to be
through SQLPlus and that would not be supported.

To set the sequence number

Step 1. In the Application Developer responsibility,
Menu: Application=>Database=>Sequence
Step 2. Query on sequence RA_CUSTOMERS_NUM_S
This will bring up the sequence for the customer numbers and you can enter the number that you want it to start from.

To set automatic numbering for customer after setting the sequence:

Step 1. Menu:=>System=>System Options
Step 2. Region - Invoicing and Customers
Step 3. Check the box for Automatic Customer Numbering.

3. When doing Migration from other system, adviced to use TRIM Function

  • When loading interface tables remove all trailing spaces from import data.
    Example: LTRIM(RTRIM(customer_name))

4.If importing large number of customers, run in smaller batches instead of all at once.

Oracle benchmark is about 10,000 records per batch is ideal, it is suggested to keep the batch size small.

5.When rolling out in Multi-Org , then you must populate the org_IDs in the interface tables and run the customer interface for each organization set-up responsiblity.

Monday, March 5, 2012

Configure the Account Analysis Report in SLA/GL

In R12 the Account Analysis Report in applications Subledger Accounting
and General Ledger, have been completely written as XML Publisher programs. The data is extracted and generated into XML format using a Data Template extract via the JAVA program XDODTEXE.
The layout is then generated using the Output Post Processor concurrent manager. The problem for users of these reports is that they generate very large data sets so the Release 12 instance needs to be configured to handle these large programs.

A. Set the Scalable Option to on for these programs.
This prevents this error in the Subledger Accounting program's log:
Calling XDO Data Engine...
****Warning!!! Due to high volume of data, got out of memory exception...***
****Please retry with scalable option or modify the Data template to run in scalable mode...***
1. As System Administrator: Navigate to Concurrent->Program->Define.
2. Query up the report: Account Analysis Report
(do this for both the General Ledger and the Subledger Accounting program definitions).
3. Add a parameter:
ScalableFlag.
Value set should be yes_no
Default value should be Y.
Select checkboxes Enable and Required.
Do not select the check box Displayed, or users could turn this off at runtime.
Token needs to be ScalableFlag (this is a case sensitive value).

B. Configure the XML Publisher Administrator Configuration settings.
This prevents "java.lang.OutOfMemoryError" errors in the Output Post Processor log associated to the Subledger Accounting program.

1. As XML Publisher Administrator navigate to Administration->Configuration.
2. Under Temporary Directory pick a temporary file location on your concurrent processing node. This should be at least 5GB or 20x larger than largest XML data file you generate.
3. Under FO Processing:
Use XML Publisher's XSLT processor set to True
Enable scalable feature of XSLT processor set to False
Enable XSLT runtime optimization set to True

C. Configure the Output Post Processor's JVM.
These steps set the JVM to 2GB, depending upon your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error "java.lang.OutOfMemoryError: Java heap space" in the Output Post Processor's log associated to the Subledger Accounting Program.

1. Login to SQL*Plus as APPS.
2. SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
3. Bounce the concurrent managers.

D. Test the reports.