Project Migration Extract Queries

OPEN PROJECTS

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