Obsolete and Excess Inventory Report

MD.070 Application Extension Technical Design

Intelligroup Asia Pvt Limited

Obsolete and Excess Inventory Report

Contents

Document Control................................................................................................................................... ii

Technical Overview................................................................................................................................. 1

Approach........................................................................................................................................... 1

Module List....................................................................................................................................... 1

Table and View Usage.................................................................................................................... 1

Program Logic (pseudo code)........................................................................................................ 1

In this report based on the Date Range and Item Status query will be running,.................. 1

SQL Statements............................................................................................................................... 2

Concurrent Programs.............................................................................................................................. 3

Value Sets.......................................................................................................................................... 3

Database Design...................................................................................................................................... 4

Tables, Indexes, Sequences........................................................................................................... 4

Descriptive Flexfields...................................................................................................................... 4

Grants /Synonyms........................................................................................................................... 4

Installation Requirements...................................................................................................................... 5

Pre-Installation steps....................................................................................................................... 5

Installation........................................................................................................................................ 5

Test Plans.................................................................................................................................................. 6

Unit Test Plan................................................................................................................................... 6

Open and Closed Issues.......................................................................................................................... 7

Open Issues....................................................................................................................................... 7

Closed Issues.................................................................................................................................... 7

Note:To update the table of contents, put the cursor anywhere in the table and press [F9].  To change the number of levels displayed, select the menu option Insert‑>Index and Tables, make sure the Table of Contents tab is active, and change the Number of Levels to a new value.

Technical Overview

This document defines the technical components required to implement a report for ITGR: Obsolete and Excess Inventory Report. This report has been developed using Oracle Reports 6i, which is used to display the details required by the business. Based on the user parameters, details can be filtered.

Approach

Ø  The report should display details like Part, Description, Qty on Hand, Forecast demand, Std Cost, Extended cost

Ø  The report can be generated based on the given parameter values.

Note: 

Note: 

Note: 

Note: 

Note: 

Note: 

Note: 

Note:If the Functional Design included a Technical Overview section, duplicate that information here and add additional details.

Module List

Forms

 N/A

Reports

ITGRINXOBSEX.rdf

Concurrent Programs

ITGR: Obsolete and Excess Inventory Report

This Report is developed to Return on hand quantity and cost information for all items where on hand qty is greater than forecast demand or item obsolete in system.

Table and View Usage

Table Name	SELECT	INSERT	UPDATE	DELETE
MRP_FORECAST_DATES	X
MTL_SYSTEM_ITEMS	X

Note: 

Note: 

Note: 

Note: 

Note: 

Note: 

Note: 

Note: 

Note:Add other components above as required

Program Logic (pseudo code)

In this report based on the Date Range and Item Status query will be running,

SQL Statements

SELECT            DISTINCT c.segment1, c.description,

                c.organization_id,

                (SELECT NVL

                           (SUM (p.transaction_quantity), 0)

                   FROM mtl_onhand_quantities p

                  WHERE p.organization_id =

                                           e.organization_id

                    AND p.inventory_item_id =

                                         c.inventory_item_id)

                                                 onhand_qty,

                mfd.current_forecast_quantity

                                             forecastdemand,

                (SELECT NVL (item_cost, 0)

                   FROM cst_item_cost_type_v

                  WHERE inventory_item_id = c.inventory_item_id

                    AND cost_type = 'frozen'

                    AND organization_id = e.organization_id)

                                                  item_cost,

                  (SELECT NVL

                             (SUM (p.transaction_quantity), 0)

                     FROM mtl_onhand_quantities p

                    WHERE p.organization_id =

                                           e.organization_id

                      AND p.inventory_item_id =

                                         c.inventory_item_id)

                * (SELECT NVL (m.item_cost, 0)

                     FROM cst_item_cost_type_v m

                    WHERE m.inventory_item_id = c.inventory_item_id

                      AND m.cost_type = 'frozen'

                      AND m.organization_id = e.organization_id)

                                                   extended

           FROM mtl_system_items_b c,

                org_organization_definitions e,

                mrp_forecast_dates mfd

          WHERE e.organization_code = '405'

            AND (   mfd.forecast_date BETWEEN :date_range_from

                                          AND :date_range_to

                 OR c.inventory_item_status_code =

                                                :item_status

                )

            AND mfd.inventory_item_id = c.inventory_item_id

            AND c.organization_id = e.organization_id

       ORDER BY extended DESC

Concurrent Programs

Concurrent Program Executable:

Executable	ITGR_INXOBSEX
Short Name	INXOBSEX
Application	Business Online
Description	ITGR: Obsolete and Excess Inventory Report
Execution Method	Oracle Reports
Execution File Name	ITGRINXOBSEX
Subroutine Name

Program          : ITGR: Obsolete and Excess Inventory Report			Enabled [ X ]
Short Name    : INXOBSEX
Application     : Business Online
Description    :  ITGR: Obsolete and Excess Inventory Report
Conflicts Domain Parameter
SEQ	PARAMETER	DESCRIPTION	ENABLED
1	Date Range From	Date Range From	Y
2	Date Range To	Date Range From	Y
3	Item Status	Date Range From	Y

Value Sets

Value Set	Size	Type	Rqd	Validation/Values
DATE_LIST	11	Date		Date
ITGR_ITEM_STATUS	40	Character		Character

COMMENTS/SPECIAL INSTRUCTIONS

Incompatibilities:

Application :

Name :

Scope :

Type :

Database Design

This section summarizes new and changed database objects and data required for Obsolete and Excess Inventory Report.

Tables, Indexes, Sequences   

Descriptive Flexfields

Grants /Synonyms

Sequences

Installation Requirements

Installation scripts must be prepared to perform the following actions in an automated way:

·         Define Value Sets.

·         Register Concurrent Programs.

·         Register Standard Report Submission parameters.

Pre-Installation steps

Installation Steps

Installation

All modules are installed as if part of a separate product.  A UNIX directory structure is created under $XBOL_TOP as shown in the following example:

                                                    $XBOL_TOP

                                                               |

                                                            1.0.0

                 ___________________________|___________________________________

                |       |       |               |       |       |        |      |               |

                reports

                |

                US

The directories contain the components of the enhancement as shown below (directories not listed are empty). 

Test Plans

Unit Test Plan

Seq	Action	Expected Results	Actual Results

Open and Closed Issues

Open Issues

Business ID	Issue	Resolution	Responsibility	Target Date	Impact Date
1

Closed Issues

Business ID	Issue	Resolution	Responsibility	Target Date	Impact Date
1.
2.
3.