VIEW: APPS.GMS_DRAFT_INVOICES_V

Source

View Text - Preformatted

SELECT I.ROWID ROW_ID , AW.AWARD_ID , AW.AWARD_NUMBER , AW.AWARD_SHORT_NAME , I.PROJECT_ID , P.SEGMENT1 PROJECT_NUMBER , AW.AWARD_SHORT_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 , SUBSTRB(PARTY.PARTY_NAME,1,50) , C.ACCOUNT_NUMBER, I.AGREEMENT_ID , A.AGREEMENT_NUM , AW.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 , 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(NVL(II.INV_AMOUNT,0)) 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 , SUBSTR(GMS_SECURITY.ALLOW_UPDATE(AW.AWARD_ID),1,1) UPDATE_ALLOW, 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.ACCOUNT_NUMBER, SUBSTR(PARTY1.PARTY_NAME,1,50) , SHIP_C.ACCOUNT_NUMBER, SUBSTR(PARTY2.PARTY_NAME,1,50), NULL, NULL, NULL, NULL, 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, AW.ORG_ID, LK4.meaning, LK5.meaning, 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_TYPES 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 ORG_INV, PA_CONVERSION_TYPES_V CON, PA_IMPLEMENTATIONS_ALL PI, GL_SETS_OF_BOOKS GSOB, FND_LANGUAGES F, RA_TERMS T, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS C, PER_ALL_PEOPLE_F EMP, PER_ALL_PEOPLE_F EMP2, PA_LOOKUPS LK, PA_LOOKUPS LK3, HZ_PARTIES PARTY1, HZ_CUST_ACCOUNTS BILL_C, HZ_PARTIES PARTY2, HZ_CUST_ACCOUNTS SHIP_C, GMS_AWARDS AW, PA_LOOKUPS LK4, PA_LOOKUPS LK5, HZ_CUST_SITE_USES BILL_SITE, HZ_CUST_SITE_USES SHIP_SITE, PA_PROJECT_PARTIES PPP, PA_PROJECT_ROLE_TYPES_B PPRT, PA_ROLE_CONTROLS PRC WHERE 'Y' in (select GMS_SECURITY.ALLOW_QUERY(AW.AWARD_ID) from sys.dual) AND AW.AWARD_PROJECT_ID = P.PROJECT_ID AND AW.AGREEMENT_ID = A.AGREEMENT_ID AND P.PROJECT_TYPE = PT.PROJECT_TYPE 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 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 I.ORG_ID = PI.ORG_ID AND C.CUST_ACCOUNT_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 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 BILL_C.CUST_ACCOUNT_ID(+) = I.BILL_TO_CUSTOMER_ID AND SHIP_C.CUST_ACCOUNT_ID(+) = I.SHIP_TO_CUSTOMER_ID AND AW.ORG_ID = PI.ORG_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 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.STATUS(+) = 'A' AND SHIP_SITE.SITE_USE_CODE(+) = 'SHIP_TO' AND PARTY1.PARTY_ID=BILL_C.PARTY_ID AND PARTY2.PARTY_ID=SHIP_C.PARTY_ID AND PARTY.PARTY_ID=C.PARTY_ID 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, AW.AWARD_ID, AW.AWARD_NUMBER, AW.AWARD_SHORT_NAME, I.PROJECT_ID, P.SEGMENT1, AW.AWARD_SHORT_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, SUBSTRB(PARTY.PARTY_NAME,1,50) , C.ACCOUNT_NUMBER, I.AGREEMENT_ID, A.AGREEMENT_NUM, AW.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, 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 , BILL_C.ACCOUNT_NUMBER, SUBSTR(PARTY1.PARTY_NAME,1,50) , SHIP_C.ACCOUNT_NUMBER, SUBSTR(PARTY2.PARTY_NAME,1,50), NULL, NULL, NULL, NULL, 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, 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, AW.ORG_ID

View Text - HTML Formatted

SELECT I.ROWID ROW_ID
, AW.AWARD_ID
, AW.AWARD_NUMBER
, AW.AWARD_SHORT_NAME
, I.PROJECT_ID
, P.SEGMENT1 PROJECT_NUMBER
, AW.AWARD_SHORT_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
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, C.ACCOUNT_NUMBER
, I.AGREEMENT_ID
, A.AGREEMENT_NUM
, AW.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
, 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(NVL(II.INV_AMOUNT
, 0)) 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
, SUBSTR(GMS_SECURITY.ALLOW_UPDATE(AW.AWARD_ID)
, 1
, 1) UPDATE_ALLOW
, 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.ACCOUNT_NUMBER
, SUBSTR(PARTY1.PARTY_NAME
, 1
, 50)
, SHIP_C.ACCOUNT_NUMBER
, SUBSTR(PARTY2.PARTY_NAME
, 1
, 50)
, NULL
, NULL
, NULL
, NULL
, 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
, AW.ORG_ID
, LK4.MEANING
, LK5.MEANING
, 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_TYPES 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 ORG_INV
, PA_CONVERSION_TYPES_V CON
, PA_IMPLEMENTATIONS_ALL PI
, GL_SETS_OF_BOOKS GSOB
, FND_LANGUAGES F
, RA_TERMS T
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS C
, PER_ALL_PEOPLE_F EMP
, PER_ALL_PEOPLE_F EMP2
, PA_LOOKUPS LK
, PA_LOOKUPS LK3
, HZ_PARTIES PARTY1
, HZ_CUST_ACCOUNTS BILL_C
, HZ_PARTIES PARTY2
, HZ_CUST_ACCOUNTS SHIP_C
, GMS_AWARDS AW
, PA_LOOKUPS LK4
, PA_LOOKUPS LK5
, HZ_CUST_SITE_USES BILL_SITE
, HZ_CUST_SITE_USES SHIP_SITE
, PA_PROJECT_PARTIES PPP
, PA_PROJECT_ROLE_TYPES_B PPRT
, PA_ROLE_CONTROLS PRC
WHERE 'Y' IN (SELECT GMS_SECURITY.ALLOW_QUERY(AW.AWARD_ID)
FROM SYS.DUAL)
AND AW.AWARD_PROJECT_ID = P.PROJECT_ID
AND AW.AGREEMENT_ID = A.AGREEMENT_ID
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
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 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 I.ORG_ID = PI.ORG_ID
AND C.CUST_ACCOUNT_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 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 BILL_C.CUST_ACCOUNT_ID(+) = I.BILL_TO_CUSTOMER_ID
AND SHIP_C.CUST_ACCOUNT_ID(+) = I.SHIP_TO_CUSTOMER_ID
AND AW.ORG_ID = PI.ORG_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 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.STATUS(+) = 'A'
AND SHIP_SITE.SITE_USE_CODE(+) = 'SHIP_TO'
AND PARTY1.PARTY_ID=BILL_C.PARTY_ID
AND PARTY2.PARTY_ID=SHIP_C.PARTY_ID
AND PARTY.PARTY_ID=C.PARTY_ID
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
, AW.AWARD_ID
, AW.AWARD_NUMBER
, AW.AWARD_SHORT_NAME
, I.PROJECT_ID
, P.SEGMENT1
, AW.AWARD_SHORT_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
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, C.ACCOUNT_NUMBER
, I.AGREEMENT_ID
, A.AGREEMENT_NUM
, AW.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
, 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
, BILL_C.ACCOUNT_NUMBER
, SUBSTR(PARTY1.PARTY_NAME
, 1
, 50)
, SHIP_C.ACCOUNT_NUMBER
, SUBSTR(PARTY2.PARTY_NAME
, 1
, 50)
, NULL
, NULL
, NULL
, NULL
, 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
, 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
, AW.ORG_ID