View: PA_DRAFT_INV_LINES_FORM_MRC_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
, LK.MEANING
, I.OUTPUT_TAX_EXEMPT_FLAG
, I.OUTPUT_TAX_EXEMPT_REASON_CODE
, I.OUTPUT_TAX_EXEMPT_NUMBER
, I.OUTPUT_TAX_CLASSIFICATION_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
, MRC_I.UNEARNED_REVENUE_CR
, MRC_I.UNBILLED_RECEIVABLE_DR
, GSOB.CURRENCY_CODE
, MRC_I.AMOUNT
, CON11.USER_CONVERSION_TYPE
, MRC_EV.PROJFUNC_INV_EXCHANGE_RATE
, MRC_EV.PROJFUNC_INV_RATE_DATE
, I.CREDIT_AMOUNT
FROM PA_EVENTS EV
, PA_TASKS T
, PA_TASKS TSK
, HZ_LOCATIONS HZ_L
, PA_DRAFT_INVOICE_ITEMS I
, /* AR_VAT_TAX_ALL ARTAX
, COMMENTED FOR TCA CHANGES */ FND_LOOKUPS LK
, FND_LOOKUPS LK1
, FND_LOOKUPS LK2
, PA_PROJECTS_ALL PR
, PA_MC_EVENTS MRC_EV
, PA_MC_DRAFT_INV_ITEMS MRC_I
, GL_SETS_OF_BOOKS GSOB
, PA_CONVERSION_TYPES_V CON1
, PA_CONVERSION_TYPES_V CON11
, PA_CONVERSION_TYPES_V CON2
, PA_CONVERSION_TYPES_V CON3
WHERE HZ_L.LOCATION_ID (+) = I.SHIP_TO_ADDRESS_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' /* COMMENT THE CODE FOR E TAX CHANGES
AND ADD ADDITIONAL LOOKUP TYPE FOR OUTPUT TAX CLASSIFICATION CODE
AND ARTAX.VAT_TAX_ID (+) = I.OUTPUT_VAT_TAX_ID
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 LK.LOOKUP_CODE (+) = I.OUTPUT_TAX_CLASSIFICATION_CODE
AND LK.LOOKUP_TYPE (+) = 'ZX_OUTPUT_CLASSIFICATIONS'
AND LK.ENABLED_FLAG (+) = 'Y'
AND LK1.LOOKUP_CODE (+) = NVL(I.OUTPUT_TAX_EXEMPT_FLAG
, 'S')
AND LK1.ENABLED_FLAG (+) = 'Y'
AND LK1.LOOKUP_TYPE (+) = 'ZX_EXEMPTION_CONTROL'
AND LK2.LOOKUP_TYPE (+) = 'ZX_EXEMPTION_REASON_CODE'
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(+)
AND MRC_EV.RATE_TYPE = CON11.CONVERSION_TYPE(+)
AND MRC_EV.SET_OF_BOOKS_ID(+) = NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO')
, 45
, 10))
, -99)
AND MRC_EV.EVENT_ID(+) = EV.EVENT_ID
AND MRC_EV.PRC_ASSIGNMENT_ID (+) = -99
AND MRC_I.PROJECT_ID = I.PROJECT_ID
AND MRC_I.DRAFT_INVOICE_NUM = I.DRAFT_INVOICE_NUM
AND MRC_I.LINE_NUM = I.LINE_NUM
AND MRC_I.PRC_ASSIGNMENT_ID = -99
AND MRC_I.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND GSOB.SET_OF_BOOKS_ID = NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO')
, 45
, 10))
, -99)

Columns