Few Important Tables Joins in discrete manufacturing modules oracle apps(WIP, BOM, INV)

1) BOM :

BOM_RESOURCES (BR)

BOM_BILL_OF_MATERIALS (BBOM)

BOM_DEPARTMENTS (BD)

BOM_OPERATIONAL_ROUTINGS (BOR)

BOM_OPERATION_SEQUENCES (BOS)

BOM_OPERATION_SEQUENCES (BOS)

BOM_INVENTORY_COMPONENTS (BIC)

BOM_STANDARD_OPERATIONS (BSO)

2) INV : 

MTL_PARAMETERS (MP)

MTL_SYSTEM_ITEMS_B (MSIB)

MTL_MATERIAL_TRANSACTION (MTT)

MTL_SERIAL_NUMBERS (MSN)

MTL_ITEM_REVISIONS (MIR)

MTL_ITEM_CATEGORIES (MIC)

MTL_DEMAND (MD)

MTL_TRANSACTION_TYPES (MTTY)

MTL_CATEGORIES (MC)

CST_ITEM_COSTS (CIC)

CST_COST_ELEMENTS (CCE)

3) WIP :

WIP_PARAMETERS (WP)

WIP_DISCRETE_JOBS (WDJ)

WIP_ENTITIES (WE)

WIP_OPERATIONS (WO)

WIP_REQUIREMENT_OPERATIONS (WRO)

WIP_OPERATION_RESOURCES (WOR)

WIP_TRANSACTIONS (WT)

WIP_TRANSACTION_ACCOUNTS (WTA)

WIP_LINES (WL)

WIP_EAM_WORK_REQUESTS (WEWR)

WIP_EAM_PARAMETERS (WEP)

1) JOINS IN BOM

BOM_BILL_OF_MATERIALS (BBOM)                       MTL_SYSTEM_ITEMS_B (MSIB)

organization_id                                                                  organization_id

assembly_item_id                                                              inventory_item_id

MTL_SYSTEM_ITEMS_B (MSIB)                                BOM_OPERATIONAL_ROUTINGS (BOR)

inventory_item_id                                                              assembly_item_id

organization_id                                                                  organization_id

BOM_BILL_OF_MATERIALS (BBOM)                       BOM_INVENTORY_COMPONENTS (BIC)

bill_sequence_id                                                                bill_sequence_id

BOM_INVENTORY_COMPONENTS (BIC)               MTL_SYSTEM_ITEMS_B (MSIB)

component_item_id                                                           inventory_item_id

BOM_OPERATIONAL_ROUTINGS(BOR)                  BOM_OPERATION_SEQUENCES(B0S)

routing_sequence_id                                                          routing_sequence_id

BOM_OPERATION_SEQUENCES (BOS)                   BOM_STANDARD_OPERATIONS (BSO)

standard_operation_id                                                       standard_operation_id

BOM_OPEARATION_SEQUENCES(BOS)                  BOM_DEPARTMENTS (BD)

department_id                                                                   department_id

BOM_DEPARTMENTS (BD)                                        BOM_DEPARTMENT_CLASSES

department_id                                                                   department_id

operation_sequence_id                                                      operation_sequence_id

BOM_OPERATION_RESOURCE (BORE)                   BOM_RESOURCES (BR)

resource_id                                                                       resource_id

BOM_DEPARMTENTS (BD)                                         BOM_RESOURCES (BR)

organization_id                                                                  organization_id 

2) JOINS IN INV :

MTL_SYSTEM_ITEMS_B (MSIB)                                 MTL_PARAMETERS (MP)

organization_id                                                                   organization_id

MTL_MATERIAL_TRANSACTION (MMT)                 MTL_TRANSACTION_TYPES (MTT)

transaction_type_id                                                            transaction_type_id

MTL_MATERIAL_TRANSACTION (MMT)                 MTL_ITEM_CATEGORIES (MIC)

organization_id                                                                  organization_id

inventory_item_id                                                              inventory_item_id

MTL_MATERIAL_TRANSACTION (MMT)                MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

MTL_TRANSACTION_TYPES (MTT)                         MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

MTL_ITEM_REVISIONS(MIR)                                     MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

organization_id                                                                  organization_id

CST_ITEM_COSTS(CIC)                                              MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

organization_id                                                                  organization_id

MTL_ITEM_CATEGORIES (MIC)                                MTL_CATEGORIES (MC)

category_id                                                                       category_id

JOINS IN INV WITH OTHER MODULES :

MTL_SYSTEM_ITEMS_B (MSIB)                                 RCV_TRANSACTIONS

organization_id                                                                  organization_id

MTL_SYSTEM_ITEMS_B (MSIB)                                 HR_EMPLOYEES

buyer_id                                                                            employee_id

3) JOINS IN WIP :

WIP_ENTITIES (WE)                                                     WIP_TRANSACTIONS (WT)

organization_id                                                                  organization_id

wip_entity_id                                                                     wip_entity_id

WIP_TRANSACTION_ACCOUNTS (WTA)                WIP_TRANSACTIONS (WT)

transaction_id                                                                    transaction_id

WIP_LINES (WL)                                                           WIP_TRANSACTIONS(WT)

line_id                                                                                line_id

WIP_DISCRETE_JOBS (WDJ)                                       WIP_ENTITIES (WE)

wip_entity_id                                                                      wip_entity_id

WIP_ENTITIES (WE)                                                       WIP_OPERATION_RESOURCES (WOR)

wip_entity_id                                                                       wip_entity_id

WIP_ENTITITES (WE)                                                     WIP_REQUIREMENT_OPERATIONS

organization_id                                                                    organization_id

WIP_ENTITIES                                                                 WIP_OPERATIONS

wip_entity_id                                                                       wip_entity_id

organization_id                                                                    organization_id

JOINS IN WIP WITH OTHER MODULES

WIP_TRANSACTION_ACCOUNTS (WTA)               PO_HEADERS_ALL (POH)

po_header_id                                                                   po_header_id

CST_COST_ELEMENTS (CSE)                                   WIP_TRANSACTION (WT)

cst_element_id                                                                 cst_element_id

WIP_OPERATIONS (WO)                                            BOM_DEPARTMENTS (BD)

department_id                                                                  department_id

WIP_ENTITIES (WE)                                                     MTL_SYSTEM_ITEMS_B (MSIB)

organization_id                                                                  organization_id

primary_item_id                                                                inventory_item_id

WIP_OPERATION_RESOURCES (WOR)                    BOM_RESOURCES (BR)

resource_id                                                                       resource_id

Oracle Advance PL/SQL Questions and Answers

Difference between 11i and R12

R12	11i
4C’s (Currency, calendar, char of accounts & conversions accounts)	3C (Currency, calendar, char of accounts)
SLA sub ledger accounting	NO
Multi Org access controls	NO
ledgers and ledger sets	set of books
Suppliers moved to TCA	Supplier defined in AP/PO
Supplier Sites as TCA party site for each distinct address	NO
Date base version is 10g Release 2	9i release 2
Application tier Build on fusion middleware (10g web server and 10g forms and reports)	Application tier consists of developer 6i (forms and reports)

TCA?
Trading Community Architecture.  It allows us to manage complex information about customers. This is a party customer relationship and stored in HZ tables.

1. What is the difference between REF Cursor & Normal Cursor in oracle?

REF cursor is typically used to return record set or a cursor from stored procedure. REF Cursor is basically a data type. It is normally declared as type r_cursor is REF CURSOR; REF cursor supports dynamic change of query.

Normal cursor is a static cursor in which the query is assigned at design time and cant be changed at run time.

2. Pragma Autonomous Transaction

The child code runs independently of its parent.

The child code can commit or rollback & parent resume.

The Parent Code can continue without affecting chid work.

3. What is the difference between %ROWTYPE and %TYPE and what is the main advantage to using these?

%ROWTYPE works on a full object whereas %TYPE works on a single column. 

4. What is an overloaded procedure?

It allows to reuse the same procedure name  for different subprogram inside a plsql block by varying the parameter datatype. 

example:

DECLARE

PROCEDURE callit (anumber INTEGER) IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Invoked number callit');

END callit;

 PROCEDURE callit (acharacter VARCHAR2) IS

 BEGIN

 DBMS_OUTPUT.PUT_LINE('Invoked character callit');

 END callit;

BEGIN

callit(1);

callit('1');

END;

Invoked number callit

Invoked character callit

PL/SQL procedure successfully completed.

5. What is difference between TRUNCATE & DELETE ?

Truncate is DDL statement and can not be rollback 

delete is DML stmt and can be rollback if required.

6. What is difference between SUBSTR and INSTR

    

      SUBSTR returns a specified portion of a string

      eg  SUBSTR('BCDEF',4)       output  BCDE

          INSTR  provides character position in which a pattern

      is found in a string.

      eg  INSTR('ABC-DC-F','-',2)    output   7 (2nd occurence of ‘-')

7.What is difference between DECODE and TRANSLATE ?

Translate: translate function replaces a sequence of characters in a string with another set of characters.

For example:

Translate('1tech23', '123', '456); would return '4tech56'

Translate('222tech, '2ec', '3it'); would return '333tith'

Decode: The DECODE function compares one expression to one or more other expressions and, when the base expression is equal to a search expression, it returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified

8.What is use of a cursor variable? How it is defined.

A cursor variable is associated with different statements at run time, which can hold

different values at run time. Static cursors can only be associated with  one run time

query. A cursor variable is reference type(like a pointer in C).

Declaring a cursor variable:

TYPE  type_name  IS REF CURSOR RETURN  return_type  type_name is the name of

the reference type,return_type is a record type  indicating the types of the select list

that will eventually be returned by the cursor variable.

9. B Tree and Bit map:

Bitmap indexes are widely used in data warehousing environments.

it is used for less no.of data entries for a given column in a table. example gender, marital status 

B tree:

its the default index when we use indexes in oracle. 

A type of index that uses a balanced tree structure for efficient record  retrieval. B-tree indexes store key data in ascending or descending order.

10. View and Materialized view?

View - getting the query info from multiple tables.

Materialized view- A materialized view is a database object that contains the results of a query.

11. for all and for 

FOR is an actual loop which will go through records one by one and do some processing.

FORALL is NOT an actual loop, it's just a notation for a bulk DML operation. It will NOT go through rows one by one. For example, you can do some row processing in a FOR loop, but you won't be able to do it in FORALL.

12. Bulk Collect and Bulk Binding

Bulk binding is a generic term for any PL/SQL . it make use of collection in order to improve performance.

Bulk collect  is one particular type of bulk binding that is used to quickly populate the SELECT statement using bulk collect.

13. case and decode 

Decode can be used only in SQL queries 

Case can be use in PL/SQL and SQL.

14. in and exit 

exit will come out and return the value

in wil keep on iterating till it fetch completely 

15.Key Join Conditions for P2P?

PO req and PO

PO_REQ_DISTRIBUTIONS_ALL (req_sistribution_id) = PO_DISTRIBUTIONS_ALL (distribution_id)

PO AND SHIPMENTS

PO_HEADERS_ALL(po_header_id) = RCV_TRANSACTIONS(po_header_id)

SHIPMENTS AND INVOICE

RCV_TRANSACTIONS(rcv_transaction_id)=  AP_INVOICE_DISTRIBUTIONS_ALL(rcv_transaction_id

SHIPMENT AND INVENTORY

RCV_TRANSACTIONS(organization_id)= MTL_SYSTEM_ITEMS_B (organization_id)

PO AND AP

PO_DISTRIBUTIONS_ALL(po_distribution_id)= AP_INVOICE_DISTRIBUTIONS_ALL(po_distribution_id)

PO AND INV

PO_REQUISITION_LINES_ALL(item_id) = MTL_SYSTEM_ITEM_B(inventory_item_id)

org_id = Organization_id

PO, INV, OM INTERFACE/CONVERSION FLOW:

Purchase Order Conversion:

Import Standard Purchase Order

1. According to the data file create control file
2. then registered the control file and run the concurrent program which will load the data to temporary table.
3. wrote a package to validate the data before migrating the data to base tables.
4. Validations done are:

HEADERS

1.  supplier
2. supplier site
3. buyer
4. operating unit
5. payment terms
6. bill-to-location
7. ship-to-location
8. FOB, freight terms

LINES:

1. line type
2. item
3. ship-to-org
4. ship-to-location_id     
5. requestor 
6. delivery-to-location

After the migration need to check the duplicate records in interface tables and base tables.

Interface Tables:

po_headers_interface

po_lines_interface

po_distribution_interface 

po_interface_errors

ORDER MANAGEMENT (OM):

Order Import Interface

Order Import enables you to import Sales Orders into Oracle Applications 

Validations:

1. order_date (header level)
2. delivery_lead_time (line level)
3. earliest_acceptable_date
4. freight_terms
5. sales_rep_id
6. sold_to_org_id

API:

OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER

Interface Tables:

OE_HEADERS_IFACE_ALL

OE_LINES_IFACE_ALL

Inventory (INV):

Item Import

Validation:

1. Item type
2. segment1
3. item status
4. template_id
5. uom
6. attributes
7. organization_id

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE

MTL_INTERFACE_ERRORS

MTL_SYSTEM_ITEMS_INTERFACE:

PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete,3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process,7 = Import succeeded)

These are the few validation I had mentioned in this document but in real time need to validate many more.......

Account Payables (AP) Conversion TechnoFunctional Responsibilities

Account Payables Conversions:

1. Create BR100 Document according to the client requirement 
2. Created CV40 according to the requirements.
3. Created data template according to the client legacy system data.
4. According to the data file create control file.
5. then registered the control file  and run the concurrent program which will load the data to temporary table.
6. wrote a package to validate the data before migrating the data to base tables.
7. Validations done are:
1. segment1
2. check vendor_id
3. vendor_site_id
4. vendor_site_code
5. vendor_type_lookup_code
6. Num_1099 (Tax_identification number)
7. invoice_currency_code
8. term_id  (payment term id)
9. address_line1
10. address_line2
11. address_line3
12. city
13. state
14. country
15. zip

These are the few validation which I'm mentioning but in real time we would perform more.....

  8. I have used below API's to import the data

 Supplier : ap_vendor_pub_pkg.create_vendor 

 Supplier_sites: ap_vendor_pub_pkg.create_vendor_site

 Supplier contacts ap_vendor_pub_pkg.create_vendor_contact

Bank:

IBY_EXT_BANKACCT_PUB.create_ext_bank

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

IBY_EXT_BANKACCT_PUB.create_ext_bank_acct

IBY_PMT_INSTR_USES_ALL - (Account details reflect)

Alternatively 

Interface Tables are:

ap_supplier_int

ap_supplier_sites_int

ap_sup_site_contact_int

After the Conversion done on Supplier, Sites and Contacts we need to associate the banks either at Supplier level or Site level.

There are few changes between 11i and R12 Conversion process specially you can find it when performing on Supplier banks.

Hope this Diagram gives clear idea for key join conditions for assigning banks.

AP Process Flow:

Invoice Structure:

FAQ:

1. How many types of Invoices we can create in Oracle Payables?

A. Standard

B. Debit Memo

C. Credit Memo

D. Pre-Payment

E. Expense Report

F. Withholding Tax Invoice

G. Miscellaneous Invoice

2. Describe the 4-Way Matching?

2 – Way Matching: The Invoice Amount Should match with PO, (Invoice with PO)

3 – Way Matching: The Receipts Should match with Invoice, (Invoice, PO with Receipts)

4 – Way Match: The Goods Should match with Inspection/Accepted (Invoice, PO, Inspections & Receipts)

3. How many Holds we have?

System Holds: Tax, Quantity Match, Po amount with Invoice Amount

Manual Holds: Invoice Limit, Hold on Invoice

4. Can you Release Manual Holds? If Yes, How?

Yes. Holds – Release Holds

5. How many ways you can pay the Invoice Amount?

Apply in Full Schedule Payments Installments

6. What is the difference between Debit and Credit Memo?

Debit Memo will raise the Customer

Credit Memo will raise the Vendor

7. Can you create a Duplicate Vendor ?

No

8. Vendor Number Should be Generate Automatic or Manually?

Both, Manual And Automatic

9. How many key flexfields are there in Payables?

No key flexfields in PO,AP

10. What are Payment Terms?

Payables uses payment terms to automatically calculate due dates, discount dates, and discount amounts for each invoice you enter. Payment terms will default from the supplier site. If you need to change the payment terms and the terms you want to use are not on the list of values, you can define additional terms in the Payment Terms window.

11. What are the Payment Methods available?

• Check – You can pay with a manual payment, a Quick payment, or in a payment batch.

• Clearing – Used for recording invoice payments to internal suppliers.

• Electronic – You generate an electronic payment file that you deliver to your bank to create payments. Use Electronic if the invoice will be paid using EFT or EDI.

• Wire – Used to manually record a wire transfer of funds between your bank and your supplier’s bank.

12. What are the line types of an Invoice?

Item: Item lines capture the details of the goods and services billed on your invoice.

Freight: Freight lines capture the details of your freight charges.

Miscellaneous: Miscellaneous lines capture the details of other charges on your invoices such as installation or service.

Tax: Payables integrates with Oracle E-Business Tax to automatically determine and calculate the applicable tax lines for your invoices.

13. What are the Invoice Distribution Types?

Item

Freight

Miscellaneous

Tax

Withholding

Prepayment

14.What are Holds in AP?

Holds can be placed by the system and some holds can be placed manually. Holds prevent payment and, sometimes, accounting of an invoice. Payables applies holds to invoices that fail the Invoice Validation process. Release holds from invoices to allow payment and accounting entry creation for the invoices.

15. What are the Interface Tables in AP?

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

AP_INTERFACE_CONTROLS

————————————–

AP_SUPPLIERS_INT

AP_SUPPLIER_SITES_INT

AP_SUP_SITE_CONTACT_INT

AP_SUPPLIER_INT_REJECTIONS

What is the API to cancel single AP Invoice?

AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE

16. What is the API to find invoice status?

AP_INVOICES_PKG.GET_APPROVAL_STATUS

17. Give some of the Oracle Payables interface?

a) Credit Card Transaction Interface

b) Invoice Import Interface

c) Payables Open Interface

d) Purchase Order Matching

18. What are the key tables where supplier bank information are stored?

IBY_EXTERNAL_PAYEES_ALL

IBY_EXTERNAL_PAYERS_ALL

IBY_EXT_BANK_ACCOUNTS

IBY_PMT_INSTR_USES_ALL

19. What is meant by with-holding tax invoice?

A separate tax invoice generated for withholding tax amount. Later this invoice will be knocked of when making the payment made to the authority.

20. What are all the Modules Interacting with AP?

Cash Management

Oracle iExpenses

General Ledger

Oracle Assets

Subledger Accounting (R12)

HRMS

Project Accounting

Purchasing/iprocurement

Global Accounting Engine (11i)

21. What are the mandatory setups in AP?

1- Financial Options

2- Define Suppliers

3- Define Payment Terms

4- Define Payment Methods

5- Define Banks and Banks Accounts And Banks Accounts Documents

6- Open AP Accounts Periods

22. What is pay date basis?

The Pay Date Basis for a supplier determines the pay date for a supplier’s invoices.

• Due

• Discount

23. What is the difference between quick payment and manual payment?

Quick Payment: It allows you to make a single payment against one or more invoices at a time to one supplier through payables.

Manual Payment: This is the process of entering the check details which has been paid manually in some emergency requirements into the payment form and selecting the invoices of the concerned supplier and check whether the total of the invoices and the paid amount at the header are same and save.

24. What is terms date basis?

Terms Date Basis is to calculate due date.

Due date is calculated 4way. Eg: payment term is 30days

Due date = Sysdate + 30days

Due date = Invoice date + 30days

Due date = Goods Receive Date + 30days

Due date = Invoice Received date + 30days

25. How you will transfer payables to general ledger?

When the payable accounting entries are created, then run the program called ‘Payables Transfer to GL’ Program, which sends the invoice entries and payable entries to GL interface. Then submit a request called Journal import to import journal entries to GL.

26. What are Aging Periods?

Aging periods are nothing but the periods that we setup to control and maintain the supplier outstanding bill towards the invoice. From this we can able to study the due date of the supplier form the generation of invoice.

27. What is a “Mixed” Invoice and how do I enter one?

Mixed Invoices are invoices or credit/debit memos for which you can perform both positive and negative matching to purchase orders and to other invoices.

For example, you can enter an invoice for -$100 with Invoice Type Mixed. You can match to an invoice for $-200, and match to a purchase order for $100.

To enter a Mixed invoice:

1. Enter the invoice or credit/debit memo in the Invoices Summary, and enter Mixed as the invoice Type. You can enter either a positive or negative invoice amount.

2. Match to purchase orders, and/or invoices.

SQL * LOADER Interview Question and Answers

1.What is SQL*Loader and what is it used for?

            SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database

    -- This is a sample control file

    LOAD DATA

    INFILE 'sample.dat'

    BADFILE 'sample.bad'

    DISCARD FILE 'sample.dsc'

    APPEND

    INTO TABLE emp

    WHEN (57) = '.'

   TRAILING NULLCOLS

2. Is there a SQL*Unloader to download data to a flat file?

            Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on

spool oradata.txt

select col1 || ',' || col2 || ',' || col3 from tab1 where  col2 = 'XYZ';

spool off

Alternatively use the UTL_FILE PL/SQL package:

rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter

declare

            fp utl_file.file_type;

begin

            fp := utl_file.fopen('c:\oradata','tab1.txt','w');

           utl_file.putf(fp, '%s, %s\n', 'TextField', 55);

           utl_file.fclose(fp);

end;

/

3. BAD File in Sql * loader?

BAD File contains the records which are rejected either by SQL Loader or by the database because of the bad formatting or data type mismatch.

4. DISCARD File in Sql * loader?

DISCARD file is to store the records that are neither inserted into table nor rejected as bad. This is an optional parameter with SQL Loader and by default name would be .dsc.

depends on  filtration.

5. What is TRAILING NULLCOLS?

The TRAILING NULLCOLS clause causes SQL*Loader to treat any fields that are missing at the end of a record as null. 

If the last field does not contain data, during the loading (inserting data), this clause (TRAILING NULLCOLS) says to load a 'null value’.

6.Loading positional (fixed length) data

If you need to load positional data (fixed length), look at the following control file example:

LOAD DATA

  INFILE *

  INTO TABLE load_positional_data

  (  data1 POSITION(1:5),

     data2 POSITION(6:15)

  )

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

7. Load Images, Sound Clips and Documents

CREATE TABLE image_table (

       image_id   NUMBER(5),

       file_name  VARCHAR2(30),

       image_data BLOB);

LOAD DATA

INFILE *

INTO TABLE image_table

REPLACE

FIELDS TERMINATED BY ','

(

 image_id   INTEGER(5),

 file_name  CHAR(30),

 image_data LOBFILE (file_name) TERMINATED BY EOF

)

BEGINDATA

001,image1.gif

002,image2.jpg

003,image3.jpg

8. How to stop after 1000 records.

Load=1000 is used.