Monday, November 5, 2012

Orders created from the Spares Management Queries for alerts

Orders created from the Spares Management Queries




SELECT B.requirement_header_id,a.ORDER_LINE_ID,  a.ORDER_BY_DATE, a.* FROM CSP_REQUIREMENT_LINES A , CSP_REQUIREMENT_HEADERS B  WHERE
A.REQUIREMENT_HEADER_ID = B.REQUIREMENT_HEADER_ID
AND B.ORDER_TYPE_ID = <XXX>
order by   a.ORDER_BY_DATE desc;

select OH.ORIG_SYS_DOCUMENT_REF
       ,OH.ORDER_NUMBER
       ,OH.CREATION_DATE
       ,PPF.FULL_NAME
       ,FU.EMPLOYEE_ID
from OE_ORDER_HEADERS_ALL OH
     ,PO_REQUISITION_HEADERS_ALL POH
     ,FND_USER FU
     ,PER_ALL_PEOPLE_F PPF
     where 1                    =1
and OH.ORDER_TYPE_ID       =xxx0
and OH.SOURCE_DOCUMENT_ID  = POH.REQUISITION_HEADER_ID
and POH.TYPE_LOOKUP_CODE   = 'INTERNAL'
and POH.INTERFACE_SOURCE_CODE   = 'CSP'
and FU.USER_ID                  =OH.CREATED_BY
and FU.EMPLOYEE_ID              =PPF.PERSON_ID
and TRUNC(sysdate) between TRUNC(PPF.EFFECTIVE_START_DATE) and TRUNC(PPF.EFFECTIVE_END_DATE)
and TRUNC(OH.CREATION_DATE)     = TRUNC(sysdate)

No comments:

Post a Comment