Wednesday, November 29, 2017

Oracle Enterprise Asset Management (EAM) module - An Introduction

What are aims, objects and benefits of Oracle Enterprise Asset Management (EAM) module ?

1) An asset is an entity for which users can report problems. Assets can be cooling towers, cranes, buses, buildings, conveyors, or anything that needs work.

2) EAM creates and implements maintenance procedures for both assets and rebuild-able Inventory items and takes care of comprehensive and routine maintenance requirements of asset intensive organizations. It effectively maintains assets such as vehicles, cranes and HVAC systems and rotable Inventory items such as motors and engines.

3) EAM enables users to optimally plan and schedule maintenance operations /activities with minimal disruptions to an organization’s operations and productions. It measures performance and optimizes maintenance operations. It tracks the performance of the assets and rebuild able inventory items by using meters, quality plans and conditioning systems.

4) By effectively monitoring operational data such as maintenance history, performance trends, meter readings, and operating conditions, organizations can proactively manage available resources such as inventory, equipment and skilled personnel to match them to asset maintenance demand.

5) Optimization of asset utilization, scheduling and resources improves resource efficiency and enhances maintenance quality, maximizes resource availability including equipment and labor. Maintenance costs and work history are tracked at asset level.

6) Oracle EAM enables organizations to create and implement optimal and effective asset management strategies that can increase revenue and decrease costs. Both Preventive and Predictive Maintenance strategies are supported by EAM. It allows you to create alternate maintenance strategies for seasonal or production capacity changes. It supports sophisticated, condition-based maintenance strategies for property, plants and public infrastructure, improves operation performance, and with preventive and scheduled maintenance, increases safety, optimizes Scheduling and resource efficiency.

7) It eliminates the need for spreadsheets and disparate data repositories, by enabling companies to manage reactive, planned, preventive maintenance, and adopt a centralized, proactive strategy for managing asset maintenance across an enterprise.

8) Oracle EAM's single-instance design ensures best practices and quality compliance globally, eliminates excess and obsolete spare parts inventories, promotes environmental, health and safety policies, smooths coordination of production and maintenance schedules and improves both the responsiveness and accuracy of contracted maintenance.

9) It bridges the gap between ERP/MRP solutions and Asset Maintenance for industries that rely heavily on Asset Maintenance Systems.

10) It is a linchpin of the integrated workplace incorporating visual navigation for space planning and allocation, multi-channel service request processing, building systems maintenance and technology asset tracking.

11) EAM improves return on capital assets by integrating physical with financial aspects and supporting deep collaboration between project life cycle and service life cycle operations.

12) It integrates seamlessly with other modules of E-Business suite and is suitable for Metals/Mining, Manufacturing, Pulp/ Paper, Petrochemicals, Facilities and Education and many more assets.


Mandatory and Optional Oracle Applications modules for Oracle EAM


To work with EAM, which are the Mandatory and Optional Oracle Applications modules/products?

Mandatory modules are
a) Inventory
b) Bills of Material
c) Cost Management
d) Work in Process
e) Manufacturing Scheduling
f) Quality
g) Human Resource

Which are pre-requisites for installing EAM module ?


Which are pre-requisites for installing EAM module ? 

a) Configuring Concurrent Managers and Printers
b) Set up Responsibilities and assigning responsibilities as per requirements
c) Setting up Workflow module if work request(s) require approval from a maintenance department user


Which responsibilities are automatically created during installation process of EAM ?


Which responsibilities are automatically created during installation process of EAM ?

When installing Oracle Enterprise Asset Management, the installation process automatically creates the following five responsibilities:
a) Oracle Enterprise Asset Management
b) Maintenance User Workbench
c) Maintenance Super User
d) Self-Service Work Requests
e) Self Service Time and Expenses

The 'Oracle Enterprise Asset Management' responsibility mentioned above is a super user responsibility. It includes the necessary functions to set up and implement Oracle Enterprise Asset Management.




Implementation steps for Oracle EAM module


List all the implementation steps for Oracle EAM module ?

1) Setting Up Organizations

2) Enabling Organizations for Enterprise Asset Management

3) Defining eAM Parameters

4) Setting Up Areas

5) Defining Departments and Resources

6) Defining Department Approvers

7) Defining Miscellaneous Documents

8) Defining Lookups including
- Activity Types
- Activity Causes
- Activity Sources
- Asset Activity Priorities
- Cost Category Codes
- Criticality Codes
- Asset Import Scope Codes
- Asset Import Statuses
- Contact Preferences
- Work Request Statuses
- Work Request Types
- Work Order and Work Request Priority Codes
- Work Order Reconciliation Codes
- Work Order Types

9) Setting Up Category Codes

10) Defining Asset Groups

11) Defining Activities

12) Activity Association Templates

13) Setting Up Attribute Groups

14) Defining Asset Documents

15) Defining Asset Numbers

16) Defining Asset Routes

17) Setting Up Asset Bills of Material

18) Defining Activities

19) Navigating the Activity Workbench

20) Defining Maintenance Routes

21) Setting Up Maintenance Bills of Material

22) Associating Asset Numbers with Activities

23) Creating Activity Templates

24) Defining Rebuildable Items

25) Defining Rebuildable Serial Numbers

26) Rebuildable Item/Activity Association

27) Defining Rebuildable Bills of Material

28) To set up a Non-Stock Direct Item

29) Project and Task Setup

30) Defining Meter Templates

31) Defining Meters

32) Associating Meter Templates with Asset Groups

33) Associating Meters with Asset Numbers

34) Defining Set Names

35) Defining a Preventive Maintenance Template

Oracle Engineering - Setup steps


Step 1: Set Profile Options (Must)
Set the Engineering profile options for
a) ENG: Change Order Auto numbering – System Administrator Access
b) ENG: ECO Department
c) ENG: ECO Revision Warning
d) ENG: Engineering Item Change Order Access
e) ENG: Mandatory ECO Departments
f) ENG: Model Item Change Order Access
g) ENG: Planning Item Change Order Access

Step 2: Enter Employee (Must)
Define employees for your organization. ECO requests and approvers must be defined as employees

Step 3: Define Change Order Types (Optional)
You can assign a change order type to your ECOs, either using the ’ECO’ change order type that Oracle Engineering provides or choosing a change order type from any number of types you define using your own terminology, such as Design Revision, Manufacturing Change etc

Step 4: Define ECO Departments (Optional)
You can group users that use the engineering change order (ECO) system through ECO departments, creating multiple ECO departments within your Inventory organization.

Step 5: Define Auto numbering (Optional)
You can define customized autonumbering (for a user, organization, or site) for new ECOs or mass change orders

Step 6: Define Approval Lists (Optional)
You can define lists of approvers required to approve an ECO before it
can be released

Step 7: Define Material Dispositions (Optional)
You can define your own customized material dispositions, and then assign them to revised items when defining ECOs

Step 8: Define Material Dispositions (Optional)
You can define your own customized material dispositions, and then assign them to revised items when defining ECOs

Step 9: Define Priorities (Optional)
You can define scheduling priorities for engineering changes to describe the urgency of your ECO

Step 10: Start AutoImplement Manager (Optional)
If you automatically implement ECOs, you must specify the frequency that you want the Auto Implement manager to run
Can Not Access Quality Result Entry Form At PO Receipt


  If not using Quality Module set this as an organization level /user Level for Material Quality Inspection

profile option:      'QA: PO Inspection' = 'Oracle Purchasing'


Oracle Purchasing Quality Plan will Popup during material inspection.


If you use quality then add below profile value
Besides the profile option:
'QA: PO Inspection' = 'Oracle Quality'
and the transaction type Inspection transactions (Oracle Purchasing),
please make sure that the plan(s) is defined on the same organization where the Inspection is done against.

The collection plans are organization wise so you have to define 1 plan for each organization against you want to do the receiving inspection.
(if more than 1 then only the first one in alphabetical order will be returned and used)

In order to have desired plan triggered during the Receiving Inspection, you must uniquely identify it by populating the triggers.(while adding the transaction type to the plan definition)



WIP accounting classes

What is WIP accounting class ?
Which are different accounting classes in WIP ?
Which different accounts are associated with it ? 


An accounting class is a group of various General Ledger accounts which allows you to arrive at job cost, production cost and maintenance costs.

The following are different accounting classes in Oracle WIP:

1) Standard discrete
Standard discrete accounting classes can be used to group job costs for building sub assemblies and finished goods on shop floor. You can define and attach this accounting classes so that you can separately value and report the costs associated with sub assembly and finished goods production.

2) Asset non standard discrete 
If you use non-standard discrete jobs to track production costs as assets, you can define and assign an accounting class with a type of asset non-standard. Asset non-standard discrete jobs are costed the same as standard discrete jobs. Valuation accounts are charged when material is issued to a job and final costs and variances are calculated and posted to the appropriate variance and valuation accounts when the job is closed.

3) Expense non-standard discrete
Non-standard discrete accounting classes can be used to group and report various types of non-standard production costs, such as field service repair. For example to track recurring expenses - machine maintenance or engineering projects- with non-standard jobs, you can define and assign an accounting class with a type of expense non-standard to these jobs. The valuation accounts carry the costs incurred on these expense jobs as an asset during the period and automatically writes them off to the variance accounts at period close.

4) Repetitive accounting class
Repetitive accounting classes are used to group production costs and must be assigned to each repetitive line/assembly association that is created. Every repetitive schedule for that assembly on that line uses these accounts.

5) Standard Lot based
Standard lot based jobs control the material, resources, and operations required to build an assembly and collect costs. When you build lot based jobs, the standard lot based accounting class is used to separately value and report costs associated with yielded production at each individual operation on the routing.

6) Expense non standard lot based
Expense non-standard lot based accounting class jobs control material and collects costs for miscellaneous activity. These jobs are used for expense work orders for testing, prototypes, and rework where operation yield costing is not considered. You can perform all transactions (moves, jumps, scrap, splits, and update assemblies or routing) with the exception of job merge.

7) Maintenance accounting class
Maintenance accounting classes are used to group costs for work orders used in Oracle Enterprise Asset Management (EAM) Module . For example, if you are creating work orders for plant maintenance activities, you can define your accounting classes to separately value and report the costs related to asset.

The following valuation and variance accounts are associated with each accounting class.

Valuation accounts
1) Material account
2) Material overhead
3) Resouce
4) Overhead
5) Outside Processing

Variance accounts
1) Material account
2) Material overhead
3) Resouce
4) Overhead
5) Outside Processing
6) Standard cost
7) Bridging
8) Expense

Standard cost account is applicable to only standard costing method and Bridging and Expense accounts are applicable to average costing method only. Rest all accounts are applicable to both standard and average costing methods.

Wednesday, November 22, 2017


Po detail query

select distinct poh.segment1 po_number,
       poh.REVISION_NUM,
       poh.COMMENTS Description,
       poh.TYPE_LOOKUP_CODE po_type,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0)) amount,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
from po_headers_all poh,
po_lines_all pol,
--po_line_locations_all plla,
ap_suppliers asp,
ap_supplier_sites_all assa,
per_people_x ppx,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
ap_invoice_lines_all aila,
ap_invoices_all aia
where poh.po_header_id=pol.PO_HEADER_ID
--and poh.PO_HEADER_ID=plla.PO_HEADER_ID
--and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
--and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
--and plla.SHIP_TO_ORGANIZATION_ID=nvl(rsh.ship_to_org_id,plla.SHIP_TO_ORGANIZATION_ID)
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
and rsl.SHIPMENT_LINE_ID=aila.RCV_SHIPMENT_LINE_ID(+)
and poh.PO_HEADER_ID=nvl(aila.PO_HEADER_ID,poh.PO_HEADER_ID)
and pol.PO_LINE_ID=nvl(aila.PO_LINE_ID,pol.PO_LINE_ID)
and aila.INVOICE_ID=aia.INVOICE_ID(+)
and poh.segment1='115000007'
and poh.TYPE_LOOKUP_CODE='STANDARD'
group by 
       poh.segment1,
       poh.REVISION_NUM,
       poh.COMMENTS,
       poh.TYPE_LOOKUP_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       ppx.FULL_NAME,
       poh.attribute4,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
order by 1,13,14

----------------------------------------------------------------------------------------------



select distinct poh.segment1 po_number,
       null RELEASE_NUM,
       poh.REVISION_NUM,
       poh.COMMENTS Description,
       poh.TYPE_LOOKUP_CODE po_type,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
--       sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0)) amount,
       NVL(SUM(nvl(pol.UNIT_PRICE,0)*NVL(rsl.QUANTITY_RECEIVED,0)),sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0))) amount,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
from po_headers_all poh,
po_lines_all pol,
--po_line_locations_all plla,
ap_suppliers asp,
ap_supplier_sites_all assa,
per_people_x ppx,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
ap_invoice_lines_all aila,
ap_invoices_all aia
where poh.po_header_id=pol.PO_HEADER_ID
--and poh.PO_HEADER_ID=plla.PO_HEADER_ID
--and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
--and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
--and plla.SHIP_TO_ORGANIZATION_ID=nvl(rsh.ship_to_org_id,plla.SHIP_TO_ORGANIZATION_ID)
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
and poh.PO_HEADER_ID=aila.PO_HEADER_ID(+)
and pol.PO_LINE_ID=nvl(aila.PO_LINE_ID,pol.PO_LINE_ID)
and nvl(rsl.SHIPMENT_LINE_ID,-12)=nvl(aila.RCV_SHIPMENT_LINE_ID,nvl(rsl.SHIPMENT_LINE_ID,-12))
and aila.INVOICE_ID=aia.INVOICE_ID(+)
and trunc(poh.CREATION_DATE) between trunc(:p_from) and trunc(:p_to)
--and poh.segment1='115000007'
and poh.TYPE_LOOKUP_CODE='STANDARD'
group by 
       poh.segment1,
       poh.REVISION_NUM,
       poh.COMMENTS,
       poh.TYPE_LOOKUP_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       ppx.FULL_NAME,
       poh.attribute4,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
union all
select distinct poh.segment1 po_number,
       pra.RELEASE_NUM,
       poh.REVISION_NUM,
       poh.COMMENTS Description,
       poh.TYPE_LOOKUP_CODE po_type,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
--       sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0)) amount,
       NVL(SUM(nvl(pol.UNIT_PRICE,0)*NVL(rsl.QUANTITY_RECEIVED,0)),sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0))) amount,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
from po_headers_all poh,
     PO_RELEASES_ALL PRA,
     po_lines_all pol,
--     po_line_locations_all plla,
     ap_suppliers asp,
     ap_supplier_sites_all assa,
     per_people_x ppx,
     rcv_shipment_lines rsl,
     rcv_shipment_headers rsh,
     ap_invoice_lines_all aila,
     ap_invoices_all aia
where poh.po_header_id=pol.PO_HEADER_ID
and poh.TYPE_LOOKUP_CODE='BLANKET'
and poh.PO_HEADER_ID=pra.PO_HEADER_ID
--and poh.PO_HEADER_ID=plla.PO_HEADER_ID
--and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pra.PO_RELEASE_ID=nvl(rsl.PO_RELEASE_ID,pra.PO_RELEASE_ID)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
--and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
--and plla.SHIP_TO_ORGANIZATION_ID=nvl(rsh.ship_to_org_id,plla.SHIP_TO_ORGANIZATION_ID)
--and poh.PO_HEADER_ID=nvl(aila.PO_HEADER_ID,poh.PO_HEADER_ID)
and poh.PO_HEADER_ID=aila.PO_HEADER_ID(+)
and pra.PO_RELEASE_ID=nvl(aila.PO_RELEASE_ID,pra.PO_RELEASE_ID)
and pol.PO_LINE_ID=nvl(aila.PO_LINE_ID,pol.PO_LINE_ID)
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
and nvl(rsl.SHIPMENT_LINE_ID,-12)=nvl(aila.RCV_SHIPMENT_LINE_ID,nvl(rsl.SHIPMENT_LINE_ID,-12))
and aila.INVOICE_ID=aia.INVOICE_ID(+)
and trunc(poh.CREATION_DATE) between trunc(:p_from) and trunc(:p_to)
--and poh.segment1='115000001'
group by 
       poh.segment1,
       pra.RELEASE_NUM,

       poh.REVISION_NUM,
       poh.COMMENTS,
       poh.TYPE_LOOKUP_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       ppx.FULL_NAME,
       poh.attribute4,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
order by 1,2,13,14

-------------------------------------------------------FullQuery

select distinct 
       poh.segment1 po_number,
       null RELEASE_NUM,
       poh.REVISION_NUM,
       msib.SEGMENT1 item_code,
       msib.DESCRIPTION item_desc,
       mc.SEGMENT1 item_category,
       msib.PRIMARY_UOM_CODE uom,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       pol.UNIT_PRICE,
       nvl(pol.QUANTITY,0) QUANTITY,
       poh.TYPE_LOOKUP_CODE po_type,
       sum(nvl(rsl.QUANTITY_RECEIVED,0)) QUANTITY_RECEIVED,
       (nvl(pol.QUANTITY,0)-sum(nvl(rsl.QUANTITY_RECEIVED,0))) remaining_qty,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category
from po_headers_all poh,
     po_lines_all pol,
     po_line_locations_all plla,
     mtl_system_items_b msib,
     mtl_item_categories mic,
     mtl_categories mc,
     ap_suppliers asp,
     ap_supplier_sites_all assa,
     per_people_x ppx,
     rcv_shipment_lines rsl,
     rcv_shipment_headers rsh
where poh.po_header_id=pol.PO_HEADER_ID
and poh.TYPE_LOOKUP_CODE='STANDARD'
and poh.PO_HEADER_ID=plla.PO_HEADER_ID
and pol.PO_LINE_ID=plla.PO_LINE_ID
and pol.ITEM_ID=msib.INVENTORY_ITEM_ID
and plla.SHIP_TO_ORGANIZATION_ID=msib.ORGANIZATION_ID 
and msib.INVENTORY_ITEM_ID=mic.INVENTORY_ITEM_ID
and msib.ORGANIZATION_ID=mic.ORGANIZATION_ID  
and mic.CATEGORY_ID=mc.CATEGORY_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
AND RSL.TO_ORGANIZATION_ID=346
and to_char(poh.CREATION_DATE,'dd/mm/yyyy') between '01/04/2015' and '31/03/2016'
group by poh.segment1,
       poh.REVISION_NUM,
       msib.SEGMENT1,
       msib.DESCRIPTION,
       mc.SEGMENT1,
       msib.PRIMARY_UOM_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       pol.UNIT_PRICE,
       pol.QUANTITY,
       poh.TYPE_LOOKUP_CODE,
       ppx.FULL_NAME,
       poh.attribute4
union all
select distinct 
       poh.segment1 po_number,
       pra.RELEASE_NUM,
       poh.REVISION_NUM,
       msib.SEGMENT1 item_code,
       msib.DESCRIPTION item_desc,
       mc.SEGMENT1 item_category,
       msib.PRIMARY_UOM_CODE uom,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       pol.UNIT_PRICE,
       sum(nvl(pda.QUANTITY_ORDERED,0)) QUANTITY,
       poh.TYPE_LOOKUP_CODE po_type,
       sum(nvl(rsl.QUANTITY_RECEIVED,0)) QUANTITY_RECEIVED,
       (sum(nvl(pda.QUANTITY_ORDERED,0))-sum(nvl(rsl.QUANTITY_RECEIVED,0))) remaining_qty,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category
from po_headers_all poh,
     PO_RELEASES_ALL PRA,
     po_lines_all pol,
     po_line_locations_all plla,
     po_distributions_all pda,
     mtl_system_items_b msib,
     mtl_item_categories mic,
     mtl_categories mc,
     ap_suppliers asp,
     ap_supplier_sites_all assa,
     per_people_x ppx,
     rcv_shipment_lines rsl,
     rcv_shipment_headers rsh
where poh.po_header_id=pol.PO_HEADER_ID
and poh.TYPE_LOOKUP_CODE='BLANKET'
and poh.PO_HEADER_ID=pra.PO_HEADER_ID
and poh.PO_HEADER_ID=plla.PO_HEADER_ID
and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.PO_HEADER_ID=pda.PO_HEADER_ID
and pra.PO_RELEASE_ID=pda.PO_RELEASE_ID
and pol.PO_LINE_ID=pda.PO_LINE_ID
and plla.LINE_LOCATION_ID=pda.LINE_LOCATION_ID
and pol.ITEM_ID=msib.INVENTORY_ITEM_ID
and plla.SHIP_TO_ORGANIZATION_ID=msib.ORGANIZATION_ID 
and msib.INVENTORY_ITEM_ID=mic.INVENTORY_ITEM_ID
and msib.ORGANIZATION_ID=mic.ORGANIZATION_ID  
and mic.CATEGORY_ID=mc.CATEGORY_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pra.PO_RELEASE_ID=nvl(rsl.PO_RELEASE_ID,pra.PO_RELEASE_ID)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
and pda.PO_DISTRIBUTION_ID=nvl(rsl.PO_DISTRIBUTION_ID,pda.PO_DISTRIBUTION_ID)
AND RSL.TO_ORGANIZATION_ID=346
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
--and trunc(poh.CREATION_DATE) between trunc(:p_from) and trunc(:p_to)
and to_char(poh.CREATION_DATE,'dd/mm/yyyy') between '01/04/2015' and '31/03/2016'
group by poh.segment1,
       pra.RELEASE_NUM,
       poh.REVISION_NUM,
       msib.SEGMENT1,
       msib.DESCRIPTION,
       mc.SEGMENT1,
       msib.PRIMARY_UOM_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       pol.UNIT_PRICE,
       poh.TYPE_LOOKUP_CODE,
       ppx.FULL_NAME,
       poh.attribute4
   
----------------------------------------------------------------------------------------

select 
       po_number,
       RELEASE_NUM,
       Release_date,
       REVISION_NUM,
       po_desc,
       po_type,
       PO_status,
       order_date,
       expected_delivery_date,
       pr_number,
       pr_date,
       sum(pr_value) pr_value,
       VENDOR_NAME,
       Vendor_code,
       VENDOR_SITE_CODE,
       CURRENCY_CODE,
       sum(outstanding_qty) outstanding_qty,
       sum(Amount) Amount,
       buyer,
       CLOSED_CODE,
       CANCEL_FLAG,
       creator
from(
select 
       poh.segment1 po_number,
       null RELEASE_NUM,
       null Release_date,
       plla.NEED_BY_DATE,
       poh.REVISION_NUM,
       poh.COMMENTS po_desc,
       poh.TYPE_LOOKUP_CODE po_type,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       plla.PROMISED_DATE expected_delivery_date,
       prha.SEGMENT1 pr_number,
       prha.CREATION_DATE pr_date,
       nvl(prla.UNIT_PRICE,0)*nvl(prla.QUANTITY,0) pr_value,
       asp.VENDOR_NAME,
       asp.VENDOR_TYPE_LOOKUP_CODE Vendor_code,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       nvl(pol.QUANTITY,0) outstanding_qty,
       pol.UNIT_PRICE*nvl(pol.QUANTITY,0) Amount,
       ppx.FULL_NAME buyer,
       poh.CLOSED_CODE,
       poh.CANCEL_FLAG,
       fu.USER_NAME creator
from po_headers_all poh,
     po_lines_all pol,
     po_line_locations_all plla,
     po_distributions_all pda,
     po_req_distributions_all prda,
     po_requisition_lines_all prla,
     po_requisition_headers_all prha,
     ap_suppliers asp,
     ap_supplier_sites_all assa,
     per_people_x ppx,
     fnd_user fu
where poh.po_header_id=pol.PO_HEADER_ID
and poh.PO_HEADER_ID=plla.PO_HEADER_ID
and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.po_header_id=pda.PO_HEADER_ID
and pol.PO_LINE_ID=pda.PO_LINE_ID
and plla.LINE_LOCATION_ID=pda.LINE_LOCATION_ID
and nvl(pda.REQ_DISTRIBUTION_ID,-12)=prda.DISTRIBUTION_ID(+)
and nvl(prda.REQUISITION_LINE_ID,-12)=prla.REQUISITION_LINE_ID(+)
and nvl(prla.REQUISITION_HEADER_ID,-12)=prha.REQUISITION_HEADER_ID(+)
and poh.TYPE_LOOKUP_CODE='STANDARD'
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.CREATED_BY=fu.USER_ID
--and poh.CLOSED_CODE='OPEN'
--AND POH.SEGMENT1='115009851'
and to_char(poh.CREATION_DATE,'dd/mm/yyyy') between '01/04/2015' and '31/03/2016'
union all
select 
       poh.segment1 po_number,
       PRA.RELEASE_NUM,
       PRA.Release_date,

       plla.NEED_BY_DATE,

UnCoasted, Unprocessed , Pending Quantity

---penting Transactions--Material

/* Formatted on 2016/07/19 12:13 (Formatter Plus v4.8.8) */
SELECT   transaction_source_type_name, transaction_header_id,
         transaction_interface_id, transaction_mode_desc, transaction_mode,
         request_id, process_flag_desc, lock_flag_desc, lock_flag, ERROR_CODE,
         error_explanation, organization_code, organization_name,
         transfer_subinventory,
         transfer_organization_code, transfer_organization_name,
         transaction_uom, 
         transaction_date, transfer_cost_group_id,
         transaction_action_name, transaction_type_name, transaction_cost,
         transaction_reference, reason_name, source_code, source_line_id,
         source_header_id, shipment_number,
         employee_code,
         operation_seq_num, department_code, trx_source_line_id,
         trx_source_delivery_id,
         validation_required, negative_req_flag,
         primary_uom_code, transaction_type_id,
         transaction_source_type_id, transaction_source_id,
         transaction_source_name, required_flag, organization_id,
         inventory_item_id,primary_quantity,
         transaction_quantity,
         transaction_action_id, reason_id,
         wip_entity_type,department_id,
         process_flag
    FROM mtl_transactions_interface_v
   WHERE organization_id IN (
            SELECT oav.organization_id
              FROM org_access_view oav
             WHERE oav.responsibility_id = 50369
               AND oav.resp_application_id = 7000)
     AND (process_flag <> 9)
     AND (transaction_date <=
                      TO_DATE ('2016/06/30 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
         )
--     AND (organization_id = 104)
ORDER BY transaction_header_id, transaction_interface_id

---pending shop floor
/* Formatted on 2016/07/19 12:13 (Formatter Plus v4.8.8) */
SELECT   transaction_id, GROUP_ID, process_phase_meaning,
         process_phase, process_status_meaning, process_status, source_code,
         source_line_id, created_by_name, request_id, organization_code,
         organization_id, wip_entity_name, wip_entity_id, entity_type,
         line_code, line_id, primary_item_id,
         fm_operation_seq_num, fm_operation_code, fm_department_code,
         fm_department_id, fm_intraoperation_step_meaning,
         fm_intraoperation_step_type, to_operation_seq_num, to_operation_code,
         to_department_code, to_department_id, to_intraoperation_step_meaning,
         to_intraoperation_step_type, transaction_uom, primary_uom,
         transaction_quantity, primary_quantity,
         overcompletion_transaction_qty, transaction_date, acct_period_id,
         transaction_type, transaction_type_meaning, reason_name, reason_id,
         REFERENCE
    FROM wip_move_txn_interface_v
   WHERE organization_id IN (
            SELECT oav.organization_id
              FROM org_access_view oav
             WHERE oav.responsibility_id = 50369
               AND oav.resp_application_id = 7000)
--     AND (organization_code = '12A')
     AND (transaction_date <=
                      TO_DATE ('2016/06/30 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
         )
ORDER BY process_status DESC, process_phase ASC, transaction_id ASC

--uncoasted Material

/* Formatted on 2016/07/19 12:13 (Formatter Plus v4.8.8) */
SELECT   subinventory_code, revision, transfer_subinventory,
         ship_to_location_id,
         transfer_organization_id,
         owning_organization_id,
         transaction_date, transaction_id,
         transaction_quantity, transaction_uom, primary_quantity,
         secondary_transaction_quantity, secondary_uom_code, shipment_number,
         transaction_reference, costed_flag,
         transaction_group_id,
         source_code, source_line_id,
         transfer_transaction_id,rcv_transaction_id, move_transaction_id,
         completion_transaction_id, operation_seq_num,ERROR_CODE,
         error_explanation, transaction_source_name,
         inventory_item_id,
         organization_id,reason_id,
         transaction_type_id, transaction_action_id,
         transaction_source_type_id, transaction_source_id, employee_code,
         original_transaction_temp_id
    FROM mtl_material_transactions
   WHERE costed_flag IS NOT NULL
--     AND (organization_id = 104)
     AND (transaction_date <=
                      TO_DATE ('30-06-2016 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
         )
     AND (parent_transaction_id IS NULL)
UNION ALL
SELECT   subinventory_code, revision, transfer_subinventory,
         ship_to_location_id,
         transfer_organization_id,
         owning_organization_id, transaction_date, transaction_id,
         transaction_quantity, transaction_uom, primary_quantity,
         secondary_transaction_quantity, secondary_uom_code, shipment_number,
         transaction_reference, costed_flag, transaction_group_id,
         source_code, source_line_id,
         transfer_transaction_id,rcv_transaction_id, move_transaction_id,
         completion_transaction_id, operation_seq_num,ERROR_CODE,
         error_explanation, transaction_source_name,
         inventory_item_id,
         organization_id, reason_id,
         transaction_type_id, transaction_action_id,
         transaction_source_type_id, transaction_source_id, employee_code,
         original_transaction_temp_id
    FROM mtl_material_transactions
   WHERE parent_transaction_id IS NOT NULL
     AND parent_transaction_id IN (
            SELECT parent_transaction_id
              FROM mtl_material_transactions
             WHERE costed_flag IS NOT NULL
--               AND (organization_id = 104)
               AND (transaction_date <=
                       TO_DATE ('30-06-2016 23:59:59',
                                'DD-MM-YYYY HH24:MI:SS')
                   ))
ORDER BY transaction_date DESC, transaction_id DESC, subinventory_code

---pending wip coasting

/* Formatted on 2016/07/19 12:14 (Formatter Plus v4.8.8) */
SELECT    GROUP_ID, process_phase_meaning, process_phase,
         process_status_meaning, process_status, source_code, source_line_id,
         created_by_name, created_by, request_id, organization_code,
         organization_id, wip_entity_name, wip_entity_id, entity_type,
         line_code, line_id, primary_item_id,
         operation_seq_num, department_code, department_id,
         resource_type, usage_rate_or_amount, basis_type, standard_rate_flag,
         employee_num, employee_id, activity_name, activity_id,
         autocharge_type_meaning, autocharge_type, po_number, currency_code,
         actual_resource_rate, functional_currency_flag,
         currency_conversion_date, currency_conversion_type,
         currency_conversion_rate, currency_actual_resource_rate,
         transaction_uom, primary_uom, primary_uom_class,
         transaction_quantity, primary_quantity, transaction_date,
         acct_period_id, transaction_type_meaning, transaction_type,
         reason_name, reason_id, REFERENCE, 
         move_transaction_id, rcv_transaction_id,
         receiving_account_id, po_header_id, po_line_id, 
         program_id,
         transaction_id
    FROM wip_cost_txn_interface_v
   WHERE organization_id IN (
            SELECT oav.organization_id
              FROM org_access_view oav
             WHERE oav.responsibility_id = 50369
               AND oav.resp_application_id = 7000)
--     AND (organization_code = '12A')
     AND (transaction_date <=
                      TO_DATE ('2016/06/30 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
         )
ORDER BY process_status DESC, process_phase ASC, transaction_id ASC

---------------------------------------------------------------------------------------------------------------------------------
---pending receiving

/* Formatted on 2016/07/19 12:14 (Formatter Plus v4.8.8) */
SELECT ROWID, interface_transaction_id, error_message,pe.*
  FROM po_interface_errors pe
 WHERE interface_type NOT IN ('PO_DOCS_OPEN_INTERFACE', 'REQIMPORT')
 and trunc(creation_date)<=trunc(:p_from)
 and table_name='RCV_TRANSACTIONS_INTERFACE'
--   AND interface_line_id = 3467876 

----unprocessed Material

/* Formatted on 2016/07/19 12:14 (Formatter Plus v4.8.8) */
SELECT transaction_temp_id,
       creation_date,request_id,
       program_id,
       transaction_quantity, primary_quantity,ERROR_CODE,
       serial_transaction_temp_id, group_header_id, description,
       origination_date, origination_type, date_code, change_date, status_id,
       retest_date, hold_date,
        reason_id, supplier_lot_number
          FROM mtl_transaction_lots_temp mtlt
 WHERE 1=1 
-- and (transaction_temp_id = 37673967)
 and trunc(creation_date)<=trunc(:p_from)


 -----------------------------------Cost Period

 --Cost Period

/* Formatted on 2016/07/19 12:12 (Formatter Plus v4.8.8) */
SELECT   status, period_name, period_number, period_year, start_date,
         end_date, close_date, rec_type, organization_id, acct_period_id,
         row_id, accounted_period_type, period_set_name, last_update_date,
         creation_date, last_updated_by, created_by, last_update_login
    FROM org_acct_periods_v
   WHERE (   (rec_type = 'ORG_PERIOD' AND organization_id = 104)
          OR (    rec_type = 'GL_PERIOD'
              AND period_set_name = 'Polycab Cal'
              AND accounted_period_type = '21'
              AND (period_year, period_name) NOT IN (
                                               SELECT period_year,
                                                      period_name
                                                 FROM org_acct_periods
                                                WHERE organization_id = 104)
              AND end_date >= TO_DATE ('30-06-2016', 'DD-MM-YYYY')
             )
         )

ORDER BY end_date DESC