VIEW: APPS.PA_DRAFT_INVOICE_LINES_ALL_V

Source

View Text - Preformatted

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 , TSK.TASK_NAME , 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_VAT_TAX_ID , I.output_tax_classification_code , I.OUTPUT_TAX_EXEMPT_FLAG , I.OUTPUT_TAX_EXEMPT_REASON_CODE , I.OUTPUT_TAX_EXEMPT_NUMBER , AR1.TAX_CODE , LK1.MEANING , 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 , I.credit_amount , I.credit_process_flag , PDIA.INV_CURRENCY_CODE FROM PA_EVENTS EV, PA_TASKS T, PA_TASKS TSK, HZ_LOCATIONS HZ_L, HZ_CUST_ACCT_SITES_ALL S, HZ_PARTY_SITES PS, PA_DRAFT_INVOICE_ITEMS I, PA_DRAFT_INVOICES_ALL PDIA, AR_VAT_TAX_ALL AR1, AR_LOOKUPS LK1, AR_LOOKUPS 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 AR1.VAT_TAX_ID (+) = I.OUTPUT_VAT_TAX_ID AND I.PROJECT_ID (+) = PDIA.PROJECT_ID AND I.DRAFT_INVOICE_NUM (+) = PDIA.DRAFT_INVOICE_NUM AND LK1.LOOKUP_TYPE (+) = 'TAX_CONTROL_FLAG' AND LK1.LOOKUP_CODE (+) = NVL(I.OUTPUT_TAX_EXEMPT_FLAG,'S') AND LK1.ENABLED_FLAG (+) = 'Y' AND LK2.LOOKUP_TYPE (+) = 'TAX_REASON' AND LK2.LOOKUP_CODE (+) = I.OUTPUT_TAX_EXEMPT_REASON_CODE AND 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(+)

View Text - HTML Formatted

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
, TSK.TASK_NAME
, 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_VAT_TAX_ID
, I.OUTPUT_TAX_CLASSIFICATION_CODE
, I.OUTPUT_TAX_EXEMPT_FLAG
, I.OUTPUT_TAX_EXEMPT_REASON_CODE
, I.OUTPUT_TAX_EXEMPT_NUMBER
, AR1.TAX_CODE
, LK1.MEANING
, 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
, I.CREDIT_AMOUNT
, I.CREDIT_PROCESS_FLAG
, PDIA.INV_CURRENCY_CODE
FROM PA_EVENTS EV
, PA_TASKS T
, PA_TASKS TSK
, HZ_LOCATIONS HZ_L
, HZ_CUST_ACCT_SITES_ALL S
, HZ_PARTY_SITES PS
, PA_DRAFT_INVOICE_ITEMS I
, PA_DRAFT_INVOICES_ALL PDIA
, AR_VAT_TAX_ALL AR1
, AR_LOOKUPS LK1
, AR_LOOKUPS 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 AR1.VAT_TAX_ID (+) = I.OUTPUT_VAT_TAX_ID
AND I.PROJECT_ID (+) = PDIA.PROJECT_ID
AND I.DRAFT_INVOICE_NUM (+) = PDIA.DRAFT_INVOICE_NUM
AND LK1.LOOKUP_TYPE (+) = 'TAX_CONTROL_FLAG'
AND LK1.LOOKUP_CODE (+) = NVL(I.OUTPUT_TAX_EXEMPT_FLAG
, 'S')
AND LK1.ENABLED_FLAG (+) = 'Y'
AND LK2.LOOKUP_TYPE (+) = 'TAX_REASON'
AND LK2.LOOKUP_CODE (+) = I.OUTPUT_TAX_EXEMPT_REASON_CODE
AND 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(+)