MD70 DOCUMENT

Project Fleetguard

Application RICE COMPONENTS

Technical Design

 LAST PO Price Report

Document Author	Janardhanreddy
Document Owner (POD)	Janardhanreddy
Version Number	Draft Ver 1.1
Creation Date	20-DEC-2011
Last Update Date
Item ID
Interface ID

Note: 

Note: 

Note: 

Note: 

Note: 

Note: 

Note:Title, Subject, Last Updated Date, Reference Number, and Version are marked by a Word Bookmark so that they can be easily reproduced in the header and footer of documents.  When you change any of these values, be careful not to accidentally delete the bookmark.  You can make bookmarks visible by selecting Tools->Options…View and checking the Bookmarks option in the Show region.

Note:Title, Subject, Last Updated Date, Reference Number, and Version are marked by a Word Bookmark so that they can be easily reproduced in the header and footer of documents.  When you change any of these values, be careful not to accidentally delete the bookmark.  You can make bookmarks visible by selecting Tools->Options…View and checking the Bookmarks option in the Show region.

Document Control

Change Record

Date	Author	Version	Change Reference
20-Dec-11	Janardhan reddy	1.0	Reference of MD050 of  Last_PO_PRICE_REPORT.doc
20-Dec-11	Janardhan reddy	1.1	Update MD70

Reviewers

Name	Position
Madhu	Team Lead

Distribution

Copy No.	Name	Location
1	Library Master	Project Library
2		Project Manager
3

Note: 

Note: 

Note: 

Note: 

Note:The copy numbers referenced above should be written into the Copy Number space on the cover of each distributed copy.  If the document is not controlled, you can delete this table, the Note To Holders, and the Copy Number label from the cover page.

 

Approvals

Name	Designation	Date and Signature

Note:The copy numbers referenced above should be written into the Copy Number space on the cover of each distributed copy.  If the document is not controlled, you can delete this table, the Note To Holders, and the Copy Number label from the cover page.

                                                                           

Contents

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

Approvals........................................................................................................................................ ii

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

Custom Approach......................................................................................................................... 1

Reference Document..................................................................................................................... 1

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

Tables and Views.......................................................................................................................... 2

Proposed Solution................................................................................................................................. 3

Detail Design Components................................................................................................................ 4

Processing Diagrams................................................................................................................... 4

Process Flow Diagrams............................................................................................................... 5

LAST_PO_PRICE_REPORT - Program Logic............................................................................... 6

Calling Arguments....................................................................................................................... 6

Volume/Frequency....................................................................................................................... 6

Input Archive.................................................................................................................................. 6

Report Parameters......................................................................................................................... 6

Report Output Format.................................................................................................................. 7

Report Delivery Channel............................................................................................................. 7

Log Output...................................................................................................................................... 7

Table and View Usage................................................................................................................. 7

Default Data Sources.................................................................................................................... 7

Validation Logic............................................................................................................................ 8

Incompatibility............................................................................................................................... 8

Pre-requisites/Dependencies.................................................................................................... 8

Post-requisites/Dependencies.................................................................................................. 9

Performance Considerations..................................................................................................... 9

SQL Queries 1................................................................................................................................. 9

SQL Queries 2............................................................................................................................... 12

SQL Queries 3............................................................................................................................... 12

Integration Issues................................................................................................................................ 14

Changes Required....................................................................................................................... 14

Shared Components................................................................................................................... 14

Alert conditions........................................................................................................................... 14

Incompatibilities......................................................................................................................... 14

Performance Issues..................................................................................................................... 14

Database Design.................................................................................................................................. 15

New/Updated Seed Data......................................................................................................... 15

Descriptive Flexfields................................................................................................................. 15

Value Sets...................................................................................................................................... 15

Grants/Synonyms...................................................................................................................... 16

Archiving....................................................................................................................................... 16

Tables, Indexes, Sequences....................................................................................................... 16

Registration........................................................................................................................................... 17

Menu............................................................................................................................................... 18

Messages........................................................................................................................................ 18

QuickCodes................................................................................................................................... 18

Installation............................................................................................................................................ 19

Open and Closed Issues for this Deliverable.............................................................................. 20

Open Issues................................................................................................................................... 20

Closed Issues................................................................................................................................ 20

Layout Formats...................................................................................................................................... 1

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

Oracle Purchasing generates Approved Last Po Price Report in Oracle Standard Format. InfoPrint require the Purchase order format to be distinct with InfoPrint logo, Terms and Conditions and other business requirement, hence would leverage to publish the purchase orders in the required InfoPrint format

Custom Approach

The solution will be given by developing one Report using Oracle Reports 6i. The Report can be submitted from Purchasing Responsibility.

Reference Document

Document Name	Location
MD050	Ascendent ERP

Note: 

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

Module List

Reports

The last PO Price Report includes the following Reports:

Short Name	Implementation Name	Description
LAST_REPORT	LAST_PO_PRICE_REPORT	This will be RDF Report Name.

 

 

 

Programs/Processes

The Last PO Price Report includes the following concurrent programs:

Short Name	Implementation Name	Description
LAST_REPORT	LAST_PO_PRICE_REPORT	Short Name of the Concurrent Program Name

Note: 

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

Database Triggers

The Last PO Price Report includes no Trgiger Definitions.

Tables and Views

Table/View Definitions

The Last PO Price Report includes no Custom Table/view Definitions

Proposed Solution

         The seeded Standard Printed Purchase Order Report (Portrait) (LAST_PO_PRICE_REPORT.rdf) would be the base objects for the LAST PO PRICE REPORT.

      The following are the changes in the layout

                    The entire standard PO reports parameters to be included in the custom Report parameters.

                   Added new columns as per the MD050 (please refer Data selection and Formula columns section)

Ø  Company Name

Ø  Additional Text manually attached at Header level and Line level to the Standard Purchase Order Report      

Ø  Special Instructions On individual PO.    

Ø  Standard Purchase Order created based on Global Blanket Agreement or Contract Agreement should have line level text stored at the line level text attachment on Global blanket agreement.   

§  Save as with name “LAST_PO_PRICE_REPORT.rdf”

§  Place the report in $ D:\oracle\visappl\ap\11.5.0\reports\US

§  Create an executable “LAST_REPORT”

§  Create a Concurrent program “Payables LAST PO PRICE Report” with parameters.

§  Assign this Concurrent Program to the Request group “All Reports” and Application is “Payables”

§  Register the report in Oracle Applications in the appropriate responsibility.

                               

Detail Design Components

Processing Diagrams

ER Diagram of Custom Tables

Process Flow Diagrams

LAST PO PRICE REPORT- Program Logic

The LAST PO PRICE REPORT solution will be created by developing one Custom Oracle Report based on the Standard Purchase Order Print Report. For that we need to develop

Ø  One RDF File: To Hold data Template.

Calling Arguments

The Report Custom PO Print Report will have the following parameters:

Parameter Name	Description	Optional
INV_NUM	Print Selection	Yes

Volume/Frequency

As and when Required.

Input Archive

No

Report Parameters

Same as the Calling Arguments.

Report Output Format

The output of the Report will be in PDF format and layout will be as given in MD050.

Report Delivery Channel

None

Log Output

Standard Output generated by Oracle Application Request Submission

Table and View Usage

Table Name	Select	Insert	Update	Delete
PO_HEADERS_ALL	X
PA_PROJECTS_ALL	X
RCV_TRANSACTIONS	X
AP_INVOICES_ALL	X
AP_INVOICE_DISTRIBUTIONS	X
PA_PROJECTS_ALL	x
PER_ALL_PEOPLE_F	x
AP_INVOICES_DISTIBUTIONS_ALL	x
PA_BUDGET_VERSIONS
PA_AGREEMENTS_ALL	x

Default Data Sources

Report Field Name	Description	Source Value
Address	Company Address Of the InfoPrint	None
Po Number	Purchase order number	PO_HEADERS_ALL.SEGMENT1
PO Date	Purchase order date	PO_HEADERS_ALL.CREATION_DATE
Po Approval	Purchase order approval	PO_HEADERS_ALL.AUTHORIZATION_STATUS
Receipt Number	Receipt Number	RCV_SHIPMENT_HEADERS.RECEIPT_NUM
Invoice Number	Invoice number	AP_INVOICES_ALL.INVOICE_NUM
Invoice Date	Invoice date	AP_INVOICES_ALL.INVOICE_DATE
Invoice Status	Invoice status	AP_INVOICES_ALL. MATCH_STATUS_FLAG
Quantity	Received Quantity	RCV_TRANSACTIONS.QUANTITY
Project Number	Project Number	PA_PROJECTS_ALL.SEGMENT1
Project Name	Project name	PA_PROJECTS_ALL.NAME
Start Date	Project Start Date	PA_PROJECT_ALL.START_DATE
Status	Project status	PA_PROJECT_STATUSES.PROJECT_STATUS-NAME
Project manager Name	Project Manager name	PER_ALL_PEOPLE_F.FULL_NAME
Aggrement Amount	Project agreement Amount	PA_AGGREMENTS_ALL.AMOUNT
Funding Amount	Projct Funding Amount	PA_PROJECT_FUNDINGS_F.ALLOCATED_AMOUNT

Incompatibility

None

Pre-requisites/Dependencies

None

Post-requisites/Dependencies

None

Performance Considerations

SQL Queries 1

select distinct pha.SEGMENT1,

           pha.creation_date,

          rsh.receipt_num,

         rt.QUANTITY,

         ppf.FULL_NAME "PM Name",

         ap.INVOICE_NUM,

         AP.INVOICE_DATE,

        DECODE(API.MATCH_STATUS_FLAG, 'A', 'Approved') status,

        AP.INVOICE_AMOUNT,

          ppa.name,

          ppa.PROJECT_STATUS_CODE,

          ppa.START_DATE,

          pbv.RAW_COST,

          paa.AMOUNT,

          pp.ALLOCATED_AMOUNT

from po_headers_all pha,

        pa_projects_all ppa,

        PA_BUDGET_VERSIONS pbv,

        pa_agreements_all paa,

        pa_project_fundings  pp,

         rcv_shipment_headers rsh,

          rcv_transactions rt,

          per_all_people_f ppf,

          ap_invoices_all ap,

          ap_invoice_distributions_all api

where ppa.ORG_ID=pha.ORG_ID

and ppa.PROJECT_ID=pbv.PROJECT_ID

and ppa.PROJECT_ID=pp.PROJECT_ID

AND  rt.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID

 and ppf.PERSON_ID=pha.AGENT_ID

 and ap.INVOICE_ID=api.INVOICE_ID

 and sysdate between ppf.EFFECTIVE_START_DATE

 and ppf.EFFECTIVE_END_DATE

 and pha.segment1='4473'

 and rsh.RECEIPT_NUM='7483'

 and ap.INVOICE_NUM='B12'

and ppa.NAME='ATZ Services'

and paa.amount=’50000’

Integration Issues

Changes Required

Within Product

Custom menus

Other Products

No changes required

Shared Components

Note:The Oracle Application product short names listed below are examples only.  Update the shared component information to reflect the requirements of your customization.

INV

None

BOM

None

ENG

None

MPS/MRP

None

WIP

None

Alert conditions

None.

Incompatibilities

None.

Performance Issues

None.

Database Design

This section summarized new and changed database objects and data required to support LAST PO PRICEREPORT However, the complete database design is documented in the Database Extensions Design document.

New/Updated Seed Data

Rows added to <App Prefix>_LOOKUPS:

Lookup Type	Code	Meaning

Descriptive Flexfields

Application:
Form Name:
Base Table:

Segment Name	Prompt	Size	Value Set	Default Value

Application:
Form Name:
Base Table:

Segment Name	Prompt	Size	Value Set	Default Value

Value Sets

             The Report Custom LAST PO PRICE Report will have the following Value Sets

Value Set	Size	Type	Rqd	Validation/Values
B12_INDEPENDENT	20	None	Yes	None

Grants/Synonyms

Owner	Object	Grantees

Archiving

No need to archive.

Tables, Indexes, Sequences

No new tables

Registration

Concurrent Program Executable

Executable	LAST PO PRICE REPORT
Short Name	LAST_REPORT
Application	PAYABLES
Description
Executable Method	Oracle Reports
Executable File Name	LAST_PO_PRICE_REPORT
Subroutine

Concurrent Program Registration                                                                                

Program	LAST PO PRICE REPORT
Enabled	YES
Short Name	LAST_REPORT
Application	PAYABLES
Description
Executable
Executable: Name	LAST_PO_PRICE_REPORT
Executable: Options
Executable: Method
Output
Format	TEXT
Output: Save	YES
Output: Print	YES
Output: Columns
Output: Rows
Output: Style
Output: Style Required	N/A
Output: Printer	N/A
Incompatibilities
Program	NO
Application	NO
Name	NO
Scope	NO

Concurrent Program Request Set Registration

Request Set Name	N/A
Enable	N/A
Short Name	N/A
Application	N/A
Description

Seq	10
Program	N/A
Short Name	N/A
Application	N/A
Description	N/A

Seq	20
Program	N/A
Short Name	N/A
Application	N/A
Description	N/A

Menu

Menu	User Menu Name	Type	Description
N/A

Messages

N / A

QuickCodes

                                          N / A

Note:Installation Add to or modify this list as appropriate.  Provide additional details where necessary to facilitate the creation of the installation routines.

Installation

·Complete this section after completing the <Subject> customization.  Update the design to reflect any changes made in the course of development and describe how the customization was implemented below.

All Oracle Applications customizations developed by the Technical Team will be registered within a custom application. As with other Oracle Applications, custom applications will adhere to the standard APPL_TOP directory structure. For the LAST PO PRICE REPORT custom application we will replicate the standard Oracle Applications directory structure shown below:

                         $APPL_TOP

                             |

                         $XXIPO_TOP

                             |

                        <Application Version>

        ___________________________|___________________________________

       |       |  |     |       |       |        |    |     |

       admin    bin    lib     forms  reports  out      log     install   sql

Directory Structure	Contents
$APPL_TOP	An environment variable that defines the Oracle Applications top-level directory.
$CUSTOM_TOP	An environment variable that defines the top-level directory for the custom Oracle Application for LAST PO PRICE REPORT. All custom objects will be registered under this application
<application version>	A sub-directory for each version of the custom application (this is normally just a constant eg. 11.5.10)
/admin/sql	Database object creation scripts (eg. Tables, PL/SQL Packages, Synonyms, etc)
/bin	SQL*Loader Control Files, C programs, UNIX Shell Scripts, Perl Shell Scripts
/lib	Shared Libraries
/log	Log files
/output	Output files
/forms/US	Oracle Forms and Forms Libraries
/reports	Oracle Reports
/sql	SQL Scripts for SQL*Plus concurrent programs
/out	Standard out directory for reporting when installing

The directories contain the components of the enhancement as shown below (directories not listed are empty).  All future customizations will also be stored in these directories.

FILE	Location
LAST_PO_PRICE_REPORT.rdf	$D:\oracle\visappl\ap\11.5.0\reports\US

·Indicate the specific menu options added to access new forms.

Open and Closed Issues for this Deliverable

·Add open issues that you identify while writing or reviewing this document to the open issues section.  As you resolve issues, move them to the closed issues section and keep the issue ID the same.  Include an explanation of the resolution.

When this deliverable is complete, any open issues should be transferred to the project- or process-level Risk and Issue Log (PJM.CR.040) and managed using a project level Risk and Issue Form (PJM.CR.040).  In addition, the open items should remain in the open issues section of this deliverable, but flagged in the resolution column as being transferred.

Open Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date
1	Some Fields sources are not known		System administater

Closed Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date