Workflow Related Queries Link between PO and Work flow , Open Notifications - Req ,Open Notifications - PO

Here are couple of scripts. Haven't included the basic scripts for finding 

the po_header_id,WF item attributes. 

*

*Action History - PO 

* 

select poah.sequence_num seq#, poah.action_date, poah.action_code, 

poah.employee_id emp_id, fnd.user_name, 

substr(poah.object_type_code,1,3) type, poah.object_sub_type_code sub_type, 

poah.object_revision_num rev, pohead.org_id 

from po_action_history poah, fnd_user fnd, po_headers_all pohead 

where poah.object_id = pohead.po_header_id 

and pohead.segment1 = '&PO_NUMBER' 

and pohead.org_id = '&ORG_ID' 

and substr(poah.object_type_code,1,3) = 'PO' 

and poah.employee_id = fnd.employee_id 

and fnd.session_number != 0 

order by 2,1; 

*Action History - Req * 

select poah.sequence_num seq#, poah.action_date, poah.action_code, 

poah.employee_id emp_id, fnd.user_name, 

substr(poah.object_type_code,1,3) type, poah.object_sub_type_code sub_type, 

poah.object_revision_num rev, pohead.org_id 

from po_action_history poah, fnd_user fnd, po_requisition_headers_all pohead 

where poah.object_id = pohead.requisition_header_id 

and pohead.segment1 = '&REQ_NUMBER' 

and substr(poah.object_type_code,1,3) = 'REQ' 

and pohead.org_id = '&ORG_ID' 

and poah.employee_id = fnd.employee_id 

and fnd.session_number != 0 

order by 9,2,1; 

*Open Notifications - Req * 

select reqh.segment1 req, wfn.status notify_status, wias.notification_id 

notify_id, 

wfn.to_user name, reqh.org_id org 

from wf_item_activity_statuses wias, 

wf_notifications wfn, 

po_requisition_headers_all reqh 

where wias.notification_id is not null 

and wias.notification_id = wfn.group_id 

and wfn.status = 'OPEN' 

and wias.item_type = 'REQAPPRV' 

and wias.item_key = reqh.wf_item_key 

and reqh.authorization_status IN ('IN PROCESS') 

order by 5,1,3; 

*Open Notifications - PO 

* 

select poh.segment1 po, wfn.status notify_status, wias.notification_id 

notify_id, 

wfn.to_user name, poh.org_id org 

from wf_item_activity_statuses wias, 

wf_notifications wfn, 

po_headers_all poh 

where wias.notification_id is not null 

and wias.notification_id = wfn.group_id 

and wfn.status = 'OPEN' 

and wias.item_type = 'POAPPRV' 

and wias.item_key = poh.wf_item_key 

and poh.authorization_status IN ('IN PROCESS','PRE-APPROVED') 

order by 4,5,1,3; 

*Hierarchy By Employee ID * 

SELECT superior_id, hrec.full_name ,poeh.superior_level 

FROM hr_employees_current_v hrec, po_employee_hierarchies poeh, 

PO_DOCUMENT_TYPES_ALL pdt 

WHERE pdt.org_id = 19897 

AND pdt.DOCUMENT_TYPE_CODE = 'REQUISITION' 

AND pdt.DOCUMENT_SUBTYPE = 'PURCHASE' 

AND pdt.default_approval_path_id = poeh.position_structure_id 

AND poeh.employee_id = 100958 

AND hrec.employee_id = poeh.superior_id 

AND poeh.superior_level > 0 

ORDER BY poeh.superior_level, hrec.full_name; 

SELECT prh.segment1 req_number, 

wf.from_user, 

wf.to_user, 

prh.authorization_status 

FROM po_action_history pah, 

po_requisition_headers_all prh, 

wf_notifications wf, 

wf_items wi 

WHERE pah.object_type_code = 'REQUISITION' 

AND pah.action_code IS NULL 

AND pah.object_id = prh.requisition_header_id 

AND wi.item_type = 'REQAPPRV' 

AND prh.wf_item_type = wi.item_type 

AND wi.parent_item_key = prh.wf_item_key 

AND wi.item_key = wf.item_key 

AND wf.message_type = 'REQAPPRV'

How to use Case and DECODE function???

How to use Case and DECODE function???

CASE

--Case is used instead of Decode function

--Case is new in 9i

--Case is simple to read and maintain than in Decode

E.g 1) select job,

           case job

                    when 'ANALYST' then 4000

                    when 'MANAGER' then 5000

                    when 'CLERK' then   3000

                    else

                        2000

            end "Raise"

from emp;

E.g 2) select  sal,

                 case sal

                       when 1600 then 'A'

                      when 2850 then 'B'

                else

                     ‘C’

      end "Grade"

from emp;

E.g 3)

 Drop table codes;

create table codes

(traincode varchar2(15));

insert into codes

values('M-D');

insert into codes

values('P-M');

insert into codes

values('P-D');

insert into codes

values('M-A');

insert into codes

values('C-B');

--Here the code values are ellaborated with full forms using case

select traincode,

          CASE traincode   

           when 'M-D' then 'Mumbai to Delhi'

           when 'P-M' then 'Pune to Mumbai'

           when 'P-D' then 'Pune to Delhi'

           when 'M-A' then 'Mumbai to Ahemdabad'

           else

                   'South trains'

            end    

      As "Description"

from codes;

Case in Comparison –

select sal,

case when sal >= 3000 then sal + 10000

     when sal <= 1000 then sal - 500

else

sal

end

from emp;

DECODE

select job,

            DECODE ( JOB, 'ANALYST','A','MANAGER','M','CLERK','C','ZZ') “CODE”

from emp;

How to work with Date Functions??

How to add days in current date?

 select sysdate + 3 from dual;

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

 select '07-dec-04' + 3 from dual; -- Gives error

select to_date('07-dec-04')  + 3 from dual;

select to_date('07-dec-04') - to_date('13-dec-04') from dual; -- Negative value

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

date + number/24 --> Adds a number of hours to a date

Suppose the time of sysdate is 9:30 pm and 5 hours are added in it then it will be2:30 am of the next date. So it shows the next date.

select sysdate + 4/24 from dual;

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

How to do Months_between(d1,d2)

If d1 > d2 then +ve else -ve

Select MONTHS_BETWEEN(TO_DATE('01-AUG-02'),to_date('01-dec-05')) from dual;

select months_between(sysdate,hiredate) from emp

select round(months_between(sysdate,hiredate),0) As "Months Between" from emp

 Assignment - Find the years between using months_between

select months_between('13-dec-04','24-jul-04') from dual

select round(months_between('13-dec-04','24-jul-04'),0) from dual

Add_months(date,n) [n cand be +ve or -ve]

select add_months('4-dec-04',3) from dual; Gives 04-Mar-05

select add_months('4-dec-04',-3) from dual; Gives 04-Sep-04

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

Next_Day(date,'char') -- Give the date of the next week for the specified day in the char parameter.

select next_day('04-Dec-04','Wed') from dual; Gives the date coming on next Wednesday after 4-Dec-04. The answer is 08-Dec-04

OR

Instead of character parameter a numeric value can be also given. It has Sunday as 1 Monday as 2  ...Saturday as 7

select next_day('04-Dec-04', 4) from dual. The answer is 08-Dec-04

 Note: The number cannot be negative.

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

Last_Day(date) - Returns the last date of the month.

select last_day(sysdate) from dual;

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

Round(Date,fmt)

If month is the fmt, then rounds the date to the months extremities.If date is between 1 and 15 then it rounds the date to the first of the same month. If it is 16 to end then rounds to first date of the next date.

E.g select round(to_date('04-dec-04'),'Month') from dual  ----> 01-DEC-04

select round(to_date('15-dec-04'),'Month') from dual; ----> 01-DEC-04

select round(to_date('16-dec-04'),'Month') from dual; ---->  01-JAN-05

select round(to_date('02-Feb-04'), 'Year') from dual; --> 01-JAN-04

select round(to_date('30-Jun-04'), 'Year') from dual;  --> 01-JAN-04

 select round(to_date('1-Jul-04'), 'Year') from dual; --> 01-JAN-05

Trunc

select trunc(to_date('23-dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('07-Dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('23-Dec-04'),'Year') from dual;    Gives 01-Jan-04

select trunc(to_date('02-Feb-04'),'Year') from dual;     Gives 01-Jan-04

To_Char

To see all the records of employees joined after 1982

select * from emp

where to_char(hiredate,'yy') >= 82;

Or  where to_char(hiredate,'yyyy’) >= 1982

To see all the records of employees who have joined from October of any month.

select * from emp

where to_char(hiredate,'mm') >= 10; 

(Note à Only number 1 to 12 can be used for comparison)

To see all the records of employees who have joined from 21^st date.

select * from emp

where to_char(hiredate,'dd') >= 21;

To see all the records of employees who have joined from April-81

select * from emp

where to_char(hiredate,'mm') > 04 and to_char(hiredate,'yy') >= 81

To see all the records of employees who have joined after 28-Sep-81

select * from emp

where hiredate >  '28-Sep-81'

DATE FORMAT MODEL

YEARS

select to_char(hiredate,'YYYY') || '-' || to_char(hiredate,'YEAR') FROM EMP;

MONTHS

select to_char(hiredate,'MONTH') || '-' || to_char(hiredate,'MM')

 || '-' || TO_CHAR(hiredate,'MON') FROM EMP;

DAYS

select to_char(hiredate,'DAY') || '-' || to_char(hiredate,'DD')

 || '-' || TO_CHAR(hiredate,'DY') FROM EMP;

ADVANCED FORMATS -

CENTURY

SELECT TO_CHAR(SYSDATE,’SCC’) FROM DUAL;

select to_char(HIREDATE,'SCC') || '-' || TO_CHAR(HIREDATE,'YY') "CEN-YR"

FROM EMP;

SYEAR  The spelled out year.

select to_char(HIREDATE,'SYEAR') "YEAR"

FROM EMP

Q  To get the quarter of the year (1,2,3 and 4)

select to_char(HIREDATE,'Q') "Quarter"

FROM EMP;

 RM   Roman numeral month

select to_char(HIREDATE,'RM')"MONTH"

FROM EMP;

J Julian day - The number of days since 31Dec 4713 B.C.

select to_char(HIREDATE,'J')"JulianDay"

FROM EMP;

TIME

select to_char(sysdate,'hh:mi:ss') FROM DUAL;

To show AM or PM

select to_char(sysdate,'hh:mi:ss')|| '-' || to_char(sysdate,'PM') from dual;

SSSS Seconds past midnight (0-86399)  [ (60secs * 60 min * 24) - 1]

select to_char(sysdate,'ssss') from dual;

Suffixes -

select to_char(sysdate,'ddth') from dual;

select to_char(sysdate,'ddsp') from dual;

select to_char(sysdate,'ddspth') from dual;

 RR DATE FORMAT -

create table rryy

(name varchar2(10),

 dob date);

NAME	DOB
A	24-SEP-02
B	12-DEC-67
C	14-JAN-59
D	05-APR-04

  A AND D ARE IN 2000 CENTURY.

  B AND C ARE IN 1900 CENTURY.

To see the records who have dob of 19^th century.

Suppose yy format is used -

SELECT * FROM RRYY

WHERE TO_CHAR(DOB,'YY') < 99.

It assumes all the years are in the 20^th  century (2099). So all 4 records will come.

So to show only ,B and C records  --

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90',  'DD-MON-RR');

Here 90 it understands as 1990.

If  YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-YY');

Here 90 it understands as 2090. So again all 4 records will come.

Similarly ---

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-RR');

Due to RR 90 will be 1990. So it shows all the records where DOB is above 1990.

A and D records will come.

If YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-YY');

Due to YY it takes 90 as 2090. It will not find any record above 2090.

what are the major difference between oracle 11i and R12 ?

Que: what are the major difference between oracle 11i and R12 ?

Ans :

Ø      11i only forms basis application but R12 is now forms and HTML pages.

Ø      11i is particularly in responsibility and operating unit basis but R12 is multi operating unit basis.

Ø      11i is particularly in set of books using and R12 using in ledgers.

Ø      11i in MRC Reporting level set of books called reporting set of books but in R12 reporting ledgers called as reporting currency. Banks are using at single operating unit level in 11i and ledgers level using in R12.

Differences between R12 & 11i.5.10 New R12 Upgrade to R12 – Pros and Cons Pros:

o       Sub-ledger Accounting – The new Oracle Sub-ledger Accounting (SLA) architecture allows users to customize the standard Oracle accounting entries. As a result of Cost Management's uptake of this new architecture, users can customize their accounting for Receiving, Inventory and Manufacturing transactions.

o       Enhanced Reporting Currency (MRC) Functionality – Multiple Reporting Currencies functionality is enhanced to support all journal sources. Reporting sets of books in R12 are now simply reporting currencies. Every journal that is posted in the primary currency of a ledger can be automatically converted into one or more reporting currencies.

o       Deferred COGS and Revenue Matching – R12 provides the ability to automatically maintain the same recognition rules for COGS and revenue for each sales order line for each period. Deferred COGS and Deferred Revenue are thus kept in synch.

Cons:

o       Resources – Availability of knowledgeable resources

o       Maturity – Though R12 is around since 2007, not all the modules are mature enough meaning modules like E-B Tax have many bugs being fixed by Oracle development.

o       Integration with customized applications – In a customized environment, all the extensions & interfaces need to be analyzed because of the architectural changes in R12

      R12 Features and differences with 11.5.10 – Inventory / Cost Management

o       Multi-Organization Access Control (MOAC) - Multi-Org Access Control enables uses to access multiple operating units data from single responsibility. Users can access reports , Concurrent programs , all setup screens of multiple operating units from single responsibility without switching responsibilities.

o       Unified Inventory - R12 merges Oracle Process Manufacturing OPM Inventory and Oracle Inventory applications into a single version . So OPM users can leverage the functionalities such as consigned & VMI and center led procurement which were available only to discrete inventory in 11i.5.10.

o       Inventory valuation Reports -There are a significant number of reports which have been enhanced in the area of Inventory Value Reporting

o       Inventory Genealogy - Enhanced genealogy tracking with simplified, complete access at the component level to critical lot and serial information for material throughout production.

o       Fixed Component Usage Materials Enhancement – Enhanced BOM setup screen and WIP Material Requirement screen that support materials having a fixed usage irrespective of the job size for WIP Jobs, OSFM lot-based jobs, or Flow Manufacturing

o       Component Yield Enhancements - New functionality that provides flexibility to control the value of component yield factors at WIP job level. This feature allows the user to include or exclude yield factor while calculating back flush transactions.

o       Periodic Average Cost Absorption Enhancements – Enhanced functionality for WIP Final Completion, WIP Scrap Absorption, PAC WIP Value Report, Material Overhead Absorption Rule, EAM work order, and PAC EAM Work Order Cost Estimate Processor Report.

o       Component Yield benefits - Component yield functionality user have the flexibility to control the value of component yield factors and use those factors for back flush transactions. Of course if the yield factor is not used, yield losses can be accounted for using the manual component issue transaction.

New Features:

    R12 Features and differences with 11.5.10 – Advanced Procurement Suite

o       Professional Buyer’s Work Center – To speed up buyers’ daily purchasing tasks – view & act upon requisition demand, create & manage orders and agreements, run negotiation events, manage supplier information.

o       Freight and Miscellaneous Charges – New page for viewing acquisition cost to track freight & miscellaneous delivery cost components while receiving. Actual delivery costs are tracked during invoice matching.

o       Complex Contract Payments – Support for payments for services related procurement including progress payments, recoupment of advances, and retainage.

o       Unified Inventory – Support for the converged inventory between Oracle Process Manufacturing – OPM Inventory & Oracle Inventory.

o       Document Publishing Enhancements – Support for RTF & PDF layouts and publish contracts using user specified layouts

o       Support for Contractor Purchasing Users – Support for contingent workers to create & maintain requisitions, conduct negotiations, and purchase orders.

New Features:

      R12 Features and differences with 11.5.10 – Order Management

o       Multi-Organization Access Control (MOAC) - Multi-Org Access Control enables uses to access multiple operating units data from single responsibility. Users can access reports , Concurrent programs , all setup screens of multiple operating units from single responsibility without switching responsibilities. They can also use Order Import to bring in orders for different Operating Units from within a single responsibility. The same applies to the Oracle Order Management Public Application Program Interfaces (APIs).

o       Post Booking Item Substitution - Item Substitution functionality support has been extended to post-Booking through Scheduling/re-scheduling in Sales Order, Quick Sales Order, and Scheduling Order Organizer forms. Item Substitution functionality is also supported from Planner’s Workbench (loop-back functionality) till the line is pick-released.

o       Item Orderability - Businesses need the ability to define which customers are allowed to order which products, and the ability to apply the business logic when the order is created.

o       Mass Scheduling Enhancements – Mass Scheduling can now schedule lines that never been scheduled or those that have failed manual scheduling. Mass Scheduling also supports unscheduling and rescheduling

o       Exception Management Enhancements – Improved visibility to workflow errors and eases the process of retrying workflows that have experienced processing errors

o       Sales Order Reservation for Lot-Based Jobs – Lot-Based Jobs as a Source of Supply to Reserve Against Sales Order(s). OSFM Displays Sales Order Information on Reserved Jobs

o       Cascading Attributes – Cascading means that if the Order header attributes change, the corresponding line attributes change

o       Customer Credit Check Hold Source Support across Operating Units - Order Management honors credit holds placed on customers from AR across operating Units. When Receivables places a customer on credit hold a hold source will be created in all operating units which have:

§         A site defined for that customer

§         An order placed against that customer.

New Features:

    R12 Features and differences with 11.5.10 – Shipping

o       Pick Release/Confirm Features

§         Pick Release enhancements - Enhancements will be made to the Release Sales Order Form and the Release Rules Form to support planned crossdocking and task priority for Oracle Warehouse Management (WMS) organizations. Pick release will allow a user to specify location methods and if crossdocking is required, a cross-dock rule. The task priority will be able to be set for each task in a sales order picking wave when that wave is pick released. The priority indicated at pick release will be defaulted to every Oracle WMS task created

§         Parallel Pick Release Submission - This new feature will allow users to run multiple pick release processes in parallel to improve overall performance. By distributing the workload across multiple processors, users can reduce the overall time required for a single pick release run.

o       Workflow Shipping Transaction Enhancement – Oracle has enabled Workflow in the Shipping process for: workflow, process workflow, activity and notification workflow, and business event

o       Support for Miscellaneous Shipping Transactions - Oracle Shipping Execution users will now be able to create a delivery for a shipment that is not tied to a sales order via XML (XML-equivalent of EDI 940 IN). Once this delivery has been created, users will be able to print shipping documents, plan, rate, tender, audit and record the issuance out of inventory. Additionally, an XML Shipment Advice (XML- equivalent of EDI 945 OUT) will be supported to record the outbound transactions.

o       Flexible Documents: With this new feature ,Shipping Execution users will be able to create template-based, easy-to-use formats to quickly produce and easily maintain shipping documents unique to their business. Additional attributes will be added to the XML templates for each report for added flexibility

o       Enhanced LPN Support - Oracle Shipping Execution users will now have improved visibility to the Oracle WMS packing hierarchy at Pick Confirmation. The packing hierarchy, including the License Plate Number (LPN), will be visible in the Shipping Transactions form as well as in the Quick Ship user interface.

New Features:

R12 Features and differences with 11.5.10 – Warehouse Management

o       Crossdock Execution – WMS allow you to determine final staging lane, merge with existing delivery or create a new delivery, synchronize inbound operation plan with outbound consolidation plan, enhance outbound consolidation plans and manage crossdock tasks.

o       Labor Management – WMS provides labor analysis. It gives the warehouse manager increased visibility to resource requirements. Detailed information for the productivity of individual employees and warehouses is provided

o       Warehouse Control Board Additions - Additional Task selection criteria

o       User Extensible Label Fields – WMS, users are now able to add their own variables without customizing the application, by simply defining in SQL the way to get to that data element

o       Material Consolidation across deliveries – WMS allows you to consolidate material across deliveries in a staging lane

New Features:

    R12 Features and differences with 11.5.10 – OSFM

o       Lot and Serial Controlled Assembly– Lot controlled job can now be associated with serial numbers to track and trace serialized lot/item during shop floor transactions as well as post manufacturing and beyond

o       Fixed Component Usage Support for Lot Based Jobs – OSFM now supports fixed component usage defined in the Bill of Material of an end it.

o       Support for Partial Move Transactions – Users are able to execute movement of a partial job quantity interoperation

o       Enhanced BOM to Capture Inverse Usage – Users can now capture the inverse component usage through the new inverse usage field in BOM UI

o       Support for Rosetta Net Transaction - comprising of 7B1 (work in process) and 7B5 (manufacturing work order).

New Features:

     R12 – Further Info…

o       The latest RCD (Release Content Documents) can be accessed from the metalink note 404152.1 (requires user name & password).

o       The TOI (Transfer Of Information) sessions released by Oracle Learning can be accessed from its portal at http:// www.oracle.com/education/oukc/ebs.html

o       Oracle White papers – Extending the value of Your Oracle E-Business Suite 11i.10 Investment & Application Upgrades and Service Oriented Architecture

ORACLE API for Lookup Code and Value Creation

Oracle APIs for Creation of  Lookup Code and Values in ORacle Apps.... 

DECLARE

   ln_rowid    VARCHAR2 (1000);

   ln_rowid1   VARCHAR2 (1000);

BEGIN

   fnd_lookup_types_pkg.insert_row (x_rowid                    => ln_rowid,

                                    x_lookup_type              => 'XXND BUSINESS CYCLE',

                                    x_security_group_id        => 0,

                                    x_view_application_id      => 3,

                                    x_application_id           => 20087,

                                    x_customization_level      => 'U',

                                    x_meaning                  => 'XXND BUSINESS CYCLE',

                                    x_description              => 'XXND BUSINESS CYCLE',

                                    x_creation_date            => SYSDATE,

                                    x_created_by               => 0,

                                    x_last_update_date         => SYSDATE,

                                    x_last_updated_by          => 0,

                                    x_last_update_login        => -1

                                   );

                                     

   DBMS_OUTPUT.put_line (ln_rowid);

   fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,

                                     x_lookup_type              => 'XXND BUSINESS CYCLE',

                                     x_security_group_id        => 0,

                                     x_view_application_id      => 3,

                                     x_lookup_code              => 'XXND_INFO',

                                     x_tag                      => NULL,

                                     x_attribute_category       => NULL,

                                     x_attribute1               => NULL,

                                     x_attribute2               => NULL,

                                     x_attribute3               => NULL,

                                     x_attribute4               => NULL,

                                     x_enabled_flag             => 'Y',

                                     x_start_date_active        => TO_DATE ('01-JAN-1950',

                                                                            'DD-MON-YYYY'

                                                                           ),

                                     x_end_date_active          => NULL,

                                     x_territory_code           => NULL,

                                     x_attribute5               => NULL,

                                     x_attribute6               => NULL,

                                     x_attribute7               => NULL,

                                     x_attribute8               => NULL,

                                     x_attribute9               => NULL,

                                     x_attribute10              => NULL,

                                     x_attribute11              => NULL,

                                     x_attribute12              => NULL,

                                     x_attribute13              => NULL,

                                     x_attribute14              => NULL,

                                     x_attribute15              => NULL,

                                     x_meaning                  => 'XXND Information',

                                     x_description              => NULL,

                                     x_creation_date            => SYSDATE,

                                     x_created_by               => 0,

                                     x_last_update_date         => SYSDATE,

                                     x_last_updated_by          => 0,

                                     x_last_update_login        => -1

                                    );

   DBMS_OUTPUT.put_line (ln_rowid1);

   COMMIT;

END;

Query for Employee Information: Oracle Apps Employee Data Extract

SELECT   p.FULL_NAME,p.employee_number Employee_Number,

         p.last_name Last_Name,

         p.first_name First_Name,

         p.original_date_of_hire Hire_date,

         p.DATE_OF_BIRTH DOB,

         p.SEX Gender,

         p.EMAIL_ADDRESS EMAIL_Address,

         (SELECT   name

            FROM   apps.hr_all_organization_units

           WHERE   organization_id = p.business_group_id)

            Organization,

         (SELECT   location_code

            FROM   apps.hr_locations_all_tl

           WHERE   location_id = a.location_id

                   AND language = USERENV ('LANG'))

            Location_Name,

         'A' Status_Flag,

         (SELECT   CONCATENATED_SEGMENTS

            FROM   apps.GL_CODE_COMBINATIONS_KFV

           WHERE   code_combination_id = a.DEFAULT_CODE_COMB_ID)

            Expense_Account,

         (SELECT   papf1.full_name supervisor_name

            FROM   apps.per_all_people_f papf,

                   apps.per_all_assignments_f paaf,

                   apps.per_all_people_f papf1

           WHERE       papf.person_id = paaf.person_id

                   AND paaf.primary_flag = 'Y'

                   AND paaf.assignment_type = 'E'

                   AND paaf.supervisor_id = papf1.person_id

                   AND papf1.current_employee_flag = 'Y'

                   AND papf.business_group_id = paaf.business_group_id

                   AND SYSDATE BETWEEN papf.effective_start_date

                                   AND  papf.effective_end_date

                   AND SYSDATE BETWEEN paaf.effective_start_date

                                   AND  paaf.effective_end_date

                   AND SYSDATE BETWEEN papf1.effective_start_date

                                   AND  papf1.effective_end_date

                   AND papf.employee_number = p.employee_number

                   and   papf.person_id = p.person_id)

            Supervisor

  FROM   apps.per_all_people_f p,

         apps.per_all_assignments_f a,

         apps.pay_people_groups ppg,

         apps.hr_all_positions_f hap

 WHERE       P.Person_Id = A.Person_Id

         AND SYSDATE BETWEEN P.Effective_Start_Date AND P.Effective_End_Date

         AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date

         AND a.people_group_id = ppg.people_group_id

         AND hap.position_id(+) = a.position_id

Profile Option Values for Responsibility

Profile Option Values for Responsibility

SELECT   resp.responsibility_name,

           SUBSTR (pro1.user_profile_option_name, 1, 60) Profile,

           DECODE (pov.level_id,

                   10001,

                   'Site',

                   10002,

                   'Application',

                   10003,

                   'Resp',

                   10004,

                   'User')

              Option_Level,

           DECODE (pov.level_id,

                   10001,

                   'Site',

                   10002,

                   appl.application_short_name,

                   10003,

                   resp.responsibility_name,

                   10004,

                   u.user_name)

              Level_Value,

           NVL (pov.profile_option_value, 'Is Null') Profile_option_Value

    FROM   fnd_profile_option_values pov,

           fnd_responsibility_tl resp,

           fnd_application appl,

           fnd_user u,

           fnd_profile_options pro,

           fnd_profile_options_tl pro1

   WHERE       1 = 1

           --AND pro1.user_profile_option_name LIKE ('%MO: Operating Unit%')

           AND pro.profile_option_name = pro1.profile_option_name

           AND pro.profile_option_id = pov.profile_option_id

           AND UPPER (resp.responsibility_name) LIKE

                 UPPER ('%INV') /* comment this line  if you need to check profiles for all responsibilities */

           AND pov.level_value = resp.responsibility_id(+)

           AND pov.level_value = appl.application_id(+)

           AND pov.level_value = u.user_id(+) and resp.language='US' and pro1.language='US'

ORDER BY   1, 2

Query to check RTP stuck Records in R12

 SELECT RTI.*, rti.DESTINATION_TYPE_CODE,

 rti.interface_transaction_id   --           'interface_transaction_id'

, rhi.header_interface_id       --                 'header_interface_id'

, mp.ORGANIZATION_CODE         --                  'Org'

, rti.source_document_code     --                  'Type'

, pha.segment1                --                   'PO Number'

, pla.line_num                --                   'PO Line'

, to_char(rti.transaction_date, 'dd-MON-yyyy')  -- 'Transaction Date'

, rti.transaction_type      

, rti.PROCESSING_MODE_CODE 

, rti.processing_status_code

, rhi.processing_status_code 

, to_char(rti.creation_date, 'dd-MON-yyyy')  creation_date

, rti.quantity

, msi.segment1                              --     'Item'

, msi.item_type                            --      'Item Type'

, rti.item_description

, poh.error_message

, replace(poh.error_message,chr(10),' ') ,msi.organization_id--,poh.*

--, replace(pot.error_message,chr(10),' ')

FROM   po.po_interface_errors         poh

,      po.po_interface_errors         pot

,      po.rcv_transactions_interface  rti

,      po.rcv_headers_interface       rhi 

,      inv.mtl_parameters             mp

,      po.po_headers_all              pha

,      po.po_lines_all                pla

,      inv.mtl_system_items_b         msi

WHERE 1=1

AND   rti.header_interface_id      = rhi.header_interface_id   (+)

AND   rti.interface_transaction_id = pot.interface_transaction_id (+)

AND   rhi.header_interface_id      = poh.interface_header_id (+)

--    mapping to PO

AND   rti.po_header_id = pha.po_header_id AND pha.org_id=8527

AND   rti.po_header_id = pla.po_header_id

AND   rti.po_line_id = pla.po_line_id 

--    mapping to item

AND   mp.organization_id = msi.organization_id AND msi.organization_id=106

AND   rti.item_id = msi.inventory_item_id

 and   pha.segment1='1008007890' --in ('20231192' ,'20230863')-- PUT PO NUMBER HERE

--AND pla.line_num IN (14,57)

Order by mp.ORGANIZATION_CODE, pha.segment1, pla.line_num

Supplier Master Details in R12 Query

--Supplier Master Details

SELECT asp.vendor_id ,
asp.segment1 "Supplier Num" ,
asp.vendor_name "Supplier Name" ,
ass.vendor_site_code "site name" ,
hou.name "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.email_address
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.org_party_site_id = ass.party_site_id

PO CREATED And RECEIPT NOT DONE,For Partially received POS

--PO CREATED And RECEIPT NOT DONE

SELECT ORG_ID,SEGMENT1,CREATION_DATE,CLOSED_CODE,pha.po_Header_id,To_char(CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') Creation_Date_Time
FROM apps. po_Headers_all pha
WHERE pha. vendor_order_num LIKE '%SA%'
AND attribute15 NOT LIKE '%X'
AND cancel_flag <> 'Y'
AND NOT EXISTS
  (SELECT 1
  FROM apps. rcv_shipment_lines rsl
  WHERE PHA. PO_HEADER_ID = RSL. PO_HEADER_ID
  )ORDER BY CREATION_DATE DESC;

----------------------------------------------------------------------------------------------
--For Partially received POS

SELECT --pha. ORG_ID,pha. po_HEADER_ID,pha. SEGMENT1,pha. CREATION_DATE,pha. CLOSED_CODE
distinct pha. po_header_id, pha. creation_date
FROM apps. po_Headers_all pha,
apps. po_lines_all pla
WHERE pha. vendor_order_num LIKE '%SA%'
AND pha. attribute15 NOT LIKE '%X'
AND pha. cancel_flag <> 'Y'
and pla. po_header_Id = pha. po_Header_id
and Pha. po_header_id not in (harcode list of PO header IDs returned by the previous query.. they should not be included here.)
AND NOT EXISTS
(SELECT 1
FROM apps. rcv_shipment_lines rsl
WHERE pha. po_header_id = rsl. po_Header_id
and pla. po_line_id = rsl. po_line_id
)ORDER BY creation_date DESC

INV period closing Important Queries R12

1.UNPROCESSED MATERIAL
2.UNCOSTED MATERIAL
3.PENDING WIP COSTING
4.UNCOSTED WSM TRANSACTIONS
5.PENDING WSM INTERFACE
6.PENDING SHIPPING TRANSACTIONS
7.PENDING RECEIVING (Interface Errors)
8. PENDING MATERIAL
9.PENDING SHOP FLOOR MOVE
10. INCOMPLETE WORK ORDERS
11.FAILED CLOSE JOBS
12.COMPLETE JOBS
13. RECEIPT PENDING
14.PENDING CLOSE JOBS
15. RMA PENDING RECEIPTS
16.PENDING RECEIVING ACCOUNTING



1.UNPROCESSED MATERIAL
  ====================

select mtlp.organization_code
       ,count(*)
FROM   apps.mtl_material_transactions_temp mmtt,
       apps.mtl_transaction_lots_temp      mtlt,
       apps.mtl_serial_numbers_temp        msnt,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  --period_name = upper('&period')
period_name = upper(:period)
AND    oap.acct_period_id  = mmtt.acct_period_id
and    mmtt.organization_id = mtlp.organization_id
and    mmtt.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
AND    nvl(transaction_status,0) <> 2
AND    (mtlt.transaction_temp_id (+)     = mmtt.transaction_temp_id
AND msnt.transaction_temp_id (+) = mmtt.transaction_temp_id)
group by mtlp.organization_code
ORDER BY mtlp.organization_code

2.UNCOSTED MATERIAL
  =================

select mtlp.organization_code
       ,count(*)
FROM   apps.mtl_material_transactions mmt,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  --period_name = upper('&period')
period_name = upper(:period)
AND    oap.acct_period_id  = mmt.acct_period_id
and    mmt.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    mmt.organization_id = mtlp.organization_id
AND    COSTED_FLAG IS NOT NULL
group by mtlp.organization_code
ORDER BY mtlp.organization_code

3.PENDING WIP COSTING
  ===================

select mtlp.organization_code
       ,count(*)
FROM   apps.wip_cost_txn_interface wcti,
       apps.wip_txn_interface_errors wtie,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  period_name = upper('&period')
AND    oap.acct_period_id  = wcti.acct_period_id
and    wcti.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    wcti.organization_id = mtlp.organization_id
AND    wtie.transaction_id (+) = wcti.transaction_id
  group by mtlp.organization_code
ORDER BY mtlp.organization_code

4.UNCOSTED WSM TRANSACTIONS
  =========================

select mtlp.organization_code
       ,count(*)
FROM   apps.WSM_SPLIT_MERGE_TRANSACTIONS wsmt,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  --oap.period_name = upper('&period')
oap.period_name = upper(:period)
and    wsmt.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
AND    COSTED <> 4
and    trunc(transaction_date) < (trunc(to_date(oap.schedule_close_date))+1)
and    wsmt.organization_id = mtlp.organization_id
group by mtlp.organization_code
ORDER BY mtlp.organization_code

5.PENDING WSM INTERFACE
  =====================

select mtlp.organization_code
       ,count(*)
FROM   apps.WSM_SPLIT_MERGE_TXN_INTERFACE wsmt,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  --oap.period_name = upper('&Period')
period_name = upper(:period)
and    wsmt.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
AND    process_status <> 4
and    trunc(transaction_date) < (trunc(to_date(oap.schedule_close_date))+1)
and    wsmt.organization_id = mtlp.organization_id
group by mtlp.organization_code
ORDER BY mtlp.organization_code

6.PENDING SHIPPING TRANSACTIONS
  =============================

select mtlp.organization_code
       ,count(*)
from   apps.wsh_delivery_details     wdd,
       apps.wsh_delivery_assignments wda,
       apps.wsh_new_deliveries       wnd,
       apps.wsh_delivery_legs        wdl,
       apps.wsh_trip_stops           wts,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
where  wdd.source_code         = 'OE'
and    wdd.released_status     = 'C'
and    wdd.inv_interfaced_flag in ('N' ,'P')
--and    oap.period_name = upper('&Period')
and    OAP.period_name = upper(:period)
and    wda.delivery_detail_id  = wdd.delivery_detail_id
and    wnd.delivery_id         = wda.delivery_id
and    wnd.status_code in      ('CL','IT')
and    wdl.delivery_id         = wnd.delivery_id
and    wdd.organization_id = mtlp.organization_id
and    wdd.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    trunc(wts.actual_departure_date) between to_date(oap.period_start_date)
and    to_date(oap.schedule_close_date)
and    wdl.pick_up_stop_id     = wts.stop_id
group by mtlp.organization_code
ORDER BY mtlp.organization_code

7.PENDING RECEIVING (Interface Errors)
  ====================================

select mtlp.organization_code
       ,count(*)
FROM   apps.rcv_transactions_interface rcv,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE--  oap.period_name = upper('&Period')
oap.period_name = upper(:period)
and    rcv.to_organization_id = oap.organization_id
and    mtlp.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    trunc(transaction_date) < (trunc(to_date(oap.schedule_close_date))+1)
and destination_type_code = 'INVENTORY'
group by mtlp.organization_code
ORDER BY mtlp.organization_code

8. PENDING MATERIAL
   ================

select mtlp.organization_code
       ,count(*)
from   apps.mtl_transactions_interface     mti,
       apps.mtl_serial_numbers_interface   msni,
       apps.mtl_transaction_lots_interface mtli,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
where  oap.period_name = upper(:period)
and    mti.organization_id = oap.organization_id
and    mtlp.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
AND    (mti.ACCT_PERIOD_ID  = oap.acct_period_id
        OR (mti.ACCT_PERIOD_ID IS NULL
       AND TRUNC(TRANSACTION_DATE) < (trunc(to_date(oap.schedule_close_date))+1)))
AND    mti.PROCESS_FLAG <> 9
AND    (mtli.transaction_interface_id (+)     = mti.transaction_interface_id
        AND msni.transaction_interface_id (+) = mti.transaction_interface_id)
group  BY mtlp.organization_code
ORDER BY mtlp.organization_code


9.PENDING SHOP FLOOR MOVE
  =======================

select mtlp.organization_code
       ,count(*)
from   apps.wip_move_txn_interface   wmti,
       apps.wip_txn_interface_errors wtie,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
where  --oap.period_name = upper('&Period')
oap.period_name = upper(:period)
and    wmti.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    mtlp.organization_id = wmti.organization_id
AND   (wmti.ACCT_PERIOD_ID  = oap.acct_period_id
        OR (wmti.ACCT_PERIOD_ID IS NULL
AND TRUNC(TRANSACTION_DATE) < (TRUNC(to_date(oap.schedule_close_date))+ 1)))
AND   wtie.transaction_id (+) = wmti.transaction_id
group  BY mtlp.organization_code
ORDER BY mtlp.organization_code


10. INCOMPLETE WORK ORDERS
    ======================
SELECT   mtlp.organization_code,count(*)
    FROM apps.wip_discrete_jobs wdj, apps.wip_entities we, apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE
     mtlp.organization_id = oap.organization_id
     and wdj.organization_id = oap.organization_id
     and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     AND wdj.scheduled_completion_date < (trunc(to_date(oap.schedule_close_date))+1)
     AND wdj.status_type = 3
     AND wdj.wip_entity_id = we.wip_entity_id
     AND wdj.organization_id = we.organization_id
     AND we.entity_type = 6
group by mtlp.organization_code
ORDER BY mtlp.organization_code

11.FAILED CLOSE JOBS
   =================

SELECT   mtlp.organization_code,count(*) COUNT10
    FROM apps.wip_discrete_jobs wdj, apps.wip_entities we, apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE
         mtlp.organization_id = oap.organization_id
     and wdj.organization_id = oap.organization_id
    and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     --AND wdj.scheduled_completion_date < (trunc(to_date(oap.schedule_close_date))+1)
     AND wdj.status_type = 15
     AND wdj.wip_entity_id = we.wip_entity_id
     AND wdj.organization_id = we.organization_id    
group by mtlp.organization_code
ORDER BY mtlp.organization_code

12.COMPLETE JOBS
   =============

SELECT   mtlp.organization_code,count(*) COUNT11
    FROM apps.wip_discrete_jobs wdj, apps.wip_entities we, apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE
         mtlp.organization_id = oap.organization_id
     and wdj.organization_id = oap.organization_id
    and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     AND wdj.scheduled_completion_date < (trunc(to_date(oap.schedule_close_date))+1)
     AND wdj.status_type = 4
     AND wdj.wip_entity_id = we.wip_entity_id
     AND wdj.organization_id = we.organization_id    
group by mtlp.organization_code
ORDER BY mtlp.organization_code

13. RECEIPT PENDING
    ===============
 
SELECT mtlp.organization_code,  count(*) count12
   FROM apps.rcv_transactions rt,
              apps.org_acct_periods oap,
              apps.mtl_parameters mtlp,
              po.rcv_shipment_headers rsh,
              po.rcv_shipment_lines rsl
  WHERE
              rt.TRANSACTION_ID IN
                   (SELECT   rs.RCV_TRANSACTION_ID
                       FROM   apps.rcv_supply rs
                     WHERE   rs.QUANTITY > 0
                           AND rs.to_organization_id =rt.organization_id)
           AND TRUNC (rsh.creation_date) < (trunc(to_date(oap.schedule_close_date))+1)
           AND rt.SHIPMENT_LINE_ID = rsl.shipment_line_id
           AND rsh.shipment_header_id = rsl.shipment_header_id
           and mtlp.organization_id = oap.organization_id
          and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
           and rt.organization_id = oap.organization_id
           and oap.period_name = upper(:period)
           AND NVL (rsl.po_header_id, 0) = NVL (rt.po_header_id, 0)
           AND NVL (rsl.po_line_id, 0) = NVL (rt.po_line_id, 0)
           AND NVL (rsl.po_release_id, 0) = NVL (rt.po_release_id, 0)
           group by mtlp.organization_code
           order by mtlp.organization_code

14.PENDING CLOSE JOBS
   ==================
SELECT   mtlp.organization_code,count(*) COUNT14
    FROM apps.wip_discrete_jobs wdj, apps.wip_entities we, apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE
         mtlp.organization_id = oap.organization_id
     and wdj.organization_id = oap.organization_id
     and oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     AND wdj.scheduled_completion_date < (trunc(to_date(oap.schedule_close_date))+1)
     AND wdj.status_type = 14
     AND wdj.wip_entity_id = we.wip_entity_id
     AND wdj.organization_id = we.organization_id    
group by mtlp.organization_code
ORDER BY mtlp.organization_code

15. RMA PENDING RECEIPTS
    ====================

SELECT Q1.organization_code,COUNT(*) count FROM
(SELECT    oooha.sold_to_org_id party, ooola.line_id,ooola.ship_from_org_id,
         mtlp.organization_code,
         ooola.header_id, arc.customer_name, oooha.order_number rma_no,
         oooha.ordered_date rma_dt,
          oooha.cust_po_number,
         ooola.ordered_item_id,
          ooola.ordered_quantity,
         ooola.order_quantity_uom,
                           (  TO_DATE (SYSDATE, 'DD-MM-RRRR')
          - TO_DATE (ooola.creation_date, 'DD-MM-RRRR')
         ) no_of_days,
         oooha.order_type_id, ooola.flow_status_code,
         ooola.line_category_code
    FROM ont.oe_order_lines_all ooola,
         ont.oe_order_headers_all oooha,        
         ar_customers arc,
         hz_cust_accounts hca,                --Changed by Saugata on 1/7/2010
       --  inv.mtl_system_items_b imsi,        
         ont.oe_transaction_types_tl oottt,
         apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE --oooha.org_id = :p_org_id
         oooha.ship_from_org_id = nvl(:p_organization_id,oooha.ship_from_org_id)
     AND ooola.ship_from_org_id = oooha.ship_from_org_id
     AND oooha.header_id = ooola.header_id
   --  AND ooola.header_id not in (select prsl.oe_order_header_id from po.rcv_shipment_headers prsh,
     --                             po.rcv_shipment_lines prsl
       --                           where ooola.header_id = prsl.oe_order_header_id
         --                         AND  prsl.shipment_header_id = prsh.shipment_header_id)
     --AND ooola.ordered_item_id = imsi.inventory_item_id
     --AND imsi.organization_id = oooha.ship_from_org_id
     AND oooha.sold_to_org_id = arc.customer_id
     AND hca.cust_account_id = arc.customer_id  --Added by Saugata on 1/7/2010
     --AND hca.party_id BETWEEN NVL (:p_start_party, hca.party_id)
       --                   AND NVL (:p_end_party, hca.party_id)
     AND oooha.order_type_id = oottt.transaction_type_id
     --AND oottt.NAME = NVL (:p_order_type, oottt.NAME)
     AND (  TO_DATE (SYSDATE, 'DD-MM-RRRR') - TO_DATE (ooola.creation_date, 'DD-MM-RRRR')
         ) > 0 -- NVL (:p_ageing_days, 0)
     and mtlp.organization_id = oap.organization_id
     and ooola.ship_from_org_id = oap.organization_id
     and oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     AND ooola.creation_date < (trunc(to_date(oap.schedule_close_date))+1)  
     AND ooola.line_category_code = 'RETURN'                    
     AND ooola.flow_status_code IN ('ENTERED', 'AWAITING_RETURN')) Q1
     WHERE NO_OF_DAYS > 45
     GROUP BY organization_code

16.PENDING RECEIVING ACCOUNTING
   ============================

select ood.organization_code,COUNT(*) mcount16
                            --ood.organization_code UNIT, rsh.receipt_num RECEIPT_NUMBER, trunc(rsh.creation_date) receipt_date, trunc(jrt.transaction_date) transaction_date, pv.vendor_name SUPPLIER_NAME, pvsa.vendor_site_code SUPPLIER_SITE,
                            --jrt.organization_id, jrt.inventory_item_id,
                            --msi.segment1 item_code, msi.description, jrt.quantity
                    from apps.jai_rcv_transactions jrt,
                        apps.org_organization_definitions ood,
                        apps.rcv_shipment_headers rsh,
                        apps.rcv_shipment_lines rsl,
                        apps.po_vendors pv,
                        apps.po_vendor_sites_all pvsa,
                        apps.mtl_system_items_b msi
                    where 1=1
                      and msi.organization_id=rsh.ship_to_org_id
                      and msi.inventory_item_id=rsl.item_id
                      and jrt.shipment_line_id=rsl.shipment_line_id
                      and jrt.shipment_line_id = (select distinct shipment_line_id
                                from apps.jai_rcv_line_taxes jrlt
                               where jrlt.shipment_line_id=jrt.shipment_line_id)
                      and rsh.vendor_site_id=pvsa.vendor_site_id
                      and rsh.vendor_id=pv.vendor_id
                      and jrt.shipment_header_id=rsh.shipment_header_id
                      and jrt.organization_id=ood.organization_id
                      AND ood.organization_id = NVL(:P_ORGANIZATION_ID,ood.organization_id)
                    --and jrt.organization_id=89
                      and jrt.transaction_date>='01-APR-2014'
                      and jrt.transaction_type IN ('DELIVER' ,'RECEIVE')
                      and  jrt.process_message is null
                    GROUP BY organization_code;