Lot wise Inventory Query

select 'Inventory' col_Type,
        p.* , l.creation_date ,l.ORIGINATION_DATE,
 (select min(creation_Date) from mtl_lot_numbers where lot_number=l.lot_number
and organization_id in (select organization_id from org_organization_definitions where operating_unit=:P_ORG_ID)) rec_date,
NVL(round(to_date(:P_TO_DATE)-l.creation_DATE),0) Days_on_hand,
trunc(l.EXPIRATION_DATE) expiry_date,
trunc((l.EXPIRATION_DATE) -180 ) use_by_date
from
(select a.ORGANIZATION_ID,
c.ORGANIZATION_CODE,
c.ORGANIZATION_NAME,
a.INVENTORY_ITEM_ID,
sum(a.PRIMARY_QUANTITY) qty,
a.LOT_NUMBER,
--mmt.TRANSACTION_UOM,
b.PRIMARY_UOM_CODE TRANSACTION_UOM,
--a.ORIGINATION_DATE,
b.segment1 || '-' || b.segment2 ITEM,
b.description,
mmt.subinventory_code
from
MTL_TRANSACTION_LOT_VAL_V a,
MTL_system_items b, ORG_ORganization_definitions c,
mtl_material_transactions mmt
--where a.Organization_ID in (145, 146, 154, 147, 148, 149, 150, 151, 155, 153, 152, 182, 222, 242, 342, 362, 402, 422, 442, 462, 662, 831, 832) --DKCLLC
--where a.Organization_ID in (157, 158, 159, 160, 161, 162, 163, 164, 165, 202, 223, 322, 323) -- DKSCLLC
--where a.Organization_ID in (284, 291, 285, 289, 286, 290, 288, 302, 303, 382, 287, 542, 582, 602, 622, 642, 682, 702, 791) -- DKSG
--where a.Organization_ID in (506, 505, 507, 522, 562, 772) -- DKMY
where  TRUNC(a.Transaction_date) <= :P_TO_DATE
and a.inventory_item_id = b.Inventory_item_id
and a.ORGANIZATION_ID = b.ORGANIZATION_ID
and a.ORGANIZATION_ID = c.ORGANIZATION_ID
and a.transaction_id = mmt.transaction_id
   AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND c.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, c.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, c.ORGANIZATION_CODE )
   AND c.operating_unit = :P_ORG_ID    
group by
a.ORGANIZATION_ID,
c.ORGANIZATION_CODE,
c.ORGANIZATION_NAME,
a.INVENTORY_ITEM_ID,
a.LOT_NUMBER,
b.PRIMARY_UOM_CODE,
b.segment1,
mmt.subinventory_code,
b.segment2,
b.description
order by
a.ORGANIZATION_ID,
a.INVENTORY_ITEM_ID,
a.LOT_NUMBER
--a.ORIGINATION_DATE
asc) p,
mtl_lot_numbers l
where l.lot_number=p.lot_number
--l.inventory_item_id = p.Inventory_item_id
and p.ORGANIZATION_ID = l.ORGANIZATION_ID
--and l.LOT_NUMBER=p.LOT_NUMBER
and l.creation_date = (select min(creation_Date) from mtl_lot_numbers where lot_number=l.lot_number and organization_id=l.ORGANIZATION_ID)
and p.qty <>0
union all
select 'Inventory' col_Type , p.* ,
null creation_date,null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
null expire_date,
null use_by_date
from
(select
mmt.ORGANIZATION_ID,
c.ORGANIZATION_CODE,
c.ORGANIZATION_NAME,
mmt.INVENTORY_ITEM_ID,
sum(mmt.PRIMARY_QUANTITY) qty,
a.LOT_NUMBER,
--mmt.TRANSACTION_UOM,
b.PRIMARY_UOM_CODE TRANSACTION_UOM,
--a.ORIGINATION_DATE,
b.segment1 || '-' || b.segment2 ITEM,
b.description ,
null subinventory_code
from
MTL_TRANSACTION_LOT_VAL_V a,
MTL_system_items b, ORG_ORganization_definitions c,
mtl_material_transactions mmt
--where a.Organization_ID in (145, 146, 154, 147, 148, 149, 150, 151, 155, 153, 152, 182, 222, 242, 342, 362, 402, 422, 442, 462, 662, 831, 832) --DKCLLC
--where a.Organization_ID in (157, 158, 159, 160, 161, 162, 163, 164, 165, 202, 223, 322, 323) -- DKSCLLC
--where a.Organization_ID in (284, 291, 285, 289, 286, 290, 288, 302, 303, 382, 287, 542, 582, 602, 622, 642, 682, 702, 791) -- DKSG
--where a.Organization_ID in (506, 505, 507, 522, 562, 772) -- DKMY
where  TRUNC(mmt.Transaction_date) <= :P_TO_DATE
and mmt.inventory_item_id = b.Inventory_item_id
and mmt.ORGANIZATION_ID = b.ORGANIZATION_ID
and mmt.ORGANIZATION_ID = c.ORGANIZATION_ID
and mmt.transaction_id = a.transaction_id(+)
--and mmt.organization_id =702
and mmt.transaction_type_id not in ( 10008,76,100002,59,60,65)
   AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND c.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, c.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, c.ORGANIZATION_CODE )
   AND c.operating_unit in (283,482)-- :P_ORG_ID
AND c.operating_unit = :p_org_id
   group by
mmt.ORGANIZATION_ID,
c.ORGANIZATION_CODE,
c.ORGANIZATION_NAME,
mmt.INVENTORY_ITEM_ID,
a.LOT_NUMBER,
trunc (a.EXPIRATION_DATE),
b.PRIMARY_UOM_CODE,
b.segment1,
b.segment2,
b.description ) p
  where p.qty<>0
   and p.lot_number is null  
union all
Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
null subinventory_code,
null creation_date,
null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
trunc (lot.EXPIRATION_DATE) expire_date,
trunc((lot.EXPIRATION_DATE) -180 ) use_by_date
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where  ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id (+) =rsl.shipment_line_id
and rcv.shipment_header_id (+) =rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is  null
and trunc(ve.shipped_date) <= :P_TO_DATE
  AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID  
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
trunc (lot.EXPIRATION_DATE) ,
trunc((lot.EXPIRATION_DATE) -180 ),
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description
union all
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
null subinventory_code,
null creation_date,
null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
trunc (lot.EXPIRATION_DATE) expire_date,
trunc((lot.EXPIRATION_DATE) -180 ) use_by_date
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id=rsl.shipment_line_id
and rcv.shipment_header_id=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
and rcv.TRANSACTION_TYPE='DELIVER' and trunc(rcv.TRANSACTION_DATE) > :P_TO_DATE and trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
trunc (lot.EXPIRATION_DATE) ,
trunc((lot.EXPIRATION_DATE) -180 ),
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description
UNION  ALL
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
null subinventory_code,
null creation_date,
null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
trunc (lot.EXPIRATION_DATE) expire_date,
trunc((lot.EXPIRATION_DATE) -180 ) use_by_date
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS NULL
AND trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
trunc (lot.EXPIRATION_DATE) ,
trunc((lot.EXPIRATION_DATE) -180 ),
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description
union all
Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
null subinventory_code,
null creation_date,
null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
trunc (lot.EXPIRATION_DATE) expire_date,
trunc((lot.EXPIRATION_DATE) -180 ) use_by_date
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS not NULL
and rcv.shipment_line_id NOT IN (
                                    SELECT shipment_line_id
                                      FROM rcv_transactions rcv1, rcv_shipment_headers rsh1
                                     WHERE rcv1.shipment_header_id = rsh1.shipment_header_id
                                       AND rcv1.transaction_type IN ('ACCEPT', 'DELIVER','RETURN TO VENDOR'))
AND trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
trunc (lot.EXPIRATION_DATE),
trunc((lot.EXPIRATION_DATE) -180 ),
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description
order by 3,9,7

Match Approval Level and Invoice Match Option Summary

What is the difference between Match Approval Level and Invoice Match Option??

From Oracle User GuideSelect one of the following options for Match Approval Level:

• Two-Way: Purchase order and invoice quantities must match within tolerancebefore the corresponding invoice can be paid.

• Three-Way: Purchase order, receipt, and invoice quantities must match withintolerance before the corresponding invoice can be paid

.• Four-Way: Purchase order, receipt, accepted, and invoice quantities must matchwithin tolerance before the corresponding invoice can be paid.If you enter an item, a value for this field is defaulted. See: Purchase OrderDefaulting Rules, page 4-10.Select an Invoice Match Option:

• Purchase Order: Payables must match the invoice to the purchase order.

• Receipt: Payables must match the invoice to the receipt.Choose Receipt if you want to update exchange rate information on the receiptor if you want your accounting to use exchange rate information based on thereceipt date. (If you use Periodic Costing, you must match to the receipt toensure accurate cost accounting.)The Invoice Match Option defaults from the Supplier Sites window. You canchange the Invoice Match Option on the shipment until you receive against theshipment.

Note: The Invoice Match Option and the Match Approval Level areindependent options. You can perform whichever Invoice MatchOption you want regardless of the Match Approval Level.

1. What is 2-way, 3-way, 4-way matching? Can you give me an example of 2 way matching? 
2-way matching: 2-way matching verifies that Purchase order and invoice quantities must match within your tolerances as follows:
Quantity billed <= Quantity Ordered Invoice price <= Purchase order price (<= sign is used because of tolerances) Often used for services where no receiver is generated.
3-way matching: 3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined: Quantity billed <= Quantity received
4-way matching:
4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined: Quantity billed <= Quantity accepted. (Acceptance is done at the time of Inspecting goods).


A: Accrue On Receipt means that when a receipt is saved, accrual transactions are immediately recorded and sent to the general ledger interface. This is also known as "online" accruals. Accrue at Period End means that when a receipt is saved, the accrual transactions are not immediately recorded and sent to the general ledger; instead, the accounting entries are generated and sent at the end of the month by running the Receipt Accruals - Period-End Process.
All items with a destination type of either Inventory and Outside Processing are accrued on receipt. For items with a destination type of Expense, you have the option of accruing on receipt or at period end.

What is the difference between 'Accrue On Receipt' and 'Accrue at Period End'

What is 'Matching Level' in Purchasing?

When you are configuring Oracle Purchasing, there is a configuration called 'Invoice Matching'. What is it?

Matching Level determines the various documents that you check and match before you make the payment to the supplier. Just to refresh your 'Procure To Pay (P2P)' flow, the steps involved in the P2P flow are shown in the diagram below. 

As you can see above, the last step before making payment is to match the documents. In this step, the finance manager verifies various documents and ensures that everything is ok before making the payment to the supplier.

The process of checking the documents is called 'Matching'.

So what are the documents to be matched?

1. Purchase Order

2. Receipt

3. Inspection Certificate

4. Supplier Invoice.

The various matching options are: 

1. 2-Way Matching: In this matching option, the payables accountant matches that the Item, Quantity, Terms and Price match between Purchase Order and the Supplier Invoice. Since the accountant is not checking if material is received, this kind of matching option applies in the following situations.

1. There is a lot of trust between the Company and the Supplier

2. The value of the items received is very low

3. The order is a Service order and Items are not available for these PO.

2. 3-Way Matching: In this matching option the following documents are matched. PO, Receipt and Invoice

3. 4-Way Matching: In this matching option all the four documents vis. PO, GRN, Inspection Certificate and Invoice are matched.

Many organizations get into the mistake of using 4 way matching where they need only 3-Way matching. 'We inspect the materials before we receive the items into inventory', they say. The issue in configuring matching option in ERP is not if you do a physical inspection (Everyone does a physical before receiving the material into inventory) but whether the payables accountant has to match Inspection Certificate before he makes payments to the supplier. Looking from that perspective, most of the organizations do not use 4-Way matching, but they have configured the same in ERP.

What is the issue of configuring 4-Way Matching? 

If you use 4-Way matching, you have to have an additional step of 'Inspection' to be completed in the system before you complete the receipt process. This additional step can add up to a lot of wasted effort in the Organization.

Normally Matching Option goes along with another option in Oracle known as 'Routing Method'. They go hand in hand. More about 'Routing Method' later.

IR-ISP Set Up Basics

Internal Sales Order Setup Flow

Setup Instructions For Internal Sales Orders Across Operating Units [ID 403786.1]
________________________________________
Modified 03-NOV-2011 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
References
________________________________________
Applies to:
Oracle Purchasing - Version: 11.5.10.2 to 12.0.6 - Release: 11.5 to 12
Oracle Order Management - Version: 11.5.6 to 12.0.6 [Release: 11.5 to 12.0]
Information in this document applies to any platform.
Goal
What are the mandatory setups for creating internal sales order between operating units?
Solution
Setup for creating Internal Sales orders between Operating units

There should be two Operating Unit one is Destination Organization and the other is Source Organization

1) choose responsibility: Human Resources
People> Enter and Maintain
Enter Last Name, First Name, Type
click Assignment:
(both Job and Position are Key flex fields)

2) choose responsibility: System Administrator
Security > User > Define

3) choose responsibility: Purchasing, Destination Organization
Setup > Personnel > Buyer > define
It is required for creating requisition and purchase order in PO.

4) Setup > Purchasing > Document Type
choose internal Requisition
make sure 'Owner Can Approve' checked.

5) Setup > Approvals > Approval Groups
Name = Executive
make sure Document Total and Account Range are defined.

6) Setup > Approvals > Approval Assignment
choose Position (from step 1)
define Document Type = Approve Internal Requisitions
and Approval Group = Executive

7) Setup > Organizations > location
define new location (destination location) for Internal requisition use.
Name
Inventory organization = destination organization
Ship to location = the same name as location name
check Ship-to site and Internal Site and Receiving Site

8) Items > Master Items
a) define Master Item
make sure Item
Purchased = Yes
Purchasable = Yes
Stock able = Yes
Internal Order Enabled = Yes
Inventory item = Yes.
List price = Enter the list price

b) click Special > Organization Assignment
Make sure Item assign to destination organization, source organization/warehouse

c) Setups > Inventory > Costs
Set inventory cost for that item

d) Setups > Inventory > Cost Update
Run the "update standard cost" concurrent program

9) choose resp:Order Management, Source organization
Setup > Document > Define
Name = Internal
Application = Oracle Order Management
Type = automatic
Initial Value =

10) Pricing > Lists > Price Lists
Price List = Corporation

11) Setup > Transaction Type > Define
Order Type =
Transaction Type Code = ORDER
Order Category = Order
Order Work flow = Order Flow - Generic
Price List =
click Assign Line Flows…
Order Type =
Line type =
Item Type = Standard Item
Line Flow - Generic

12) Setup > Document > Assign
Application = Oracle Order Management
Category = Internal
Sets of Books =
Method =
Sequence =
Need to define customer location associations for both source and destination operating units

13) Choose responsibility: Order Management, Destination organization > Customer > Standard
make sure the internal customer for each destination organization connected with destination
location.
query on Customer =
choose Address .. - destination operating unit customer site address..
choose Ship-to...
Internal zone
location :
Organization :destination inventory organization
(OE requires both a ship-to and an invoice-to for each order, even though internal order lines
are not invoiced., you can make the ship-to address both a ship-to and bill-to business purpose
and set the Primary flag to Yes.)

14) Choose responsibility: Order Management, Source organization > Customer > Standard
make sure the internal customer for each source organization connected with destination
location.
query on Customer =
choose Address .. - source operating unit customer site address..
choose Ship-to...
Internal zone
location :
Organization : source inventory organization
(OE requires both a ship-to and an invoice-to for each order, even though internal order lines
are not invoiced., you can make the ship-to address both a ship-to and bill-to business purpose
and set the Primary flag to Yes.)

Setups - OM Customer Association - click details provide the internal organization name

15) choose responsibility: Inventory, Destination organization > On-Hand,Available > On-hand quantity
choose Item = and Sub inventory =
make sure there is quantity available in source organization

16) Setup > Organizations > Shipping Network
choose ... add the following:
From = source organization, TO = Destination organization and From = Destination organization,
TO = source organization
Tran fer Type = In transit
FOB = Receipt,
Receiving Routing = Standard
Internal Order Required = 'Y'.

17) choose responsibility: Purchasing, Source organization
Setup > Organizations > Purchasing Options
choose Internal Requisition zone
define Order type = Internal1 and Order Source = Internal

18) choose responsibility: Purchasing, Destination organization
Requisitions > Requisitions
create an Internal Requisition
Item#=
Qty=
Destination Type =
Organization =
Location =
Source =
Organization =
Need By date = <current date>
created a requisition #
approve the requisition.
go to Requisition Summary and query out this requisition, make sure it has been approved.

19) choose responsibility: Purchasing, Destination organization
Report > run > run Create Internal Sales Orders

20) choose responsibility: Order Entry, Source organization
Orders, Returns > Import Orders > Order Import Request submit the report.
References
NOTE:744481.1 - Internal Sales Order Cycle In Order Management

AIM (Application Implementation Methodology) Brief summary

Oracle Documents which are used in PROJECTS - A.I.M Implimentations

                     A.I.M.Introduction

Oracle A.I.M. Methodology encompasses a project management methodology with documentation templates that support the life cycle of an implementation. The life cycle methodology and documentation templates allows A.I.M. to be a very useful tool for managing implementation projects successfully.

The A.I.M. methodology can actually be used for any type IT software implementations however the value of A.I.M is within the documentation template. The software includes the documentation templates, manuals and an html website to manage these templates.

This is a depiction of the A.I.M. methodology life cycle:

                                               A.I.M. Document Deliverables

Business Process Architecture (BP)

BP.010 Define Business and Process Strategy

BP.020 Catalog and Analyze Potential Changes

BP.030 Determine Data Gathering Requirements

BP.040 Develop Current Process Model

BP.050 Review Leading Practices

BP.060 Develop High-Level Process Vision

BP.070 Develop High-Level Process Design

BP.080 Develop Future Process Model

BP.090 Document Business Procedure

Business Requirements Definition (RD)

RD.010 Identify Current Financial and Operating Structure

RD.020 Conduct Current Business Baseline

RD.030 Establish Process and Mapping Summary

RD.040 Gather Business Volumes and Metrics

RD.050 Gather Business Requirements

RD.060 Determine Audit and Control Requirements

RD.070 Identify Business Availability Requirements

RD.080 Identify Reporting and Information Access Requirements

Business Requirements Mapping

BR.010 Analyze High-Level Gaps

BR.020 Prepare mapping environment

BR.030 Map Business requirements

BR.040 Map Business Data

BR.050 Conduct Integration Fit Analysis

BR.060 Create Information Model

BR.070 Create Reporting Fit Analysis

BR.080 Test Business Solutions

BR.090 Confirm Integrated Business Solutions

BR.100 Define Applications Setup

BR.110 Define security Profiles

Application and Technical Architecture (TA)

TA.010 Define Architecture Requirements and Strategy

TA.020 Identify Current Technical Architecture

TA.030 Develop Preliminary Conceptual Architecture

TA.040 Define Application Architecture

TA.050 Define System Availability Strategy

TA.060 Define Reporting and Information Access Strategy

TA.070 Revise Conceptual Architecture

TA.080 Define Application Security Architecture

TA.090 Define Application and Database Server Architecture

TA.100 Define and Propose Architecture Subsystems

TA.110 Define System Capacity Plan

TA.120 Define Platform and Network Architecture

TA.130 Define Application Deployment Plan

TA.140 Assess Performance Risks

TA.150 Define System Management Procedures

Module Design and Build (MD)

MD.010 Define Application Extension Strategy

MD.020 Define and estimate application extensions

MD.030 Define design standards

MD.040 Define Build Standards

MD.050 Create Application extensions functional design

MD.060 Design Database extensions

MD.070 Create Application extensions technical design

MD.080 Review functional and Technical designs

MD.090 Prepare Development environment

MD.100 Create Database extensions

MD.110 Create Application extension modules

MD.120 Create Installation routines

Data Conversion (CV)

CV.010 Define data conversion requirements and strategy

CV.020 Define Conversion standards

CV.030 Prepare conversion environment

CV.040 Perform conversion data mapping

CV.050 Define manual conversion procedures

CV.060 Design conversion programs

CV.070 Prepare conversion test plans

CV.080 Develop conversion programs

CV.090 Perform conversion unit tests

CV.100 Perform conversion business objects

CV.110 Perform conversion validation tests

CV.120 Install conversion programs

CV.130 Convert and verify data

Documentation (DO)

DO.010 Define documentation requirements and strategy

DO.020 Define Documentation standards and procedures

DO.030 Prepare glossary

DO.040 Prepare documentation environment

DO.050 Produce documentation prototypes and templates

DO.060 Publish user reference manual

DO.070 Publish user guide

DO.080 Publish technical reference manual

DO.090 Publish system management guide

Business System Testing (TE)

TE.010 Define testing requirements and strategy

TE.020 Develop unit test script

TE.030 Develop link test script

TE.040 Develop system test script

TE.050 Develop systems integration test script

TE.060 Prepare testing environments

TE.070 Perform unit test

TE.080 Perform link test

TE.090 perform installation test

TE.100 Prepare key users for testing

TE.110 Perform system test

TE.120 Perform systems integration test

TE.130 Perform Acceptance test

PERFORMACE TESTING(PT)

PT.010 – Define Performance Testing Strategy

PT.020 – Identify Performance Test Scenarios

PT.030 – Identify Performance Test Transaction

PT.040 – Create Performance Test Scripts

PT.050 – Design Performance Test Transaction Programs

PT.060 – Design Performance Test Data

PT.070 – Design Test Database Load Programs

PT.080 – Create Performance Test TransactionPrograms

PT.090 – Create Test Database Load Programs

PT.100 – Construct Performance Test Database

PT.110 – Prepare Performance Test Environment

PT.120 – Execute Performance Test

Adoption and Learning (AP)

AP.010 – Define Executive Project Strategy

AP.020 – Conduct Initial Project Team Orientation

AP.030 – Develop Project Team Learning Plan

AP.040 – Prepare Project Team Learning Environment

AP.050 – Conduct Project Team Learning Events

AP.060 – Develop Business Unit Managers’Readiness Plan

AP.070 – Develop Project Readiness Roadmap

AP.080 – Develop and Execute CommunicationCampaign

AP.090 – Develop Managers’ Readiness Plan

AP.100 – Identify Business Process Impact onOrganization

AP.110 – Align Human Performance SupportSystems

AP.120 – Align Information Technology Groups

AP.130 – Conduct User Learning Needs Analysis

AP.140 – Develop User Learning Plan

AP.150 – Develop User Learningware

AP.160 – Prepare User Learning Environment

AP.170 – Conduct User Learning Events

AP.180 – Conduct Effectiveness Assessment

Production Migration (PM)

PM.010 – Define Transition Strategy

PM.020 – Design Production Support Infrastructure

PM.030 – Develop Transition and Contingency Plan

PM.040 – Prepare Production Environment

PM.050 – Set Up Applications

PM.060 – Implement Production Support Infrastructure

PM.070 – Verify Production Readiness

PM.080 – Begin Production

PM.090 – Measure System Performance

PM.100 – Maintain System

PM.110 – Refine Production System

PM.120 – Decommission Former Systems

PM.130 – Propose Future Business Direction

PM.140 – Propose Future Technical Direction

                                               AIM Methodology Summary

Application Implementation Method is a proven approach for all the activities required to implement oracle applications.

The scope of the AIM is focused on an enterprise as a whole.

There are eleven processes of implementation.

1. Business Process Architecture [BP]

This phase outlines:

• Existing business practices
• Catalog change practices
• Leading practices
• Future practices 

2. Business Requirement Definition[RD]

This phase explains about the initial baseline questionnaire and gathering of requirements.

3. Business Requirement Mapping[BR]

In this phase the requirements of business are matched with the standard functionality of the oracle applications.

4. Application and Technical Architecture [TA]

This outlines the infrastructure requirements to implement oracle applications.

5. Build and Module Design [MD]

This phase emphasizes the development of new functionality (customization) required by the client. It mainly details how to design the required forms, database and reports.

6. Data Conversion [CV]

Data Conversion is the process of converting or transferring the data from legacy system to oracle applications.

Ex. Transferring customer records from the legacy to the Customer Master.

7. Documentation [DO]

Documentation prepared per module that includes user guides and implementation manuals.

8. Business System Testing [TE]

A process of validating the setup’s and functionality by QA(functional consultant) to certify status.

9. Performance Testing [PT]

Performance testing is the evaluation of transactions saving time, transaction retrieval times, workflow background process, database performance, etc…

10; Adoption and Learning [AP]

This phase explains the removal of the legacy system and oracle application roll out enterprise wide.

11. Production Migration [PM]

The process of “decommissioning” of legacy system and the usage(adoption) of oracle application system.

Tracking for Purchase Requisitions Till GRN

SELECT po_req.company,
po_req.organization_code,
po_req.LINE_NUM,
po_req.PO_RELEASE_ID,
po_req.po_line_id,
po_req.organization_name,
       po_req.requisition_number,
       po_req.ER_Number,
       po_req.segment1,
       po_req.requisition_date,
       po_req.requisition_type,
       po_req.requester,
       po_req.person_id,
       po_req.org_id,
       po_req.requisition_header_id,
       po_req.inventory_item_id,
       po_req.authorization_status,
       po_req.approved_date,
       po_req.item,
       po_req.item_description,
       po_req.quantity_required,
       po_req.quantity_delivered,
       po_req.unit_price,
       po_req.buyer_name,
       po_req.buyer_id,
       po_req.PO_UNIT_PRICE,--------------------
       po_req.QUOTE_PRICE,
       po_req.PO_QUANTITY,--------------------
       po_req.PO_ITEM_TOTAL_VALUE,-----------------------
       po_req.PO_APPROVAL_DATE,-----------------  
       po_req.status,
       po_req.req_need_by_date,
       po_req.full_lead_time,
       po_req.vendor_name,
       po_req.order_number,
       po_req.order_date,
       po_req.order_need_by_date,
       po_req.receipt_num,
       po_req.shipment_header_id,
       po_req.receipt_date,
       po_req.days_from_pr_to_po,
       po_req.days_from_pr_to_grn
  FROM (( SELECT   hou.NAME company, ood.organization_code,pl.LINE_NUM,pll.PO_RELEASE_ID,pl.po_line_id,
                  ood.organization_name, prh.segment1 requisition_number,
                  prh.attribute10 ER_Number,
                  nvl(pap.segment1,' ') segment1,
                  ppf.full_name requester, ppf.person_id, prh.org_id,
                  prh.requisition_header_id, msik.inventory_item_id,
                  TRUNC (prh.creation_date) requisition_date,
                  podt.type_name requisition_type, prh.authorization_status,
                  TRUNC (prh.approved_date) approved_date,
                  msik.concatenated_segments item,
                  prl.item_description item_description,
                  SUM (prl.quantity) quantity_required,
                  SUM (prl.quantity_delivered) quantity_delivered,
                  NVL (prl.unit_price, 0) unit_price,
                  ppf1.full_name buyer_name,
                  nvl(ppf1.person_id,1) buyer_id,
                  NVL(PL.UNIT_PRICE,0) PO_UNIT_PRICE,--------------------
                  NVL(PL.ATTRIBUTE2,0) QUOTE_PRICE,
                  NVL (PL.QUANTITY,0)  PO_QUANTITY,--------------------
                  NVL(pl.unit_price*PL.QUANTITY,0) PO_ITEM_TOTAL_VALUE,-----------------------
                  TRUNC(PH.APPROVED_DATE) PO_APPROVAL_DATE,------------------
                  PH.CLOSED_CODE STATUS,----------------
                  TRUNC (prl.need_by_date) req_need_by_date,
                  msik.full_lead_time, pve.vendor_name,
                  ph.segment1 order_number,
                  TRUNC (ph.creation_date) order_date,
                  TRUNC (pll.need_by_date) order_need_by_date,
                  NVL (rcv.receipt_num, inl.receipt_num) receipt_num,
                  NVL (rcv.shipment_header_id, inl.shipment_header_id) shipment_header_id,
                  NVL (rcv.receipt_date, inl.receipt_date) receipt_date,
                  TRUNC (  (TRUNC (ph.creation_date))
                         - (TRUNC (prh.creation_date))
                        ) days_from_pr_to_po,
                  TRUNC (  (NVL (rcv.receipt_date, inl.receipt_date))
                         - (TRUNC (prh.creation_date))
                        ) days_from_pr_to_grn
             FROM po_requisition_headers_all prh,
                  po_document_types_all_tl podt,
                  po_requisition_lines_all prl,
                  po_req_distributions_all prd,
                  pa_projects_all pap,
                  per_all_people_f ppf,
                  per_all_people_f ppf1,
                  mtl_system_items_kfv msik,
                  hr_operating_units hou,
                  po_line_locations_all pll,
                  po_lines_all pl,
                  po_headers_all ph,
                  po_vendors pve,
                  org_organization_definitions ood,
                  (SELECT   rsl.po_line_location_id, rsl.shipment_line_id,
                            rsh.receipt_num,rsh.shipment_header_id,
                            NVL2 (rsh.receipt_num,
                                  TRUNC (rsh.creation_date),
                                  NULL
                                 ) receipt_date
                       FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl
                      WHERE rsh.shipment_header_id = rsl.shipment_header_id
                        AND rsl.lcm_shipment_line_id IS NULL
                   GROUP BY rsl.po_line_location_id,
                            rsl.shipment_line_id,
                            rsh.receipt_num,
                            rsh.shipment_header_id,
                            NVL2 (rsh.receipt_num,
                                  TRUNC (rsh.creation_date),
                                  NULL
                                 )) rcv,
                  (SELECT   isl.ship_line_source_id,
                            isl.ship_to_organization_id, rsl.shipment_line_id,
                            rsh.receipt_num,rsh.shipment_header_id,
                            NVL2 (rsh.receipt_num,
                                  TRUNC (rsh.creation_date),
                                  NULL
                                 ) receipt_date
                       FROM inl_ship_headers_all ish,
                            inl_ship_lines_all isl,
                            rcv_shipment_headers rsh,
                            rcv_shipment_lines rsl
                      WHERE ish.ship_header_id = isl.ship_header_id
                        AND isl.ship_line_id =
                               (SELECT MAX (sl1.ship_line_id)
                                  FROM inl_ship_lines_all sl1
                                 WHERE sl1.ship_header_id = isl.ship_header_id
                                   AND sl1.ship_line_group_id =
                                                        isl.ship_line_group_id
                                   AND sl1.ship_line_num = isl.ship_line_num)
                        AND NVL (isl.parent_ship_line_id, isl.ship_line_id) =
                                                                           rsl.lcm_shipment_line_id(+)
                        AND rsl.shipment_header_id = rsh.shipment_header_id(+)
                   GROUP BY isl.ship_line_source_id,
                            isl.ship_to_organization_id,
                            rsl.shipment_line_id,
                            rsh.shipment_header_id,
                            rsh.receipt_num,
                            NVL2 (rsh.receipt_num,
                                  TRUNC (rsh.creation_date),
                                  NULL
                                 )) inl
            WHERE prh.requisition_header_id = prl.requisition_header_id
              AND podt.document_type_code = 'REQUISITION'
              AND podt.document_subtype = prh.type_lookup_code
              AND podt.org_id = prh.org_id
              and prl.requisition_line_id=prd.requisition_line_id
              and prd.project_id=pap.project_id(+)
              AND pve.vendor_id = ph.vendor_id
              AND prl.to_person_id = ppf.person_id
              AND ph.agent_id=ppf1.person_id
              AND prl.item_id = msik.inventory_item_id(+)
              AND prh.org_id = hou.organization_id
              AND prl.destination_organization_id = msik.organization_id(+)
              --AND prl.destination_organization_id = msik.organization_id
              AND prl.line_location_id = pll.line_location_id
              AND pll.po_line_id = pl.po_line_id
              AND pl.po_header_id = ph.po_header_id
              AND prl.destination_organization_id = ood.organization_id
              AND pll.line_location_id = rcv.po_line_location_id(+)
              AND pll.line_location_id = inl.ship_line_source_id(+)
              AND podt.language='US'
              AND pll.ship_to_organization_id = inl.ship_to_organization_id(+)
         --AND prh.segment1 = '21400049'
         GROUP BY hou.NAME,
                  ood.organization_code,
                  pl.LINE_NUM,pll.PO_RELEASE_ID,pl.po_line_id,
                  ood.organization_name,
                  prh.segment1,
                  prh.attribute10 ,
                 -- pap.segment1,
                 nvl(pap.segment1,' '),
                  ppf.full_name,
                  ppf.person_id,
                  prh.org_id,
                  prh.requisition_header_id,
                  ppf1.full_name,
                  nvl(ppf1.person_id,1),
                 -- ppf1.person_id,
                  msik.inventory_item_id,
                  TRUNC (prh.creation_date),
                  TRUNC(PH.APPROVED_DATE),
                  podt.type_name,
                  prh.authorization_status,
                  TRUNC (prh.approved_date),
                  msik.concatenated_segments,
                  prl.item_description,
                  TRUNC (prl.need_by_date),
                  msik.full_lead_time,
                  ph.segment1,
                  pve.vendor_name,
                  PH.CLOSED_CODE,
                  NVL (prl.unit_price, 0),
                  NVL(PL.ATTRIBUTE2,0),
                  TRUNC(PH.APPROVED_DATE),
                  NVL(PL.UNIT_PRICE,0),
                  TRUNC (ph.creation_date),
                  NVL (PL.QUANTITY,0),
                  NVL(pl.unit_price*PL.QUANTITY,0),
                  TRUNC (pll.need_by_date),
                  NVL (rcv.receipt_num, inl.receipt_num),
                  NVL (rcv.shipment_header_id, inl.shipment_header_id) ,
                  NVL (rcv.receipt_date, inl.receipt_date)
                  )
        UNION
        (SELECT   hou.NAME company, ood.organization_code,NULL LINE_NUM,NULL PO_RELEASE_ID,NULL po_line_id,
                  ood.organization_name, prh.segment1 requisition_number,
                  prh.attribute10 ER_Number,
                  nvl(pap.segment1,' ') segment1,--pap.segment1,
                  ppf.full_name requester, ppf.person_id, prh.org_id,
                  prh.requisition_header_id, msik.inventory_item_id,
                  TRUNC (prh.creation_date) requisition_date,
                  podt.type_name requisition_type, prh.authorization_status,
                  TRUNC (prh.approved_date) approved_date,
                  msik.concatenated_segments item,
                  prl.item_description item_description,
                  SUM (prl.quantity) quantity_required,
                  SUM (prl.quantity_delivered) quantity_delivered,
                  NVL (prl.unit_price, 0) unit_price,
                  null buyer_name,
                  NULL BUYER_ID,
                 -- null person_id,
                  NULL PO_UNIT_PRICE,--------------------
                  NULL QUOTE_PRICE,
                  NULL PO_QUANTITY,--------------------
                  NULL PO_ITEM_TOTAL_VALUE,-----------------------
                  NULL PO_APPROVAL_DATE,-----------------
                  NULL STATUS,
                  TRUNC (prl.need_by_date) req_need_by_date,
                  msik.full_lead_time, NULL vendor_name,
                  TO_CHAR (ooh.order_number) order_number,
                  TRUNC (ooh.ordered_date) order_date,
                  TRUNC (ool.request_date) order_need_by_date,
                  wnd.NAME receipt_num,
                  null shipment_header_id,
                  wnd.ultimate_dropoff_date receipt_date,
                  TRUNC (  (TRUNC (ooh.ordered_date))
                         - (TRUNC (prh.creation_date))
                        ) days_from_pr_to_po,
                  TRUNC
                      (  (TRUNC (wnd.ultimate_dropoff_date))
                       - (TRUNC (prh.creation_date))
                      ) days_from_pr_to_grn
             FROM po_requisition_headers_all prh,
                  po_requisition_lines_all prl,
                  po_req_distributions_all prd,
                  pa_projects_all pap,
                  per_all_people_f ppf,
                  po_document_types_all_tl podt,
                  mtl_system_items_kfv msik,
                  hr_operating_units hou,
                  org_organization_definitions ood,
                  oe_order_headers_all ooh,
                  oe_order_lines_all ool,
                  wsh_delivery_details wdd,
                  --   po_vendors pve,
                  wsh_delivery_assignments wda,
                  wsh_new_deliveries wnd
            WHERE prh.requisition_header_id = prl.requisition_header_id
              and prl.requisition_line_id=prd.requisition_line_id
              and prd.project_id=pap.project_id(+)
              AND podt.document_type_code = 'REQUISITION'
              AND podt.document_subtype = prh.type_lookup_code
              AND podt.org_id = prh.org_id
              --  and ph.vendor_id=pve.vendor_id
              AND prl.to_person_id = ppf.person_id
              AND prl.item_id = msik.inventory_item_id(+)
              AND prl.destination_organization_id = msik.organization_id(+)
              AND prl.destination_organization_id = ood.organization_id
              AND prh.org_id = hou.organization_id
              AND prl.requisition_header_id = ooh.source_document_id
              AND prh.segment1 = ooh.orig_sys_document_ref
              AND ooh.header_id = ool.header_id
              AND ool.orig_sys_line_ref = TO_CHAR (prl.line_num)
              AND ool.header_id = wdd.source_header_id(+)
              AND ool.line_id = wdd.source_line_id(+)
              AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
             AND podt.language='US'
              AND TO_CHAR (wda.delivery_id) = TO_CHAR (wnd.delivery_id(+))
         --AND prh.segment1 = '21400004'
         GROUP BY hou.NAME,
                  ood.organization_code,
                  ood.organization_name,
                  prh.segment1,
                  prh.attribute10,
                  nvl(pap.segment1,' '),--pap.segment1,
                  --    PVE.vendor_name,
                  ppf.full_name,
                  ppf.person_id,
                  prh.org_id,
                  prh.requisition_header_id,
                  msik.inventory_item_id,
                  TRUNC (prh.creation_date),
                  podt.type_name,
                  NVL (prl.unit_price, 0),
                  prh.authorization_status,
                  TRUNC (prh.approved_date),
                  msik.concatenated_segments,
                  prl.item_description,
                  TRUNC (prl.need_by_date),
                  msik.full_lead_time,
                  ooh.order_number,
                  TRUNC (ooh.ordered_date),
                  TRUNC (ool.request_date),
                  wnd.NAME,
                  wnd.ultimate_dropoff_date)
        UNION
        (SELECT   hou.NAME company, ood.organization_code, NULL LINE_NUM,NULL PO_RELEASE_ID,NULL po_line_id,
                  ood.organization_name, prh.segment1 requisition_number,
                  prh.attribute10 ER_Number,
                  nvl(pap.segment1,' ') segment1,--pap.segment1,
                  ppf.full_name requester, ppf.person_id, prh.org_id,
                  prh.requisition_header_id, msik.inventory_item_id,
                  TRUNC (prh.creation_date) requisition_date,
                  podt.type_name requisition_type, prh.authorization_status,
                  TRUNC (prh.approved_date) approved_date,
                  msik.concatenated_segments item,
                  prl.item_description item_description,
                  SUM (prl.quantity) quantity_required,
                  SUM (prl.quantity_delivered) quantity_delivered,
                  NVL (prl.unit_price, 0) unit_price,
                  null buyer_name,
                  NULL BUYER_ID,
                --  null person_id,
                  NULL PO_UNIT_PRICE,--------------------
                  NULL QUOTE_PRICE,
                  NULL PO_QUANTITY,--------------------
                  NULL PO_ITEM_TOTAL_VALUE,-----------------------
                  NULL PO_APPROVAL_DATE,-----------------
                  NULL STATUS,
                  TRUNC (prl.need_by_date) req_need_by_date,
                  msik.full_lead_time, NULL vendor_name, NULL order_number,
                  NULL order_date, NULL order_need_by_date, NULL receipt_num,
                  null shipment_header_id,
                  NULL receipt_date, NULL days_from_pr_to_po,
                  NULL days_from_pr_to_grn
             FROM po_requisition_headers_all prh,
                  po_document_types_all_tl podt,
                  po_requisition_lines_all prl,
                  po_req_distributions_all prd,
                  pa_projects_all pap,
                  per_all_people_f ppf,
                  mtl_system_items_kfv msik,
                  hr_operating_units hou,
                  org_organization_definitions ood
            WHERE prh.requisition_header_id = prl.requisition_header_id
              and prl.requisition_line_id=prd.requisition_line_id
              and prd.project_id=pap.project_id(+)
              AND podt.document_type_code = 'REQUISITION'
              AND podt.document_subtype = prh.type_lookup_code
              AND podt.org_id = prh.org_id
              AND prl.to_person_id = ppf.person_id
              AND prl.item_id = msik.inventory_item_id(+)
              AND prl.destination_organization_id = msik.organization_id(+)
              AND prh.org_id = hou.organization_id
             AND podt.language='US'
              AND prl.destination_organization_id = ood.organization_id
              AND prh.authorization_status = 'APPROVED'
              --AND prh.segment1 = '12400031'
              AND NOT EXISTS (
                             SELECT 1
                               FROM po_line_locations_all pll
                              WHERE pll.line_location_id =
                                                          prl.line_location_id)
              AND NOT EXISTS (SELECT 1
                                FROM oe_order_headers_all ooh
                               WHERE ooh.orig_sys_document_ref = prh.segment1)
         GROUP BY hou.NAME,
                  ood.organization_code,
                  ood.organization_name,
                  prh.attribute10 ,
                  prh.segment1,
                 nvl(pap.segment1,' '),--pap.segment1,
                  ppf.full_name,
                  ppf.person_id,
                  prh.org_id,
                  NVL (prl.unit_price, 0),
                  prh.requisition_header_id,
                  msik.inventory_item_id,
                  TRUNC (prh.creation_date),
                  podt.type_name,
                  prh.authorization_status,
                  TRUNC (prh.approved_date),
                  msik.concatenated_segments,
                  prl.item_description,
                  TRUNC (prl.need_by_date),
                  msik.full_lead_time)) po_req
 WHERE po_req.org_id = :p_org_id
   AND po_req.requisition_date BETWEEN NVL(:P_FROM_DATE,po_req.requisition_date) AND nvl(:P_TO_DATE,po_req.requisition_date)
   --and po_req.segment1 = nvl(:p_project_num,po_req.segment1)
  -- AND po_req.item between  NVL (:p_from_item_id, po_req.item) and  NVL (:p_to_item_id, po_req.item)
   AND po_req.organization_code = NVL (:p_organization_code, po_req.organization_code)
  -- AND po_req.requisition_type= NVL (:p_req_TYPE,  po_req.requisition_type)
 --  AND po_req.requisition_header_id = NVL (:p_header_id, po_req.requisition_header_id)
   AND po_req.requisition_number between  NVL (:p_from_req_no, po_req.requisition_number) and NVL (:p_to_req_no, po_req.requisition_number)
--   AND po_req.authorization_status =   NVL (:p_authorization_status, po_req.authorization_status)
   AND po_req.person_id = NVL (:p_person_id, po_req.person_id)
  -- or po_req.buyer_id = NVL (:p_buyer_id, po_req.buyer_id))
   and po_req.segment1 = nvl(nvl(:p_project_num,po_req.segment1),' ')
--   and po_req.buyer_id = nvl(NVL (:p_buyer_id, po_req.buyer_id),1)
   --AND po_req.item = '560001-000028'
  -- and
order by 7,4