ORDER MANAGEMENT ( OM )

ORDER MANAGEMENT ( OM ) :



1) What are the Base Tables and Interface Tables for Order Management?

Interface Tables :

OE_HEADERS_IFACE_ALL,
OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL,
OE_ACTIONS_IFACE_ALL
OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)

Base Tables :

OE_ORDER_HEADERS_ALL: Order Header Information
OE_ORDER_LINES_ALL: Items Information
OE_PRICE_ADJUSTMENTS: Discounts Information
OE_SALES_CREDITS: Sales Representative Credits.

Shipping Tables :

WSH_NEW_DELIVERIES,
WSH_DELIVERY_DETAILS,
WSH_DELIVERY_ASSIGNMENTS,
WSH_DELIVERIES.

2) What are the Base Tables and Interface Tables for Order Management?

Interface Tables :

OE_HEADERS_IFACE_ALL,
OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL,
OE_ACTIONS_IFACE_ALL
OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)

Base Tables :

OE_ORDER_HEADERS_ALL: Order Header Information
OE_ORDER_LINES_ALL: Items Information
OE_PRICE_ADJUSTMENTS: Discounts Information
OE_SALES_CREDITS: Sales Representative Credits.

Shipping Tables :

WSH_NEW_DELIVERIES,
WSH_DELIVERY_DETAILS,
WSH_DELIVERY_ASSIGNMENTS,
WSH_DELIVERIES.

3) What is Order Import and What are the Setup's involved in Order Import?

A) Order Import is an open interface that consists of open interface tables and a set of API’s.
It imports New, updated, or changed sales orders from other applications such as Legacy systems.

Order Import features include validations, Defaulting, Processing Constraints checks, Applying and releasing of order holds, scheduling of shipments, then ultimately inserting, updating or deleting orders from the OM base tables.

Order management checks all the data during the import process to ensure its validity with OM. Valid Transactions are then converted into orders with lines, reservations ,price adjustments, and sales credits in the OM base tables.

B) Setups:· Setup every aspect of order management that we want to use with imported orders, including customers, pricing, items, and bills.· Define and enable the order import sources using the order import source window.

4) Explain the Order Cycle?

i) Enter the Sales Order
ii) Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
iii) Release sales order(Pickslip Report is generated and Deliveries are created)(Deliveries – details about the delivery.
Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.iv) Transaction Move Order (creates reservations determines the source and transfers the inventory into the staging areas)
v) Launch Pick Release
(vi) Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))
vii) Auto invoice and closed

5) Explain the Order to Cash Flow?

I. Enter the Sales Order
II. Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
III. Release sales order(Pickslip Report is generated and Deliveries are created)(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
IV. Transaction Move Order (Selects the serial number of the product which has to be moved/ shipped)
V. Launch Pick Release
VI. Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))
VII. AutoInvoice (Creation of Invoice in Accounts Receivable Module)
VIII.Autolockbox ( Appling Receipts to Invoices In AR)
IX. Transfer to General Ledger ( Populates GL interface tables)
X. Journal Import ( Populates GL base tables)
XI. Posting ( Account Balances Updated).

5. What are the Process Constraints?

A. Process Constraints prevent users from adding updating, deleting, splitting lines and canceling order or return information beyond certain points in the order cycle.

Oracle has provided certain process constraints which prevent data integrity violations.

Process constraints are defined for entities and attributes. Entities include regions on the sales order window such as order, line, order price adjustments, line price adjustments, order sales credits and line sales credits.

Attributes include individual fields (of a particular entity) such as warehouse, shit to location, or agreement.

6. What are Validation Templates?

A) Validation Templates are used to define the validation conditions in process constraints.
A validation template names a conditions and defines the semantic of how to validate that condition.
These are used in processing constraints framework to specify the constraining conditions for a given constraint.
These conditions are based on
1 Where the entity is in its work flow.
2 The state of attributes on an entity.
3 Any other validation condition that cannot be modeled using the above condition.

7. What are different types of Holds?

1 GSA(General Services Administration) Violation Hold(Ensures that specific customers always get better pricing for example Govt. Customers)
2 Credit Checking Hold( Used for credit checking feature Ex: Credit Limit)
3 Configurator Validation Hold ( Cause: If we invalidate a configuration after booking)

8. What is Document Sequence?

A) Document sequence is defined to automatically generate numbers for your orders or returns as you enter them. Single / multiple document sequences can be defined for different order types.

Document sequences can be defined as three types Automatic (Does not ensure that the numbers are contiguous), Gapless (Ensures that the numbering is contiguous), Manual Numbering. Order Management validates that the number specified is unique for order type.

9. What are Defaulting Rules?

A) A defaulting rule is a value that OM automatically places in an order field of the sales order window. Defaulting rules reduce the amount of information one must enter.

A defaulting rule is a collection of defaulting sources for objects and their attributes.It involves the following steps
1 Defaulting Conditions - Conditions for Defaulting
2 Sequence – Priority for search
3 Source – Entity ,Attribute, Value
4 Defaulting source/Value

10. When an order cannot be cancelled?

A) An order cannot be cancelled if,
1 It has been closed
2 It has already been cancelled
3 A work order is open for an ATO line
4 Any part of the line has been shipped or invoiced
5 Any return line has been returned or credited.

11. When an order cannot be deleted?

A) you cannot delete an order line until there is a need for recording reason.

12. What is order type?

A) An order type is the classification of order. It controls the order work flow activity, order number sequence, credit check point and transaction type. Order Type is associated to a work flow process which drives the processing of the order.

13. What are primary and secondary price lists?

A) Every order is associated to a price list as each item on the order ought to have a price. A price list is contains basic list information and one or more pricing lines, pricing attributes, qualifiers, and secondary price lists.

The price list that is primarily associated to an order is termed as Primary price list.The pricing engine uses a Secondary Price list if it cannot determine the price of the item ordered in the Primary price list.

14. What is pick slip? Types?

A) It is an internal shipping document that pickers use to locate items to ship for an order.

1 Standard Pick Slip – Each order will have its own pick slip with in each picking batch.
2 Consolidated Pickslip – Pick slip will have all the orders released in the each picking batch.

15. What is packing slip?

A) It is an external shipping document that accompanies the shipment itemizing the contents of the shipment.

16. What are picking rules?

A) Picking rules define the sources and prioritization of sub inventories, lots, revisions and locators when the item is pick released by order management. They are user defined set of rules to define the priorities order management must use when picking items from finished goods inventory to ship to a customer.

17. Where do you find the order status column?

A) In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.

18. When the order import program is run it validates and the errors occurred can be seen in?

A) Responsibility: Order Management Super UserNavigation: Order, Returns > Import Orders > Corrections

KEY FLEX FIELDS(KFF), DESC FLEX FIELDS(DFF)

QUERY TO RETRIEVE KFF'S IN ORACLE E BUSINESS SUITE :

SELECT COUNT(*), FAT.APPLICATION_NAME
FROM FND_ID_FLEXS FIF, FND_APPLICATION_TL FAT
WHERE FAT.APPLICATION_ID = FIF.APPLICATION_ID
AND FAT.LANGUAGE = 'US'GROUP BY FAT.APPLICATION_NAME
ORDER BY FAT.APPLICATION_NAME

SELECT FA.APPLICATION_SHORT_NAME, FAT.APPLICATION_NAME, FIF.ID_FLEX_CODE, FIF.ID_FLEX_NAME
FROM FND_ID_FLEXS FIF, FND_APPLICATION FA, FND_APPLICATION_TL FAT
WHERE FA.APPLICATION_ID = FIF.APPLICATION_ID
AND FAT.APPLICATION_ID = FA.APPLICATION_ID
AND FAT.LANGUAGE = 'US'
ORDER BY FAT.APPLICATION_NAME, FIF.ID_FLEX_NAME



KFF'S & DFF'S


Steps Involved :

1. Registering A Tables & Columns :

Register the Table & Columns if the KFF/DFF is on User Defined Table.

The following is theexample

AD_DD.register_table('AR','RAJ_KFF_TEST','T',8,10,90);

ad_dd.register_column('AR','RAJ_KFF_TEST','Attribute_category',1,'Varchar2',20,'N','N');

ad_dd.register_column('AR','RAJ_KFF_TEST','CCID',2,'NUMBER',10,'N’,'N');

ad_dd.register_column('AR','RAJ_KFF_TEST','attribute1',3,'VARCHAR2',20,'N','N');

For a KFF ....It is necessary to have a column to store CCID and a column to Store Structure Information In the above example the Attribute_category is used to store Structure Info.
and CCID to store ccid number.

These are specified at Registration of the Flexflied.For A DFF...It is enough to store only the Structure Info.Example the Attribute_category may act as an Structure Field.

2. Registering The FlexFiled :

After creating the table and registering it, register the FlexField You You want to Use.Remember : U have to Use this name when referencing the flexfield.Also u have to specify the CCID column & Structure Column for KFF here.Remember : U have to enable the columns of the table here, otherwise u can't define segments for the same.

3. Defining the Segments :Every Flexfiled must have Segment Qualifiers And FlexField columns these are defined in the AOLat Segment Definition.After defining these segments freeze and compile the flexfield segments definition.

These 3 steps complete the process of Registration of table and flexfield & definition of Segments.Some Finer Points :
1. You have to check the Dynamic Insertion Allowed ( for KFF only) to allow the users to dynamically createan intelligent combination key.
2. You can check the Protect check box to ensure that users do not change the definition of flexfield by mistake.Incorporating DFF's / KFF's in the forms :The whole process essentially requires
4 steps ....

1. Modify 7 triggers
2. Create Hidden Fields corresponding to the segments
3. Define the FlexField in the New-Form-Instance-Trigger
4. Set the Profile Options ( Flex:Open_Descr_Window , Flex:Open-Key_Window ) to YESGeneric Activities to Open A Form In Apps ....You have to go through some steps to enable a form in Apps.

1. Open your Template.fmb ( this is provided by Oracle-Apps) .
2. Delete the BLOCKNAME ( datablock , canvas , window ) from the Template.fmb
3. Save this form Module with ur custom name.
4. Create A Canvas , subclass it with Profile class Canvas
5. Similary create a Window ( subclass it with Window profile class) , Datablock.
6. Modify the APPS_CUSTOM package body with following code....if (wnd = '') thenapp_window.close_first_window;Give your name of Window at the Bolded place.
7. Modify The pre-form trigger as follows..app_window.set_window_position('BLOCKNAME', 'FIRST_WINDOW');

Give Your block name here at the bolded place.Now modify the required triggers for implementing the DFF's and KFF's .......1. The Following triggers should be change...

a. When-new-form-instance
b. pre-query
c. post-query
d. WHEN-NEW-ITEM-INSTANCE
e. PRE-UPDATE
f. WHEN-VALIDATE-RECORD
g. WHEN-VALIDATE-ITEM
add FND_FLEX.EVENT('EVENT NAME') where EVENT NAME is the trigger name itself.
eg. FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');

2. Create Hidden Fields ( Set Canvas to null) . These should be as many as the number of segments u defined .

3. Define Your flex-filed at the New-Form-Instance trigger as follows ...FND_KEY_FLEX.DEFINE(BLOCK=>'FLEX_BLOCK',FIELD=>'KFF_SEGS',APPL_SHORT_NAME=>'AR', DESC_FLEX_NAME=>'DFF_FLEX')for DFF....Block - is the block name in the formField - Field which is acting as DFF/KFFAppl_Short_name :- AR/ AP / CS etc.,

Desc_FLEX_NAME :- Name of the Flex Filed U have given at the time of Registration.
For KFF.....CODE - the number u give at the time of registering ur KFFNUM - Use the following SQL to get the number . ( default is 101 )

SELECT ID_FLEX_NUM
FROM FND_ID_FLEX_STRUCTURES
WHERE ID_FLEX_CODE='CODE';

4. Create a TEXT-FIELD to act as DFF or KFFFor DFF subclass it with ....TEXT_ITEM_DESC_FLEXFor Kff subclass it with ...TEXT_ITEM itself

5. Save ur work andattach this form to a functionFunction to a menu.That's all....Good Luck !! Happy Experimenting...

ACCOUNT PAYABLES(AP) AND PURCHASE ORDER(PO)

ACCOUNT PAYABLES (AP) & PURCHASE ORDER ( PO ) :



1) What are the different types of Interfaces?


a) Vendor conversion / ImportA vendor is any company or person that we buy goods or services from.

Interface table --- we need to create our own custom interface table.Base tablesPO_VENDORS (Segment1-Vendor number is unique)

PO_VENDOR_CONTACTS
PO_VENDOR_SITES_ALL

b) Legacy InvoiceInterface Tables

AP_INVOICE _INTERFACE is the header info
AP_INVOICES_LINES_INTERFACE is the lines table.

RUN PAYABLES INVOICE IMPORT CONCURRENT PROGRAM

Base Tables
AP_INVOICE_ALL is also the header information is stored.
AP_INVOICES_DISTRIBUTIONS_ALL is the lines information table.
AP_PAYMENTS_SCHEDULES_ALL (AMOUNT_REMAINING stores balance amount to be paid)
AP_INVOICE_PAYMENTS_ALL (INVOICE_ID)AP_CHECKS_ALL (CHECK_ID)AP_INTERFACE_REJECTIONS (REJECT_LOOKUP_CODE stores the error occurred during the invoice import ex: account required, invalid supplier site )

2) What is the process of Vendor Conversion?

This is always custom work. There is no predefined program to import the vendors/supplier from legacy system to oracle.

We need write the SQL*Loader file to custom interface table and write pl/sql programs to hit the base tables directly into the po_vendors table,po_vendor_sites_table and po_vendors_contacts.

3) In which table and column Vendor Number stores?

In PO_VENDORS table SEGMENT1 stores the vendor number. User doesn’t know about the (vendor id) ids only the developer knows them.

4) After conversions how do you get the Next Vendor column?

We need to resync the PO_UNIQUE_IDENTIFIER_CONTROL (field called CURRENT_MAX_UNIQUE_IDENTIFIER) with the sequence called PO_VENDORS_S.
This resync was run after inserting into vendors and updating the info in the sites table.
The current max unique identifier should be equal to the nextval in the po_vendors_s sequence.

5) What are the setups required for vendor conversions?

Before loading from the legacy system to base table we need to defineSupplier type, Payment type, Payment terms, Payment group.

6) Which module is the owner for vendor (supplier) tables?

Ans--- Purchasing module is the owner of the supplier table.

7) What is the process of creating an Invoices and transferring it to GL?

1. create batch
2. create invoice
3. create distribution
4. validate the invoice
5. actions -à approve
6. if individual create accounting click ok
7. If batch go to batch create accounting.
8. Create accounting hits Payable Accounting(Transfer) ?Program which will create accounting.
9. Run Transfer to GL Concurrent Program
10. Journal Import
11. Post journals
12. Hits balances.

8) How do u Transfer from AP to GL?

Ans---“Payables transfer to GL program” is used to transfer from AP to GL.

10) How many types of Transactions are there in AP?

1. Standard Invoice : The amount is g
2. Debit memo (increases balances owed to supplier)
1. Raised by organization
2. Raised by Supplier.
3. Credit memo
4. Prepayment
5. Mixed Invoices both debit & credit
6. Expense Report employees
7. Quick Match
8. P.O.Default

11) Tell me about PO cycle( Procure To Pay )?

1. Requisition
2. Manager
3. Approval
4. Request For Quote (RFQ)
5. Quotation
6. Quote Analysis (Track/check record)
7. Issue Purchase Order (PO)
8. Goods Receipt Note(GRN)
9. Invoice
10. Transfer To GL (Payables transfer to GL program)
11. Journal Import
12. GL Balances

12) How many types of purchase order types/agreements are there?

a) Standard Purchase Order

b) Planned PO : A planned purchase order is a long-term agreement committing to buy ititems or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities and estimated cost.EX: Buying goods for Christmas from a specific dealer.

c) Contract PO : You create contract purchase agreement with your supplier to agree on specific terms and conditions without indicating the goods and services that you will be purchasing i.e. for $ amount you must supply this much quantity. You can later issue standard PO referencing your contracts and you can encumber these purchase orders if you use encumbrance accounting.

d) Blanket PO : You create blanket purchase agreements when you know the detail of goods or services you plan to buy from a specific supplier in a period , but you do not yet know the detail of your delivery schedules.

You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.

A Blanket Purchase Agreement is a sort of contract between the you and ur supplier about the price at which you will purchase the items from the supplier in future. Here you enter the price of the item not the quantity of the items.

When you create the release you enter the quantity of the items. The price is not updatable in the release. The quantity * price makes the Released Amount.

Now suppose your contract with your supplier is such that you can only purchase the items worth a fixed amount against the contract.

13) What is 2-way, 3-way, 4-way matching?

2-way matching: 2-way matching verifies that Purchase order and invoice quantities must match within your tolerances as follows:Quantity billed <= Quantity OrderedInvoice price <= Purchase order price(<= sign is used because of tolerances)Often used for services where no receiver is generated.

3-way matching: 3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:Quantity billed <= Quantity received

4-way matching: 4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:Quantity billed <= Quantity accepted.(Acceptance is done at the time of Inspecting goods).

Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character)Receipt required Inspection required MatchingYes Yes 4-wayYes No 3-wayNo No 2-way

ACCOUNT RECEIVABLES (AR)

ACCOUNT RECEIVABLES (AR)


1. What is TCA? Tables?

A) Trading Community Architecture.

It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.

HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:Organization for example, Oracle CorporationPerson for example, Jane DoeGroup for example, World Wide Web ConsortiumRelationship for example, Jane Doe at Oracle Corporation.

HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.

HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HZ_CUST_CONTACT_POINTS etc.

2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?

A) Customer Conversion:

Interface Tables :
RA_CUSTOMERS_INTERFACE_ALL,
RA_CUSTOMER_PROFILES_INT_ALL,
RA_CONTACT_PHONES_INT_ALL,
RA_CUSTOMER_BANKS_INT_ALL,
RA_CUST_PAY_METHOD_INT_ALL

Base Tables :
RA_CUSTOMERS,
RA_ADDRESSES,
RA_SITE_USES_ALL,
RA_CUSTOMER_PROFILES_ALL,
RA_PHONES etc

B) Auto Invoice:

Interface Tables :

RA_INTERFACE_LINES_ALL,
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL,
RA_INTERFACE_ERRORS_ALL

Base Tables :
RA_CUSTOMER_TRX_ALL,
RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL,
RA_CUST_TRX_LINE_SALESREPS_ALL,
RA_CUST_TRX_TYPES_ALL

C) AutoLockBox:Interface Tables :

AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)

Interim tables :

AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation): AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING

Base Tables :

AR_CASH_RECEIPTS_ALL,
AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)

3. What are the tables in which Invoices/transactions information is stored?

A) RA_CUSTOMER_TRX_ALL,
The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment,
bills receivable, and credit memo header information.
Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.

RA_CUSTOMER_TRX_LINES_ALL,
The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).

RA_CUST_TRX_LINE_SALESREPS_ALL,
The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.

The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line.

Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.

The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.

4. What are the tables In which Receipt information is stored?

A) AR_PAYMENT_SCHEDULES_ALL,
The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.

Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table.

Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column,

the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.AR_CASH_RECEIPTS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter.

Oracle Receivables concurrently creates records in the
AR_CASH_RECEIPT_HISTORY_ALL,
AR_PAYMENT_SCHEDULES_ALL,
and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts.

For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the
AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.
AR_RECEIVABLE_APPLICATIONS_ALL,
The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter.

Oracle Receivables concurrently creates records in the

AR_CASH_RECEIPT_HISTORY_ALL,
AR_PAYMENT_SCHEDULES_ALL,
and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts.

For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.

Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.

5. What are the tables in which Accounts information is stored?

RA_CUST_TRX_LINE_GL_DIST_ALL

6. What are the different statuses for Receipts?

A) Unidentified – Lack of Customer InformationUnapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)Applied – When all the required information is provided.On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.

8. What is Autolockbox?

A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing.

Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks.

It involves three steps

1 Import (Formats data from bank file and populates the Interface Table),

2 Validation(Validates the data and then Populates data into Interim Tables),

3 Post Quick Cash(Applies Receipts and updates Balances in BaseTables).

9. What is Transmission Format?

A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables.

Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.

10. What is Auto Invoice?

A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables.

Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) .

Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.

11. What are the Mandatory Interface Tables in Auto Invoice?

RA_INTERFACE_LINES_ALL,
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.

12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?

A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.

13. What is AutoAccounting?

A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice.

Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.

14. What are Autocash rules?

A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items.
Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.

15. What are Grouping Rules? (Used by Autoinvoice)

A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction.
After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.

16. What are Line Ordering Rules? (Used by Autoinvoice)

A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.

17. In which table you can see the amount due of a customer?

A) AR_PAYMENT_SCHEDULES_ALL

18. How do you tie Credit Memo to the Invoice?

At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited.
In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.

19. What are the available Key Flex Fields in Oracle Receivables?

A) Sales Tax Location Flex field, It’s used for sales tax calculations.Territory Flex field is used for capturing address information.

20. What are Transaction types? Types of Transactions in AR?

A) Transaction types are used to define accounting for different transactions such as

Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.

ALERTS IN ORACLE APPS

ALERTS IN ORACLE APPS :

Overview:

You can use alerts simply to notify one person/multiple persons about an event or some data change.

There are two type of alerts:

Event based Alerts
Periodic Alerts

Event based Alerts:

These Alerts are fired/triggered based on some change in data in the database. Ex: If you want to notify your manager when you create an item in the inventory you can use Event based alerts. When you create an item in the inventory it will create a new record in mtl_system_items_b, here inserting a record in the table is an event so when ever a new record is inserted it will send the alert. In same alert you can also send the information related to that particular item.

Periodic Alerts:

These Alerts are triggered hourly, daily, weekly, monthly or yearly based on your input. Ex: If you want to know list of all items created on that day at the end of day you can use periodic alerts repeating periodically by single day. This alert is not based on any changes to database. This alert will notify you everyday regardless of data exists or not that means even if no items are created you will get a blank notification.


What can be done with Alerts?

You can send notifications
You can send log files as attachments to notifications
You can call PL/SQL stored procedures.
You can send approval emails and get the results.
Print some content dynamically

How to create an Alert?

Study your Business requirement and decide what type of alert you need either periodic alert or event based alert.
If you are going for periodic alert decide the frequency
If you have chosen event based alert then find out on what event (insert, update, delete) you want to fire the alert.
Decide what data need to be included in the alert
Based on the data you want in the alert write a SELECT SQL Statement to pull the data.
Create a distribution list grouping all the people to whom you want to send the alert.

Navigation to define an alert:

Go to “Alert Manager” Responsibility
Alert >> Define
Transfer Alert from one instance/database to other:
Go to “Alert Manager” Responsibility
Alert >> Define
Go to "Tools" Menu on top
Click on "Transfer Alert"
Enter source and destination fields and click Transfer.

CONCURRENT PROGRAM FROM MENU

CONCURRENT PROGRAM FROM MENU

Normally we assign any concurrent program to a request group corresponding to the responsibility from which we want to run our concurrent program.

What in case if user want to launch concurrent program directly from a menu.

To assign a concrrent program to a menu follow the steps :

Create a new function of form type and name it as your concurrent program
In the parameter field pass a parameter

Program=

Assign this function to a responsibility menu from which you want to run this concurrent program.

Now go to that responsibility and click on the function.

It will directly launch the concurrent program.

Advantage: User can directly launch the concurrent program instead of navigating to

view >> Requests >> Submit

Disadvantage: If you create a function for each concurrent program then in each responsibility you will have around 100 functions which looks weird.

SQL LOADER

SQL * LOADER

SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded.

SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files

called a log file, bad file or reject file, discard file.

Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.

Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.

Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.

Structure of the data file:

The data file can be in fixed record format or variable record format.

Fixed Record Format would look like the below.

In this case you give a specific position where the Control file can expect a data field:

7369 SMITH CLERK 7902 12/17/1980 800
7499 ALLEN SALESMAN 7698 2/20/1981 1600
7521 WARD SALESMAN 7698 2/22/1981 1250
7566 JONES MANAGER 7839 4/2/1981 2975
7654 MARTIN SALESMAN 7698 9/28/1981 1250
7698 BLAKE MANAGER 7839 5/1/1981 2850
7782 CLARK MANAGER 7839 6/9/1981 2450
7788 SCOTT ANALYST 7566 12/9/1982 3000
7839 KING PRESIDENT 11/17/1981 5000
7844 TURNER SALESMAN 7698 9/8/1981 1500
7876 ADAMS CLERK 7788 1/12/1983 1100
7900 JAMES CLERK 7698 12/3/1981 950
7902 FORD ANALYST 7566 12/3/1981 3000
7934 MILLER CLERK 7782 1/23/1982 1300

Variable Record Format would like below where the data fields are separated by a delimiter.

Note: The Delimiter can be anything you like. In this case it is ""

119670090692.64
137890123900488.62
141870022320467.92
14187021487404056.36
1499100103.68
1632800301866.66
163290017012.64
1637600500755.5

Structure of a Control file:

Sample CTL file for loading a Variable record data file:

OPTIONS (SKIP = 1) --The first row in the data file is skipped without loading
LOAD DATA
INFILE '$FILE' -- Specify the data file path and name
APPEND -- type of loading (INSERT, APPEND, REPLACE, TRUNCATE
INTO TABLE "APPS"."BUDGET" -- the table to be loaded into
FIELDS TERMINATED BY '' -- Specify the delimiter if variable format datafile
OPTIONALLY ENCLOSED BY '"' --the values of the data fields may be enclosed in "
TRAILING NULLCOLS -- columns that are not present in the record treated as null
(ITEM_NUMBER "TRIM(:ITEM_NUMBER)", -- Can use all SQL functions on columns
QTY DECIMAL EXTERNAL,
REVENUE DECIMAL EXTERNAL,
EXT_COST DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))" ,
MONTH "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,
DIVISION_CODE CONSTANT "AUD" -- Can specify constant value instead of
Getting value from datafile
)


OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n -- Number of logical records to skip (Default 0)
LOAD = n -- Number of logical records to load (Default all)
ERRORS = n -- Number of errors to allow (Default 50)
ROWS = n -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)
BINDSIZE = n -- Size of conventional path bind array in bytes (System-dependent default)
SILENT = {FEEDBACK ERRORS DISCARDS ALL} -- Suppress messages during run
(header, feedback, errors, discards, partitions, all)
DIRECT = {TRUE FALSE} --Use direct path (Default FALSE)
PARALLEL = {TRUE FALSE} -- Perform parallel load (Default FALSE)

LOAD DATA statement is required at the beginning of the control file.

INFILE: INFILE keyword is used to specify location of the datafile or datafiles.
INFILE * specifies that the data is found in the control file and not in an external file. INFILE '$FILE', can be used to send the filepath and filename as a parameter when registered as a concurrent program.
INFILE '/home/vision/kap/import2.csv' specifies the filepath and the filename.

Example where datafile is an external file:

LOAD DATA
INFILE '/home/vision/kap/import2.csv'
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )

Example where datafile is in the Control file:

LOAD DATA
INFILE *
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
BEGINDATA
7369,SMITH,7902,Accounting
7499,ALLEN,7698,Sales
7521,WARD,7698,Accounting
7566,JONES,7839,Sales
7654,MARTIN,7698,Accounting


Example where file name and path is sent as a parameter when registered as a concurrent program

LOAD DATA
INFILE '$FILE'
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )

TYPE OF LOADING:

INSERT -- If the table you are loading is empty, INSERT can be used.
APPEND -- If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded.
REPLACE -- All rows in the table are deleted and the new data is loaded
TRUNCATE -- SQL*Loader uses the SQL TRUNCATE command.

INTO TABLE is required to identify the table to be loaded into. In the above example INTO TABLE "APPS"."BUDGET", APPS refers to the Schema and BUDGET is the Table name.

FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)

OPTIONALLY ENCLOSED BY '"' specifies that data fields may also be enclosed by quotation marks.

TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

Loading a fixed format data file:

LOAD DATA
INFILE 'sample.dat'
INTO TABLE emp
( empno POSITION(01:04) INTEGER EXTERNAL,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL)

Steps to Run the SQL* LOADER from UNIX:
At the prompt, invoke SQL*Loader as follows:

sqlldr USERID=scott/tiger CONTROL= LOG=

SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

Register as concurrent Program:

Place the Control file in $CUSTOM_TOP/bin.
Define the Executable. Give the Execution Method as SQL*LOADER.
Define the Program. Add the Parameter for FILENAME.

Skip columns:

You can skip columns using the 'FILLER' option.

Load Data------TRAILING NULLCOLS(name Filler,Empno ,sal)

here the column name will be skipped.

INTERFACE PROGRAMS

1: ORDER IMPORT INTERFACE (SALES ORDER CONVERSION)

Interface tables:
· OE_HEADERS_IFACE_ALL
· OE_LINES_IFACE_ALL
· OE_ACTIONS_IFACE_ALL
· OE_ORDER_SOURCES
· OE_CUSTOMER_INFO_IFACE_ALL
· OE_PRICE_ADJS_IFACE_ALL
· OE_PRICE_ATTS_IFACE_ALL

Base tables:
· OE_ORDER_HEADERS_ALL
· OE_ORDER_LINES_ALL
Pricing tables
· QP_PRICING_ATTRIBUTES

During import of orders, shipping tables are not populated.

If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL
Base Tables: HZ_PARTIES HZ_LOCATIONS

Orders can be categorized based on their status:
1. Entered orders
2. Booked orders
3. Closed orders

Concurrent Program: Order Import

Validations:
· Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
· Check for sales_rep_id. Should exist for a booked order.
· Ordered_date should exist. -------- header level
· Delivery_lead_time should exist. ----------- line level
· Earliest_acceptable_date should exist.
· Freight_terms should exist

Order Import API OE_ORDER_PUB.GET_ORDER, PROCESS_ORDER

Concurrent programs will in turn call APIs.
2 APIs are called during order import process.
· OE_CNCL_ORDER_IMPORT_PVT (cancelled orders)
· ORDER_IMPORT_PRIVATE
Procedure: import_order()

2: Item import (Item conversion)
Always import master and child records.

Interface tables:
· MTL_SYSTEM_ITEMS_INTERFACE
· MTL_ITEM_REVISIONS_INTERFACE( If importing revisions, populate)
· MTL_ITEM_CATEGORIES_INTERFACE(If importing categories, populate)
· MTL_INTERFACE_ERRORS

Item import can be run in create mode or update mode.
Running the Item Open Interface In Create Mode:
Populate the mtl_system_items_interface with the following minimum required columns when creating new items:
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
ORGANIZATION_ID = Master Org id.
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n)

Note: Using the ITEM_NUMBER column in the mtl_system_items_interface is required if you are populating revision history data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both.

Running the Item Open Interface In Update Mode:
To update existing item(s), set TRANSACTION_TYPE = 'UPDATE'.

For best performance, use inventory_item_id when updating items.

Functionality
Every attribute updateable from the Item form is updateable through the interface, and all required validations are performed to enforce:
· Item Attribute Interdependencies
· Master - Child Attribute Dependencies
· Status Controlled Attributes
· Templates can be applied to existing Items. For best results, use template_id and template_name.
· The Item status can be changed for existing Items, and the proper attributes are Defaulted / Set accordingly. The Status change is recorded in
MTL_PENDING_ITEM_STATUS.
· To populate material costs from IOI: Populate the LIST_PRICE_PER_UNIT column with a value while importing items and you will see your material cost for your item in MTL_SYSTEM_ITEMS after running the Item Import process. (CREATE transaction_type only)
· When launching items into the Master Item Org, the Child records are copied into MTL_SYSTEM_ITEMS_INTERFACE for validation, and are identified with transaction_type of 'AUTO_CHILD'. These records are deleted if the parameter 'Delete Processed Rows' has been passed as 'Yes', and remain for diagnostic purposes if the parameter is passed as 'No'. When the defining attribute for a Functional area is enabled, the proper default category set and category is assigned to the Item.
· Master Items were loaded before child records in MTL_SYSTEM_ITEMS.

Not Supported Issues
=========================================
· Item Costs cannot be UPDATED (using "UPDATE" transaction_type) through the interface.
· New Item revisions cannot be added to existing Items.
· Current functionality does not support updates to a PTO MODEL ITEM through
the IOI update feature. See notes: 1076412.6 and 2121870.6 Updating Item Attributes to NULL The method to update these columns to NULL is to use the following values:
1. for Numeric fields: insert -999999
2. for Character fields: insert '!'
3. for Date fields: the above list does not include any updateable date fields.

Importing Master and Child Records
==================================
The user procedures are as follows :
1. Populate the item interface tables (mtl_system_items_interface). This step is necessary if you are creating items and categories in the same run. For importing item category assignments for already existing items, you do not need to populate item interface table.
2. Populate the item categories interface table (mtl_item_categories_interface).

The user needs to populate the following mandatory columns in item categories interface table:
A. Either inventory_item_id or item_number. When item and category are being imported together, then user can only specify the item_number, since item id will be generated by the import process.
B. Either organization_id or organization_code or both.
C. The transaction_type column should be 'CREATE'. We do not support 'UPDATE' or 'DELETE' for item category assignment.
D. Either category_set_id or category_set_name or both.
E. Either category_id or category_name or both.
F. Process_flag column as 1.
G. Populate the set_process_id column. The item and category interface records should have the same set_process_id, if you are importing item and category assignment together.

3. After populating the item and category interface tables, launch the Item Import process from the applications. In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have have any value. Through the import process, we can only create item category assignment(s).
Updation or Deletion of item category assignment is not supported.
4. Once the concurrent process completes, check the mtl_interface_errors table for any error(s) during the item and category import. Correct those error conditions in the interface tables and run the item import again. If the process_flag is 7, that means the item category interface records were successfully imported.

Revisions
==============================
Note: Using the ITEM_NUMBER column in the mtl_system_items_interface table is required if you are populating revision data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported, plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both. For historical item revision data, do NOT populate the REVISION column in the mtl_system_items_interface table. This column is used only if the current revision of the item is being imported.
Populate these columns in the mtl_item_revisions_interface table:
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
ORGANIZATION_ID = Master Org ID.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = (Must match the item_number in mtl_system_items_interface table.)
Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Run the IOI process. Navigate --> Inventory: Items: Import Items
There are 6 parameters to enter to begin the process:
1. Specify one or all organizations.
2. Validate items, yes or no.
3. Process items, yes or no.
4. Delete processed rows, yes or no.
5. Process set (null for all)
6. Create or update items (1 for create, 2 for update)
Note: If you are importing Master and Child records, insert them into the mtl_system_items_interface and mtl_item_revisions_interface tables, and run them at the same time by setting the 'All organizations' parameter to 'Yes'. If you do not do this, then the Child revision records will not be imported.

Error Checking:
======================================
When importing multiple revisions, if one record for an item fails validation, all revisions for that item fail. Resolve failed rows by checking the mtl_interface_errors table.
SELECT table_name, column_name, error_message, message_name
FROM mtl_interface_errors;

Base tables:
§ MTL_SYSTEM_ITEMS_B
§ MTL_ITEM_REVISIONS_B
§ MTL_CATEGORIES_B
§ MTL_CATEGORY_SETS_B
§ MTL_ITEM_STATUS
§ MTL_ITEM_TEMPLATES

Concurrent program: Item Import

Validations:

check for valid item type.
Check for valid part_id/segment of the source table.
Validate part_id/segment1 for master org.
Validate and translate template id of the source table.
Check for valid template id. (attributes are already set for items, default attributes for
that template, i.e., purchasable, stockable, etc)
Check for valid item status.
Validate primary uom of the source table.
Validate attribute values.
Validate other UOMs of the source table.
Check for unique item type. Discard the item, if part has non-unique item type.
Check for description, inv_um uniqueness
Validate organization id.

Load master records and category records only if all validations are passed.
Load child record if no error found.

Interface Tables Base Tables
MTL_SYSTEM_ITEMS_INTERFACE MTL_SYSTEM_ITEMS
MTL_TRANSACTIONS_INTERFACE
MTL_ITEM_REVISION_INTERFACE MTL_ITEM_REVISIONS
MTL_DEMAND_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE MTL_ITEM_CATEGORIES
MTL_CROSS_REFERENCES_INTERFACE MTL_CROSS_REFERENCES


On-hand quantity
Interface tables: MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, PROCESS_FLAG to manipulate the records in the table. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base tables: MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS MTL_SERIAL_NUMBERS

Concurrent program:

Validations: validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.

3: Customer conversion

Interface tables: RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

Base tables: HZ_PARTIES
HZ_CONTACTS
HZ_PROFILES
HZ_LOCATIONS

Base tables for RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL

Uses TCA APIs.

Concurrent program: Customer Interface

Validations: Check if legacy values fetched are valid.

; Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

Customer API

1. Set the organization id
Exec dbms_application_info.set_client_info(‘204’);

2. Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

3. Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE

4. Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE

5. Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE

6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base table:
· HZ_PARTIES
· HZ_PARTY_SITES
· HZ_LOCATIONS
· HZ_CUST_ACCOUNTS
· HZ_CUST_SITE_USES_ALL
· HZ_CUST_ACCT_SITES_ALL
· HZ_PARTY_SITE_USES

Validations: Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

4 : Auto Invoice interface

Interface tables: RA_INTERFACE_LINES_ALL

Base tables:
RA_CUSTOMER_TRX_ALL
RA_BATCHES
RA_CUSTOMER_TRX_LINES_ALL
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUST_TRX_GL_DIST_ALL
AR_RECEIVABLES_APPLICATIONS
AR_ADJUSTMENTS
AR_CASH_RECEIPTS
RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program: Auto invoice master program

Validations: check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id, quantity.
Validate if the amount includes tax flag.

Receipt API
AR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY

To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.

Base tables: AR_CASH_RECEIPTS

Validations: check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.

5: Lockbox interface

Interface tables: AR_PAYMENTS_INTERFACE_ALL (Import data from bank file )

Base tables: AR_INTERIM_CASH_RECEIPTS_ALL AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)

Related Tables:

AR_BANK_ACCOUNTS_ALL AR_RECEIPT_METHODS
AR_TRANSMISSIONS_ALL HZ_CUST_ACCOUNTS HZ_CUST_SITE_USES_ALL AR_CASH_RECEIPTS
(POST QUICK CASH -- applies the receipts and updates customer balances)
Concurrent program: nav-> receivables->interfaces->lockbox

Validations:

check for valid record type, transmission record id.
Validate sum of the payments within the transmission.
Identify the lockbox number (no given by a bank to identify a lockbox).

AP invoice interface

Interface tables: AP_INVOICES_INTERFACE AP_INVOICE_LINES_INTERFACE

Base tables: AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program: Payables Open Interface Import

Validations: check for valid vendor
Check for valid vendor site code.
Check if record already exists in payables interface table.

Vendor conversion/interface
No interface tables

Base tables: PO_VENDORS PO_VENDOR_SITES_ALL

No concurrent program as data is directly populated into base tables.

Validations: check if a vendor already exists with the same name as the TIMSS customer
mail name.
Check if the proper site code and id exists based on the site code from TIMSS.
Check for uppercase value of the vendor name existed in Oracle and in TIMSS, vendor name is mixed case, a new Oracle vendor will not be created.

6: Purchasing:

Interface Tables Base Tables
PO_HEADERS_INTERFACE PO_HEADERS_ALL
PO_LINES_INTERFACE PO_LINES_ALL
PO_REQUISITIONS_INTERFACE_ALL PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_REQ_DIST_INTERFACE_ALL PO_REQ_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_INTERFACE PO_DISTRIBUTIONS_ALL
PO_RESCHEDULE_INTERFACE PO_REQUISITION_LINES_ALL

Requisition import
Interface tables: PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL

Basetables: PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Concurrent program: REQUISITION IMPORT

Validations: check for interface transaction source code, requisition destination type.
Check for quantity ordered, authorization status type.

7: PO Receipts Interface

Interface tables:
· RCV_HEADERS_INTERFACE
· RCV_TRANSACTIONS_INTERFACE

Base tables:
· RCV_SHIPMENT_HEADERS
· RCV_SHIPMENT_LINES
· RCV_TRANSACTIONS

Concurrent program: RECEIVING OPEN INTERFACE

Error messages: 1. Run RECEIVING INTERFACE ERRORS REPORT
2. Look in PO_INTERFACE_ERRORS
Query to check interface errors: PO_INTERFACE_ERRORS .inteface_transaction_id =
RCV_HEADERS_INTERFACE.header_interface_id and processing_status_code in (‘error’ ,’print’)

Validations:

8: GL interface

Interface tables: GL_INTERFACE

Base tables:

GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program: Journal Import
Journal Posting --- populates GL_BALANCES

Validations: check SOB, journal source name, journal category name, actual flag
A – actual amounts
B – budget amounts
E – encumbrance amount
If u enter E in the interface table, then enter appropriate encumbrance ID.
B – budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if valid code combination.
Check if ccid is enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.

Validations for the staging table:

Check if the input data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.

9: GL budget interface

Interface tables: GL_BUDGET_INTERFACE

Base tables:
GL_BUDGETS
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_TYPES

Concurrent program: Budget Upload

Validations:

Check Account combination is valid or not.

You check this in GL_CODE_COMBINATIONS table.

10 :GL daily conversion rates

Interface tables: GL_DAILY_RATES_INTERFACE

Base tables:

· GL_DAILY_RATES
· GL_DAILY_CONVERSION_TYPES

Concurrent Program: Program - Daily Rates Import and Calculation

SHELL FROM DATABASE PROCEDURE

EXECUTE SHELL SCRIPT FROM DATABASE PROCEDURE


Why do we need to execute shell script commands from database?

Advantages and Disadvantages

Implementation Code



Why do we need to execute shell script commands from database?

To explain this better i will give you one example. Suppose you have a 2 tier(applications reside on one server
(XX) and database on another server(YY))oracle apps system. Now you have to write a concurrent program to check if the file erps.txt exists on the server or not. if file exists the concurrent program should exit successfully if not it should return warning and for any other problems it should error out.

Here we have two choices to check if file exists or not. one is write a shell script to check the file on server and second option is write a PL/SQL procedure to check the file exists or not.

If you pick the first choice that is writing the shell script to check if file exists or not this program is going to check files on only appsserver which is XX and in the second choice it is going to check the file only on database server which is YY. what in case if you need to do both in one program? thats where you need to call shell script commands from database.


Advantages and Disadvantages

When you call use database procedure instead shell script you have the flexibility to pass what ever the return code you want to the concurrent manager thus making the concurrent program to exit with warning/success/error while with shell script you can not make the concurrent request as warning.

Main disadvantage would be security problem. If you say rm * as a shell command in the database procedure you write it will delete all the files on your server. you can avoid this by restricting the permissions only to few commands like ls, cp, mv e.t.c

Implementation Code:

Execute all the below code connecgted to your oracle apps database as APPS user.

-- Block to give the permission to the APPS user
begin
dbms_java.grant_permission
('APPS',
'java.io.FilePermission',
'/usr/bin/*',
'execute');

dbms_java.grant_permission
('APPS',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/

--Connect as APPS user and then compile the java code

create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;

public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args);

int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];

// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

-- PL/SQL function to execute the host commands and return the code

create or replace
function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/

-- If running from SQL*PLUS set these before you execute
variable x number;
set serveroutput on;
exec dbms_java.set_output(100000);

--PL/SQL Block to execute the commands

declare
x number;
begin
x := RUN_CMD('/usr/bin/ls /home/oracle; echo $?');
dbms_output.put_line('shell Returned with status:'x);
end;
/