VIEW: APPS.PA_DRAFT_INVOICES_ALL_V

Source

View Text - Preformatted

SELECT I.ROWID ROW_ID , I.PROJECT_ID , P.ORG_ID , P.SEGMENT1 PROJECT_NUMBER , P.NAME PROJECT_NAME , P.DISTRIBUTION_RULE , DR.MEANING DISTRIBUTION_RULE_M , P.PROJECT_TYPE , PS.PROJECT_STATUS_NAME PROJECT_STATUS_M , P.PROJECT_STATUS_CODE PROJECT_STATUS_CODE , ORG.ORGANIZATION_ID PROJECT_ORGANIZATION_ID , ORG.NAME PROJECT_ORGANIZATION_NAME , A.CUSTOMER_ID , HZ_P.PARTY_NAME , HZ_P.PARTY_NUMBER , I.AGREEMENT_ID , A.AGREEMENT_NUM , A.AGREEMENT_TYPE , A.TERM_ID , T.NAME 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 , CON.USER_CONVERSION_TYPE , P.PROJECT_CURRENCY_CODE , LK3.MEANING INVOICE_CLASS , X.FORM_CONTEXT_PROMPT , 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 , F.NLS_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 ,HZ_P_bill.PARTY_number ,HZ_P_bill.PARTY_name ,HZ_P_ship.PARTY_number ,HZ_P_ship.PARTY_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 ,LK4.meaning ,LK5.meaning ,A.customer_order_number ,A.line_of_account ,I.payment_set_id FROM PA_DRAFT_INVOICES_ALL I, PA_DRAFT_INVOICE_ITEMS II, PA_PROJECTS_ALL P, PA_PROJECT_TYPES_ALL PT, PA_PROJECT_CUSTOMERS PC, PA_PROJECT_STATUSES PS, PA_DISTRIBUTION_RULES DR, HR_ALL_ORGANIZATION_UNITS ORG, PA_AGREEMENTS_ALL A, PA_DRAFT_INVOICES_ALL ORG_INV, PA_CONVERSION_TYPES_V CON, PA_IMPLEMENTATIONS_ALL PI, GL_SETS_OF_BOOKS GSOB, FND_LANGUAGES F, RA_TERMS T, HZ_PARTIES HZ_P, HZ_CUST_ACCOUNTS HZ_C, PER_ALL_PEOPLE_F EMP, PER_ALL_PEOPLE_F EMP2, PA_LOOKUPS LK, PA_LOOKUPS LK3, HZ_PARTIES HZ_P_BILL, HZ_CUST_ACCOUNTS HZ_C_BILL, HZ_PARTIES HZ_P_SHIP, HZ_CUST_ACCOUNTS HZ_C_SHIP, PA_LOOKUPS LK4, PA_LOOKUPS LK5, FND_DESCRIPTIVE_FLEXS_VL X WHERE 'Y' IN (SELECT pa_security.allow_query(I.Project_id) from sys.dual) AND P.PROJECT_TYPE = PT.PROJECT_TYPE AND P.ORG_ID = PT.ORG_ID AND P.PROJECT_ID = PC.PROJECT_ID AND A.CUSTOMER_ID = PC.CUSTOMER_ID AND P.PROJECT_ID = I.PROJECT_ID AND P.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE AND PS.STATUS_TYPE = 'PROJECT' AND I.AGREEMENT_ID = A.AGREEMENT_ID AND PI.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID AND PI.ORG_ID = P.ORG_ID AND CON.CONVERSION_TYPE (+) = I.INV_RATE_TYPE AND DR.DISTRIBUTION_RULE = P.DISTRIBUTION_RULE AND ORG_INV.PROJECT_ID (+) = I.PROJECT_ID AND ORG_INV.DRAFT_INVOICE_NUM (+) = I.DRAFT_INVOICE_NUM_CREDITED AND T.TERM_ID = A.TERM_ID 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 HZ_C.CUST_ACCOUNT_ID = A.CUSTOMER_ID AND HZ_C.PARTY_ID = HZ_P.PARTY_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 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)) 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 I.LANGUAGE = F.LANGUAGE_CODE(+) AND HZ_C_BILL.CUST_ACCOUNT_ID (+)= I.BILL_TO_CUSTOMER_ID AND HZ_P_BILL.PARTY_ID (+) = HZ_C_BILL.PARTY_ID AND HZ_C_SHIP.CUST_ACCOUNT_ID(+) = I.SHIP_TO_CUSTOMER_ID AND HZ_P_SHIP.PARTY_ID (+) = HZ_C_SHIP.PARTY_ID AND LK4.LOOKUP_TYPE = 'REVENUE ACCRUAL METHOD' AND LK4.LOOKUP_CODE = P.REVENUE_ACCRUAL_METHOD AND LK5.LOOKUP_TYPE = 'INVOICE METHOD' AND LK5.LOOKUP_CODE = P.INVOICE_METHOD and x.application_id(+) = I.program_application_id and x.application_table_name(+) = 'PA_DRAFT_INVOICES_ALL' and x.descriptive_flexfield_name(+) = 'PA_DRAFT_INVOICES_DESC_FLEX' GROUP BY I.ROWID, I.PROJECT_ID, P.ORG_ID, P.SEGMENT1, P.NAME, P.DISTRIBUTION_RULE, DR.MEANING, P.PROJECT_TYPE, PS.PROJECT_STATUS_NAME, P.PROJECT_STATUS_CODE, ORG.ORGANIZATION_ID, ORG.NAME, A.CUSTOMER_ID, HZ_P.PARTY_NAME, HZ_P.PARTY_NUMBER, I.AGREEMENT_ID, A.AGREEMENT_NUM, A.AGREEMENT_TYPE, A.TERM_ID, T.NAME, 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, CON.USER_CONVERSION_TYPE, P.PROJECT_CURRENCY_CODE, LK3.MEANING, X.FORM_CONTEXT_PROMPT, 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 , F.NLS_LANGUAGE , I.ACCTD_CURR_CODE , I.ACCTD_RATE_TYPE , I.ACCTD_RATE_DATE , I.ACCTD_EXCHG_RATE , decode(nvl(PT.CC_PRVDR_FLAG,'N'), 'Y', 'INTERCOMPANY INVOICE', decode(nvl(PC.BILL_ANOTHER_PROJECT_FLAG,'N'), 'Y', 'INTER-PROJECT INVOICE', 'EXTERNAL INVOICE')) , 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 ,HZ_P_bill.PARTY_number ,HZ_P_bill.PARTY_name ,HZ_P_ship.PARTY_number ,HZ_P_ship.PARTY_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 ,LK4.meaning ,LK5.meaning ,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.ORG_ID
, P.SEGMENT1 PROJECT_NUMBER
, P.NAME PROJECT_NAME
, P.DISTRIBUTION_RULE
, DR.MEANING DISTRIBUTION_RULE_M
, P.PROJECT_TYPE
, PS.PROJECT_STATUS_NAME PROJECT_STATUS_M
, P.PROJECT_STATUS_CODE PROJECT_STATUS_CODE
, ORG.ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, ORG.NAME PROJECT_ORGANIZATION_NAME
, A.CUSTOMER_ID
, HZ_P.PARTY_NAME
, HZ_P.PARTY_NUMBER
, I.AGREEMENT_ID
, A.AGREEMENT_NUM
, A.AGREEMENT_TYPE
, A.TERM_ID
, T.NAME 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
, CON.USER_CONVERSION_TYPE
, P.PROJECT_CURRENCY_CODE
, LK3.MEANING INVOICE_CLASS
, X.FORM_CONTEXT_PROMPT
, 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
, F.NLS_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
, HZ_P_BILL.PARTY_NUMBER
, HZ_P_BILL.PARTY_NAME
, HZ_P_SHIP.PARTY_NUMBER
, HZ_P_SHIP.PARTY_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
, LK4.MEANING
, LK5.MEANING
, A.CUSTOMER_ORDER_NUMBER
, A.LINE_OF_ACCOUNT
, I.PAYMENT_SET_ID
FROM PA_DRAFT_INVOICES_ALL I
, PA_DRAFT_INVOICE_ITEMS II
, PA_PROJECTS_ALL P
, PA_PROJECT_TYPES_ALL PT
, PA_PROJECT_CUSTOMERS PC
, PA_PROJECT_STATUSES PS
, PA_DISTRIBUTION_RULES DR
, HR_ALL_ORGANIZATION_UNITS ORG
, PA_AGREEMENTS_ALL A
, PA_DRAFT_INVOICES_ALL ORG_INV
, PA_CONVERSION_TYPES_V CON
, PA_IMPLEMENTATIONS_ALL PI
, GL_SETS_OF_BOOKS GSOB
, FND_LANGUAGES F
, RA_TERMS T
, HZ_PARTIES HZ_P
, HZ_CUST_ACCOUNTS HZ_C
, PER_ALL_PEOPLE_F EMP
, PER_ALL_PEOPLE_F EMP2
, PA_LOOKUPS LK
, PA_LOOKUPS LK3
, HZ_PARTIES HZ_P_BILL
, HZ_CUST_ACCOUNTS HZ_C_BILL
, HZ_PARTIES HZ_P_SHIP
, HZ_CUST_ACCOUNTS HZ_C_SHIP
, PA_LOOKUPS LK4
, PA_LOOKUPS LK5
, FND_DESCRIPTIVE_FLEXS_VL X
WHERE 'Y' IN (SELECT PA_SECURITY.ALLOW_QUERY(I.PROJECT_ID)
FROM SYS.DUAL)
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND P.ORG_ID = PT.ORG_ID
AND P.PROJECT_ID = PC.PROJECT_ID
AND A.CUSTOMER_ID = PC.CUSTOMER_ID
AND P.PROJECT_ID = I.PROJECT_ID
AND P.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE
AND PS.STATUS_TYPE = 'PROJECT'
AND I.AGREEMENT_ID = A.AGREEMENT_ID
AND PI.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND PI.ORG_ID = P.ORG_ID
AND CON.CONVERSION_TYPE (+) = I.INV_RATE_TYPE
AND DR.DISTRIBUTION_RULE = P.DISTRIBUTION_RULE
AND ORG_INV.PROJECT_ID (+) = I.PROJECT_ID
AND ORG_INV.DRAFT_INVOICE_NUM (+) = I.DRAFT_INVOICE_NUM_CREDITED
AND T.TERM_ID = A.TERM_ID
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 HZ_C.CUST_ACCOUNT_ID = A.CUSTOMER_ID
AND HZ_C.PARTY_ID = HZ_P.PARTY_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 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))
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 I.LANGUAGE = F.LANGUAGE_CODE(+)
AND HZ_C_BILL.CUST_ACCOUNT_ID (+)= I.BILL_TO_CUSTOMER_ID
AND HZ_P_BILL.PARTY_ID (+) = HZ_C_BILL.PARTY_ID
AND HZ_C_SHIP.CUST_ACCOUNT_ID(+) = I.SHIP_TO_CUSTOMER_ID
AND HZ_P_SHIP.PARTY_ID (+) = HZ_C_SHIP.PARTY_ID
AND LK4.LOOKUP_TYPE = 'REVENUE ACCRUAL METHOD'
AND LK4.LOOKUP_CODE = P.REVENUE_ACCRUAL_METHOD
AND LK5.LOOKUP_TYPE = 'INVOICE METHOD'
AND LK5.LOOKUP_CODE = P.INVOICE_METHOD
AND X.APPLICATION_ID(+) = I.PROGRAM_APPLICATION_ID
AND X.APPLICATION_TABLE_NAME(+) = 'PA_DRAFT_INVOICES_ALL'
AND X.DESCRIPTIVE_FLEXFIELD_NAME(+) = 'PA_DRAFT_INVOICES_DESC_FLEX' GROUP BY I.ROWID
, I.PROJECT_ID
, P.ORG_ID
, P.SEGMENT1
, P.NAME
, P.DISTRIBUTION_RULE
, DR.MEANING
, P.PROJECT_TYPE
, PS.PROJECT_STATUS_NAME
, P.PROJECT_STATUS_CODE
, ORG.ORGANIZATION_ID
, ORG.NAME
, A.CUSTOMER_ID
, HZ_P.PARTY_NAME
, HZ_P.PARTY_NUMBER
, I.AGREEMENT_ID
, A.AGREEMENT_NUM
, A.AGREEMENT_TYPE
, A.TERM_ID
, T.NAME
, 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
, CON.USER_CONVERSION_TYPE
, P.PROJECT_CURRENCY_CODE
, LK3.MEANING
, X.FORM_CONTEXT_PROMPT
, 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
, F.NLS_LANGUAGE
, I.ACCTD_CURR_CODE
, I.ACCTD_RATE_TYPE
, I.ACCTD_RATE_DATE
, I.ACCTD_EXCHG_RATE
, DECODE(NVL(PT.CC_PRVDR_FLAG
, 'N')
, 'Y'
, 'INTERCOMPANY INVOICE'
, DECODE(NVL(PC.BILL_ANOTHER_PROJECT_FLAG
, 'N')
, 'Y'
, 'INTER-PROJECT INVOICE'
, 'EXTERNAL INVOICE'))
, 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
, HZ_P_BILL.PARTY_NUMBER
, HZ_P_BILL.PARTY_NAME
, HZ_P_SHIP.PARTY_NUMBER
, HZ_P_SHIP.PARTY_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
, LK4.MEANING
, LK5.MEANING
, A.CUSTOMER_ORDER_NUMBER
, A.LINE_OF_ACCOUNT
, I.PAYMENT_SET_ID