1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
|
select
1 as S4_REPORT_ACTION_PLAN_ID
, 'A' + convert(char, a.S4_ACTIVITY_NUMBER) as S4_REPORT_ACTION_PLAN_NUMBER
, convert(char(8),a.S4_REQUEST_DATE,112) as S4_REPORT_ACTION_PLAN_DEAD_LINE
, sourc_coun.RF_COUNTRY_CODE as S4_REPORT_ACTION_PLAN_SOURCING_COUNTRY
, sourc_coun.RF_COUNTRY_DESC as S4_REPORT_ACTION_PLAN_SOURCING_COUNTRY_DESC
, dest_coun.SAM_RF_COUNTRY_CODE as S4_REPORT_ACTION_PLAN_DEST_COUNTRY
, dest_coun.SAM_RF_COUNTRY_DESC as S4_REPORT_ACTION_PLAN_DEST_COUNTRY_DESC
, a.S4_SOURCING_REGIONAL_UNIT as S4_REPORT_ACTION_PLAN_SOURCING_REGIONAL_UNIT
, a.S4_SOURCING_REGION as S4_REPORT_ACTION_PLAN_SOURCING_REGION
, dest_ru.SAM_RF_REGIONAL_UNIT_REGION as S4_REPORT_ACTION_PLAN_DEST_REGION_DESC
, dest_ru.SAM_RF_REGIONAL_UNIT_REGION as S4_REPORT_ACTION_PLAN_DEST_REGION
, a.S4_DESTINATION_REGIONAL_UNIT as S4_REPORT_ACTION_PLAN_DEST_REGIONAL_UNIT
, dest_ru.SAM_RF_REGIONAL_UNIT_DESC as S4_REPORT_ACTION_PLAN_DEST_REGIONAL_UNIT_DESC
, p.S4_PROJECT_NAME as S4_REPORT_ACTION_PLAN_PROJECT_NAME
, isnull(p.S4_PROJECT_NAME,'') + ' - ' + convert(varchar(20),isnull(p.S4_PROJECT_NUMBER,0)) as S4_REPORT_ACTION_PLAN_PROJECT_NAME_NUMBER
, p.S4_PROJECT_NUMBER as S4_REPORT_ACTION_PLAN_PROJECT_NUMBER
, '' as S4_REPORT_ACTION_PLAN_STRATEGICAL_PROJECT
, p.S4_CUSTOMER as S4_REPORT_ACTION_PLAN_CUSTOMER
, a.S4_BUSINESS_DIVISION as S4_REPORT_ACTION_PLAN_BD
, a.S4_BUSINESS_GROUP as S4_REPORT_ACTION_PLAN_BG
, isnull(a.S4_SERVICE_BUSINESS_GROUP,'') + isnull(a.S4_SERVICE_BUSINESS_DIVISION,'') as S4_REPORT_ACTION_PLAN_SERVICE_BD_BG
, a.S4_ACTIVITY_DESCRIPTION as S4_REPORT_ACTION_PLAN_ACTION_DESC
, sfam.RF_ORG4_SUBFAM_DESC as S4_REPORT_ACTION_PLAN_SUB_FAMILY
, a.S4_FAMILY as S4_REPORT_ACTION_PLAN_FAMILY
, a.S4_COUNCIL as S4_REPORT_ACTION_PLAN_COUNCIL
, a.S4_NEGOTIATION_TYPE as S4_REPORT_ACTION_PLAN_NEGO_TYPE
, convert(char(8),a.S4_AXIS_PRICE_VALID_FROM_DATE,112) as S4_REPORT_ACTION_PLAN_PRICE_VALID_FROM
, round(isnull(a.S4_ACTIVITY_PROBABILITY_PERCENT,0) * isnull(a.S4_PROJECT_PROBABILITY_PERCENT,0) * 100,2) as S4_REPORT_ACTION_PLAN_TOTAL_PROBABILITY
, round(isnull(a.S4_NEGO_ESTIMATED_SUCCESS_RATE,0) * 100, 2) as S4_REPORT_ACTION_PLAN_NEGO_RATE
, round(isnull(S4_ACTUAL_SAVING_PERCENT,0) * 100,2) as S4_REPORT_ACTION_PLAN_GPS_SAVING
, case when a.S4_BUDGET_PROVISION_FLAG = 'N' then 'NO' when a.S4_BUDGET_PROVISION_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_BUDGET_PROVISION_FLAG
, case when a.S4_RENEGOTIATION_FLAG = 'N' then 'NO' when a.S4_RENEGOTIATION_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_RENEGOTIATION_FLAG
, p.S4_PROJECT_ACTIVITY_GROSS_SPEND_EUR as S4_REPORT_ACTION_PLAN_GROSS_SPEND_EUR
, case when a.S4_AXIS_CONTRACT_NUMBER = '0' then '' else convert(varchar(20),a.S4_AXIS_CONTRACT_NUMBER) end as S4_REPORT_ACTION_PLAN_AXIS_CONTRACT_NUMBER
, List.FieldLabel as S4_REPORT_ACTION_PLAN_CRP_CATEGORY
, case when a.S4_SPOT_TEAM_FLAG = 'N' then 'NO' when a.S4_SPOT_TEAM_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_SPOT_TEAM_FLAG
, case when a.S4_SYNERGY_FLAG = 'N' then 'NO' when a.S4_SYNERGY_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_SYNERGY_FLAG
, case when a.S4_RECURRENT_FLAG = 'N' then 'NO' when a.S4_RECURRENT_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_RECURRENT_FLAG
, case when a.S4_LOCAL_ACTIVITY_FLAG = 'N' then 'NO' when a.S4_LOCAL_ACTIVITY_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_LOCAL_ACTIVITY_FLAG
, 'Project Sourcing' as S4_REPORT_ACTION_PLAN_DOMAIN
, a.S4_RPIS_OFFICIAL_EUR as S4_REPORT_ACTION_PLAN_RPIS_OFFICIAL_EUR
, a.S4_SERVICE_BUSINESS_GROUP as S4_REPORT_ACTION_PLAN_SERVICE_BG
, a.S4_SERVICE_BUSINESS_DIVISION as S4_REPORT_ACTION_PLAN_SERVICE_BD
, a.S4_BUSINESS_UNIT as S4_REPORT_ACTION_PLAN_BUSINESS_UNIT
, '' as S4_REPORT_ACTION_PLAN_BLANK_1
, '' as S4_REPORT_ACTION_PLAN_BLANK_2
, '' as S4_REPORT_ACTION_PLAN_BLANK_3
, '' as S4_REPORT_ACTION_PLAN_BLANK_4
, '' as S4_REPORT_ACTION_PLAN_BLANK_5
, '' as S4_REPORT_ACTION_PLAN_BLANK_6
, '' as S4_REPORT_ACTION_PLAN_CARY_SAVING_Y1
, '' as S4_REPORT_ACTION_PLAN_CARY_APPLICATION
, '' as S4_REPORT_ACTION_PLAN_SPEND_TBD
, '' as S4_REPORT_ACTION_PLAN_SAVING_TBD
, '' as S4_REPORT_ACTION_PLAN_SYNERGY_SPEND
, '' as S4_REPORT_ACTION_PLAN_SYNERGY_SAVING
, round(isnull(a.S4_ACTIVITY_PROBABILITY_PERCENT,0) * isnull(a.S4_PROJECT_PROBABILITY_PERCENT,0) * 100,2) as S4_REPORT_ACTION_PLAN_RISK
, round(convert(numeric(30,9),
IsNull(S4_ACTUAL_SPEND_TOTAL_EUR, 0)
* IsNull(ys.S4_SHARE_PERCENT, 0)), 2)
as S4_REPORT_ACTION_PLAN_ESTIMATED_TOTAL
, u_nego.RF_LAST_NAME + ' ' + u_nego.RF_FIRST_NAME as S4_REPORT_ACTION_PLAN_NEGOTIATOR
, ys.S4_YEAR as S4_REPORT_ACTION_PLAN_YEAR
, convert(varchar(10),a.S4_SNAPSHOT_NUMBER) as S4_REPORT_ACTION_PLAN_SNAPSHOT
, p.S4_PROJECT_PRIME_PPM as S4_REPORT_ACTION_PLAN_PRIME_PPM
from S4_ACTIVITY_SNAPSHOT a
INNER JOIN S4_PROJECT_SNAPSHOT p ON a.S4_PROJECT_ID = p.S4_PROJECT_ID and p.S4_LATEST_PROJECT_FLAG='Y' and p.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER
LEFT OUTER JOIN S4_YEAR_SHARE_SNAPSHOT ys ON ys.S4_PARENT_TYPE_ACTIVITY='A' and a.S4_ACTIVITY_ID=ys.S4_PARENT_ID and ys.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER and ys.S4_ACTIVE='A' -- Added on 12-Oct-2008
LEFT JOIN RF_ORG4_SUBFAM sfam ON sfam.RF_ORG4_SUBFAM_CODE= a.S4_SUB_FAMILY OR upper(sfam.RF_ORG4_SUBFAM_DESC) = upper(a.S4_SUB_FAMILY)
LEFT JOIN RF_ORG3_FAMILY fam ON fam.RF_ORG3_FAMILY_CODE = a.S4_FAMILY OR upper(fam.RF_ORG3_FAMILY_DESC) = upper(a.S4_FAMILY)
LEFT JOIN RF_ORG2_COUNCIL cou ON cou.RF_ORG2_COUNCIL_CODE = a.S4_COUNCIL OR upper(cou.RF_ORG2_COUNCIL_DESC) = upper(a.S4_COUNCIL)
LEFT JOIN SAM_RF_REGIONAL_UNIT dest_ru ON dest_ru.SAM_RF_REGIONAL_UNIT_CODE = a.S4_DESTINATION_REGIONAL_UNIT
LEFT JOIN SAM_RF_COUNTRY dest_coun ON dest_coun.SAM_RF_COUNTRY_CODE = a.S4_DESTINATION_COUNTRY OR upper(a.S4_DESTINATION_COUNTRY) = upper(dest_coun.SAM_RF_COUNTRY_DESC)
LEFT JOIN RF_PURCH_SUB_AREA sourc_ru ON sourc_ru.RF_PURCH_SUB_AREA_CODE = a.S4_SOURCING_REGIONAL_UNIT
LEFT JOIN RF_COUNTRY sourc_coun ON sourc_coun.RF_COUNTRY_CODE = a.S4_SOURCING_COUNTRY OR upper(a.S4_SOURCING_COUNTRY) = upper(sourc_coun.RF_COUNTRY_DESC)
LEFT JOIN RF_USER u_nego ON u_nego.RF_USER_CN = a.S4_NEGOTIATOR
LEFT JOIN SAM_GEN_STATIC_LIST List ON List.FieldId = 'ActivityType' and List.FieldValue = a.S4_ACTIVITY_TYPE
-- Modified on 12-Oct-2008 - BEGIN
LEFT JOIN S4_QUOTATION_SNAPSHOT quo ON quo.S4_PARENT_ID = a.S4_ACTIVITY_ID AND quo.S4_PARENT_TYPE_ACTIVITY = 'A' AND quo.S4_QUOTATION_ROUND=1 AND quo.S4_QUOTE_COMPLETE = 'Y' and quo.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER
-- Modified on 12-Oct-2008 - END
where a.S4_LATEST_ACTIVITY_FLAG='Y'
AND a.S4_PHASE_FLAG = 2
AND a.S4_ACTIVITY_STATUS not in ('D','H') |
Partager