select Project_type,segment1, name,project_status_code,start_date,completion_date,baseline_funding_flag,carrying_out_organization_id,project_level_funding_flag from PA_PROJECTS_ALL where org_id = '1062' and template_flag = 'N'
Events
selectA.SEGMENT1,B.COMPLETION_DATE,B.EVENT_TYPE,B.DESCRIPTION,B.BILL_AMOUNT,B.BILL_HOLD_FLAG,B.BILL_TRANS_REV_AMOUNT, B.PROJECT_CURRENCY_CODE,A.ORG_ID,C.NAME from PA_EVENTS B,PA_PROJECTS_ALL A,HR_ORGANIZATION_UNITS C where A.PROJECT_ID = B.PROJECT_ID and B.ORGANIZATION_ID = C.ORGANIZATION_ID AND ORG_id = '1062'
Revenue Events
select A.SEGMENT1,B.COMPLETION_DATE,B.EVENT_TYPE,B.DESCRIPTION,B.BILL_TRANS_REV_AMOUNT, B.PROJECT_CURRENCY_CODE,A.ORG_ID,C.NAME from PA_EVENTS B,PA_PROJECTS_ALL A,HR_ORGANIZATION_UNITS C where A.PROJECT_ID = B.PROJECT_ID and B.ORGANIZATION_ID = C.ORGANIZATION_ID AND ORG_id = '1062' and EVENT_TYPE like 'Revenue%' Billing Events
select A.SEGMENT1,B.COMPLETION_DATE,B.EVENT_TYPE,B.DESCRIPTION,B.BILL_AMOUNT,B.BILL_HOLD_FLAG, B.PROJECT_CURRENCY_CODE,A.ORG_ID,C.NAME from PA_EVENTS B,PA_PROJECTS_ALL A,HR_ORGANIZATION_UNITS C where A.PROJECT_ID = B.PROJECT_ID and B.ORGANIZATION_ID = C.ORGANIZATION_ID AND ORG_id = '1062' and EVENT_TYPE like 'Invoice%'
PROJECT WIP
select a.segment1,b.expenditure_item_date, b.expenditure_type,b.burden_cost,b.raw_cost,b.transaction_source from PA_PROJECTS_ALL a, PA_EXPENDITURE_ITEMS_ALL b where a.project_id = b.project_id and a.org_id = '1062' and transaction_source = 'XX FLS Excel Based Migration C'
Project Funding
select ppa.segment1,acv.customer_name,paa.agreement_num,ppf.funding_currency_code,ppf.allocated_amount,ppf.projfunc_currency_code,ppf.projfunc_allocated_amount,ppf.budget_type_code from pa_projects_all ppa, PA_PROJECT_FUNDINGS ppf,PA_AGREEMENTS_ALL paa,AR_CUSTOMERS_V acv where ppa.project_id = ppf.project_id and ppa.org_id = '1062' and paa.agreement_id = ppf.agreement_id and paa.customer_id = acv.customer_id
CREDIT MEMO CREATION FOR PROJECTS MIGRATED INVOICES 1. Take RA Interface table backup create table xxs_dc_ra_intf_lns_v01 as select inf_lns.* from apps.ra_interface_lines_all inf_lns where 1=1 and inf_lns.batch_source_name = 'FNW_PROJECTS_INVOICES' 2. Create Upload data and sent for loading
select 'OPNAR' RECORD_TYPE, 'FNW' CONV_CODE, 'FNW' ORGANIZATION_CODE, rownum RECORD_NUMBER, 'S' TRANS_STATUS, 'LINE' LINE_TYPE, DESCRIPTION DESCRIPTION, null PAYMENT_TERMS, ra.TAX_CODE, arc.orig_system_reference CUSTOMER_NUMBER, 'FNW' ORIG_SYS_REF, ( select max(hcsua.orig_system_reference) from apps.HZ_CUST_SITE_USES_ALL hcsua where hcsua.cust_acct_site_id = ra.ORIG_SYSTEM_BILL_ADDRESS_ID ) ORIG_SYSTEM_SHIP_ADDRESS_REF, ( select max(hcsua.orig_system_reference) from apps.HZ_CUST_SITE_USES_ALL hcsua where hcsua.cust_acct_site_id = ra.ORIG_SYSTEM_BILL_ADDRESS_ID ) ORIG_SYSTEM_BILL_ADDRESS_REF, ra.TRX_NUMBER INVOICE_NUMBER, ra.TRX_DATE INVOICE_DATE, ra.AMOUNT*(-1) AMOUNT, ra.AMOUNT*(-1) AMT_COUNTRY_CURRENCY, ra.CURRENCY_CODE, 'FNW_IMPORTED_INV' TRANSACTION_SOURCE, ra.GL_DATE, 'FNW PROJECTS CR MEMO' TXN_TYPE_NAME, ra.INTERFACE_LINE_ATTRIBUTE1 PROJECT_NUMBER, '00' TASK_NUMBER, null SHIP_DATE_ACTUAL from xxs_dc_ra_intf_lns_v01 ra , AR_CUSTOMERS_V arc where 1=1 and ra.batch_source_name = 'FNW_PROJECTS_INVOICES' and ra.orig_system_bill_customer_id = arc.customer_id