Infolinks

Thursday 21 June 2012

Item Cost ComparisonTechnical Spec


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









No comments:

Post a Comment