VIEW: APPS.PA_CP_GENERATE_ASSET_BASE_V

Source

View Text - Preformatted

SELECT PP.SEGMENT1 , PP.NAME , NVL(PPA.ASSET_NAME,L.MEANING) , PPAL.REQUEST_ID , 0 , 0 , COUNT(PPAL.PROJECT_ASSET_LINE_ID) GENERATE_LINES , SUM(PPAL.CURRENT_ASSET_COST) GENERATE_AMOUNT , PP.PROJECT_CURRENCY_CODE , PPAL.LINE_TYPE , PPAL.retirement_cost_type , PP.ORG_ID FROM PA_PROJECTS_ALL PP, PA_PROJECT_ASSETS_ALL PPA, PA_PROJECT_ASSET_LINES_ALL PPAL, PA_LOOKUPS L WHERE PPAL.PROJECT_ASSET_ID = PPA.PROJECT_ASSET_ID(+) AND PPAL.PROJECT_ID = PP.PROJECT_ID AND PPAL.REV_PROJ_ASSET_LINE_ID IS NULL AND L.LOOKUP_TYPE ='UNASSIGNED ASSET' AND L.LOOKUP_CODE||'' ='UNASSIGNED' AND ((mo_global.get_current_org_id IS NULL AND mo_global.check_access(pp.org_id) = 'Y') OR ( mo_global.get_current_org_id is NOT NULL and pp.org_id = mo_global.get_current_org_id )) GROUP BY PP.SEGMENT1, PP.NAME,NVL( PPA.ASSET_NAME,L.MEANING), PPAL.REQUEST_ID, PP.PROJECT_CURRENCY_CODE,PPAL.LINE_TYPE,PPAL.retirement_cost_type,PP.ORG_ID UNION SELECT PP.SEGMENT1, PP.NAME, NVL(PPA.ASSET_NAME, L.MEANING), PPAL.REQUEST_ID, COUNT(PPAL.PROJECT_ASSET_LINE_ID) REVERSE_LINES, SUM(PPAL.CURRENT_ASSET_COST) REVERSE_AMOUNT, 0, 0, PP.PROJECT_CURRENCY_CODE, PPAL.LINE_TYPE, PPAL.retirement_cost_type, PP.ORG_ID FROM PA_PROJECTS_ALL PP, PA_PROJECT_ASSETS_ALL PPA, PA_PROJECT_ASSET_LINES_ALL PPAL, PA_LOOKUPS L WHERE PPAL.PROJECT_ASSET_ID = PPA.PROJECT_ASSET_ID(+) AND PPAL.PROJECT_ID = PP.PROJECT_ID AND PPAL.REV_PROJ_ASSET_LINE_ID IS NOT NULL AND PPAL.TRANSFER_STATUS_CODE <> 'R' AND L.LOOKUP_TYPE ='UNASSIGNED ASSET' AND L.LOOKUP_CODE||'' ='UNASSIGNED' AND ((mo_global.get_current_org_id IS NULL AND mo_global.check_access(pp.org_id) = 'Y') OR ( mo_global.get_current_org_id is NOT NULL and pp.org_id = mo_global.get_current_org_id )) GROUP BY PP.SEGMENT1, PP.NAME,NVL( PPA.ASSET_NAME,L.MEANING), PPAL.REQUEST_ID, PP.PROJECT_CURRENCY_CODE,PPAL.LINE_TYPE,PPAL.retirement_cost_type,PP.ORG_ID

View Text - HTML Formatted

SELECT PP.SEGMENT1
, PP.NAME
, NVL(PPA.ASSET_NAME
, L.MEANING)
, PPAL.REQUEST_ID
, 0
, 0
, COUNT(PPAL.PROJECT_ASSET_LINE_ID) GENERATE_LINES
, SUM(PPAL.CURRENT_ASSET_COST) GENERATE_AMOUNT
, PP.PROJECT_CURRENCY_CODE
, PPAL.LINE_TYPE
, PPAL.RETIREMENT_COST_TYPE
, PP.ORG_ID
FROM PA_PROJECTS_ALL PP
, PA_PROJECT_ASSETS_ALL PPA
, PA_PROJECT_ASSET_LINES_ALL PPAL
, PA_LOOKUPS L
WHERE PPAL.PROJECT_ASSET_ID = PPA.PROJECT_ASSET_ID(+)
AND PPAL.PROJECT_ID = PP.PROJECT_ID
AND PPAL.REV_PROJ_ASSET_LINE_ID IS NULL
AND L.LOOKUP_TYPE ='UNASSIGNED ASSET'
AND L.LOOKUP_CODE||'' ='UNASSIGNED'
AND ((MO_GLOBAL.GET_CURRENT_ORG_ID IS NULL
AND MO_GLOBAL.CHECK_ACCESS(PP.ORG_ID) = 'Y') OR ( MO_GLOBAL.GET_CURRENT_ORG_ID IS NOT NULL
AND PP.ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID )) GROUP BY PP.SEGMENT1
, PP.NAME
, NVL( PPA.ASSET_NAME
, L.MEANING)
, PPAL.REQUEST_ID
, PP.PROJECT_CURRENCY_CODE
, PPAL.LINE_TYPE
, PPAL.RETIREMENT_COST_TYPE
, PP.ORG_ID UNION SELECT PP.SEGMENT1
, PP.NAME
, NVL(PPA.ASSET_NAME
, L.MEANING)
, PPAL.REQUEST_ID
, COUNT(PPAL.PROJECT_ASSET_LINE_ID) REVERSE_LINES
, SUM(PPAL.CURRENT_ASSET_COST) REVERSE_AMOUNT
, 0
, 0
, PP.PROJECT_CURRENCY_CODE
, PPAL.LINE_TYPE
, PPAL.RETIREMENT_COST_TYPE
, PP.ORG_ID
FROM PA_PROJECTS_ALL PP
, PA_PROJECT_ASSETS_ALL PPA
, PA_PROJECT_ASSET_LINES_ALL PPAL
, PA_LOOKUPS L
WHERE PPAL.PROJECT_ASSET_ID = PPA.PROJECT_ASSET_ID(+)
AND PPAL.PROJECT_ID = PP.PROJECT_ID
AND PPAL.REV_PROJ_ASSET_LINE_ID IS NOT NULL
AND PPAL.TRANSFER_STATUS_CODE <> 'R'
AND L.LOOKUP_TYPE ='UNASSIGNED ASSET'
AND L.LOOKUP_CODE||'' ='UNASSIGNED'
AND ((MO_GLOBAL.GET_CURRENT_ORG_ID IS NULL
AND MO_GLOBAL.CHECK_ACCESS(PP.ORG_ID) = 'Y') OR ( MO_GLOBAL.GET_CURRENT_ORG_ID IS NOT NULL
AND PP.ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID )) GROUP BY PP.SEGMENT1
, PP.NAME
, NVL( PPA.ASSET_NAME
, L.MEANING)
, PPAL.REQUEST_ID
, PP.PROJECT_CURRENCY_CODE
, PPAL.LINE_TYPE
, PPAL.RETIREMENT_COST_TYPE
, PP.ORG_ID