Query to retrieve the AP invoices from Oracle Expenses and other sources.

SELECT DISTINCT
APIA.INVOICE_ID,
HROU.NAME as "Business Unit", --Business Unit
APIA.PRODUCT_TABLE,
APIA.INVOICE_NUM,
APIA.INVOICE_AMOUNT,
APIA.INVOICE_DATE,
HP.PARTY_NAME "Supplier Name", --Supplier
APIA.VENDOR_ID,
PSS.VENDOR_SITE_CODE "Supplier Site Name", -- Site Name
APIA.INVOICE_CURRENCY_CODE,
APIA.PAYMENT_CURRENCY_CODE,
APIA.DESCRIPTION,
APIA.INVOICE_TYPE_LOOKUP_CODE,
APT.NAME "Payment Terms",
APIA.TERMS_DATE,
APIA.GL_DATE,
APIA.PAYMENT_METHOD_CODE,
APIA.PAY_GROUP_LOOKUP_CODE,
APIA.MRC_EXCHANGE_RATE,
APIA.MRC_EXCHANGE_RATE_TYPE,
APIA.MRC_EXCHANGE_DATE,
APIA.ACCTS_PAY_CODE_COMBINATION_ID,
APIA.SETTLEMENT_PRIORITY,
APIA.PAYMENT_STATUS_FLAG,
GLCC.SEGMENT1||' - '||GLCC.SEGMENT2 ||' - ' ||GLCC.SEGMENT3||' - ' ||GLCC.SEGMENT4||' - ' ||GLCC.SEGMENT5||' - ' ||GLCC.SEGMENT6||' - ' ||GLCC.SEGMENT7 "Liability Code Combination",
APILA.LINE_NUMBER,
APILA.LINE_TYPE_LOOKUP_CODE,
APILA.AMOUNT "Line Amount",
APILA.QUANTITY_INVOICED,
APILA.UNIT_MEAS_LOOKUP_CODE,
APILA.DESCRIPTION "Line Description",
APILA.ITEM_DESCRIPTION

FROM
AP_INVOICES_ALL APIA,
POZ_SUPPLIERS PS,
HZ_PARTIES HP,
HR_ORGANIZATION_UNITS HROU,
POZ_SUPPLIER_SITES_ALL_M PSS,
AP_TERMS_TL APT,
GL_CODE_COMBINATIONS GLCC,
AP_INVOICE_LINES_ALL APILA

WHERE
APIA.VENDOR_ID>=0
AND PS.VENDOR_ID=APIA.VENDOR_ID
AND PS.PARTY_ID=HP.PARTY_ID
AND HROU.ORGANIZATION_ID=APIA.ORG_ID
AND APIA.VENDOR_SITE_ID=PSS.VENDOR_SITE_ID
AND APT.TERM_ID=APIA.TERMS_ID
AND GLCC.CODE_COMBINATION_ID=APIA.ACCTS_PAY_CODE_COMBINATION_ID
AND APIA.PAYMENT_STATUS_FLAG <> 'Y'
AND APIA.INVOICE_ID=APILA.INVOICE_ID
AND APIA.APPROVAL_STATUS <> 'CANCELLED'
--AND APIA.INVOICE_NUM like 'XXXXXXX'
--APIA.INVOICE_NUM like 'XXXXXX'

UNION

SELECT DISTINCT
APIA.INVOICE_ID,
HROU.NAME as "Business Unit", --Business Unit
APIA.PRODUCT_TABLE,
APIA.INVOICE_NUM,
APIA.INVOICE_AMOUNT,
APIA.INVOICE_DATE,
HP.PARTY_NAME "Supplier Name", --Supplier
APIA.VENDOR_ID,
NULL "Supplier Site Name", -- Site Name
APIA.INVOICE_CURRENCY_CODE,
APIA.PAYMENT_CURRENCY_CODE,
APIA.DESCRIPTION,
APIA.INVOICE_TYPE_LOOKUP_CODE,
APT.NAME "Payment Terms",
APIA.TERMS_DATE,
APIA.GL_DATE,
APIA.PAYMENT_METHOD_CODE,
APIA.PAY_GROUP_LOOKUP_CODE,
APIA.MRC_EXCHANGE_RATE,
APIA.MRC_EXCHANGE_RATE_TYPE,
APIA.MRC_EXCHANGE_DATE,
APIA.ACCTS_PAY_CODE_COMBINATION_ID,
APIA.SETTLEMENT_PRIORITY,
APIA.PAYMENT_STATUS_FLAG,
GLCC.SEGMENT1||' - '||GLCC.SEGMENT2 ||' - ' ||GLCC.SEGMENT3||' - ' ||GLCC.SEGMENT4||' - ' ||GLCC.SEGMENT5||' - ' ||GLCC.SEGMENT6||' - ' ||GLCC.SEGMENT7 "Liability Code Combination",
APILA.LINE_NUMBER,
APILA.LINE_TYPE_LOOKUP_CODE,
APILA.AMOUNT "Line Amount",
APILA.QUANTITY_INVOICED,
APILA.UNIT_MEAS_LOOKUP_CODE,
APILA.DESCRIPTION "Line Description",
APILA.ITEM_DESCRIPTION

FROM
AP_INVOICES_ALL APIA,
HZ_PARTIES HP,
HR_ORGANIZATION_UNITS HROU,
AP_TERMS_TL APT,
GL_CODE_COMBINATIONS GLCC,
AP_INVOICE_LINES_ALL APILA

WHERE
APIA.VENDOR_ID<0
AND APIA.PARTY_ID=HP.PARTY_ID
AND HROU.ORGANIZATION_ID=APIA.ORG_ID
AND APT.TERM_ID=APIA.TERMS_ID
AND GLCC.CODE_COMBINATION_ID=APIA.ACCTS_PAY_CODE_COMBINATION_ID
AND APIA.PAYMENT_STATUS_FLAG <> 'Y'
AND APIA.INVOICE_ID=APILA.INVOICE_ID
AND APIA.APPROVAL_STATUS <> 'CANCELLED'
--AND APIA.INVOICE_NUM like 'XXXXXX'
--AND APIA.PRODUCT_TABLE is null

Query to get the status of the Sales Order in Oracle Fusion

SELECT DISTINCT
DHA.ORDER_NUMBER,
DLA.LINE_NUMBER || DFLA.FULFILL_LINE_NUMBER ,
DST.DISPLAY_NAME "STATUS",
DFLA.STATUS_CODE "LINE STATUS",
DFLA.ORDERED_QTY,
DFLA.UNIT_SELLING_PRICE,
DFLA.EXTENDED_AMOUNT,
ESIL.DESCRIPTION

FROM
DOO_HEADERS_ALL DHA,
DOO_STATUSES_B DSB,
DOO_STATUSES_TL DST,
DOO_LINES_ALL DLA,
DOO_FULFILL_LINES_ALL  DFLA,
EGP_SYSTEM_ITEMS_TL ESIL

WHERE
DSB.STATUS_CODE=DHA.STATUS_CODE
AND DST.STATUS_ID=DSB.STATUS_ID
AND DHA.HEADER_ID=DLA.HEADER_ID
AND DFLA.LINE_ID=DLA.LINE_ID
AND ESIL.INVENTORY_ITEM_ID=DFLA.INVENTORY_ITEM_ID
AND DHA.OPEN_FLAG='Y'
AND DST.LANGUAGE='US'
AND ESIL.LANGUAGE='US'

ORDER BY DHA.ORDER_NUMBER, DLA.LINE_NUMBER

Query to get the status of the Transfer Order

SELECT DISTINCT
ITOH.HEADER_NUMBER "TRANSFER ORDER NUMBER",
HROU1.NAME "SOURCE ORGANIZATION",
ITOL.LINE_NUMBER "LINE NUMBER",
ITOL.STATUS_LOOKUP "LINE STATUS",
ITOL.REQUESTED_QTY "REQUESTED QTY",
NVL(ITOL.SHIPPED_QTY, 0) "SHIPPED QTY",
NVL(ITOL.RECEIVED_QTY, 0) "RECEIVED QTY",
NVL(ITOL.DELIVERED_QTY, 0) "DELIVERED QTY",
ITOL.QTY_UOM_CODE "UOM",
CASE
WHEN ITOL.STATUS_LOOKUP='CLOSED' THEN 'Closed'
WHEN ITOL.STATUS_LOOKUP='CANCELED' THEN 'Canceled'
WHEN (ITOL.SHIPPED_QTY=ITOL.RECEIVED_QTY) THEN 'Partially Shipped and Partially Received'
WHEN NVL(ITOL.SHIPPED_QTY, 0)=0 THEN 'Awaiting Fulfillment'
WHEN (ITOL.REQUESTED_QTY-ITOL.SHIPPED_QTY)=0 THEN 'Fulfilled'
WHEN (ITOL.REQUESTED_QTY-ITOL.SHIPPED_QTY)>0 THEN 'Partially Shipped'
END
STATUS,
FLV.MEANING "INTERFACE STATUS",
ITOL.SOURCE_TYPE_LOOKUP,
HROU2.NAME "DESTINATION ORGANIZATION"

from
INV_TRANSFER_ORDER_HEADERS ITOH,
INV_TRANSFER_ORDER_LINES ITOL,
HR_ORGANIZATION_UNITS HROU1,
HR_ORGANIZATION_UNITS HROU2,
FND_LOOKUP_VALUES FLV

WHERE
ITOL.HEADER_ID=ITOH.HEADER_ID
AND FLV.LOOKUP_CODE=ITOL.INTERFACE_STATUS_LOOKUP
AND FLV.SOURCE_LANG='US'
AND HROU1.ORGANIZATION_ID=ITOL.SOURCE_ORGANIZATION_ID
AND HROU2.ORGANIZATION_ID=ITOL.DESTINATION_ORGANIZATION_ID
AND ITOL.STATUS_LOOKUP like :LOV||'%'

ORDER BY ITOH.HEADER_NUMBER, ITOL.LINE_NUMBER

Query to get the AP invoices from the Oracle Fusion

This query doesn't extract the AP invoice that are created through Expense module/others (stay tuned)..

SELECT DISTINCT
APIA.INVOICE_ID,
HROU.NAME as "Business Unit", --Business Unit
APIA.PRODUCT_TABLE,
APIA.INVOICE_NUM,
APIA.INVOICE_AMOUNT,
APIA.INVOICE_DATE,
HP.PARTY_NAME "Supplier Name", --Supplier
APIA.VENDOR_ID,
PSS.VENDOR_SITE_CODE "Supplier Site Name", -- Site Name
APIA.INVOICE_CURRENCY_CODE,
APIA.PAYMENT_CURRENCY_CODE,
APIA.DESCRIPTION,
APIA.INVOICE_TYPE_LOOKUP_CODE,
APT.NAME "Payment Terms",
APIA.TERMS_DATE,
APIA.GL_DATE,
APIA.PAYMENT_METHOD_CODE,
APIA.PAY_GROUP_LOOKUP_CODE,
APIA.MRC_EXCHANGE_RATE,
APIA.MRC_EXCHANGE_RATE_TYPE,
APIA.MRC_EXCHANGE_DATE,
APIA.ACCTS_PAY_CODE_COMBINATION_ID,
APIA.SETTLEMENT_PRIORITY,
APIA.PAYMENT_STATUS_FLAG,
GLCC.SEGMENT1||' - '||GLCC.SEGMENT2 ||' - ' ||GLCC.SEGMENT3||' - ' ||GLCC.SEGMENT4||' - ' ||GLCC.SEGMENT5||' - ' ||GLCC.SEGMENT6||' - ' ||GLCC.SEGMENT7 "Liability Code Combination",
--' - ' ||GLCC.SEGMENT8||' - ' ||GLCC.SEGMENT9||' - ' ||GLCC.SEGMENT10
--APILA.INVOICE_ID,
--APIA.INVOICE_NUM,
--APIA.INVOICE_AMOUNT,
APILA.LINE_NUMBER,
APILA.LINE_TYPE_LOOKUP_CODE,
APILA.AMOUNT "Line Amount",
APILA.QUANTITY_INVOICED,
APILA.UNIT_MEAS_LOOKUP_CODE,
APILA.DESCRIPTION "Line Description",
APILA.ITEM_DESCRIPTION

FROM
AP_INVOICES_ALL APIA,
POZ_SUPPLIERS PS,
HZ_PARTIES HP,
HR_ORGANIZATION_UNITS HROU,
POZ_SUPPLIER_SITES_ALL_M PSS,
AP_TERMS_TL APT,
GL_CODE_COMBINATIONS GLCC,
AP_INVOICE_LINES_ALL APILA

WHERE
 PS.VENDOR_ID=APIA.VENDOR_ID
AND PS.PARTY_ID=HP.PARTY_ID
AND HROU.ORGANIZATION_ID=APIA.ORG_ID
AND APIA.VENDOR_SITE_ID=PSS.VENDOR_SITE_ID
AND APT.TERM_ID=APIA.TERMS_ID
AND GLCC.CODE_COMBINATION_ID=APIA.ACCTS_PAY_CODE_COMBINATION_ID
AND APIA.PAYMENT_STATUS_FLAG <> 'Y'
AND APIA.INVOICE_ID=APILA.INVOICE_ID
--APIA.INVOICE_NUM like 'XXXXXXX'

ORDER BY APIA.INVOICE_NUM, APILA.LINE_NUMBER

Query to get the Status of the Sales Order.

SELECT DISTINCT
DHA.ORDER_NUMBER,
--DHA.CREATION_DATE,
DHA.SOURCE_ORDER_SYSTEM,
DHA.SOURCE_ORDER_NUMBER,
DHA.ORG_ID,
DHA.SOLD_TO_CUSTOMER_ID,
--DHA.ORDERED_DATE,
DHA.CUSTOMER_PO_NUMBER,
DHA.OPEN_FLAG,
DHA.ON_HOLD,
DHA.CANCELED_FLAG,
--DHA.STATUS_CODE,
DST.DISPLAY_NAME


FROM
DOO_HEADERS_ALL DHA,
DOO_STATUSES_B DSB,
DOO_STATUSES_TL DST

WHERE
 DSB.STATUS_CODE=DHA.STATUS_CODE
AND DST.STATUS_ID=DSB.STATUS_ID

Query to get the PO header details in Oracle Fusion

SELECT DISTINCT
POHA.PO_HEADER_ID,
POHA.SEGMENT1,
HAOT.NAME as PRC_BU_ID,
HAOT.NAME as REQ_BU_ID,
HAOT.NAME as BILLTO_BU_ID,
XEP.NAME as SOLDTO_LE_ID,
HRLA1.LOCATION_NAME as BILL_TO_LOCATION_ID,
HRLA.LOCATION_NAME as SHIP_TO_LOCATION_ID,
--HRLA.LOCATION_NAME,
HP.PARTY_NAME as VENDOR_NAME,
--HPS.PARTY_SITE_NAME,
--PSS.VENDOR_SITE_CODE,
APT.NAME,
PPNF.LAST_NAME||', '||PPNF.FIRST_NAME "Buyer Name",
POHA.DOCUMENT_STATUS,
PSS.VENDOR_SITE_CODE

FROM
PO_HEADERS_ALL POHA,
HR_ORGANIZATION_UNITS_F_TL HAOT,
XLE_ENTITY_PROFILES XEP,
HR_LOCATIONS_ALL HRLA,
HR_LOCATIONS_ALL HRLA1,
POZ_SUPPLIERS PS,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
AP_TERMS_TL APT,
PER_PERSON_NAMES_F PPNF,
POZ_SUPPLIER_SITES_ALL_M PSS

WHERE
    HAOT.ORGANIZATION_ID = POHA.BILLTO_BU_ID
AND HAOT.ORGANIZATION_ID = POHA.PRC_BU_ID
AND HAOT.ORGANIZATION_ID = POHA.REQ_BU_ID
AND XEP.LEGAL_ENTITY_ID = POHA.SOLDTO_LE_ID
AND HRLA.LOCATION_ID=POHA.SHIP_TO_LOCATION_ID
AND HRLA1.LOCATION_ID=POHA.BILL_TO_LOCATION_ID
AND PS.VENDOR_ID=POHA.VENDOR_ID
AND PS.PARTY_ID=HP.PARTY_ID
AND HP.PARTY_ID=HPS.PARTY_ID
AND APT.TERM_ID=POHA.TERMS_ID
AND PPNF.PERSON_ID=POHA.AGENT_ID
AND PSS.VENDOR_SITE_ID=POHA.VENDOR_SITE_ID
--AND POHA.SEGMENT1 like 'XXXXXX'
AND POHA.DOCUMENT_STATUS <>'CLOSED'
AND POHA.DOCUMENT_STATUS <>'CANCELED'
AND POHA.DOCUMENT_STATUS <>'CLOSED FOR RECEIVING'
AND POHA.DOCUMENT_STATUS <>'INCOMPLETE'
AND POHA.DOCUMENT_STATUS <>'CLOSED FOR INVOICING'

ORDER BY POHA.SEGMENT1

Query for the Open Purchase Orders (PO's) line level details in Oracle Fusion

SELECT DISTINCT
POHA.PO_HEADER_ID,
POHA.SEGMENT1,
POLA.LINE_NUM,
POLT.LINE_TYPE,
ESIT.DESCRIPTION,
POLA.QUANTITY,
POLA.UNIT_PRICE

FROM
PO_HEADERS_ALL POHA,
PO_LINES_ALL POLA,
PO_LINE_TYPES_TL POLT,
EGP_SYSTEM_ITEMS_TL ESIT,
PO_LINE_LOCATIONS_ALL POLLA

WHERE
 POHA.PO_HEADER_ID=POLA.PO_HEADER_ID
AND POLA.LINE_TYPE_ID=POLT.LINE_TYPE_ID
AND ESIT.INVENTORY_ITEM_ID=POLA.ITEM_ID
--AND POHA.SEGMENT1 like 'XXXXXXXX'
AND POHA.DOCUMENT_STATUS <>'CLOSED'
AND POHA.DOCUMENT_STATUS <>'CANCELED'
AND POHA.DOCUMENT_STATUS <>'CLOSED FOR RECEIVING'
AND POHA.DOCUMENT_STATUS <>'INCOMPLETE'
AND POHA.DOCUMENT_STATUS <>'CLOSED FOR INVOICING'

ORDER BY POHA.SEGMENT1

Query for the Open Purchase Orders (PO's) line location level details in Oracle Fusion

SELECT DISTINCT
POHA.PO_HEADER_ID,
POHA.SEGMENT1,
POLA.LINE_NUM,
POLLA.NEED_BY_DATE,
POLLA.PROMISED_DATE

FROM
PO_LINE_LOCATIONS_ALL POLLA,
PO_LINES_ALL POLA,
PO_HEADERS_ALL POHA

WHERE
 POHA.PO_HEADER_ID=POLA.PO_HEADER_ID
AND POLA.PO_LINE_ID=POLLA.PO_LINE_ID
--AND POHA.SEGMENT1 like 'XXXXX'
AND POHA.DOCUMENT_STATUS <>'CLOSED'
AND POHA.DOCUMENT_STATUS <>'CANCELED'
AND POHA.DOCUMENT_STATUS <>'CLOSED FOR RECEIVING'
AND POHA.DOCUMENT_STATUS <>'INCOMPLETE'
AND POHA.DOCUMENT_STATUS <>'CLOSED FOR INVOICING'

ORDER BY POHA.SEGMENT1

Query for the Open Purchase Orders (PO's) distribution details in Oracle Fusion

SELECT DISTINCT
POHA.PO_HEADER_ID,
POHA.SEGMENT1,
POLA.LINE_NUM,
PODA.QUANTITY_ORDERED,
PODA.QUANTITY_DELIVERED,
PODA.AMOUNT_BILLED,
PODA.QUANTITY_ORDERED - PODA.QUANTITY_DELIVERED as NEW_PO_TO_BE_CREATED,
PODA.PJC_PROJECT_ID,
PODA.PJC_TASK_ID,
PODA.PJC_EXPENDITURE_ITEM_DATE,
PODA.PJC_ORGANIZATION_ID,
PODA.PJC_BILLABLE_FLAG

FROM
PO_HEADERS_ALL POHA,
PO_LINES_ALL POLA,
PO_LINE_LOCATIONS_ALL POLLA,
PO_DISTRIBUTIONS_ALL PODA

WHERE
 POHA.PO_HEADER_ID=POLA.PO_HEADER_ID
AND POLA.PO_LINE_ID=POLLA.PO_LINE_ID
AND POLLA.LINE_LOCATION_ID=PODA.LINE_LOCATION_ID
--AND POHA.SEGMENT1 like 'P2'
AND POHA.DOCUMENT_STATUS <>'CLOSED'
AND POHA.DOCUMENT_STATUS <>'CANCELED'
AND POHA.DOCUMENT_STATUS <>'CLOSED FOR RECEIVING'
AND POHA.DOCUMENT_STATUS <>'INCOMPLETE'
AND POHA.DOCUMENT_STATUS <>'CLOSED FOR INVOICING'

ORDER BY POHA.SEGMENT1