[Home] [Help]
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 *
2767: * x_exception_count - Number of exceptions occurred. *
3500: p_app_short_name => 'GMA'
3501: );
3502:
3503: G_Migration_run_id := P_migration_run_id;
3504: G_Table_name := 'GMF_PERIOD_STATUSES';
3505: G_Context := 'Cost Calendar Period Statuses Migration';
3506:
3507: /********************************
3508: * Migration Started Log Message *
3519: p_app_short_name => 'GMA'
3520: );
3521:
3522: /*****************************************
3523: * Insert a row into gmf_period_statuses *
3524: *****************************************/
3525:
3526: BEGIN
3527:
3524: *****************************************/
3525:
3526: BEGIN
3527:
3528: INSERT INTO gmf_period_statuses
3529: (
3530: PERIOD_ID,
3531: LEGAL_ENTITY_ID,
3532: COST_TYPE_ID,
3566: AND h.co_code IS NOT NULL
3567: AND h.cost_mthd_code IS NOT NULL
3568: AND NOT EXISTS (
3569: SELECT 'X'
3570: FROM gmf_period_statuses p
3571: WHERE p.legal_entity_id = a.legal_entity_id
3572: AND p.cost_type_id = a.cost_type_id
3573: AND p.calendar_code = b.calendar_code
3574: AND p.period_code = b.period_code
3573: AND p.calendar_code = b.calendar_code
3574: AND p.period_code = b.period_code
3575: );
3576:
3577: UPDATE gmf_period_statuses a
3578: SET a.delete_mark = 1
3579: WHERE EXISTS (
3580: SELECT 'X'
3581: FROM gmf_calendar_assignments x
3879: = (
3880: SELECT x.cost_type_id,
3881: x.period_id,
3882: x.legal_entity_id
3883: FROM gmf_period_statuses x,
3884: cm_mthd_mst y,
3885: cm_cldr_hdr_b z,
3886: gl_plcy_mst w
3887: WHERE y.cost_mthd_code = a.cost_mthd_code
10857: )
10858: =
10859: (
10860: select gps.legal_entity_id, gps.period_id, gps.cost_type_id
10861: from gmf_period_statuses gps,
10862: cm_mthd_mst cmm,
10863: cm_cldr_hdr_b cch,
10864: gl_plcy_mst gpm
10865: where gps.calendar_code = cac.calendar_code
11051: (
11052: SELECT gps.legal_entity_id,
11053: gps.period_id,
11054: gps.cost_type_id
11055: FROM gmf_period_statuses gps,
11056: cm_mthd_mst cmm,
11057: cm_cldr_hdr_b cch,
11058: gl_plcy_mst gpm
11059: WHERE gps.calendar_code = crc.calendar_code
11362: )
11363: =
11364: (
11365: select gps.legal_entity_id, gps.period_id, gps.cost_type_id
11366: from gmf_period_statuses gps,
11367: cm_mthd_mst cmm,
11368: gl_plcy_mst gpm
11369: where gps.calendar_code = ccc.calendar_code
11370: and gpm.co_code = ccc.co_code
11592: )
11593: =
11594: (
11595: select gps.cost_type_id , gps.period_id
11596: from gmf_period_statuses gps,
11597: cm_mthd_mst cmm
11598: where gps.calendar_code = gss.crev_curr_calendar
11599: and gps.period_code = gss.crev_curr_period
11600: and cmm.cost_mthd_code = gss.crev_curr_mthd
11607: )
11608: =
11609: (
11610: select gps.cost_type_id , gps.period_id
11611: from gmf_period_statuses gps,
11612: cm_mthd_mst cmm
11613: where gps.calendar_code = gss.crev_prev_calendar
11614: and gps.period_code = gss.crev_prev_period
11615: and cmm.cost_mthd_code = gss.crev_prev_mthd
11618: ),
11619: gss.period_id
11620: = (
11621: SELECT x.period_id
11622: FROM gmf_period_statuses x
11623: WHERE x.legal_entity_id = gss.legal_entity_id
11624: AND x.cost_type_id = gss.cost_type_id
11625: AND gss.period_start_date between x.start_date and x.end_date
11626: AND gss.period_end_date between x.start_date and x.end_date