Item Cost ComparisonTechnical Spec

CCO

Technical Design

ITEM COST COMPARISON REPORT BETWEEN ORGANIZATIONS

                       

Author:                        C.GOWRI SHANKAR        

Creation Date:            

Last Updated:            

Last Updated By:      

Version:                                  

Approvals

Name	Title	Date

Contents

Approvals................................................................................................................................................ 1 Overview................................................................................................................................................................. 3 Assumptions............................................................................................................................................ 3 High level program information...................................................................................................................... 4 Extract program..................................................................................................................................... 4 Export program components............................................................................................................................ 5 Concurrent programs............................................................................................................................. 5 Indexes..................................................................................................................................................... 5 PL/SQL packages................................................................................................................................... 5 Profile options......................................................................................................................................... 5 Extract<ObjectName> Program...................................................................................................................... 6 Exception handling................................................................................................................................ 6 Extract file format................................................................................................................................. 6 Data mapping......................................................................................................................................... 7 Extract<ObjectName>  program parameters................................................................................... 8 PL/SQL Package Design....................................................................................................................... 9 Global constants and variables........................................................................................................... 9 Cursor definitions................................................................................................................................... 9 Procedure Extract_<ObjectName>.................................................................................................. 10 Open/Closed Issues............................................................................................................................................. 13 Open issues............................................................................................................................................ 13 Closed issues......................................................................................................................................... 13

Overview

The Item Cost Comparison Report will display a report which will provide the information about the cost of a particular item for which costs are defined for various cost types across various organization.

We will build the following concurrent program to run the report to get the comparison details:

1.      Item cost comparison report between the organizations

The Item Cost Comparison Report will extract the details from the following tables (or) views:

1. cst_item_cost_view

2.      cst_cost_type_v

3.      cst_organization_definitions

4.      mtl_items_vl

The report to be run will be kept in the location ‘D:\oracle\prodappl\custom\ 11.5.0\ reports\US’

Assumptions

This design document assumes the following:

·         The items exists in atleast one organization.

·         The item is cost enabled.

·         Costs are defined for the items for any of the cost types.

High level program information

The following lists summarize the Item Cost Comparison Report program information:

Extract program

Property	Value
Name	ITEM COST COMPARISON REPORT BETWEEN ORGANIZATIONS
Concurrent program name	Item cost comparison report between the organizations
Data range	Full
Incompatibility	Itself
Requires locking underlying tables	No
Report file name	ITEM_COST_COMPARISON.rdf
Report directory name	D:\oracle\prodappl\custom\11.5.0\reports\US
Sorting required	No
Pre-requisites	None
Restart restrictions	None

Report program components

The following list contains all components required to completely install the Item Cost Compsrison Report program:

Concurrent programs

None

Indexes

None

PL/SQL packages

None

Profile options

None

Cost Comparison Report Program

The ExtractObject program performs the following steps:

1.      Opens and execute the Report file.

2.      Collect the details about the cost of the itsm across all organizations.

3.      Displays the details in the predefined format.

Exception handling

The Item Cost Comparison  program stops processing upon encountering any errors. It writes an error message to the concurrent manager log file and returns a status of failure.

Main error conditions

·         The item was not cost enabled

Item Cost Comparison Report program parameters

The Item Cost Comparison Report program runs with the following parameters:

Parameter name	Prompt	Value set name	Default	Required
ITEM	ITEM	ITEM VALUE SET 4		Yes
COST	COST TYPE	COST VALUE SET 4		No

 

 

Report Block Design

      Filename          ITEM_COST_COMPARISON.rdf              

Directory                                 custom_top\11.5.0\reports\US

Conc. program            :  Item cost comparison report between the organizations

The Statement in the Report Query block will be like this:

SELECT DISTINCT CST_ITEM_COSTS_VIEW.INVENTORY_ITEM_ID, CST_ITEM_COSTS_VIEW.ORGANIZATION_ID,

CST_ITEM_COSTS_VIEW.COST_TYPE_ID, CST_ITEM_COSTS_VIEW.ITEM_COST, CST_COST_TYPES_V.COST_TYPE,

CST_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME,

MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID,

MTL_SYSTEM_ITEMS_VL.SEGMENT1

FROM CST_ITEM_COSTS_VIEW, CST_COST_TYPES_V, CST_ORGANIZATION_DEFINITIONS, MTL_SYSTEM_ITEMS_VL

WHERE ((CST_ITEM_COSTS_VIEW.COST_TYPE_ID = CST_COST_TYPES_V.COST_TYPE_ID)

 AND (CST_ITEM_COSTS_VIEW.ORGANIZATION_ID = CST_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID)

AND (CST_ITEM_COSTS_VIEW.INVENTORY_ITEM_ID = MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID))

AND (MTL_SYSTEM_ITEMS_VL.SEGMENT1 = :ITEM)

AND (CST_COST_TYPES_V.COST_TYPE = :COST)

 ==============QUERY==================================================

            ITEM COST COMPARISON REPORT BETWEEN ORGANIZATIONS
*******************************************************************************************************************
Tables :     cst_item_costs
    cst_cost_types
   
Views :    cst_item_cost_view
    cst_cost_type_v
    cst_organization_definitions
    mtl_items_vl
   
   
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Application: Custom Application
Request Group: Custom Request Group

Concurrent Program Name: Item cost comparison report between the organizations
Short Name: ITEM_COST_COMPARISON_REPORT
Executable Name: ITEM_COST_COMPARISON_REPORT
Short Name: ITEM_COST_COMPARISON_REPORT
Execution File Name: ITEM_COST_COMPARISON.rdf

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Parameters to be given while running the report :
---------------------------------------------------------------

1. Item name
2. Cost type

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The query in the report wizard will be like this:

SELECT DISTINCT CST_ITEM_COSTS_VIEW.INVENTORY_ITEM_ID, CST_ITEM_COSTS_VIEW.ORGANIZATION_ID,
CST_ITEM_COSTS_VIEW.COST_TYPE_ID, CST_ITEM_COSTS_VIEW.ITEM_COST, CST_COST_TYPES_V.COST_TYPE,
CST_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME,
MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID,
MTL_SYSTEM_ITEMS_VL.SEGMENT1
FROM CST_ITEM_COSTS_VIEW, CST_COST_TYPES_V, CST_ORGANIZATION_DEFINITIONS, MTL_SYSTEM_ITEMS_VL
WHERE ((CST_ITEM_COSTS_VIEW.COST_TYPE_ID = CST_COST_TYPES_V.COST_TYPE_ID)
 AND (CST_ITEM_COSTS_VIEW.ORGANIZATION_ID = CST_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID)
AND (CST_ITEM_COSTS_VIEW.INVENTORY_ITEM_ID = MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID))
AND (MTL_SYSTEM_ITEMS_VL.SEGMENT1 = :ITEM)
AND (CST_COST_TYPES_V.COST_TYPE = :COST)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            ITEM COST COMPARISON REPORT BETWEEN ORGANIZATIONS
*******************************************************************************************************************
Tables :     cst_item_costs
    cst_cost_types
   
Views :    cst_item_cost_view
    cst_cost_type_v
    cst_organization_definitions
    mtl_items_vl
   
   
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Application: Custom Application
Request Group: Custom Request Group

Concurrent Program Name: Item cost comparison report between the organizations
Short Name: ITEM_COST_COMPARISON_REPORT
Executable Name: ITEM_COST_COMPARISON_REPORT
Short Name: ITEM_COST_COMPARISON_REPORT
Execution File Name: ITEM_COST_COMPARISON.rdf

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Parameters to be given while running the report :
---------------------------------------------------------------

1. Item name
2. Cost type

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The query in the report wizard will be like this:

SELECT DISTINCT CST_ITEM_COSTS_VIEW.INVENTORY_ITEM_ID, CST_ITEM_COSTS_VIEW.ORGANIZATION_ID,
CST_ITEM_COSTS_VIEW.COST_TYPE_ID, CST_ITEM_COSTS_VIEW.ITEM_COST, CST_COST_TYPES_V.COST_TYPE,
CST_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME,
MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID,
MTL_SYSTEM_ITEMS_VL.SEGMENT1
FROM CST_ITEM_COSTS_VIEW, CST_COST_TYPES_V, CST_ORGANIZATION_DEFINITIONS, MTL_SYSTEM_ITEMS_VL
WHERE ((CST_ITEM_COSTS_VIEW.COST_TYPE_ID = CST_COST_TYPES_V.COST_TYPE_ID)
 AND (CST_ITEM_COSTS_VIEW.ORGANIZATION_ID = CST_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID)
AND (CST_ITEM_COSTS_VIEW.INVENTORY_ITEM_ID = MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID))
AND (MTL_SYSTEM_ITEMS_VL.SEGMENT1 = :ITEM)
AND (CST_COST_TYPES_V.COST_TYPE = :COST)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------








 

Open/Closed Issues

Open issues

Num	Issue description	Resolution	Owner

Closed issues

Num	Issue description	Resolution	Owner