VIEW: APPS.PA_DRAFT_INVOICE_PP_V

Source

View Text - Preformatted

SELECT I.PROJECT_ID, I.DRAFT_INVOICE_NUM, I.LAST_UPDATE_DATE, I.LAST_UPDATED_BY, I.CREATION_DATE, I.CREATED_BY, I.TRANSFER_STATUS_CODE, I.GENERATION_ERROR_FLAG, I.AGREEMENT_ID, I.PA_DATE, I.REQUEST_ID, I.PROGRAM_APPLICATION_ID, I.PROGRAM_ID, I.PROGRAM_UPDATE_DATE, I.CUSTOMER_BILL_SPLIT, I.BILL_THROUGH_DATE, I.INVOICE_COMMENT, I.APPROVED_DATE, I.APPROVED_BY_PERSON_ID, I.RELEASED_DATE, I.RELEASED_BY_PERSON_ID, I.INVOICE_DATE, I.RA_INVOICE_NUMBER, I.TRANSFERRED_DATE, I.TRANSFER_REJECTION_REASON, I.UNEARNED_REVENUE_CR, I.UNBILLED_RECEIVABLE_DR, I.GL_DATE, I.SYSTEM_REFERENCE, I.DRAFT_INVOICE_NUM_CREDITED, I.CANCELED_FLAG, I.CANCEL_CREDIT_MEMO_FLAG, I.WRITE_OFF_FLAG, I.CONVERTED_FLAG, I.EXTRACTED_DATE, I.LAST_UPDATE_LOGIN, I.ATTRIBUTE_CATEGORY, I.ATTRIBUTE1, I.ATTRIBUTE2, I.ATTRIBUTE3, I.ATTRIBUTE4, I.ATTRIBUTE5, I.ATTRIBUTE6, I.ATTRIBUTE7, I.ATTRIBUTE8, I.ATTRIBUTE9, I.ATTRIBUTE10, I.RETENTION_PERCENTAGE, I.INVOICE_SET_ID, I.ORG_ID, I.INV_CURRENCY_CODE, I.INV_RATE_TYPE, I.INV_RATE_DATE, I.INV_EXCHANGE_RATE, I.BILL_TO_ADDRESS_ID, I.SHIP_TO_ADDRESS_ID, I.PRC_GENERATED_FLAG, I.RECEIVABLE_CODE_COMBINATION_ID, I.ROUNDING_CODE_COMBINATION_ID, I.UNBILLED_CODE_COMBINATION_ID, I.UNEARNED_CODE_COMBINATION_ID, I.WOFF_CODE_COMBINATION_ID, I.ACCTD_CURR_CODE, I.ACCTD_RATE_TYPE, I.ACCTD_RATE_DATE, I.ACCTD_EXCHG_RATE, I.LANGUAGE, I.CC_INVOICE_GROUP_CODE, I.CC_PROJECT_ID, I.IB_AP_TRANSFER_STATUS_CODE, I.IB_AP_TRANSFER_ERROR_CODE, I.INVPROC_CURRENCY_CODE, I.PROJFUNC_INVTRANS_RATE_TYPE, I.PROJFUNC_INVTRANS_RATE_DATE, I.PROJFUNC_INVTRANS_EX_RATE, I.PA_PERIOD_NAME, I.GL_PERIOD_NAME, I.UBR_SUMMARY_ID, I.UER_SUMMARY_ID, I.UBR_UER_PROCESS_FLAG, I.PJI_SUMMARIZED_FLAG, I.RETENTION_INVOICE_FLAG, I.RETN_CODE_COMBINATION_ID, I.PURGE_FLAG, I.CUSTOMER_ID, I.BILL_TO_CUSTOMER_ID, I.SHIP_TO_CUSTOMER_ID, I.BILL_TO_CONTACT_ID, I.SHIP_TO_CONTACT_ID, I.CREDIT_MEMO_REASON_CODE, I.CONCESSION_FLAG, I.RECORD_VERSION_NUMBER, I.LAST_CREDIT_REQUEST_ID, I.PAYMENT_SET_ID , I.ROWID , PPRT.PROJECT_ROLE_TYPE , PPP.RESOURCE_SOURCE_ID , PPP.PROJECT_ROLE_ID FROM PA_DRAFT_INVOICES_ALL I, PA_PROJECT_PARTIES PPP, PA_PROJECT_ROLE_TYPES_B PPRT, PA_ROLE_CONTROLS PRC WHERE PPP.OBJECT_TYPE = 'PA_PROJECTS' AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID AND PPP.PROJECT_ROLE_ID = PRC.PROJECT_ROLE_ID AND PRC.ROLE_CONTROL_CODE = 'ALLOW_AS_PROJ_MEMBER' AND I.PROJECT_ID = PPP.PROJECT_ID AND PPP.RESOURCE_TYPE_ID = 101 AND TRUNC(NVL(I.INVOICE_DATE,SYSDATE)) BETWEEN TRUNC(NVL(PPP.START_DATE_ACTIVE,NVL(I.INVOICE_DATE,SYSDATE))) AND TRUNC(NVL(PPP.END_DATE_ACTIVE,NVL(I.INVOICE_DATE,SYSDATE)))

View Text - HTML Formatted

SELECT I.PROJECT_ID
, I.DRAFT_INVOICE_NUM
, I.LAST_UPDATE_DATE
, I.LAST_UPDATED_BY
, I.CREATION_DATE
, I.CREATED_BY
, I.TRANSFER_STATUS_CODE
, I.GENERATION_ERROR_FLAG
, I.AGREEMENT_ID
, I.PA_DATE
, I.REQUEST_ID
, I.PROGRAM_APPLICATION_ID
, I.PROGRAM_ID
, I.PROGRAM_UPDATE_DATE
, I.CUSTOMER_BILL_SPLIT
, I.BILL_THROUGH_DATE
, I.INVOICE_COMMENT
, I.APPROVED_DATE
, I.APPROVED_BY_PERSON_ID
, I.RELEASED_DATE
, I.RELEASED_BY_PERSON_ID
, I.INVOICE_DATE
, I.RA_INVOICE_NUMBER
, I.TRANSFERRED_DATE
, I.TRANSFER_REJECTION_REASON
, I.UNEARNED_REVENUE_CR
, I.UNBILLED_RECEIVABLE_DR
, I.GL_DATE
, I.SYSTEM_REFERENCE
, I.DRAFT_INVOICE_NUM_CREDITED
, I.CANCELED_FLAG
, I.CANCEL_CREDIT_MEMO_FLAG
, I.WRITE_OFF_FLAG
, I.CONVERTED_FLAG
, I.EXTRACTED_DATE
, I.LAST_UPDATE_LOGIN
, I.ATTRIBUTE_CATEGORY
, I.ATTRIBUTE1
, I.ATTRIBUTE2
, I.ATTRIBUTE3
, I.ATTRIBUTE4
, I.ATTRIBUTE5
, I.ATTRIBUTE6
, I.ATTRIBUTE7
, I.ATTRIBUTE8
, I.ATTRIBUTE9
, I.ATTRIBUTE10
, I.RETENTION_PERCENTAGE
, I.INVOICE_SET_ID
, I.ORG_ID
, I.INV_CURRENCY_CODE
, I.INV_RATE_TYPE
, I.INV_RATE_DATE
, I.INV_EXCHANGE_RATE
, I.BILL_TO_ADDRESS_ID
, I.SHIP_TO_ADDRESS_ID
, I.PRC_GENERATED_FLAG
, I.RECEIVABLE_CODE_COMBINATION_ID
, I.ROUNDING_CODE_COMBINATION_ID
, I.UNBILLED_CODE_COMBINATION_ID
, I.UNEARNED_CODE_COMBINATION_ID
, I.WOFF_CODE_COMBINATION_ID
, I.ACCTD_CURR_CODE
, I.ACCTD_RATE_TYPE
, I.ACCTD_RATE_DATE
, I.ACCTD_EXCHG_RATE
, I.LANGUAGE
, I.CC_INVOICE_GROUP_CODE
, I.CC_PROJECT_ID
, I.IB_AP_TRANSFER_STATUS_CODE
, I.IB_AP_TRANSFER_ERROR_CODE
, I.INVPROC_CURRENCY_CODE
, I.PROJFUNC_INVTRANS_RATE_TYPE
, I.PROJFUNC_INVTRANS_RATE_DATE
, I.PROJFUNC_INVTRANS_EX_RATE
, I.PA_PERIOD_NAME
, I.GL_PERIOD_NAME
, I.UBR_SUMMARY_ID
, I.UER_SUMMARY_ID
, I.UBR_UER_PROCESS_FLAG
, I.PJI_SUMMARIZED_FLAG
, I.RETENTION_INVOICE_FLAG
, I.RETN_CODE_COMBINATION_ID
, I.PURGE_FLAG
, I.CUSTOMER_ID
, I.BILL_TO_CUSTOMER_ID
, I.SHIP_TO_CUSTOMER_ID
, I.BILL_TO_CONTACT_ID
, I.SHIP_TO_CONTACT_ID
, I.CREDIT_MEMO_REASON_CODE
, I.CONCESSION_FLAG
, I.RECORD_VERSION_NUMBER
, I.LAST_CREDIT_REQUEST_ID
, I.PAYMENT_SET_ID
, I.ROWID
, PPRT.PROJECT_ROLE_TYPE
, PPP.RESOURCE_SOURCE_ID
, PPP.PROJECT_ROLE_ID
FROM PA_DRAFT_INVOICES_ALL I
, PA_PROJECT_PARTIES PPP
, PA_PROJECT_ROLE_TYPES_B PPRT
, PA_ROLE_CONTROLS PRC
WHERE PPP.OBJECT_TYPE = 'PA_PROJECTS'
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND PPP.PROJECT_ROLE_ID = PRC.PROJECT_ROLE_ID
AND PRC.ROLE_CONTROL_CODE = 'ALLOW_AS_PROJ_MEMBER'
AND I.PROJECT_ID = PPP.PROJECT_ID
AND PPP.RESOURCE_TYPE_ID = 101
AND TRUNC(NVL(I.INVOICE_DATE
, SYSDATE)) BETWEEN TRUNC(NVL(PPP.START_DATE_ACTIVE
, NVL(I.INVOICE_DATE
, SYSDATE)))
AND TRUNC(NVL(PPP.END_DATE_ACTIVE
, NVL(I.INVOICE_DATE
, SYSDATE)))