eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: APPS.PA_VRM_SOURCE_DOCUMENT

Source

View Text - Preformatted


SELECT 275 APPLICATION_ID, PR.PROJECT_ID DOC_ID_INT_1, PR.START_DATE DOCUMENT_DATE, PR.SEGMENT1 DOCUMENT_NUMBER, DECODE(PR.REVENUE_ACCRUAL_METHOD,'WORK','T&'||'M','Fixed Bid') DOCUMENT_TYPE, PR.CREATION_DATE DOCUMENT_CREATION_DATE, PR.LAST_UPDATE_DATE DOCUMENT_UPDATE_DATE, PR.PROJFUNC_CURRENCY_CODE CURRENCY_CODE, HCA1.PARTY_ID BILL_TO_CUSTOMER_PARTY_ID, HP1.PARTY_NUMBER BILL_TO_CUSTOMER_PARTY_NUM, HP1.PARTY_NAME BILL_TO_CUSTOMER_PARTY_NAME, PC.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID, PC.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID, HCA1.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUM, HCA2.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NUM, HCA1.ACCOUNT_NAME BILL_TO_CUSTOMER_NAME, HCA2.ACCOUNT_NAME SHIP_TO_CUSTOMER_NAME, PC.BILL_TO_ADDRESS_ID BILL_TO_CUSTOMER_SITE_ID, HPS1.PARTY_SITE_NAME BILL_TO_CUST_SITE_NAME, PC.SHIP_TO_ADDRESS_ID SHIP_TO_CUSTOMER_SITE_ID, HPS2.PARTY_SITE_NAME SHIP_TO_CUST_SITE_NAME, HL1.COUNTRY BILL_TO_COUNTRY, HL2.COUNTRY SHIP_TO_COUNTRY, PR.ORG_ID ORG_ID, HOU.NAME ORGANIZATION_NAME, PI.SET_OF_BOOKS_ID LEDGER_ID, GLL.NAME LEDGER_NAME, XEP.LEGAL_ENTITY_ID LEGAL_ENTITY_ID, XEP.NAME LEGAL_ENTITY_NAME, XEP.COUNTRY LEGAL_ENTITY_COUNTRY, XEP.ADDRESS_LINE_1 LEGAL_ENTITY_ADDRESS, DECODE(PS.PROJECT_SYSTEM_STATUS_CODE, 'CLOSED','N','Y') OPEN_FLAG, HL1.STATE BILL_TO_CUSTOMER_STATE, HL1.COUNTY BILL_TO_CUSTOMER_COUNTY, HL1.CITY BILL_TO_CUSTOMER_CITY, HL1.POSTAL_CODE BILL_TO_CUSTOMER_POSTAL_CODE, HCA1.CUSTOMER_CLASS_CODE BILL_TO_CUST_CLASSIFICATION, PR.ATTRIBUTE1 SRC_ATTRIBUTE_CHAR1, PR.ATTRIBUTE2 SRC_ATTRIBUTE_CHAR2, PR.ATTRIBUTE3 SRC_ATTRIBUTE_CHAR3, PR.ATTRIBUTE4 SRC_ATTRIBUTE_CHAR4, PR.ATTRIBUTE5 SRC_ATTRIBUTE_CHAR5, PR.ATTRIBUTE6 SRC_ATTRIBUTE_CHAR6, PR.ATTRIBUTE7 SRC_ATTRIBUTE_CHAR7, PR.ATTRIBUTE8 SRC_ATTRIBUTE_CHAR8, PR.ATTRIBUTE9 SRC_ATTRIBUTE_CHAR9, PR.ATTRIBUTE10 SRC_ATTRIBUTE_CHAR10, 1 OBJECT_VERSION_NUMBER, 'Y' ACCOUNTING_EFFECT_FLAG, FU1.USER_NAME CREATED_BY, PR.CREATION_DATE CREATION_DATE, FU2.USER_NAME LAST_UPDATED_BY, PR.LAST_UPDATE_DATE LAST_UPDATE_DATE, PR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, HPS1.PARTY_SITE_NUMBER SOURCE_BILL_TO_CUST_SITE_NUM, HL1.ADDRESS1||','||HL1.ADDRESS2||','||HL1.ADDRESS3||','||HL1.ADDRESS4||','||HL1.CITY||','||HL1.POSTAL_CODE SOURCE_BILL_TO_CUST_ADDRESS, HPS2.PARTY_SITE_NUMBER SOURCE_SHIP_TO_CUST_SITE_NUM, HL2.ADDRESS1||','||HL2.ADDRESS2||','||HL2.ADDRESS3||','||HL2.ADDRESS4||','||HL2.CITY||','||HL2.POSTAL_CODE SOURCE_SHIP_TO_CUST_ADDRESS FROM PA_PROJECTS_ALL PR, PA_PROJECT_TYPES_ALL PT, PA_PROJECT_CUSTOMERS PC, PA_PROJECT_STATUSES PS, HZ_CUST_ACCOUNTS HCA1, HZ_CUST_ACCT_SITES_ALL HCS1, HZ_PARTIES HP1, HZ_PARTY_SITES HPS1, HZ_LOCATIONS HL1, HZ_CUST_ACCOUNTS HCA2, HZ_CUST_ACCT_SITES_ALL HCS2, HZ_PARTIES HP2, HZ_PARTY_SITES HPS2, HZ_LOCATIONS HL2, PA_IMPLEMENTATIONS_ALL PI, FND_USER FU1, FND_USER FU2, HR_ALL_ORGANIZATION_UNITS HOU, GL_LEDGERS GLL, HR_ORGANIZATION_INFORMATION HOI, XLE_FIRSTPARTY_INFORMATION_V XEP WHERE PR.TEMPLATE_FLAG = 'N' AND PR.INV_BY_BILL_TRANS_CURR_FLAG = 'N' AND PR.ALLOW_CROSS_CHARGE_FLAG = 'N' AND PR.PROJECT_ID = PC.PROJECT_ID AND PR.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE AND PT.PROJECT_TYPE = PR.PROJECT_TYPE AND PT.ORG_ID=PR.ORG_ID AND PT.PROJECT_TYPE_CLASS_CODE = 'CONTRACT' AND PR.REVENUE_ACCRUAL_METHOD IN ('WORK','EVENT') AND PR.ORG_ID = PI.ORG_ID AND PR.CREATED_BY = FU1.USER_ID AND PR.LAST_UPDATED_BY = FU2.USER_ID AND 1 = (SELECT COUNT(*) FROM PA_PROJECT_CUSTOMERS PC1 WHERE PC1.PROJECT_ID = PR.PROJECT_ID) AND HCA1.CUST_ACCOUNT_ID = PC.BILL_TO_CUSTOMER_ID AND HCA1.PARTY_ID = HP1.PARTY_ID AND HP1.PARTY_ID = HPS1.PARTY_ID AND HCS1.CUST_ACCT_SITE_ID = PC.BILL_TO_ADDRESS_ID AND HCS1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID AND HL1.LOCATION_ID = HPS1.LOCATION_ID AND HCA2.CUST_ACCOUNT_ID = PC.SHIP_TO_CUSTOMER_ID AND HCA2.PARTY_ID = HP2.PARTY_ID AND HP2.PARTY_ID = HPS2.PARTY_ID AND HCS2.CUST_ACCT_SITE_ID = PC.SHIP_TO_ADDRESS_ID AND HCS2.PARTY_SITE_ID = HPS2.PARTY_SITE_ID AND HL2.LOCATION_ID = HPS2.LOCATION_ID AND HOU.ORGANIZATION_ID = PR.ORG_ID AND GLL.LEDGER_ID = PI.SET_OF_BOOKS_ID AND HOI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND HOI.ORGANIZATION_ID = PI.ORG_ID AND XEP.LEGAL_ENTITY_ID = HOI.ORG_INFORMATION2 AND EXISTS( SELECT 1 FROM PA_BUDGET_VERSIONS PBV WHERE PBV.PROJECT_ID = PR.PROJECT_ID AND PBV.APPROVED_COST_PLAN_TYPE_FLAG='Y' AND PBV.BUDGET_STATUS_CODE = 'B') AND EXISTS( SELECT 1 FROM PA_BUDGET_VERSIONS PBV WHERE PBV.PROJECT_ID = PR.PROJECT_ID AND PBV.APPROVED_REV_PLAN_TYPE_FLAG='Y' AND PBV.BUDGET_STATUS_CODE = 'B')

View Text - HTML Formatted

SELECT 275 APPLICATION_ID
, PR.PROJECT_ID DOC_ID_INT_1
, PR.START_DATE DOCUMENT_DATE
, PR.SEGMENT1 DOCUMENT_NUMBER
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 'T&'||'M'
, 'FIXED BID') DOCUMENT_TYPE
, PR.CREATION_DATE DOCUMENT_CREATION_DATE
, PR.LAST_UPDATE_DATE DOCUMENT_UPDATE_DATE
, PR.PROJFUNC_CURRENCY_CODE CURRENCY_CODE
, HCA1.PARTY_ID BILL_TO_CUSTOMER_PARTY_ID
, HP1.PARTY_NUMBER BILL_TO_CUSTOMER_PARTY_NUM
, HP1.PARTY_NAME BILL_TO_CUSTOMER_PARTY_NAME
, PC.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, PC.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID
, HCA1.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUM
, HCA2.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NUM
, HCA1.ACCOUNT_NAME BILL_TO_CUSTOMER_NAME
, HCA2.ACCOUNT_NAME SHIP_TO_CUSTOMER_NAME
, PC.BILL_TO_ADDRESS_ID BILL_TO_CUSTOMER_SITE_ID
, HPS1.PARTY_SITE_NAME BILL_TO_CUST_SITE_NAME
, PC.SHIP_TO_ADDRESS_ID SHIP_TO_CUSTOMER_SITE_ID
, HPS2.PARTY_SITE_NAME SHIP_TO_CUST_SITE_NAME
, HL1.COUNTRY BILL_TO_COUNTRY
, HL2.COUNTRY SHIP_TO_COUNTRY
, PR.ORG_ID ORG_ID
, HOU.NAME ORGANIZATION_NAME
, PI.SET_OF_BOOKS_ID LEDGER_ID
, GLL.NAME LEDGER_NAME
, XEP.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, XEP.NAME LEGAL_ENTITY_NAME
, XEP.COUNTRY LEGAL_ENTITY_COUNTRY
, XEP.ADDRESS_LINE_1 LEGAL_ENTITY_ADDRESS
, DECODE(PS.PROJECT_SYSTEM_STATUS_CODE
, 'CLOSED'
, 'N'
, 'Y') OPEN_FLAG
, HL1.STATE BILL_TO_CUSTOMER_STATE
, HL1.COUNTY BILL_TO_CUSTOMER_COUNTY
, HL1.CITY BILL_TO_CUSTOMER_CITY
, HL1.POSTAL_CODE BILL_TO_CUSTOMER_POSTAL_CODE
, HCA1.CUSTOMER_CLASS_CODE BILL_TO_CUST_CLASSIFICATION
, PR.ATTRIBUTE1 SRC_ATTRIBUTE_CHAR1
, PR.ATTRIBUTE2 SRC_ATTRIBUTE_CHAR2
, PR.ATTRIBUTE3 SRC_ATTRIBUTE_CHAR3
, PR.ATTRIBUTE4 SRC_ATTRIBUTE_CHAR4
, PR.ATTRIBUTE5 SRC_ATTRIBUTE_CHAR5
, PR.ATTRIBUTE6 SRC_ATTRIBUTE_CHAR6
, PR.ATTRIBUTE7 SRC_ATTRIBUTE_CHAR7
, PR.ATTRIBUTE8 SRC_ATTRIBUTE_CHAR8
, PR.ATTRIBUTE9 SRC_ATTRIBUTE_CHAR9
, PR.ATTRIBUTE10 SRC_ATTRIBUTE_CHAR10
, 1 OBJECT_VERSION_NUMBER
, 'Y' ACCOUNTING_EFFECT_FLAG
, FU1.USER_NAME CREATED_BY
, PR.CREATION_DATE CREATION_DATE
, FU2.USER_NAME LAST_UPDATED_BY
, PR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, HPS1.PARTY_SITE_NUMBER SOURCE_BILL_TO_CUST_SITE_NUM
, HL1.ADDRESS1||'
, '||HL1.ADDRESS2||'
, '||HL1.ADDRESS3||'
, '||HL1.ADDRESS4||'
, '||HL1.CITY||'
, '||HL1.POSTAL_CODE SOURCE_BILL_TO_CUST_ADDRESS
, HPS2.PARTY_SITE_NUMBER SOURCE_SHIP_TO_CUST_SITE_NUM
, HL2.ADDRESS1||'
, '||HL2.ADDRESS2||'
, '||HL2.ADDRESS3||'
, '||HL2.ADDRESS4||'
, '||HL2.CITY||'
, '||HL2.POSTAL_CODE SOURCE_SHIP_TO_CUST_ADDRESS
FROM PA_PROJECTS_ALL PR
, PA_PROJECT_TYPES_ALL PT
, PA_PROJECT_CUSTOMERS PC
, PA_PROJECT_STATUSES PS
, HZ_CUST_ACCOUNTS HCA1
, HZ_CUST_ACCT_SITES_ALL HCS1
, HZ_PARTIES HP1
, HZ_PARTY_SITES HPS1
, HZ_LOCATIONS HL1
, HZ_CUST_ACCOUNTS HCA2
, HZ_CUST_ACCT_SITES_ALL HCS2
, HZ_PARTIES HP2
, HZ_PARTY_SITES HPS2
, HZ_LOCATIONS HL2
, PA_IMPLEMENTATIONS_ALL PI
, FND_USER FU1
, FND_USER FU2
, HR_ALL_ORGANIZATION_UNITS HOU
, GL_LEDGERS GLL
, HR_ORGANIZATION_INFORMATION HOI
, XLE_FIRSTPARTY_INFORMATION_V XEP
WHERE PR.TEMPLATE_FLAG = 'N'
AND PR.INV_BY_BILL_TRANS_CURR_FLAG = 'N'
AND PR.ALLOW_CROSS_CHARGE_FLAG = 'N'
AND PR.PROJECT_ID = PC.PROJECT_ID
AND PR.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE
AND PT.PROJECT_TYPE = PR.PROJECT_TYPE
AND PT.ORG_ID=PR.ORG_ID
AND PT.PROJECT_TYPE_CLASS_CODE = 'CONTRACT'
AND PR.REVENUE_ACCRUAL_METHOD IN ('WORK'
, 'EVENT')
AND PR.ORG_ID = PI.ORG_ID
AND PR.CREATED_BY = FU1.USER_ID
AND PR.LAST_UPDATED_BY = FU2.USER_ID
AND 1 = (SELECT COUNT(*)
FROM PA_PROJECT_CUSTOMERS PC1
WHERE PC1.PROJECT_ID = PR.PROJECT_ID)
AND HCA1.CUST_ACCOUNT_ID = PC.BILL_TO_CUSTOMER_ID
AND HCA1.PARTY_ID = HP1.PARTY_ID
AND HP1.PARTY_ID = HPS1.PARTY_ID
AND HCS1.CUST_ACCT_SITE_ID = PC.BILL_TO_ADDRESS_ID
AND HCS1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID
AND HL1.LOCATION_ID = HPS1.LOCATION_ID
AND HCA2.CUST_ACCOUNT_ID = PC.SHIP_TO_CUSTOMER_ID
AND HCA2.PARTY_ID = HP2.PARTY_ID
AND HP2.PARTY_ID = HPS2.PARTY_ID
AND HCS2.CUST_ACCT_SITE_ID = PC.SHIP_TO_ADDRESS_ID
AND HCS2.PARTY_SITE_ID = HPS2.PARTY_SITE_ID
AND HL2.LOCATION_ID = HPS2.LOCATION_ID
AND HOU.ORGANIZATION_ID = PR.ORG_ID
AND GLL.LEDGER_ID = PI.SET_OF_BOOKS_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND HOI.ORGANIZATION_ID = PI.ORG_ID
AND XEP.LEGAL_ENTITY_ID = HOI.ORG_INFORMATION2
AND EXISTS( SELECT 1
FROM PA_BUDGET_VERSIONS PBV
WHERE PBV.PROJECT_ID = PR.PROJECT_ID
AND PBV.APPROVED_COST_PLAN_TYPE_FLAG='Y'
AND PBV.BUDGET_STATUS_CODE = 'B')
AND EXISTS( SELECT 1
FROM PA_BUDGET_VERSIONS PBV
WHERE PBV.PROJECT_ID = PR.PROJECT_ID
AND PBV.APPROVED_REV_PLAN_TYPE_FLAG='Y'
AND PBV.BUDGET_STATUS_CODE = 'B')