Report on Inactive Items that exists on active POs by buyer and by Organization

MD.050 Report Design &

Features

Steris Corporation

Functional Design

Report on Inactive Items that exists on active POs by buyer and by Organization

 

Topical Essay

The purpose of this deliverable is to document the functional specifications for report on inactive items that exists on active purchase orders by buyer and by organization. We submit a request, which generates the report with the help of an executable attached to a concurrent program.

Based on this report we can know the status of the items on which a purchase order has been raised.

 

Basic Business Needs

This report provides the features you need to satisfy the following basic business needs.  You will be able to address the following businesss requirements:

·         Organization-wise status of inactive items for which an active purchase order exists.

·         Buyer-wise status of inactive items for which an active purchase order exists.

 

Major Features

§  The report gives the status based on user specified date.

The report can be generated based on either organization or buyer or by selecting both the organization and the buyer in the parameter window

 

Assumptions

This specification document assumes that the following statements are true:

1.      Inventory set up Complete

2.      Purchase Order set up Complete

 

Pre-requisites

To ensure report generates smoothly, you need to have the following data:

Transaction date for items

Status for purchase orders

Definitions

Inactive Item: The item, which don’t have any inventory transaction since the specified date is called as an inactive item.

Active Purchase Order: The Purchase order, which is neither closed nor expired, is called as an active purchase order.

 

User Procedures

The user procedures for the business process are as follows:

·         NA

Business rules

The report on inactive items that exists on active POs has to be generated based on the ‘organization’ and the ‘buyer’.

 

 

 

Report Description

This report will be generated based on the data provided by both Oracle Inventory and Oracle Purchasing.

Application Integration Scope

Name	Source Application	Source Platform	Targe Application	Target Platform	Frequency	File size	# of trx
Report on inactive Items that exist on acitve POs	Oracle Inventory & Oracle Purchasing		Oracle Applications 11i (11.5.10)		Once

<Source/Target Application Requirements/Dependencies>

Data Feed Format/Mapping

<Data Feed Format>

1.      MTL_SYSTEM_ITEMS_B

Column	Position (From/To)	Max length	Validation (LOV)	Data type	Required
INVENTORY_ITEM_ID
ORGANIZATION_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
CREATION_DATE
CREATED_BY
LAST_UPDATE_LOGIN
SUMMARY_FLAG
ENABLED_FLAG
DESCRIPTION
BUYER_ID
SEGMENT1
SEGMENT2
PURCHASING_ITEM_FLAG
INVENTORY_ITEM_FLAG
PRIMARY_UNIT_OF_MEASURE

2.       MTL_MATERIAL_TRANSACTIONS

Column	Position (From/To)	Max length	Validation (LOV)	Data type	Required
INVENTORY_ITEM_ID
REVISION
ORGANIZATION_ID
TRANSACTION_DATE

3.       PO_LINES_ALL

Column	Position (From/To)	Max length	Validation (LOV)	Data type	Required
PO_LINE_ID, ITEM_ID
ITEM_DESCRIPTION
CLOSED_CODE
CLOSED_DATE
ORG_ID
EXPIRATION_DATE

 

Validations

None

 

 

Data Transformation

None

 

 

Data Transfer Mechanism

None

Interface Logic

None

Frequency/ Run Time

1 time conversion.

Response time

 

====================================================

 

Report in Inactive Items that exists on active POs
	Name	Path
Application	Custom_Appl_Inv	XXMCA
Responsibility
Name	Custom_Inv_Responsibility
Application	Custom_Appl_Inv
Description	Report on Inactice Items
Data Group Name	Standard
Data Group Application	Custom_Appl_Inv
Request Group Name	Custom_RequestGroup
Request Group Application	Custom_Appl_Inv
Menu	Custom_MENU
Report Executable File
	Riteminactive.rep	CUST_I/11.5.0/reports/US
Staging Table
Interface Table
Base Table
	MTL_SYSTEM_ITEMS_B
	MTL_MATERIAL_TRANSACTIONS
	PO_LINES_ALL
	PER_ALL_PEOPLE_F
Loader Script
PL/SQL Script
Concurrnet Executable
	Riteminactive
Concurrent Program

======================================================

 

MD.070 Report Design &

Features

Steris Corporation

Technical Design

Report on Inactive Items that exists on active POs by buyer and by Organization

                       

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

Inactive items are the items, which don’t have any inventory transaction since the specified date. Here the report gives the list of items that are inactive and also contain an active purchase order. 

So based on this report we can know the status of the items on which a purchase order has been raised.

Assumptions

This design document assumes the following:

n  Assumption1

n  Assumption2

Scope

Data selection criteria:

All item information required for the report generation is collected from

mtl_system_items,

mtl_material_transactions,

po_lines_all,

per_all_people_f

Data manipulation criteria:

N.A

Data production criteria:

N.A

Data exclusion criteria:

N.A

Approach

The file (Riteminactive.rdf) was created to generate the report of inactive items that exist on active purchase orders. The file is executed by running the request from the submit request window or by running the request from the reports menu of Oracle inventory application. 

Module List

		Name		Type	Description
Ritmeinacive.rdf	Oracle Report		Report on inactive items that exists on active POs

Base Tables

The following tables are used for the generation of the report.

mtl_system_items,

mtl_material_transactions,

po_lines_all,

per_all_people_f

Dependencies

The value for org_id is dependent on the table

MTL_MATERIAL_TRANSACTIONS

The value for buyer_id is dependent on the table

MTL_SYSTEM_ITEMS_B

High level program information

The following lists summarizes the information of report on inactive items that exists on active POs:

Property	Value
Name	Riteminactive
Concurrent program name	Riteminactive
Data range	Full
Incompatibility	Itself
Requires locking underlying tables	No
File name	Riteminactive.rdf
Interface  directory name	CUST_I
Sorting required	No
Pre-requisites	Executable file
Restart restrictions	None

Report components

The following list contains all components required to generate the report.

Concurrent programs

Name : Riteminactive

Executable Name: Riteminactive

Type: Oracle Reports

Parameters

There are three parameters to be defined in the parameter window for which we have to pass the values from the value sets in the parameter form at runtime.

Cutoff Date

Organization Id

Buyer Id

For these parameters we have to assign the value sets.

Value sets

Name : INV_SRS_ORG

Table Name: ORG_ORGANIZATION_DEFINITIONS

Name : BUYER_ID

Table Name: PER_ALL_PEOPLE_F

Name : INV_SRS_DATE

Format Type: Date

Report generation process:

1.      Put the rdf file in

D:\oracle\prodappl\cust_I\11.5.0\reports\US

2.      Create an executable with the type as Oracle Report

3.  &nbst;   Define a concurrent program with the executable and attach it to a request group.

Program Code

j

SQL Statement

SELECT

msi.segment1,

msi.description,

msi.inventory_item_id,                 

mmt.subinventory_code,

max(mmt.transaction_date) last_trans_date,                 

msi.inventory_item_status_code,

msi.buyer_id,mmt.organization_id

FROM

mtl_system_items msi,

mtl_material_transactions mmt,

po_lines_all pla

WHERE

msi.organization_id=mmt.organization_id and                                           msi.inventory_item_id=mmt.inventory_item_id and

msi.inventory_item_id=pla.item_id and

mmt.inventory_item_id=pla.item_id and

msi.inventory_item_flag = 'Y' and

msi.purchasing_item_flag='Y' and 

((expiration_date>sysdate or expiration_date is null) and

(closed_code not in ('CLOSED','FINALLY CLOSED') or closed_code is null)) 

&pass

GROUP BY

msi.segment1,

msi.description,

msi.inventory_item_id,

mmt.subinventory_code,

msi.inventory_item_status_code,

msi.buyer_id,

mmt.organization_id

HAVING

max(mmt.transaction_date)<=:enterdate

After Parameter Form Trigger

Function AfterPForm return boolean is

Begin

  if :buyerid is null and :orgid is not null then

            :pass:='and mmt.organization_id='||:orgid;

  elsif :buyerid is not null and :orgid is null then

            :pass:='and msi.buyer_id='||:buyerid;

  elsif :buyerid is not null and :orgid is not null then

            :pass:='and msi.buyer_id='||:buyerid||' and mmt.organization_id='||:orgid;

    end if;

    return (TRUE);

end;

Here ‘pass’ is the lexical parameter and ‘buyerid’ and ‘orgid’ are the bind variables for which we have to pass the values at runtime.