md070 cu-293 quarterly po price updaterev1

AIM

MD.070 APPLICATION EXTENSION TECHNICAL DESIGN AND UNIT TEST PLAN

VeriFone Corporation CU-293 Quarterly PO Price Update

Author: Madhukar Salunke

Creation Date: October 12, 2009

Last Updated: 10/12/2009 09:36:00 PM

Document Ref: MD050- Quarterly PO Price Updaterev1.doc

Version: 1.0

Approvals:

<Approver 1>

<Approver 2>

MD.070 Application Extension Technical Design

Document Control

Change Record

1

Date Author Version

Change Reference

12-Oct-09 Madhukar Salunke 1.0 No Previous Document

Reviewers

Name Role

Siva Servanna IT ManagerShaik Basha Functional Consulatant

Distribution

Copy No.

Name Location

1 Library Master Project Library2 Project Manager34

Note To Holders:

If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.

File Ref: document.docLast Updated: 10/12/2009 09:36:00 PM Oracle and VeriFone Confidential - For internal use only

14 of 17

Doc Ref: CU-293 Quarterly PO Price Update


Contents

Document Control.........................................................................................iii

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

Approach..................................................................................................1Assumptions.............................................................................................1Module List...............................................................................................2

XXPO: Upload Requisitions to Oracle from Veriweb - Concurrent Program Logic...............................................................................................................4

Calling Arguments...................................................................................4Log Output...............................................................................................4

VFI - PR to Oracle Interface Load file names - Concurrent Program Logic...5

Calling Arguments...................................................................................5Log Output...............................................................................................5

XXPO: PR to Oracle Interface (Sql Loader) - Concurrent Program Logic......6

Calling Arguments...................................................................................6Log Output...............................................................................................6Table and View Usage.............................................................................6Program Logic (pseudo code)..................................................................6SQL Statements.......................................................................................7Default Data Sources...............................................................................8Validation Logic.......................................................................................8Incompatibility.........................................................................................8Performance Considerations...................................................................8Other Considerations...............................................................................9

Integration Issues........................................................................................10

Changes Required.................................................................................10Shared Components..............................................................................10Alert conditions......................................................................................10Incompatibilities....................................................................................10Performance Issues...............................................................................10

Database Design.........................................................................................11

Desired Table Changes..........................................................................11New/Updated Seed Data.......................................................................11Descriptive Flexfields.............................................................................11Value Sets..............................................................................................11Grants/Synonyms..................................................................................11Archiving................................................................................................11Database Diagram.................................................................................11Tables, Indexes, Sequences..................................................................12

Unit Test Plan...............................................................................................13


14 of 17



Installation Requirements...........................................................................14

Implementation Notes.................................................................................15

Design Summary...................................................................................15Coding Summary...................................................................................15Testing Summary...................................................................................16Installation.............................................................................................16

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

Open Issues............................................................................................17Closed Issues.........................................................................................17


14 of 17


Technical Overview

VeriFone business wants to avoid DCC execution process for an update script.

VeriFone business has been spending 3 to 4 hours for each DCC execution for every quarter, during this process, business need to verify/validate the date accuracy before request for each DCC execution.

Approach

Once new GPA is approved, Business user submits an IT Request with the following Input parameters to update Old GPA Number with New GPA Number and old GPA Line Number with new GPA Line number for Specific Buyer for all open PO lines.

Old GPA Number

New GPA Number

Buyer Name (Last Name, First Name)

1. All the input parameters should be LOV type.

2. All the approved GPAs should be listed in LOV to select.

3. Active buyer names should be listed in LOV to select.

Assumptions

This design assumes that the following statements are true:

Module List

Forms

CU-293 Quarterly PO Price Update includes the following forms:

None


14 of 17


Reports

CU-293 Quarterly PO Price Update includes the following reports:

None

Concurrent Programs

CU-293 Quarterly PO Price Update includes the following concurrent programs:

XXVFI: Quarterly PO Price Update

PL/SQL Packages

CU-293 Quarterly PO Price Updateincludes the following package:

The custom package XXVFI_QTLY_POPRICE_UPDATE will be developed for all logic CU-293 Quarterly PO Price Update

Workflows

CU-293 Quarterly PO Price Update includes the following workflow:

None

Database Triggers

CU-293 Quarterly PO Price Update includes the following forms:

None

Tables

CU-293 Quarterly PO Price Update includes the following tables:

None

Index

CU-293 Quarterly PO Price Update includes the following indexs:

None

Synonyms

CU-293 Quarterly PO Price Update includes the following synonyms:

None


14 of 17


XXVFI: Quarterly PO Price Update - Concurrent Program Logic

A concurrent program ‘XXVFI: Quarterly PO Price Update’ will be registered. This is the main program for Quaterly PO Price Update to update from old to new GPA number.

Calling Arguments

There are three mandatory arguments attached to ‘XXVFI: Quarterly PO Price Update’ program.

Old GPA Number

New GPA Number

Buyer Name (Last Name, First Name)

Log Output+---------------------------------------------------------------------------+Start of log messages from FND_FILE+---------------------------------------------------------------------------+Entered New GPA Number:- SANSG_EXW_Q409Entered Old GPA Number:- SANSG_EXW_Q309Entered Buyer Name :- Lim, RachelEntered Old GPA Number SANSG_EXW_Q309 is approved GPA NumberEntered New GPA Number SANSG_EXW_Q409 is approved GPA NumberError no data found for part number:- OP4343DDLA123S3M88-LPO not found :- 2703095PO line not found :- 299942PO item id not found :- 897246Error no data found for part number:- OP4343DDLA123S3M88-LPO not found :- 2701175PO line not found :- 351483PO item id not found :- 373241Error no data found for part number:- OP4343DDLA123S3M88-LPO not found :- 2701629PO line not found :- 265266PO item id not found :- 473253Error no data found for part number:- OP4343DDLA123S3M88-LPO not found :- 2704528PO line not found :- 351161PO item id not found :- 306892+---------------------------------------------------------------------------+End of log messages from FND_FILE+---------------------------------------------------------------------------+


14 of 17


Table and View Usage

Table Name Select Insert Update Delete

PO_HEADERS_ALL X

PO_LINES_ALL X X

MTL_SYSTEM_ITEMS_B X

PO_LINE_LOCATIONS_ALL X X

PO_AGENTS X

PER_ALL_PEOPLE_F X

Program Logic (pseudo code)

Updating PO price on Quaterly basis from old to new GPA number..

SQL Statements

1 - Data Selection SQL 1:

SELECT ph.segment1, pl.item_id, pll.po_header_id, pll.po_line_id, pll.line_location_id FROM po_headers_all ph, po_lines_all pl, po_line_locations_all pll, po_agents pa, per_all_people_f ppf, po_headers_all gpa_ph, po_lines_all gpa_pl WHERE ph.authorization_status = 'APPROVED' AND ph.type_lookup_code = 'STANDARD' AND NVL(pl.cancel_flag,'N') = 'N' AND pll.quantity_cancelled <> pll.quantity AND pll.po_line_id = pl.po_line_id AND pl.po_header_id = ph.po_header_id AND ph.agent_id = pa.agent_id AND pa.agent_id = ppf.person_id AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND ppf.full_name = p_buyer_name AND pll.quantity_received = 0 AND NOT EXISTS (


14 of 17


SELECT NULL FROM rcv_shipment_lines rsl WHERE rsl.shipment_line_status_code = 'EXPECTED' AND rsl.po_header_id = pll.po_header_id AND rsl.po_line_id = pll.po_line_id AND rsl.po_line_location_id = pll.line_location_id) AND gpa_ph.segment1 = p_old_gpa_number AND gpa_ph.po_header_id = gpa_pl.po_header_id AND gpa_ph.po_header_id = pl.from_header_id AND gpa_pl.po_line_id = pl.from_line_id AND EXISTS ( SELECT sum(quantity_received) from po_line_locations_all pll1 where pll1.po_header_id = pll.po_header_id and pll1.po_line_id = pll.po_line_id having sum(quantity_received) = 0);

SQL 2.

SELECT COUNT(*) INTO l_old_gpa_approved FROM po_headers_all ph, po_lines_all pl WHERE ph.po_header_id = pl.po_header_id AND ph.segment1 = p_old_gpa_number AND ph.authorization_status = 'APPROVED' AND ph.global_agreement_flag = 'Y';

SQL 3.

SELECT pl.po_header_id, pl.po_line_id, msi.segment1 INTO l_po_header_id, l_po_line_id, l_part_number FROM po_headers_all ph, po_lines_all pl, mtl_system_items_b msi WHERE ph.po_header_id = pl.po_header_id AND ph.segment1 = p_new_gpa_number AND pl.item_id = c_cur.item_id AND pl.item_id = msi.inventory_item_id AND msi.organization_id = 161;SQL 4.

UPDATE po_lines_all SET from_header_id = l_po_header_id, from_line_id = l_po_line_id, attribute13 = l_po_header_id||'/'||l_po_line_id WHERE po_header_id = c_cur.po_header_id AND po_line_id = c_cur.po_line_id;

SQL 5.

UPDATE po_line_locations_all SET --retroactive_date = SYSDATE, from_header_id = DECODE (from_header_id,NULL, NULL,l_po_header_id), from_line_id = DECODE (from_line_id, NULL, NULL, l_po_line_id), attribute13 = DECODE (from_header_id,NULL, NULL,l_po_header_id||'/'||l_po_line_id) WHERE po_header_id = c_cur.po_header_id AND po_line_id = c_cur.po_line_id


14 of 17


AND line_location_id = c_cur.line_location_id;

SQL 6. UPDATE po_lines_all SET retroactive_date = SYSDATE WHERE po_header_id = l_po_header_id AND po_line_id = l_po_line_id;

Default Data Sources

None

Validation Logic

Error Conditions:

None

Warning Conditions:

None

Incompatibility

None

Performance Considerations

None

Other Considerations

Restart Strategy

None

Crash Recovery

None


14 of 17


Integration Issues

Changes Required

None

Shared Components

INV

None

BOM

None

ENG

None

MPS/MRP

None

WIP

None

Alert conditions

None

Incompatibilities

None

Performance Issues

None


14 of 17


Database Design

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

Desired Table Changes

None

New/Updated Seed Data

None

Descriptive Flexfields

None

Value Sets

None

Grants/Synonyms

Owner Object Grantees

Archiving

None

Database Diagram

None

Tables, Indexes, Sequences

Nonr


14 of 17


Unit Test Plan

This Unit Test Script verifies that each application extension conforms to development standards by using this standards checklist. This test script has been updated to incorporate the standards at this implementation site. The tests outlined here were performed as the first step during unit testing of each application extension.

This checklist covers only common and easily tested problems, and makes no pretense of being a comprehensive check for compliance to standards. If it were, this checklist would be as big as the UI standards document and would take far too long to go through. All developers and testers have already read the entire user interface standards document (and any relevant areas of the coding standards document). They have checked things they see against those standards even when they do not appear on the checklist.

Further, after doing your own checking, the Tester will review every window for standards problems missed by the initial tester. The Tester also identified general user interface improvements possible beyond simple standards issues. The Tester reviewed for possible overall improvements (better ways to present the information or lay out the window, better methods of how to do the necessary actions, and so on). This was done allowing enough time to make any necessary changes, not just before release. Testers were available for preliminary checks and user interface ideas at any point during the design. Preliminary reviews were starting with the un-coded window layout, or even a sketch, before beginning programming code.

Regression testing was performed to check that bug fixes did not break previously tested code. The testing execution plan is an iteration of the following

Test -> Find Error -> Fix Bug Retest

Following test scenarios are being tested for this conversion object

The following Steps need to be performed to check the correctness of the Program.

Step 1: Making sure that the PR file was transferred on the right template.

Step 2: Making sure that the Interface program will hold all the cross validation rules regarding COA, Buyers, Currency, OU etc…

Step 3: Making sure that the PR to oracle interface program will run on as daily basic and send notification for the status of the PR. (Failed / successfully loaded).

Step 4: Making sure the segment Entity will populate the Company value.


14 of 17


Step 5: Changing the program to hold the new segments of the COA.

Step 6: Making sure to support the relationship between OU to the Company (Entity). Can be a relationship of one to many. i.e. – Turkey.

Step 7: Making sure to hold the original requisition number as created from VeriWeb.


14 of 17


Installation Requirements

Installation scripts must be prepared to perform the following actions in an automated way:

1. Create XXVFI_QTLY_POPRICE_UPDATE package in the APPS schema.

2. Register Concurrent Program.


14 of 17


Implementation Notes

This document describes exactly how CU-293 Quarterly PO Price Update was developed and implemented at VeriFone Corporation.

Design Summary

Functional Design

Shaik Basha

Functional Design Approval

Siva S

Technical Design

Madhukar Salunke

Design Review

Sivasankar B

Final Acceptance

Siva S

Coding Summary

Development was done on Release 11.5.10.2 of Oracle Applications. All coding follows the standards defined in the MD030 Design Standards document for VeriFone.

Program Files

The files required for this customization are as follows:

File Description

XXVFI_QTLY_POPRICE_UPDATE.sql Package XXVFIQTYPOPRICEUPDATE.ldt FNDLOAD file

Testing Summary

The customizations were tested in VeriFone's test environment before being moved to production. See the Link Test scripts for more information.


14 of 17


Installation

All modules are installed as if part of a separate product. A UNIX directory structure is created under $APPL_TOP as shown in the following example:

$APPL_TOP | $XXVFI_TOP | 1.0.0

___________________________|___________________________________| | | | | | | | |

forms bin src sql log out srw install sql

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.

forms/bin/src/sql/srw/install/sql/


14 of 17


Open and Closed Issues for this Deliverable

Open Issues

ID Issue Resolution Responsibility Target Date Impact Date

Closed Issues

ID Issue Resolution Responsibility Target Date Impact Date


14 of 17


md070 cu-293 quarterly po price updaterev1

Documents