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 , pa_output_tax.get_meaning(I.OUTPUT_TAX_CLASSIFICATION_CODE,pr.org_id,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 , con4.user_conversion_type , I.INV_RATE_DATE , I.INV_EXCHANGE_RATE ,I.global_attribute_category ,I.global_attribute1 ,I.global_attribute2 ,I.global_attribute3 ,I.global_attribute4 ,I.global_attribute5 ,I.global_attribute6 ,I.global_attribute7 ,I.global_attribute8 ,I.global_attribute9 ,I.global_attribute10 ,I.global_attribute11 ,I.global_attribute12 ,I.global_attribute13 ,I.global_attribute14 ,I.global_attribute15 ,I.global_attribute16 ,I.global_attribute17 ,I.global_attribute18 ,I.global_attribute19 ,I.global_attribute20 ,I.global_attribute21 ,I.global_attribute22 ,I.global_attribute23 ,I.global_attribute24 ,I.global_attribute25 ,I.global_attribute26 ,I.global_attribute27 ,I.global_attribute28 ,I.global_attribute29 ,I.global_attribute30 ,hr.name provider_organization_name ,hr1.name receiver_organization_name 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, hr_all_organization_units hr, hr_all_organization_units hr1, pa_conversion_types_v con1, pa_conversion_types_v con2, pa_conversion_types_v con3, pa_conversion_types_v con4 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(+) AND I.INV_RATE_TYPE = con4.conversion_type(+) AND I.provider_organization_id=hr.organization_id(+) AND I.receiver_organization_id=hr1.organization_id(+)

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
, PA_OUTPUT_TAX.GET_MEANING(I.OUTPUT_TAX_CLASSIFICATION_CODE
, PR.ORG_ID
, 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
, CON4.USER_CONVERSION_TYPE
, I.INV_RATE_DATE
, I.INV_EXCHANGE_RATE
, I.GLOBAL_ATTRIBUTE_CATEGORY
, I.GLOBAL_ATTRIBUTE1
, I.GLOBAL_ATTRIBUTE2
, I.GLOBAL_ATTRIBUTE3
, I.GLOBAL_ATTRIBUTE4
, I.GLOBAL_ATTRIBUTE5
, I.GLOBAL_ATTRIBUTE6
, I.GLOBAL_ATTRIBUTE7
, I.GLOBAL_ATTRIBUTE8
, I.GLOBAL_ATTRIBUTE9
, I.GLOBAL_ATTRIBUTE10
, I.GLOBAL_ATTRIBUTE11
, I.GLOBAL_ATTRIBUTE12
, I.GLOBAL_ATTRIBUTE13
, I.GLOBAL_ATTRIBUTE14
, I.GLOBAL_ATTRIBUTE15
, I.GLOBAL_ATTRIBUTE16
, I.GLOBAL_ATTRIBUTE17
, I.GLOBAL_ATTRIBUTE18
, I.GLOBAL_ATTRIBUTE19
, I.GLOBAL_ATTRIBUTE20
, I.GLOBAL_ATTRIBUTE21
, I.GLOBAL_ATTRIBUTE22
, I.GLOBAL_ATTRIBUTE23
, I.GLOBAL_ATTRIBUTE24
, I.GLOBAL_ATTRIBUTE25
, I.GLOBAL_ATTRIBUTE26
, I.GLOBAL_ATTRIBUTE27
, I.GLOBAL_ATTRIBUTE28
, I.GLOBAL_ATTRIBUTE29
, I.GLOBAL_ATTRIBUTE30
, HR.NAME PROVIDER_ORGANIZATION_NAME
, HR1.NAME RECEIVER_ORGANIZATION_NAME
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
, HR_ALL_ORGANIZATION_UNITS HR
, HR_ALL_ORGANIZATION_UNITS HR1
, PA_CONVERSION_TYPES_V CON1
, PA_CONVERSION_TYPES_V CON2
, PA_CONVERSION_TYPES_V CON3
, PA_CONVERSION_TYPES_V CON4
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(+)
AND I.INV_RATE_TYPE = CON4.CONVERSION_TYPE(+)
AND I.PROVIDER_ORGANIZATION_ID=HR.ORGANIZATION_ID(+)
AND I.RECEIVER_ORGANIZATION_ID=HR1.ORGANIZATION_ID(+)