PO Requisitions (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Purchase Order (PO-Fusion-SQL's)
1

This PO Requisitions query retrieves requisition numbers along with the names of the operating units associated with each requisition. It helps in identifying which operating unit made each requisition. This data is useful for tracking requisitions across different business units or departments.

#sqlquery

select
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves requisition numbers along with their operating unit names.
  PRHA.REQUISITION_NUMBER,
  PRHA.DESCRIPTION REQUISITION_DESCRIPTION,
  PRHA.DOCUMENT_STATUS DOC_STATUS,
  PRHA.DOCUMENT_SUB_TYPE,
  PRHA.ATTRIBUTE_CATEGORY,
  PRHA.FUNDS_STATUS,
  PRHA.INSUFFICIENT_FUNDS_FLAG,
  PRLA.LINE_NUMBER,
  PRLA.ITEM_DESCRIPTION,
  PRLA.RATE_TYPE,
  PRLA.RATE_DATE,
  PRLA.RATE,
  PRLA.ITEM_SOURCE,
  PRLA.PRODUCT_CATEGORY,
  PRLA.PRODUCT_TYPE,
  PRLA.LINE_STATUS,
  HROU.NAME AS OPERATING_UNIT,
  GL.NAME LEDGER_NAME,
  PRLA.CURRENCY_CODE,
  PRLA.QUANTITY,
  PRLA.UNIT_PRICE,
  PRLA.AMOUNT LINE_AMOUNT,
  PRLA.CURRENCY_AMOUNT,
  PRDA.DISTRIBUTION_AMOUNT
from
  POR_REQ_DISTRIBUTIONS_ALL             PRDA
  ,POR_REQUISITION_LINES_ALL            PRLA
  ,POR_REQUISITION_HEADERS_ALL          PRHA
  ,PO_DOCUMENT_TYPES_ALL_B              PDT
  ,GL_LEDGERS                           GL
  ,PER_ALL_PEOPLE_F                     PAPF
  ,HR_ALL_ORGANIZATION_UNITS            HROU
WHERE 1=1
  AND PRDA.REQUISITION_LINE_ID          = PRLA.REQUISITION_LINE_ID
  AND PRLA.REQUISITION_HEADER_ID        = PRHA.REQUISITION_HEADER_ID
  AND PDT.DOCUMENT_SUBTYPE              = 'STANDARD'
  AND PDT.PRC_BU_ID                     = PRHA.REQ_BU_ID
  AND PRDA.PRIMARY_LEDGER_ID            = GL.LEDGER_ID
  AND PRHA.PREPARER_ID                  = PAPF.PERSON_ID(+)
  AND PAPF.EFFECTIVE_START_DATE(+)      <= TRUNC(SYSDATE)
  AND PAPF.EFFECTIVE_END_DATE(+)        >= TRUNC(SYSDATE)
  AND PRHA.REQ_BU_ID                    = HROU.ORGANIZATION_ID