View: PA_DRAFT_INVOICE_LINES_V

Product:	PA - Projects
Description:	10SC Only
Implementation/DBA Data:	APPS.PA_DRAFT_INVOICE_LINES_V

View Text

SELECT I.ROWID ROW_ID
, I.PROJECT_ID
, I.DRAFT_INVOICE_NUM
, I.LINE_NUM
, I.TEXT
, I.TAXABLE_FLAG
, I.AMOUNT
, I.INV_AMOUNT
, I.UNEARNED_REVENUE_CR
, I.UNBILLED_RECEIVABLE_DR
, I.TASK_ID
, I.EVENT_TASK_ID
, I.EVENT_NUM
, EV.EVENT_ID
, I.SHIP_TO_ADDRESS_ID
, I.INVOICE_LINE_TYPE
, I.DRAFT_INV_LINE_NUM_CREDITED
, I.LAST_UPDATE_DATE
, I.LAST_UPDATED_BY
, I.LAST_UPDATE_LOGIN
, I.CREATION_DATE
, I.CREATED_BY
, I.PROGRAM_APPLICATION_ID
, I.PROGRAM_ID
, I.PROGRAM_UPDATE_DATE
, I.REQUEST_ID
, T.TASK_NUMBER EVENT_TASK_NUMBER
, EV.COMPLETION_DATE
, DECODE(HZ_L.CITY
, NULL
, NULL
, HZ_L.CITY || '
, ') || HZ_L.STATE || ' '|| HZ_L.POSTAL_CODE WORK_SITE
, TSK.TASK_NUMBER
, I.OUTPUT_TAX_EXEMPT_FLAG
, I.OUTPUT_TAX_EXEMPT_REASON_CODE
, I.OUTPUT_TAX_EXEMPT_NUMBER
, I.OUTPUT_TAX_CLASSIFICATION_CODE
, FND_LK.MEANING
, FND_LK1.MEANING
, FND_LK2.MEANING
, I.ACCT_AMOUNT
, I.ROUNDING_AMOUNT
, I.UNBILLED_ROUNDING_AMOUNT_DR
, I.UNEARNED_ROUNDING_AMOUNT_CR
, I.TRANSLATED_TEXT
, I.CC_PROJECT_ID
, I.CC_TAX_TASK_ID
, I.CC_REV_CODE_COMBINATION_ID
, EV.BILL_TRANS_CURRENCY_CODE
, I.INVPROC_CURRENCY_CODE
, DECODE(I.INVPROC_CURRENCY_CODE
, EV.BILL_TRANS_CURRENCY_CODE
, NULL
, DECODE(PR.INVPROC_CURRENCY_TYPE
, 'PROJFUNC_CURRENCY'
, CON1.USER_CONVERSION_TYPE
, 'PROJECT_CURRENCY'
, CON2.USER_CONVERSION_TYPE
, CON3.USER_CONVERSION_TYPE) )
, DECODE(PR.INVPROC_CURRENCY_TYPE
, 'PROJFUNC_CURRENCY'
, EV.PROJFUNC_INV_RATE_DATE
, 'PROJECT_CURRENCY'
, EV.PROJECT_INV_RATE_DATE
, I.FUNDING_RATE_DATE)
, DECODE(PR.INVPROC_CURRENCY_TYPE
, 'PROJFUNC_CURRENCY'
, EV.PROJFUNC_INV_EXCHANGE_RATE
, 'PROJECT_CURRENCY'
, EV.PROJECT_INV_EXCHANGE_RATE
, I.FUNDING_EXCHANGE_RATE)
, I.AMOUNT
, I.PROJFUNC_CURRENCY_CODE
, DECODE(EV.BILL_TRANS_CURRENCY_CODE
, I.PROJFUNC_CURRENCY_CODE
, NULL
, CON1.USER_CONVERSION_TYPE)
, EV.PROJFUNC_INV_RATE_DATE
, EV.PROJFUNC_INV_EXCHANGE_RATE
, I.PROJFUNC_BILL_AMOUNT
, I.PROJECT_CURRENCY_CODE
, CON2.USER_CONVERSION_TYPE
, EV.PROJECT_INV_RATE_DATE
, EV.PROJECT_INV_EXCHANGE_RATE
, I.PROJECT_BILL_AMOUNT
, I.FUNDING_CURRENCY_CODE
, DECODE(I.FUNDING_CURRENCY_CODE
, EV.BILL_TRANS_CURRENCY_CODE
, NULL
, CON3.USER_CONVERSION_TYPE)
, I.FUNDING_RATE_DATE
, I.FUNDING_EXCHANGE_RATE
, I.FUNDING_BILL_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, I.CREDIT_AMOUNT
, I.CREDIT_PROCESS_FLAG
FROM PA_EVENTS EV
, PA_TASKS T
, PA_TASKS TSK
, HZ_LOCATIONS HZ_L
, HZ_CUST_ACCT_SITES_ALL S
, HZ_PARTY_SITES PS
, (SELECT DII.*
, DI.INVOICE_DATE
FROM PA_DRAFT_INVOICE_ITEMS DII
, PA_DRAFT_INVOICES_ALL DI
WHERE DI.PROJECT_ID = DII.PROJECT_ID
AND DI.DRAFT_INVOICE_NUM = DII.DRAFT_INVOICE_NUM) I
, ZX_OUTPUT_CLASSIFICATIONS_V FND_LK
, FND_LOOKUPS FND_LK1
, FND_LOOKUPS FND_LK2
, PA_PROJECTS_ALL PR
, PA_CONVERSION_TYPES_V CON1
, PA_CONVERSION_TYPES_V CON2
, PA_CONVERSION_TYPES_V CON3
WHERE I.SHIP_TO_ADDRESS_ID = S.CUST_ACCT_SITE_ID(+)
AND PS.PARTY_SITE_ID(+) = S.PARTY_SITE_ID
AND HZ_L.LOCATION_ID(+) = PS.LOCATION_ID
AND TSK.TASK_ID (+) = I.TASK_ID
AND T.TASK_ID (+) = I.EVENT_TASK_ID
AND EV.PROJECT_ID (+) = I.PROJECT_ID
AND EV.EVENT_NUM (+) = I.EVENT_NUM
AND NVL(EV.TASK_ID (+)
, 0) = NVL(I.EVENT_TASK_ID
, 0)
AND I.INVOICE_LINE_TYPE <> 'NET ZERO ADJUSTMENT'
AND FND_LK.LOOKUP_CODE (+) = I.OUTPUT_TAX_CLASSIFICATION_CODE
AND FND_LK.ENABLED_FLAG (+) = 'Y'
AND NVL(FND_LK.ORG_ID
, PR.ORG_ID) IN (PR.ORG_ID
, -99)
AND FND_LK1.LOOKUP_CODE (+) = NVL(I.OUTPUT_TAX_EXEMPT_FLAG
, 'S')
AND FND_LK1.ENABLED_FLAG (+) = 'Y'
AND FND_LK1.LOOKUP_TYPE (+) = 'ZX_EXEMPTION_CONTROL'
AND FND_LK2.LOOKUP_TYPE (+) = 'ZX_EXEMPTION_REASON_CODE'
AND FND_LK2.LOOKUP_CODE (+) = I.OUTPUT_TAX_EXEMPT_REASON_CODE
AND FND_LK2.ENABLED_FLAG (+) = 'Y'
AND PR.PROJECT_ID = I.PROJECT_ID
AND EV.PROJFUNC_RATE_TYPE = CON1.CONVERSION_TYPE(+)
AND EV.PROJECT_RATE_TYPE = CON2.CONVERSION_TYPE(+)
AND EV.FUNDING_RATE_TYPE = CON3.CONVERSION_TYPE(+)
AND I.INVOICE_DATE BETWEEN FND_LK.START_DATE_ACTIVE(+)
AND NVL(FND_LK.END_DATE_ACTIVE(+)
, I.INVOICE_DATE)

Columns