VIEW: APPS.PA_DRAFT_INVOICE_LINES_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 , 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 ,(select distinct fnd_lk.meaning from ZX_OUTPUT_CLASSIFICATIONS_V FND_LK WHERE 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 I.INVOICE_DATE BETWEEN FND_LK.START_DATE_ACTIVE(+) AND NVL(FND_LK.END_DATE_ACTIVE(+),I.INVOICE_DATE)) , 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, 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_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(+)

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
, 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
, (SELECT DISTINCT FND_LK.MEANING
FROM ZX_OUTPUT_CLASSIFICATIONS_V FND_LK
WHERE 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 I.INVOICE_DATE BETWEEN FND_LK.START_DATE_ACTIVE(+)
AND NVL(FND_LK.END_DATE_ACTIVE(+)
, I.INVOICE_DATE))
, 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
, 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_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(+)