SELECT DISTINCT
AILA.LINE_TYPE_LOOKUP_CODE LINE_TYPE
, AILA.DESCRIPTION INV_LINE_DESCRIPTION
, (
SELECT DISTINCT
SEGMENT1
FROM
PO_HEADERS_ALL PHA
WHERE
AILA.PO_HEADER_ID=PHA.PO_HEADER_ID
)
PO_NUM
, (
SELECT DISTINCT
TYPE_LOOKUP_CODE
FROM
PO_HEADERS_ALL PHA
WHERE
AILA.PO_HEADER_ID=PHA.PO_HEADER_ID
)
PO_TYPE
, (
SELECT DISTINCT
COMMENTS
FROM
PO_HEADERS_ALL PHA
WHERE
AILA.PO_HEADER_ID=PHA.PO_HEADER_ID
)
PO_DESCRIPTION
, (
SELECT DISTINCT
CREATION_DATE
FROM
PO_HEADERS_ALL PHA
WHERE
AILA.PO_HEADER_ID=PHA.PO_HEADER_ID
)
PO_CREATION_DATE
, NULL NULL_RECEIPT_NUM
, (
SELECT DISTINCT
LOCATION_CODE
FROM
HR_LOCATIONS HL
WHERE
AILA.SHIP_TO_LOCATION_ID=HL.LOCATION_ID
)
SHIP_TO
, NULL QTY_AP_LINES
, NULL RATE_AP_LINES
, AILA.AMOUNT LINE_AMOUNT
, APDA.LINE_TYPE_LOOKUP_CODE DIST_TYPE
, APDA.DESCRIPTION DIST_DESCRIPTION
, (
SELECT DISTINCT
CONCATENATED_SEGMENTS
FROM
GL_CODE_COMBINATIONS_KFV
WHERE
APDA.DIST_CODE_COMBINATION_ID=CODE_COMBINATION_ID
)
CHARGE_ACCOUNT
, APDA.AMOUNT DIST_AMOUNT
, (
SELECT DISTINCT
SEGMENT1
FROM
PA_PROJECTS
WHERE
PROJECT_ID=APDA.PROJECT_ID
)
PROJECT
, (
SELECT DISTINCT
NAME
FROM
PA_PROJECTS
WHERE
PROJECT_ID=APDA.PROJECT_ID
)
PROJECT_NAME
, (
SELECT DISTINCT
DESCRIPTION
FROM
PA_PROJECTS
WHERE
PROJECT_ID=APDA.PROJECT_ID
)
PROJECT_DESC
, (
SELECT DISTINCT
TASK_NAME
FROM
PA_TASKS
WHERE
TASK_ID=APDA.TASK_ID
)
TASK
, APDA.EXPENDITURE_TYPE
, (
SELECT DISTINCT
NAME
FROM
HR_ORGANIZATION_UNITS
WHERE
APDA.EXPENDITURE_ORGANIZATION_ID=ORGANIZATION_ID
)
EXPENDITURE_ORGANIZATION
, TO_CHAR(APDA.ACCOUNTING_DATE,'DD-MON-YYYY') ACCOUNTING_DATE
FROM
AP_INVOICES_ALL AIA
, AP_INVOICE_LINES_ALL AILA
, AP_INVOICE_DISTRIBUTIONS_ALL APDA
WHERE
APDA.INVOICE_ID(+) = AILA.INVOICE_ID
AND AIA.INVOICE_ID =AILA.INVOICE_ID
AND AIA.INVOICE_ID =NVL(:P_INVOICE_NUM,AIA.INVOICE_NUM)
AND AIA.INVOICE_DATE BETWEEN NVL(TO_DATE(:P_DATE_FROM,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE) AND NVL(TO_DATE(:P_DATE_TO,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE)
