View: PA_DRAFT_INVOICES_V

Product:	PA - Projects
Description:	10SC Only
Implementation/DBA Data:	APPS.PA_DRAFT_INVOICES_V

View Text

SELECT I.ROWID ROW_ID
, I.PROJECT_ID
, P.SEGMENT1 PROJECT_NUMBER
, P.NAME PROJECT_NAME
, P.DISTRIBUTION_RULE
, (SELECT DR.MEANING DISTRIBUTION_RULE_M
FROM PA_DISTRIBUTION_RULES DR
WHERE DR.DISTRIBUTION_RULE = P.DISTRIBUTION_RULE)
, P.PROJECT_TYPE
, (SELECT PS.PROJECT_STATUS_NAME
FROM PA_PROJECT_STATUSES PS
WHERE PS.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE
AND PS.STATUS_TYPE = 'PROJECT') PROJECT_STATUS_M
, P.PROJECT_STATUS_CODE PROJECT_STATUS_CODE
, ORG.ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, ORG.NAME PROJECT_ORGANIZATION_NAME
, A.CUSTOMER_ID
, C.CUSTOMER_NAME
, C.CUSTOMER_NUMBER
, I.AGREEMENT_ID
, A.AGREEMENT_NUM
, A.AGREEMENT_TYPE
, A.TERM_ID
, (SELECT T.NAME
FROM RA_TERMS T
WHERE T.TERM_ID = A.TERM_ID ) TERM_NAME
, I.DRAFT_INVOICE_NUM
, I.TRANSFER_STATUS_CODE
, LK.LOOKUP_CODE INVOICE_STATUS_CODE
, LK.MEANING INVOICE_STATUS_M
, DECODE(I.GENERATION_ERROR_FLAG
, 'Y'
, 'ERROR'
, DECODE(I.APPROVED_DATE
, NULL
, 'UNAPPROVED'
, DECODE(I.RELEASED_DATE
, NULL
, 'UNRELEASED'
, DECODE(I.TRANSFER_STATUS_CODE
, 'P'
, 'RELEASED'
, 'X'
, 'REJECTED'
, 'T'
, 'TRANSFERRED'
, 'A'
, 'ACCEPTED'
, 'R'
, 'REJECTED'
, 'OTHER' ) ) ) ) CHK_STATUS
, I.BILL_THROUGH_DATE
, I.APPROVED_DATE
, I.APPROVED_BY_PERSON_ID
, EMP2.FULL_NAME APPROVED_BY_PERSON_NAME
, DECODE(EMP2.CURRENT_NPW_FLAG
, 'Y'
, EMP2.NPW_NUMBER
, EMP2.EMPLOYEE_NUMBER) APPROVED_BY_PERSON_NUMBER
, I.RELEASED_DATE
, I.RELEASED_BY_PERSON_ID
, EMP.FULL_NAME RELEASED_BY_PERSON_NAME
, DECODE(EMP.CURRENT_NPW_FLAG
, 'Y'
, EMP.NPW_NUMBER
, EMP.EMPLOYEE_NUMBER) RELEASED_BY_PERSON_NUMBER
, I.INVOICE_DATE
, I.RA_INVOICE_NUMBER
, I.PA_DATE
, I.GL_DATE
, I.CREATION_DATE
, I.INVOICE_COMMENT
, I.DRAFT_INVOICE_NUM_CREDITED
, I.CANCELED_FLAG
, I.CANCEL_CREDIT_MEMO_FLAG
, I.WRITE_OFF_FLAG
, I.CONVERTED_FLAG
, I.EXTRACTED_DATE
, I.RETENTION_PERCENTAGE
, I.INV_CURRENCY_CODE
, I.INV_CURRENCY_CODE
, I.INV_RATE_TYPE
, I.INV_RATE_DATE
, I.INV_EXCHANGE_RATE
, (SELECT CON.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V CON
WHERE CON.CONVERSION_TYPE = I.INV_RATE_TYPE)
, P.PROJECT_CURRENCY_CODE
, (SELECT LK3.MEANING
FROM PA_LOOKUPS LK3
WHERE LK3.LOOKUP_TYPE = 'INVOICE_CLASS'
AND LK3.LOOKUP_CODE = DECODE(ORG_INV.CANCELED_FLAG
, 'Y'
, 'CANCEL'
, DECODE(I.WRITE_OFF_FLAG
, 'Y'
, 'WRITE_OFF'
, DECODE(I.CONCESSION_FLAG
, 'Y'
, 'CONCESSION'
, DECODE(NVL(I.DRAFT_INVOICE_NUM_CREDITED
, 0)
, 0
, 'INVOICE'
, 'CREDIT_MEMO'))))
AND LK3.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(LK3.START_DATE_ACTIVE
, SYSDATE- 1))
AND TRUNC(NVL(LK3.END_DATE_ACTIVE
, SYSDATE))) INVOICE_CLASS
, I.ATTRIBUTE_CATEGORY
, I.ATTRIBUTE1
, I.ATTRIBUTE2
, I.ATTRIBUTE3
, I.ATTRIBUTE4
, I.ATTRIBUTE5
, I.ATTRIBUTE6
, I.ATTRIBUTE7
, I.ATTRIBUTE8
, I.ATTRIBUTE9
, I.ATTRIBUTE10
, I.GENERATION_ERROR_FLAG
, I.TRANSFERRED_DATE
, I.TRANSFER_REJECTION_REASON
, DECODE(I.GENERATION_ERROR_FLAG
, 'Y'
, 'Y'
, DECODE(I.TRANSFER_STATUS_CODE
, 'R'
, 'Y'
, 'X'
, 'Y'
, 'N')) EXCEPTION_FLAG
, SUM(NVL(II.UNEARNED_REVENUE_CR
, 0))
, SUM(NVL(II.UNBILLED_RECEIVABLE_DR
, 0))
, I.SYSTEM_REFERENCE
, I.CUSTOMER_BILL_SPLIT
, I.INVOICE_SET_ID
, SUM(II.INV_AMOUNT) INVOICE_AMOUNT
, SUM(NVL(II.AMOUNT
, 0)) AMOUNT
, DECODE(I.SYSTEM_REFERENCE
, NULL
, SUM(NVL(II.INV_AMOUNT
, 0))
, NULL) ORIGINAL_BALANCE
, DECODE(I.SYSTEM_REFERENCE
, NULL
, SUM(NVL(II.INV_AMOUNT
, 0))
, NULL) BALANCE_DUE
, NULL AMOUNT_APPLIED
, NULL AMOUNT_CREDITED
, NULL WRITE_OFF
, I.LAST_UPDATE_DATE
, I.LAST_UPDATED_BY
, I.LAST_UPDATE_LOGIN
, I.PROGRAM_APPLICATION_ID
, I.PROGRAM_ID
, I.PROGRAM_UPDATE_DATE
, I.BILL_TO_ADDRESS_ID
, I.SHIP_TO_ADDRESS_ID
, PA_SECURITY.ALLOW_UPDATE(I.PROJECT_ID)
, PA_SECURITY.VIEW_LABOR_COSTS(I.PROJECT_ID)
, 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
, SUM(DECODE(I.TRANSFER_STATUS_CODE
, 'A'
, II.ACCT_AMOUNT
, 0)) RECV_INV_AMT
, SUM(DECODE(I.TRANSFER_STATUS_CODE
, 'A'
, II.ROUNDING_AMOUNT
, 0)) ROUND_AMT
, I.LANGUAGE
, (SELECT F.NLS_LANGUAGE
FROM FND_LANGUAGES F
WHERE F.LANGUAGE_CODE=I.LANGUAGE)
, DECODE(I.TRANSFER_STATUS_CODE
, 'A'
, I.ACCTD_CURR_CODE
, NULL)
, DECODE(I.TRANSFER_STATUS_CODE
, 'A'
, I.ACCTD_RATE_TYPE
, NULL)
, DECODE(I.TRANSFER_STATUS_CODE
, 'A'
, I.ACCTD_RATE_DATE
, NULL)
, DECODE(I.TRANSFER_STATUS_CODE
, 'A'
, I.ACCTD_EXCHG_RATE
, NULL)
, PA_OUTPUT_TAX.GET_DRAFT_INVOICE_TAX_AMT(I.SYSTEM_REFERENCE)
, DECODE(NVL(PT.CC_PRVDR_FLAG
, 'N')
, 'Y'
, 'INTERCOMPANY INVOICE'
, DECODE(NVL(PC.BILL_ANOTHER_PROJECT_FLAG
, 'N')
, 'Y'
, 'INTER-PROJECT INVOICE'
, 'EXTERNAL INVOICE')) INVOICE_CATEGORY
, PI.INVOICE_BATCH_SOURCE_ID
, PI.CC_IC_AR_BATCH_SOURCE_ID
, I.CC_PROJECT_ID
, I.IB_AP_TRANSFER_STATUS_CODE
, I.IB_AP_TRANSFER_ERROR_CODE
, II.INVPROC_CURRENCY_CODE
, SUM(II.AMOUNT) INVPRC_BILL_AMOUNT
, II.PROJFUNC_CURRENCY_CODE
, SUM(NVL(II.PROJFUNC_BILL_AMOUNT
, 0)) PROJFUNC_BILL_AMOUNT
, SUM(II.PROJECT_BILL_AMOUNT) PROJECT_BILL_AMOUNT
, II.FUNDING_CURRENCY_CODE
, SUM(II.FUNDING_BILL_AMOUNT) FUNDING_BILL_AMOUNT
, I.PROJFUNC_INVTRANS_RATE_TYPE PROJFUNC_INVTRANS_RATE_TYPE
, PA_MULTI_CURRENCY.GET_USER_CONVERSION_TYPE(I.PROJFUNC_INVTRANS_RATE_TYPE)
, I.PROJFUNC_INVTRANS_RATE_DATE PROJFUNC_INVTRANS_RATE_DATE
, TO_NUMBER(I.PROJFUNC_INVTRANS_EX_RATE) PROJFUNC_INVTRANS_EX_RATE
, I.RETENTION_INVOICE_FLAG
, I.RETN_CODE_COMBINATION_ID
, I.CREDIT_MEMO_REASON_CODE
, I.BILL_TO_CUSTOMER_ID
, I.SHIP_TO_CUSTOMER_ID
, BILL_C.CUSTOMER_NUMBER
, BILL_C.CUSTOMER_NAME
, SHIP_C.CUSTOMER_NUMBER
, SHIP_C.CUSTOMER_NAME
, I.CONCESSION_FLAG
, I.RECORD_VERSION_NUMBER
, I.LAST_CREDIT_REQUEST_ID
, P.ENABLE_TOP_TASK_INV_MTH_FLAG
, P.REVENUE_ACCRUAL_METHOD
, P.INVOICE_METHOD
, (SELECT LK4.MEANING
FROM PA_LOOKUPS LK4
WHERE LK4.LOOKUP_TYPE = 'REVENUE ACCRUAL METHOD'
AND LK4.LOOKUP_CODE=P.REVENUE_ACCRUAL_METHOD)
, (SELECT LK5.MEANING
FROM PA_LOOKUPS LK5
WHERE LK5.LOOKUP_TYPE = 'INVOICE METHOD'
AND LK5.LOOKUP_CODE=P.INVOICE_METHOD)
, BILL_C.PARTY_ID
, SHIP_C.PARTY_ID
, BILL_SITE.SITE_USE_ID BILL_TO_SITE_USE_ID
, SHIP_SITE.SITE_USE_ID SHIP_TO_SITE_USE_ID
, A.CUSTOMER_ORDER_NUMBER
, A.LINE_OF_ACCOUNT
, I.PAYMENT_SET_ID
, PC.BILL_ANOTHER_PROJECT_FLAG BILL_ANOTHER_PROJECT_FLAG
, PT.CC_PRVDR_FLAG CC_PRVDR_FLAG
, PPRT.PROJECT_ROLE_TYPE PROJECT_ROLE_TYPE
, PPP.RESOURCE_SOURCE_ID PERSON_ID
FROM PA_DRAFT_INVOICES_ALL I
, PA_DRAFT_INV_ITEMS_BAS II
, PA_PROJECTS_ALL P
, PA_PROJECT_CUSTOMERS PC
, HR_ALL_ORGANIZATION_UNITS ORG
, PA_AGREEMENTS_ALL A
, PA_DRAFT_INVOICES_ALL ORG_INV
, PA_IMPLEMENTATIONS PI
, PA_CUSTOMERS_V C
, PER_ALL_PEOPLE_F EMP
, PER_ALL_PEOPLE_F EMP2
, PA_LOOKUPS LK
, PA_CUSTOMERS_V BILL_C
, PA_CUSTOMERS_V SHIP_C
, HZ_CUST_SITE_USES BILL_SITE
, HZ_CUST_SITE_USES SHIP_SITE
, PA_PROJECT_TYPES PT
, PA_PROJECT_PARTIES PPP
, PA_PROJECT_ROLE_TYPES_B PPRT
, PA_ROLE_CONTROLS PRC
WHERE PT.PROJECT_TYPE = P.PROJECT_TYPE
AND 'Y' = PA_SECURITY.ALLOW_QUERY(I.PROJECT_ID)
AND P.PROJECT_ID = PC.PROJECT_ID
AND A.CUSTOMER_ID = PC.CUSTOMER_ID
AND P.PROJECT_ID = I.PROJECT_ID
AND I.AGREEMENT_ID = A.AGREEMENT_ID
AND P.ORG_ID = PI.ORG_ID
AND ORG_INV.PROJECT_ID (+) = I.PROJECT_ID
AND ORG_INV.DRAFT_INVOICE_NUM (+) = I.DRAFT_INVOICE_NUM_CREDITED
AND II.PROJECT_ID (+) = I.PROJECT_ID
AND II.DRAFT_INVOICE_NUM (+) = I.DRAFT_INVOICE_NUM
AND P.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND C.CUSTOMER_ID = A.CUSTOMER_ID
AND EMP.PERSON_ID (+) = I.RELEASED_BY_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+)
AND EMP.EFFECTIVE_END_DATE (+)
AND EMP2.PERSON_ID (+) = I.APPROVED_BY_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN EMP2.EFFECTIVE_START_DATE (+)
AND EMP2.EFFECTIVE_END_DATE (+)
AND LK.LOOKUP_TYPE = 'INVOICE STATUS'
AND LK.LOOKUP_CODE = DECODE(I.GENERATION_ERROR_FLAG
, 'Y'
, 'GENERATION ERROR'
, DECODE(I.APPROVED_DATE
, NULL
, 'UNAPPROVED'
, DECODE(I.RELEASED_DATE
, NULL
, 'APPROVED'
, DECODE(I.TRANSFER_STATUS_CODE
, 'P'
, 'RELEASED'
, 'X'
, 'REJECTED IN TRANSFER'
, 'T'
, 'TRANSFERRED'
, 'A'
, 'ACCEPTED'
, 'R'
, 'REJECTED' ) ) ) )
AND BILL_C.CUSTOMER_ID (+)= I.BILL_TO_CUSTOMER_ID
AND SHIP_C.CUSTOMER_ID(+) = I.SHIP_TO_CUSTOMER_ID
AND BILL_SITE.CUST_ACCT_SITE_ID (+) = I.BILL_TO_ADDRESS_ID
AND BILL_SITE.SITE_USE_CODE(+) = 'BILL_TO'
AND BILL_SITE.STATUS(+) = 'A'
AND SHIP_SITE.CUST_ACCT_SITE_ID(+) = I.SHIP_TO_ADDRESS_ID
AND SHIP_SITE.SITE_USE_CODE(+) = 'SHIP_TO'
AND SHIP_SITE.STATUS(+)='A'
AND 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 NVL(PPP.START_DATE_ACTIVE
, TRUNC(NVL(I.INVOICE_DATE
, SYSDATE)))
AND NVL(PPP.END_DATE_ACTIVE
, TRUNC(NVL(I.INVOICE_DATE
, SYSDATE))) GROUP BY I.ROWID
, I.PROJECT_ID
, P.SEGMENT1
, P.NAME
, P.DISTRIBUTION_RULE
, P.PROJECT_TYPE
, P.PROJECT_STATUS_CODE
, ORG.ORGANIZATION_ID
, ORG.NAME
, A.CUSTOMER_ID
, C.CUSTOMER_NAME
, C.CUSTOMER_NUMBER
, I.AGREEMENT_ID
, A.AGREEMENT_NUM
, A.AGREEMENT_TYPE
, A.TERM_ID
, I.DRAFT_INVOICE_NUM
, I.TRANSFER_STATUS_CODE
, LK.LOOKUP_CODE
, LK.MEANING
, DECODE(I.GENERATION_ERROR_FLAG
, 'Y'
, 'ERROR'
, DECODE(I.APPROVED_DATE
, NULL
, 'UNAPPROVED'
, DECODE(I.RELEASED_DATE
, NULL
, 'UNRELEASED'
, DECODE(I.TRANSFER_STATUS_CODE
, 'P'
, 'RELEASED'
, 'X'
, 'REJECTED IN TRANSFER'
, 'T'
, 'TRANSFERRED'
, 'A'
, 'ACCEPTED'
, 'R'
, 'REJECTED'
, 'OTHER' ) ) ) )
, I.BILL_THROUGH_DATE
, I.APPROVED_DATE
, I.APPROVED_BY_PERSON_ID
, EMP2.FULL_NAME
, DECODE(EMP2.CURRENT_NPW_FLAG
, 'Y'
, EMP2.NPW_NUMBER
, EMP2.EMPLOYEE_NUMBER)
, I.RELEASED_DATE
, I.RELEASED_BY_PERSON_ID
, EMP.FULL_NAME
, DECODE(EMP.CURRENT_NPW_FLAG
, 'Y'
, EMP.NPW_NUMBER
, EMP.EMPLOYEE_NUMBER)
, I.INVOICE_DATE
, I.RA_INVOICE_NUMBER
, I.PA_DATE
, I.GL_DATE
, I.CREATION_DATE
, I.INVOICE_COMMENT
, I.DRAFT_INVOICE_NUM_CREDITED
, I.CANCELED_FLAG
, I.CANCEL_CREDIT_MEMO_FLAG
, I.WRITE_OFF_FLAG
, I.CONVERTED_FLAG
, I.EXTRACTED_DATE
, I.RETENTION_PERCENTAGE
, I.INV_CURRENCY_CODE
, I.INV_RATE_TYPE
, I.INV_RATE_DATE
, I.INV_EXCHANGE_RATE
, P.PROJECT_CURRENCY_CODE
, ORG_INV.CANCELED_FLAG
, I.ATTRIBUTE_CATEGORY
, I.ATTRIBUTE1
, I.ATTRIBUTE2
, I.ATTRIBUTE3
, I.ATTRIBUTE4
, I.ATTRIBUTE5
, I.ATTRIBUTE6
, I.ATTRIBUTE7
, I.ATTRIBUTE8
, I.ATTRIBUTE9
, I.ATTRIBUTE10
, I.GENERATION_ERROR_FLAG
, I.TRANSFERRED_DATE
, I.TRANSFER_REJECTION_REASON
, DECODE(I.GENERATION_ERROR_FLAG
, 'Y'
, 'Y'
, DECODE(I.TRANSFER_STATUS_CODE
, 'R'
, 'Y'
, 'X'
, 'Y'
, 'N'))
, I.SYSTEM_REFERENCE
, I.CUSTOMER_BILL_SPLIT
, I.INVOICE_SET_ID
, I.LAST_UPDATE_DATE
, I.LAST_UPDATED_BY
, I.LAST_UPDATE_LOGIN
, I.PROGRAM_APPLICATION_ID
, I.PROGRAM_ID
, I.PROGRAM_UPDATE_DATE
, I.BILL_TO_ADDRESS_ID
, I.SHIP_TO_ADDRESS_ID
, P.TEMPLATE_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.LANGUAGE
, I.ACCTD_CURR_CODE
, I.ACCTD_RATE_TYPE
, I.ACCTD_RATE_DATE
, I.ACCTD_EXCHG_RATE
, P.PROJECT_TYPE
, PC.BILL_ANOTHER_PROJECT_FLAG
, PI.INVOICE_BATCH_SOURCE_ID
, PI.CC_IC_AR_BATCH_SOURCE_ID
, I.CC_PROJECT_ID
, I.IB_AP_TRANSFER_STATUS_CODE
, I.IB_AP_TRANSFER_ERROR_CODE
, II.INVPROC_CURRENCY_CODE
, II.PROJFUNC_CURRENCY_CODE
, II.FUNDING_CURRENCY_CODE
, I.PROJFUNC_INVTRANS_RATE_TYPE
, I.PROJFUNC_INVTRANS_RATE_DATE
, TO_NUMBER(I.PROJFUNC_INVTRANS_EX_RATE)
, I.RETENTION_INVOICE_FLAG
, I.RETN_CODE_COMBINATION_ID
, I.CREDIT_MEMO_REASON_CODE
, I.BILL_TO_CUSTOMER_ID
, I.SHIP_TO_CUSTOMER_ID
, BILL_C.CUSTOMER_NUMBER
, BILL_C.CUSTOMER_NAME
, SHIP_C.CUSTOMER_NUMBER
, SHIP_C.CUSTOMER_NAME
, I.CONCESSION_FLAG
, I.RECORD_VERSION_NUMBER
, I.LAST_CREDIT_REQUEST_ID
, P.ENABLE_TOP_TASK_INV_MTH_FLAG
, P.REVENUE_ACCRUAL_METHOD
, P.INVOICE_METHOD
, BILL_C.PARTY_ID
, SHIP_C.PARTY_ID
, BILL_SITE.SITE_USE_ID
, SHIP_SITE.SITE_USE_ID
, A.CUSTOMER_ORDER_NUMBER
, A.LINE_OF_ACCOUNT
, I.PAYMENT_SET_ID
, PC.BILL_ANOTHER_PROJECT_FLAG
, PT.CC_PRVDR_FLAG
, PPRT.PROJECT_ROLE_TYPE
, PPP.RESOURCE_SOURCE_ID

Columns