Oracle INV, BOM and WIP Table Joinings

1) JOINS IN BOM and BOM-INV

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 R12 Purchase Order Requisition Table Information

Oracle Financial Accounting

Oracle General Ledger

• The Oracle General Ledger is the central repository of accounting information. The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help people inside and outside the organization make decisions

Oracle General Ledger is a comprehensive financial management solution that enables user to

1.Record and Review Accounting Information

2.Import data from subsidiary ledgers, or enter journals to record actual or budget transaction directly into oracle general ledger

3.Enter encumbrance journals to track encumbrances through the purchase process and to control spending against budgeted amounts.

4.Review account balances online or through reports.

5.Analyze, correct, and adjust accounting information.

6.Correct actual, budget, and encumbrance information.

7.Revalue and translate balances denominated in foreign currencies

8.Consolidate balances from multiple ledgers.

9.Analyze Accounting Information

10.Integrate Oracle GL with Oracle Discoverer, or Web Applications Desktop Integrator to simply the budgeting and forecasting process.

11.Use FSG to build customized reports with reusable report objects.

12.Use Oracle Report Manager to submit and publish FSGsx

General Ledger Accounting Cycle

1-Open period

2-Create/reverse journal entries

3-Post

4-Review

5-Revalue

6-Translate

7-Consolidate

8-Review/correct balances

9-Run accounting reports

10-Close accounting period

1.Open an accounting period. See: Opening and Closing an Accounting Period

2.Enter manual journal entries, including:

• Standard journal entries

• Foreign and dual currency journal entries and Dual Currency Journals

• Statistical journal entries. See: Entering Statistical Journals

• Intercompany journal entries.

3.Import journals from subledgers. If you encounter an error when trying to import a subledger journal, you can correct the import data and rerun journal import. See: Importing Journals: page

4.Define recurring journal formulas for transactions that have a common format or that you enter frequently. You can also create recurring journal formulas to create allocation entries. You can use recurring journals to create three types of journal entries:

• Skeleton entries affect the same accounts each period, but have different posting amounts.

• Standard recurring journal entries use the same accounts and amounts each period

• Formula entries use formulas to calculate journal amounts that vary from period to period. Entering Recurring Journal and Budget Formulas

5.Define Mass Allocation formulas to allocate a cost pool across a group of departments, companies, etc.

6.Generate recurring journal and Mass Allocation journal batches based on formulas you defined.

7.Review the details of your unposted journal batches.

• To view and optionally change unposted journal batches online, use the Enter Journals window.

• To view unposted journal batch detail online, use the Journal Inquiry window.

• To print a report showing unposted batch detail, produce a Journals – General report (set the Posting Status parameter to unposted).

8.Edit unposted journals to change information about an unposted batch or its journal detail, including the batch period and the journal currency.

9.Post your journal batches manually or automatically.

10.Check for posting errors. General Ledger automatically produces a Posting Execution Report so you can check the results of your posting. This report notifies you of any errors.

11.Reverse journals. You can reverse a posted or unposted journal entry. Once you assign a reversing period to the journal, generate and post the reversing batch.

12.Revalue your foreign–denominated assets and liabilities to reflect exchange rate fluctuations at the end of each accounting period.

13.Translate your actual account balances to any foreign currency for reporting purposes.

14.Consolidate sets of books by defining and running a consolidation. You can consolidate sets of books that have different charts of accounts and calendars

15.Produce financial reports and perform online inquiries to review current account balances.

• Review account balances online using the Account Inquiry window.

• Review posted journal details in the Posted Journals Report, as well as in the General Ledger and Account Analysis reports.

• You can also define an unlimited variety of custom reports using the Financial Statement Generator to review account balances in the format of your choice.

16.Enter journals to clear suspense account balances. Examine General Ledger and Account Analysis reports to identify the source of suspense account entries.

17.Close the current accounting period.

18.Open the next accounting period.

General Ledger provides the following categories of standard reports and listings:

• Account Analysis: These reports list the accumulated balances of a range of accounts and all journal entry lines that affect that range. The same segment security rules defined for your chart of accounts extend to account analysis reports.

• Budget: These reports and listings contain information about your budgets and budget organizations, including account assignments and budget hierarchies.

• Chart of Accounts: These reports and listings provide information about the accounts in your chart of accounts, including segment values, rollup ranges and suspense accounts.

• Multi–Company Accounting and Consolidation: These reports and listings provide information about your multi–company accounting and consolidation activities. You can request reports about intercompany transactions made using General Ledger’s CENTRA feature.

• Currency: These listings show the daily, period and historical rates you defined for foreign currencies.

• Financial Statement Generator: These listings provide summary or detail information about the definitions of your Financial Statement Generator report components, reports and report sets.

• General Ledger: These reports list beginning and ending account balances, and all journal entry lines affecting each account balance in your functional and foreign currencies. The same segment security rules defined for your chart of accounts extend to general ledger reports.

• Journals: These reports provide journal information in functional and foreign currencies, including posted, unposted and error journals. You can also review journal activity for particular periods and balancing segments. The same segment security rules defined for your chart of accounts extend to journals reports.

• Trial Balance: These reports list account balances and activity for functional and foreign currencies, budgets, encumbrances and actuals. You can request this information by currency, accounts, and so on. The same segment security rules defined for your chart of accounts extend to trial balance reports.

• Other: These reports and listings provide information about MassAllocation/MassBudget definitions, actual or budget recurring journal formulas, statistical units of measure and value–added taxes received and paid

Outbound Interface Process in Oracle

Outbound Interface process:
Outbound Interface will be used to extract the data from oracle Database tables into the flat files.
While developing the outbound Interface we will use UTL_File to Extract the data.

Inbound Interface will be used to upload the data from legacy system (Flat files) into Oracle Applications base tables.
While Developing the Inbound interface we will use SQL * loader to import the data into base tables.

UTL_FILE Package :
==================
This is One of the PL/SQL Package which will be used to transfer the data from table to files from files to tables But when we are working for file to table we will use SQl *Loader to transfer from table to file We have no alternative we have to use UTL_FILE.

We will use following three functions to generate the file.
1)Utl_File.fopen = To open (or) Create the file
2)Utl_File.Put_line = To Transfer the data into the File.
3)Utl_File.fclose = To close the File after Data transfer.

Outbound Interface Process:
=============================
1)Develop the PL/SQL Program (Either Procedure or Package)
2)Write the Cursor to retrieve the data from database tables.
3)Create file or Open the File by using UTL_File.fopen().
4)Open the Cursor
5)If any validations are there write the validations
6)Transfer the Data into File by using UTL_File.Put_Line().
7)Close the Cursor.
8)Close the File by using UTL_File.fclose();
9)Register the Procedure or Package as Concurrent Program and submit from SRS Window.

Example:

TYPE Select * from v$parameter where name like '%utl_file%'

COPY ANY PATH IN THE ABOVE QUERY RESULT FOR OUTBOUND FILE DESTINATION

WE WILL USE THIS PATH IN PROCEDURE

NOTE: ONLY ABOVE QUERY PATHS ARE SUITABLE FOR OUTBOUND FILES

CREATE OUTBOUND PROCEDURE

CREATE OR REPLACE PROCEDURE XXAA_INV_OUTBOUND

    (

      Errbuf OUT VARCHAR2

      ,Retcode OUT VARCHAR2

--    ,f_id IN NUMBER

--    ,t_id IN VARCHAR2

    )

AS

  CURSOR c1

  IS

    SELECT msi.segment1 item,

      msi.inventory_item_id Itemid,

      msi.description itemdesc,

      msi.primary_uom_code Uom,

      ood.organization_name name,

      ood.organization_id id,

      mc . segment1

      ||','

      ||mc.segment2 Category

    FROM mtl_system_items_b msi,

      org_organization_definitions ood,

      mtl_item_categories mic,

      mtl_categories mc

    WHERE msi.organization_id    = ood.organization_id

    AND msi.inventory_item_id    = mic.inventory_item_id

    AND msi.organization_id      = mic.organization_id

    AND mic.category_id          = mc.category_id

    AND msi.purchasing_item_flag = 'Y'

--    AND msi.organization_id BETWEEN f_id AND t_id;

    AND msi.organization_id = 204;

   

  x_id utl_file.file_type;

  l_count NUMBER(5) DEFAULT 0;

 

BEGIN

  x_id:=utl_file.fopen('/usr/tmp', 'XXAA_INV_OUTBOUND.csv','W');

  --select * from v$parameter where name like '%utl_file%'

  FOR x1 IN c1

  LOOP

    l_count:=l_count+1;

    utl_file.put_line(x_id,x1.item ||'-'|| x1.itemid ||'-'|| x1.itemdesc||'-'|| x1.uom ||'-'|| x1.name ||'-'|| x1.id ||'-'|| x1.category );

  END LOOP;

  utl_file.fclose(x_id);

  Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'||l_count);

  Fnd_File.Put_line(fnd_File.Output,' ');

  Fnd_File.Put_line(fnd_File.Output,'Submitted User name  '||Fnd_Profile.Value('USERNAME'));

  Fnd_File.Put_line(fnd_File.Output,' ');

  Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));

  Fnd_File.Put_line(fnd_File.Output,' ');

  Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);

EXCEPTION

  WHEN utl_file.invalid_operation

  THEN

    fnd_file.put_line(fnd_File.log,'invalid operation');

    utl_file.fclose_all;

  WHEN utl_file.invalid_path

  THEN

    fnd_file.put_line(fnd_File.log,'invalid path');

    utl_file.fclose_all;

  WHEN utl_file.invalid_mode

  THEN

    fnd_file.put_line(fnd_File.log,'invalid mode');

    utl_file.fclose_all;

  WHEN utl_file.invalid_filehandle

  THEN

    fnd_file.put_line(fnd_File.log,'invalid filehandle');

    utl_file.fclose_all;

  WHEN utl_file.read_error

  THEN

    fnd_file.put_line(fnd_File.log,'read error');

    utl_file.fclose_all;

  WHEN utl_file.internal_error

  THEN

    fnd_file.put_line(fnd_File.log,'internal error');

    utl_file.fclose_all;

  WHEN OTHERS

  THEN

    fnd_file.put_line(fnd_File.log,'other error');

    utl_file.fclose_all;

END XXAA_INV_OUTBOUND;

RUN ABOVE PROCEDURE SUCCESSFULLY

GOTO SYSTEM ADMINISTRATOR

Create executable as pl/SQL stored procedure

Create concurrent program and attach the executable

Attach the concurrent program to Request Group

Attach the request group to responsibility

Run the request from Responsibility