Report On Sales Order With Zero Onhand Quantity

Client

Sharada Terry Coat Project

Technical Design

Report for sales order

With zero on hand quantity

                       

Author                : M Viswanadha Raju

Creation Date     :04-july-05

Last Updated     :04-july-05

Last Updated By:  M Viswanadha Raju

           

Version           :DRAFT 1A   

Approvals

Name	Title	Date

Contents

Approvals................................................................................................................................................ 1 Overview................................................................................................................................................................. 3 Assumptions............................................................................................................................................ 3 High level program information...................................................................................................................... 4 Table -Column Details.......................................................................................................................... 4 Concurrent Program Details................................................................................................................. 4 Parameter Details................................................................................................................................... 5 SRS -Screen............................................................................................................................................. 6 Example Report..................................................................................................................................... 7 SQL Script................................................................................................................................................ 8 Open/Closed Issues............................................................................................................................................... 9 Open issues.............................................................................................................................................. 9 Closed issues........................................................................................................................................... 9

Overview

    This is to describe about the  report generation on Sales Orders with Zero on Hand Quantity. The report ‘ Report For Sales order With Zero Onhand Quantity’ will provide the following  details Organization id, item, Sales order no, order Quantity,Flow Status Code of the Sales order with Zero On Hand Quantityt.

The concurrent request  ‘ST_REPORT_FOR_SALESORDER’ is attached to Order Management responsibility. The report can be generated for particular organization and for particulat status of Sales orders.

High level program information

The following tables and columns are used to retireve the data in the report.]

Table – Column Details:

Table Name	Column Name
MTL_SYSTEM_ITEMS_B	INVENTORY_ITEM_ID, SEGMENT1
MTL_ONHAND_QUANTITIES	INVENTORY_ITEM_ID
OE_ORDER_HEADERS_ALL	ORDER_NUMBER FLOW_STATUS_CODE
OE_ORDER_LINES_ALL	ORDERED_QUANTITY

Concurrent Program Details

Name	Details
Program	ST_REPORT_FOR_SALESORDER
Short name	ST
Application	ST Custom Application
Executable	ST_ORDER
Output Format	HTML
Output Style	A4
Report Name	ST_ORDER.rdf
Location	/oraAPP/prodappl/st/11.5.0/reports/US

Sample REPORT:

SQL Script

select distinct m.segment1 as item, h.order_number, h.flow_status_code,

l.ordered_quantity

from oe_order_headers_all h,oe_order_lines_all l,

mtl_system_items_b m

where l.HEADER_ID=h.header_id

and

l.INVENTORY_ITEM_ID=m.INVENTORY_ITEM_ID

and 

l.inventory_item_id not in(select inventory_item_id from mtl_onhand_quantities)

and h.flow_status_code not in ('CLOSED', 'CANCELLED')

/*======================End-of-Program==========================*/

     

Open/Closed Issues

Open issues

Num	Issue description	Resolution	Owner

Closed issues

Num	Issue description	Resolution	Owner