OTA Budget and Cost Reconciliation

SELECT OTMT.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID2,
       OTMT.UNIT                   UNIT,
       ' '                         BUDGET_ITEM_NAME,
       LKPS2.MEANING               EMP_CATEGORY,
       SUM(Decode(OTMT.UNIT,'M',hr_currency_pkg.convert_amount(
                                 OTPV.BUDGET_CURRENCY,
                                 :P_CURRENCY,
                                 OTPV.PERIOD_START_DATE,
                                 PBVA.VALUE,
                                 :C_CURR_CONV_TYPE),
                                 PBVA.VALUE)
           )                       BUDGET
FROM OTA_TP_MEASUREMENT_TYPES  OTMT,
     FND_COMMON_LOOKUPS       LKPS2,
     PER_BUDGETS                PB,
     PER_BUDGET_VERSIONS        PBV,
     PER_BUDGET_ELEMENTS        PBE,
     PER_BUDGET_VALUES          PBVA,
     OTA_TRAINING_PLANS_V     OTPV
WHERE :MEASUREMENT_LEVEL           = 'PLAN'
  AND PB.UNIT                      = OTMT.TP_MEASUREMENT_CODE
  AND PB.BUSINESS_GROUP_ID         = :P_BUSINESS_GROUP_ID
  AND PB.BUDGET_TYPE_CODE          = 'OTA_BUDGET'
  AND PBV.BUDGET_ID (+)            = PB.BUDGET_ID
  AND PBE.BUDGET_VERSION_ID (+)    = PBV.BUDGET_VERSION_ID
  AND PBE.TRAINING_PLAN_ID         = :P_TRAINING_PLAN_ID
  AND PBE.TRAINING_PLAN_MEMBER_ID  IS NULL
  AND PBE.EVENT_ID                 IS NULL
  AND PBVA.BUDGET_ELEMENT_ID (+)   = PBE.BUDGET_ELEMENT_ID
  AND LKPS2.LOOKUP_TYPE (+)        = 'FR_EMPLOYEE_CATEGORY'
  AND LKPS2.APPLICATION_ID (+)     = 800
  AND LKPS2.LOOKUP_CODE (+)        = PBVA.BUDGET_INFORMATION1
  AND OTPV.TRAINING_PLAN_ID (+)    = :P_TRAINING_PLAN_ID
 and OTMT.TP_MEASUREMENT_TYPE_ID=:MEASUREMENT_TYPE_ID
GROUP BY OTMT.TP_MEASUREMENT_TYPE_ID,
         OTMT.UNIT,
         LKPS2.MEANING
UNION
(
SELECT OTMT.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID2,
       OTMT.UNIT                   UNIT,
       OAD_TL.NAME                    BUDGET_ITEM_NAME,
       LKPS2.MEANING               EMP_CATEGORY,
       SUM(Decode(OTMT.UNIT,'M',
                            hr_currency_pkg.convert_amount(
                            OTPV.BUDGET_CURRENCY,
                            :P_CURRENCY,
                            OTPV.PERIOD_START_DATE,
                            PBVA.VALUE,
                            :C_CURR_CONV_TYPE),
                        PBVA.VALUE)
           )                        BUDGET
FROM OTA_TP_MEASUREMENT_TYPES    OTMT,
     PER_BUDGETS                  PB,
     PER_BUDGET_VERSIONS          PBV,
     PER_BUDGET_ELEMENTS          PBE,
     PER_BUDGET_VALUES            PBVA,
     OTA_TRAINING_PLAN_MEMBERS   OTPM,
     OTA_ACTIVITY_DEFINITIONS    OAD,
     OTA_ACTIVITY_DEFINITIONS_TL OAD_TL,
     FND_COMMON_LOOKUPS         LKPS2,
     OTA_TRAINING_PLANS_V            OTPV
WHERE OAD.ACTIVITY_ID = OAD_TL.ACTIVITY_ID(+)
  AND OAD_TL.LANGUAGE(+) = USERENV('LANG')
  AND :MEASUREMENT_LEVEL               = 'ACTIVITY'
  AND PB.UNIT                          = OTMT.TP_MEASUREMENT_CODE
  AND PB.BUSINESS_GROUP_ID             = :P_BUSINESS_GROUP_ID
  AND PB.BUDGET_TYPE_CODE              = 'OTA_BUDGET'
  AND PBV.BUDGET_ID (+)                = PB.BUDGET_ID
  AND PBE.BUDGET_VERSION_ID (+)        = PBV.BUDGET_VERSION_ID
  AND PBE.TRAINING_PLAN_ID             = :P_TRAINING_PLAN_ID
  AND PBE.TRAINING_PLAN_MEMBER_ID      IS NOT NULL
  AND PBE.EVENT_ID                     IS NULL
  AND OTPM.TRAINING_PLAN_MEMBER_ID (+) = PBE.TRAINING_PLAN_MEMBER_ID
  AND OTPM.MEMBER_STATUS_TYPE_ID       <> 'CANCELLED'
  AND OTPM.ACTIVITY_VERSION_ID         IS NULL
  AND OTPM.ACTIVITY_DEFINITION_ID      IS NOT NULL
  AND OAD.ACTIVITY_ID (+)              = OTPM.ACTIVITY_DEFINITION_ID
  AND PBVA.BUDGET_ELEMENT_ID (+)       = PBE.BUDGET_ELEMENT_ID
  AND LKPS2.LOOKUP_TYPE (+)            = 'FR_EMPLOYEE_CATEGORY'
  AND LKPS2.APPLICATION_ID (+)         = 800
  AND LKPS2.LOOKUP_CODE (+)            = PBVA.BUDGET_INFORMATION1
  AND OTPV.TRAINING_PLAN_ID            = :P_TRAINING_PLAN_ID
GROUP BY OTMT.TP_MEASUREMENT_TYPE_ID,
         OTMT.UNIT,
         OAD_TL.NAME,
         LKPS2.MEANING
UNION
SELECT OTMT.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID2,
       OTMT.UNIT                   UNIT,
       OAV_TL.VERSION_NAME            BUDGET_ITEM_NAME,
       LKPS2.MEANING               EMP_CATEGORY,
       SUM(Decode(OTMT.UNIT,'M',
                         hr_currency_pkg.convert_amount(
                            OTPV.BUDGET_CURRENCY,
                            :P_CURRENCY,
                            OTPV.PERIOD_START_DATE,
                            PBVA.VALUE,
                            :C_CURR_CONV_TYPE),
                        PBVA.VALUE)
          )                     BUDGET
FROM OTA_TP_MEASUREMENT_TYPES   OTMT,
     PER_BUDGETS                 PB,
     PER_BUDGET_VERSIONS         PBV,
     PER_BUDGET_ELEMENTS         PBE,
     OTA_TRAINING_PLAN_MEMBERS  OTPM,
     OTA_ACTIVITY_VERSIONS      OAV,
     OTA_ACTIVITY_VERSIONS_TL OAV_TL,
     PER_BUDGET_VALUES           PBVA,
     FND_COMMON_LOOKUPS        LKPS2,
     OTA_TRAINING_PLANS_V      OTPV
WHERE OAV.ACTIVITY_VERSION_ID = OAV_TL.ACTIVITY_VERSION_ID(+)
  AND OAV_TL.LANGUAGE(+) = USERENV('LANG')
  AND :MEASUREMENT_LEVEL               = 'ACTIVITY'
  AND PB.UNIT                          = OTMT.TP_MEASUREMENT_CODE
  AND PB.BUSINESS_GROUP_ID             = :P_BUSINESS_GROUP_ID
  AND PB.BUDGET_TYPE_CODE              = 'OTA_BUDGET'
  AND PBV.BUDGET_ID (+)                = PB.BUDGET_ID
  AND PBE.BUDGET_VERSION_ID (+)        = PBV.BUDGET_VERSION_ID
  AND PBE.TRAINING_PLAN_ID             = :P_TRAINING_PLAN_ID
  AND PBE.TRAINING_PLAN_MEMBER_ID      IS NOT NULL
  AND PBE.EVENT_ID                     IS NULL
  AND OTPM.TRAINING_PLAN_MEMBER_ID (+) = PBE.TRAINING_PLAN_MEMBER_ID
  AND OTPM.MEMBER_STATUS_TYPE_ID       <> 'CANCELLED'
  AND OTPM.ACTIVITY_VERSION_ID         IS NOT NULL
  AND OTPM.ACTIVITY_DEFINITION_ID      IS NULL
  AND OAV.ACTIVITY_VERSION_ID (+)      = OTPM.ACTIVITY_VERSION_ID
  AND PBVA.BUDGET_ELEMENT_ID (+)       = PBE.BUDGET_ELEMENT_ID
  AND LKPS2.LOOKUP_TYPE (+)            = 'FR_EMPLOYEE_CATEGORY'
  AND LKPS2.APPLICATION_ID (+)         = 800
  AND LKPS2.LOOKUP_CODE (+)            = PBVA.BUDGET_INFORMATION1
  AND OTPV.TRAINING_PLAN_ID            = :P_TRAINING_PLAN_ID
GROUP BY OTMT.TP_MEASUREMENT_TYPE_ID,
         OTMT.UNIT,
         OAV_TL.VERSION_NAME,
         LKPS2.MEANING
)
UNION
(
SELECT OTMT.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID2,
       OTMT.UNIT                   UNIT,
       OE_TL.TITLE                    BUDGET_ITEM_NAME,
       LKPS2.MEANING               EMP_CATEGORY,
       SUM(Decode(OTMT.UNIT,'M',
                           hr_currency_pkg.convert_amount(
                            OTPV.BUDGET_CURRENCY,
                            :P_CURRENCY,
                            OTPV.PERIOD_START_DATE,
                            PBVA.VALUE,
                            :C_CURR_CONV_TYPE),
                          PBVA.VALUE)
             )                      BUDGET
FROM OTA_TP_MEASUREMENT_TYPES  OTMT,
     PER_BUDGETS                PB,
     PER_BUDGET_VERSIONS        PBV,
     PER_BUDGET_ELEMENTS        PBE,
     OTA_EVENTS                OE,
     OTA_EVENTS_TL          OE_TL,
     OTA_TRAINING_PLAN_MEMBERS OTPM,
     PER_BUDGET_VALUES          PBVA,
     FND_COMMON_LOOKUPS       LKPS2,
     OTA_TRAINING_PLANS_V     OTPV
WHERE OE.EVENT_ID = OE_TL.EVENT_ID(+)
  AND OE_TL.LANGUAGE(+) = USERENV('LANG')
  AND :MEASUREMENT_LEVEL               = 'EVENT'
  AND PB.UNIT                          = OTMT.TP_MEASUREMENT_CODE
  AND PB.BUSINESS_GROUP_ID             = :P_BUSINESS_GROUP_ID
  AND PB.BUDGET_TYPE_CODE              = 'OTA_BUDGET'
  AND PBV.BUDGET_ID                    = PB.BUDGET_ID
  AND PBE.BUDGET_VERSION_ID            = PBV.BUDGET_VERSION_ID
  AND PBE.TRAINING_PLAN_ID             = :P_TRAINING_PLAN_ID
  AND PBE.TRAINING_PLAN_MEMBER_ID      IS NOT NULL
  AND PBE.EVENT_ID                     IS NOT NULL
  AND OTPM.TRAINING_PLAN_MEMBER_ID (+) = PBE.TRAINING_PLAN_MEMBER_ID
  AND OTPM.MEMBER_STATUS_TYPE_ID       <> 'CANCELLED'
  AND OE.EVENT_ID                      = PBE.EVENT_ID
  AND PBVA.BUDGET_ELEMENT_ID (+)       = PBE.BUDGET_ELEMENT_ID
  AND LKPS2.LOOKUP_TYPE (+)            = 'FR_EMPLOYEE_CATEGORY'
  AND LKPS2.APPLICATION_ID (+)         = 800
  AND LKPS2.LOOKUP_CODE (+)            = PBVA.BUDGET_INFORMATION1
  AND OTPV.TRAINING_PLAN_ID        = :P_TRAINING_PLAN_ID
GROUP BY OTMT.TP_MEASUREMENT_TYPE_ID,
         OTMT.UNIT,
         OE_TL.TITLE,
         LKPS2.MEANING
)