[Home] [Help]
60:
61: PROCEDURE UpdatePartyDetailsTime;
62:
63: -- kbasavar migrated chi's changes for bug 3089951
64: -- Synchronizes the records of both ams_dm_party_details
65: -- and ams_dm_party_details_time. The commits done at
66: -- the end of each procedure to release rollback space
67: -- could cause the records to go out of sync if one table's
68: -- insert completes while the other fails.
61: PROCEDURE UpdatePartyDetailsTime;
62:
63: -- kbasavar migrated chi's changes for bug 3089951
64: -- Synchronizes the records of both ams_dm_party_details
65: -- and ams_dm_party_details_time. The commits done at
66: -- the end of each procedure to release rollback space
67: -- could cause the records to go out of sync if one table's
68: -- insert completes while the other fails.
69: PROCEDURE sync_party_tables;
215: WHERE ads.used_for_object_id = p_object_id
216: AND ads.arc_used_for_object = p_object_type
217: AND NOT EXISTS (
218: SELECT pdt.party_id party_id
219: FROM ams_dm_party_details pdt
220: WHERE ads.party_id = pdt.party_id
221: );
222:
223: -------------------- finish --------------------------
266: INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG_GT,DEFAULT,DEFAULT)*/
267: INTO ams_dm_drv_stg_gt (
268: party_id)
269: SELECT x.party_id
270: FROM ams_dm_party_details x
271: WHERE x.party_type = p_party_type
272: /* AND EXISTS (
273: (SELECT a.party_id
274: FROM hz_parties a
2892: END IF;
2893:
2894: ----------------------- Insert ----------------------
2895: -- Move Data from Staging to Party Details (Insert Process)
2896: INSERT -- /*+ APPEND PARALLEL(AMS_DM_PARTY_DETAILS,DEFAULT,DEFAULT)*/
2897: INTO ams_dm_party_details (
2898: party_id,
2899: created_by,
2900: creation_date,
2893:
2894: ----------------------- Insert ----------------------
2895: -- Move Data from Staging to Party Details (Insert Process)
2896: INSERT -- /*+ APPEND PARALLEL(AMS_DM_PARTY_DETAILS,DEFAULT,DEFAULT)*/
2897: INTO ams_dm_party_details (
2898: party_id,
2899: created_by,
2900: creation_date,
2901: last_updated_by,
3242:
3243: ----------------------- Insert ----------------------
3244: -- Move Data from Staging to Party Details Time (Insert Process)
3245:
3246: INSERT -- /*+ APPEND PARALLEL(AMS_DM_PARTY_DETAILS_TIME,DEFAULT,DEFAULT)*/
3247: INTO ams_dm_party_details_time (
3248: party_id,
3249: created_by,
3250: creation_date,
3243: ----------------------- Insert ----------------------
3244: -- Move Data from Staging to Party Details Time (Insert Process)
3245:
3246: INSERT -- /*+ APPEND PARALLEL(AMS_DM_PARTY_DETAILS_TIME,DEFAULT,DEFAULT)*/
3247: INTO ams_dm_party_details_time (
3248: party_id,
3249: created_by,
3250: creation_date,
3251: last_updated_by,
3469: END IF;
3470:
3471: ----------------------- Update ----------------------
3472: -- Update Party Details with changed data from Staging Area
3473: UPDATE /*+ PARALLEL(AMS_DM_PARTY_DETAILS)*/
3474: ams_dm_party_details pdt SET (
3475: last_updated_by,
3476: last_update_date,
3477: last_update_login,
3470:
3471: ----------------------- Update ----------------------
3472: -- Update Party Details with changed data from Staging Area
3473: UPDATE /*+ PARALLEL(AMS_DM_PARTY_DETAILS)*/
3474: ams_dm_party_details pdt SET (
3475: last_updated_by,
3476: last_update_date,
3477: last_update_login,
3478: gender,
3697:
3698: ----------------------- Insert ----------------------
3699: -- Update Party Details Time with changed data from Staging Area
3700:
3701: UPDATE /*+ PARALLEL(AMS_DM_PARTY_DETAILS_TIME)*/
3702: ams_dm_party_details_time pdtt SET (
3703: last_updated_by,
3704: last_update_date,
3705: last_update_login,
3698: ----------------------- Insert ----------------------
3699: -- Update Party Details Time with changed data from Staging Area
3700:
3701: UPDATE /*+ PARALLEL(AMS_DM_PARTY_DETAILS_TIME)*/
3702: ams_dm_party_details_time pdtt SET (
3703: last_updated_by,
3704: last_update_date,
3705: last_update_login,
3706: age,
4256: );
4257:
4258: DBMS_STATS.gather_table_stats (
4259: ownname => l_ams_schema,
4260: tabname => 'AMS_DM_PARTY_DETAILS',
4261: estimate_percent => 99,
4262: cascade => TRUE
4263: );
4264:
4263: );
4264:
4265: DBMS_STATS.gather_table_stats (
4266: ownname => l_ams_schema,
4267: tabname => 'AMS_DM_PARTY_DETAILS_TIME',
4268: estimate_percent => 99,
4269: cascade => TRUE
4270: );
4271:
4281: -- PROCEDURE
4282: -- sync_party_tables
4283: --
4284: -- DESCRIPTION
4285: -- Synchronizes the party records of ams_dm_party_details
4286: -- and ams_dm_party_details_time by deleting from party
4287: -- details. This procedure is meant to be called if the
4288: -- insert for party details time fails.
4289: --
4282: -- sync_party_tables
4283: --
4284: -- DESCRIPTION
4285: -- Synchronizes the party records of ams_dm_party_details
4286: -- and ams_dm_party_details_time by deleting from party
4287: -- details. This procedure is meant to be called if the
4288: -- insert for party details time fails.
4289: --
4290: -- HISTORY
4292: -- 10-Sep-2003 kbasavar migrated to mainline
4293: PROCEDURE sync_party_tables
4294: IS
4295: BEGIN
4296: DELETE FROM ams_dm_party_details d
4297: WHERE NOT EXISTS (SELECT 1 FROM ams_dm_party_details_time t
4298: WHERE t.party_id = d.party_id)
4299: ;
4300: END;
4293: PROCEDURE sync_party_tables
4294: IS
4295: BEGIN
4296: DELETE FROM ams_dm_party_details d
4297: WHERE NOT EXISTS (SELECT 1 FROM ams_dm_party_details_time t
4298: WHERE t.party_id = d.party_id)
4299: ;
4300: END;
4301: