VIEW: APPS.PA_DRAFT_INVOICES_V

Source

View Text - Preformatted

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) , (select decode(nvl(PT.CC_PRVDR_FLAG,'N'), 'Y', 'INTERCOMPANY INVOICE', decode(nvl(PC.BILL_ANOTHER_PROJECT_FLAG,'N'), 'Y', 'INTER-PROJECT INVOICE', 'EXTERNAL INVOICE')) from PA_PROJECT_TYPES PT WHERE PT.PROJECT_TYPE = P.PROJECT_TYPE) 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 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 WHERE 'Y' IN (SELECT pa_security.allow_query(I.Project_id) from sys.dual) 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' 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

View Text - HTML Formatted

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)
, (SELECT DECODE(NVL(PT.CC_PRVDR_FLAG
, 'N')
, 'Y'
, 'INTERCOMPANY INVOICE'
, DECODE(NVL(PC.BILL_ANOTHER_PROJECT_FLAG
, 'N')
, 'Y'
, 'INTER-PROJECT INVOICE'
, 'EXTERNAL INVOICE'))
FROM PA_PROJECT_TYPES PT
WHERE PT.PROJECT_TYPE = P.PROJECT_TYPE) 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
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
WHERE 'Y' IN (SELECT PA_SECURITY.ALLOW_QUERY(I.PROJECT_ID)
FROM SYS.DUAL)
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' 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