[Home] [Help]
2758: * Migrate_Cost_Calendars *
2759: * *
2760: * DESCRIPTION: *
2761: * This PL/SQL procedure is used to transform the Cost Calendars *
2762: * data in CM_CLDR_HDR_B AND CM_CLDR_DTL to GMF_CALENDAR_ASSIGNMENTS *
2763: * and GMF_PERIOD_STATUSES *
2764: * *
2765: * PARAMETERS: *
2766: * P_migration_run_id - id to use to right to migration log *
2795: IS
2796: SELECT a.legal_entity_id,
2797: a.cost_Type_id,
2798: a.calendar_code
2799: FROM gmf_calendar_assignments a
2800: ORDER BY a.legal_entity_id,
2801: a.cost_Type_id,
2802: a.calendar_code;
2803:
2809:
2810: BEGIN
2811:
2812: G_Migration_run_id := P_migration_run_id;
2813: G_Table_name := 'GMF_CALENDAR_ASSIGNMENTS';
2814: G_Context := 'Cost Calendar Assignments Migration';
2815: X_failure_count := 0;
2816:
2817: /********************************
2829: p_app_short_name => 'GMA'
2830: );
2831:
2832: /****************************************************************************
2833: * Insert a row in gmf_calendar_assignments for Direct Calendar Assignments *
2834: *****************************************************************************/
2835: BEGIN
2836:
2837: INSERT INTO gmf_calendar_assignments
2833: * Insert a row in gmf_calendar_assignments for Direct Calendar Assignments *
2834: *****************************************************************************/
2835: BEGIN
2836:
2837: INSERT INTO gmf_calendar_assignments
2838: (
2839: ASSIGNMENT_ID,
2840: CALENDAR_CODE,
2841: LEGAL_ENTITY_ID,
2847: LAST_UPDATE_LOGIN,
2848: TEXT_CODE,
2849: DELETE_MARK
2850: )
2851: SELECT gmf_calendar_assignments_s.NEXTVAL,
2852: a.calendar_code,
2853: b.legal_entity_id,
2854: c.cost_type_id,
2855: a.creation_date,
2869: AND a.co_code = b.co_code
2870: AND c.cost_mthd_code = a.cost_mthd_code
2871: AND NOT EXISTS (
2872: SELECT 'X'
2873: FROM gmf_calendar_assignments x
2874: WHERE x.calendar_code = a.calendar_code
2875: AND x.cost_type_id = c.cost_type_id
2876: AND x.legal_entity_id = b.legal_entity_id
2877: );
2916: );
2917: END;
2918:
2919: /**********************************************************************
2920: * Insert a row in gmf_calendar_assignments for transaction table data *
2921: **********************************************************************/
2922: BEGIN
2923:
2924: INSERT INTO gmf_calendar_assignments
2920: * Insert a row in gmf_calendar_assignments for transaction table data *
2921: **********************************************************************/
2922: BEGIN
2923:
2924: INSERT INTO gmf_calendar_assignments
2925: (
2926: ASSIGNMENT_ID,
2927: CALENDAR_CODE,
2928: LEGAL_ENTITY_ID,
2935: TEXT_CODE,
2936: DELETE_MARK
2937: )
2938: (
2939: SELECT gmf_calendar_assignments_s.NEXTVAL,
2940: g.calendar_code,
2941: i.legal_entity_id,
2942: h.cost_type_id,
2943: g.creation_date,
3005: AND a.cost_mthd_code = h.cost_mthd_code
3006: )
3007: AND NOT EXISTS (
3008: SELECT 'X'
3009: FROM gmf_calendar_assignments x
3010: WHERE x.calendar_code = g.calendar_code
3011: AND x.cost_type_id = h.cost_type_id
3012: AND x.legal_entity_id = i.legal_entity_id
3013: )
3053: );
3054: END;
3055:
3056: /**********************************************************************
3057: * Insert a row in gmf_calendar_assignments for CM_RSRC_DTL table data *
3058: **********************************************************************/
3059: BEGIN
3060:
3061: INSERT INTO gmf_calendar_assignments
3057: * Insert a row in gmf_calendar_assignments for CM_RSRC_DTL table data *
3058: **********************************************************************/
3059: BEGIN
3060:
3061: INSERT INTO gmf_calendar_assignments
3062: (
3063: ASSIGNMENT_ID,
3064: CALENDAR_CODE,
3065: LEGAL_ENTITY_ID,
3072: TEXT_CODE,
3073: DELETE_MARK
3074: )
3075: (
3076: SELECT gmf_calendar_assignments_s.NEXTVAL,
3077: x.*
3078: FROM (
3079: SELECT DISTINCT
3080: g.calendar_code,
3100: AND h.cost_mthd_code = k.cost_mthd_code
3101: AND g.calendar_code = k.calendar_code
3102: AND NOT EXISTS (
3103: SELECT 'X'
3104: FROM gmf_calendar_assignments x
3105: WHERE x.calendar_code = g.calendar_code
3106: AND x.cost_type_id = h.cost_Type_id
3107: AND x.legal_entity_id = i.legal_Entity_id
3108: )
3149: );
3150: END;
3151:
3152: /**********************************************************************
3153: * Insert a row in gmf_calendar_assignments for CM_BRDN_DTL table data *
3154: **********************************************************************/
3155: BEGIN
3156:
3157: INSERT INTO gmf_calendar_assignments
3153: * Insert a row in gmf_calendar_assignments for CM_BRDN_DTL table data *
3154: **********************************************************************/
3155: BEGIN
3156:
3157: INSERT INTO gmf_calendar_assignments
3158: (
3159: ASSIGNMENT_ID,
3160: CALENDAR_CODE,
3161: LEGAL_ENTITY_ID,
3168: TEXT_CODE,
3169: DELETE_MARK
3170: )
3171: (
3172: SELECT gmf_calendar_assignments_s.NEXTVAL,
3173: x.*
3174: FROM (
3175: SELECT DISTINCT
3176: g.calendar_code,
3196: AND h.cost_mthd_code = k.cost_mthd_code
3197: AND g.calendar_code = k.calendar_code
3198: AND NOT EXISTS (
3199: SELECT 'X'
3200: FROM gmf_calendar_assignments x
3201: WHERE x.calendar_code = g.calendar_code
3202: AND x.cost_type_id = h.cost_Type_id
3203: AND x.legal_entity_id = i.legal_Entity_id
3204: )
3245: );
3246: END;
3247:
3248: /**********************************************************************
3249: * Insert a row in gmf_calendar_assignments for CM_CMPT_DTL table data *
3250: **********************************************************************/
3251: BEGIN
3252:
3253: INSERT INTO gmf_calendar_assignments
3249: * Insert a row in gmf_calendar_assignments for CM_CMPT_DTL table data *
3250: **********************************************************************/
3251: BEGIN
3252:
3253: INSERT INTO gmf_calendar_assignments
3254: (
3255: ASSIGNMENT_ID,
3256: CALENDAR_CODE,
3257: LEGAL_ENTITY_ID,
3264: TEXT_CODE,
3265: DELETE_MARK
3266: )
3267: (
3268: SELECT gmf_calendar_assignments_s.NEXTVAL,
3269: x.*
3270: FROM (
3271: SELECT DISTINCT
3272: g.calendar_code,
3294: AND h.cost_mthd_code = k.cost_mthd_code
3295: AND g.calendar_code = k.calendar_code
3296: AND NOT EXISTS (
3297: SELECT 'X'
3298: FROM gmf_calendar_assignments x
3299: WHERE x.calendar_code = g.calendar_code
3300: AND x.cost_type_id = h.cost_Type_id
3301: AND x.legal_entity_id = i.legal_Entity_id
3302: )
3343: );
3344: END;
3345:
3346: /**********************************************************************
3347: * Insert a row in gmf_calendar_assignments for CM_ADJS_DTL table data *
3348: **********************************************************************/
3349: BEGIN
3350:
3351: INSERT INTO gmf_calendar_assignments
3347: * Insert a row in gmf_calendar_assignments for CM_ADJS_DTL table data *
3348: **********************************************************************/
3349: BEGIN
3350:
3351: INSERT INTO gmf_calendar_assignments
3352: (
3353: ASSIGNMENT_ID,
3354: CALENDAR_CODE,
3355: LEGAL_ENTITY_ID,
3362: TEXT_CODE,
3363: DELETE_MARK
3364: )
3365: (
3366: SELECT gmf_calendar_assignments_s.NEXTVAL,
3367: x.*
3368: FROM (
3369: SELECT DISTINCT
3370: g.calendar_code,
3392: AND h.cost_mthd_code = k.cost_mthd_code
3393: AND g.calendar_code = k.calendar_code
3394: AND NOT EXISTS (
3395: SELECT 'X'
3396: FROM gmf_calendar_assignments x
3397: WHERE x.calendar_code = g.calendar_code
3398: AND x.cost_type_id = h.cost_Type_id
3399: AND x.legal_entity_id = i.legal_Entity_id
3400: )
3441: );
3442: END;
3443:
3444: FOR i IN cur_overlap_calendar loop
3445: UPDATE GMF_CALENDAR_ASSIGNMENTS g
3446: SET g.delete_mark = 1
3447: WHERE g.delete_mark <> 1
3448: AND EXISTS (
3449: SELECT 'X'
3446: SET g.delete_mark = 1
3447: WHERE g.delete_mark <> 1
3448: AND EXISTS (
3449: SELECT 'X'
3450: FROM gmf_calendar_assignments a,
3451: cm_cldr_dtl b
3452: WHERE a.calendar_code = b.calendar_code
3453: AND a.calendar_code = g.calendar_code
3454: AND a.legal_entity_id = g.legal_entity_id
3459: m.cost_type_id,
3460: m.calendar_code,
3461: min(n.start_date) mindate,
3462: max(n.end_date) maxdate
3463: FROM gmf_calendar_assignments m,
3464: cm_cldr_dtl n
3465: WHERE m.calendar_code = n.calendar_code
3466: AND m.calendar_code = i.calendar_code
3467: AND m.legal_entity_id = i.legal_entity_id
3557: b.last_updated_by,
3558: b.last_update_login,
3559: b.text_code,
3560: decode(a.delete_mark + b.delete_mark, 0, 0, 1)
3561: FROM gmf_calendar_assignments a,
3562: cm_cldr_dtl b,
3563: cm_cldr_hdr_b h
3564: WHERE a.calendar_code = b.calendar_code
3565: AND b.calendar_code = h.calendar_code
3577: UPDATE gmf_period_statuses a
3578: SET a.delete_mark = 1
3579: WHERE EXISTS (
3580: SELECT 'X'
3581: FROM gmf_calendar_assignments x
3582: WHERE x.legal_entity_id = a.legal_entity_id
3583: AND x.calendar_code = a.calendar_code
3584: AND x.cost_type_id = a.cost_type_id
3585: AND x.delete_mark = 1