[Home] [Help]
1583: v_start_dt igs_tr_item.start_dt%TYPE;
1584: v_business_days_ind igs_tr_item.business_days_ind%TYPE;
1585: v_sequence_ind igs_tr_item.sequence_ind%TYPE;
1586: v_step_completion_ind_temp igs_tr_step.step_completion_ind%TYPE;
1587: v_action_dt igs_tr_step_v.action_dt%TYPE;
1588: v_message_name VARCHAR2(30);
1589: v_rowid VARCHAR2(25);
1590: v_step_group_id igs_tr_step.step_group_id%TYPE;
1591: v_tracking_step_number igs_tr_step.tracking_step_number%TYPE;
2502: App_Exception.Raise_Exception;
2503:
2504: END sync_trk_type_grplmt;
2505:
2506: PROCEDURE sync_trk_item_grplmt ( p_tracking_id IGS_TR_STEP_V.tracking_id %TYPE,
2507: p_execute VARCHAR2
2508: )
2509: IS
2510:
2515: Date Created By: 11-Feb-2002
2516:
2517: Purpose: This procedure would be called from the post_forms commit trigger of the Form IGSTR007 (Tracking Items ).
2518: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
2519: of IGS_TR_STEP_V.
2520: 1. If that step group being deleted in form IGSTR007 ( block tracking step) is the last one of its
2521: kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT
2522:
2523: 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
2523: 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
2524: in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.
2525:
2526: 3. In case IGS_TR_STEP_GRP_LMT.STEP_GROUP_LIMIT is greater than the count of step_group_id's for tracking_id and step_group_id
2527: combination in IGS_TR_STEP_V view then set it equal to the lower value (i.e count of step_group_id's in the IGS_TR_STEP_V).
2528:
2529:
2530: Known limitations,enhancements,remarks:
2531:
2535: ************************************************************************************************************/
2536:
2537:
2538:
2539: -- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT and not in the view IGS_TR_STEP_V
2540: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2541: -- ssawhney view reference changed to table
2542: CURSOR c_decrement_step_grp_lmt
2543: IS
2601:
2602:
2603: -- Cursor to get the count of step_group_id's for a tracking item step
2604: -- ssawhney view reference changed to table
2605: CURSOR c_item_step ( p_tracking_id IGS_TR_STEP_V.TRACKING_ID%TYPE )
2606: IS
2607: SELECT
2608: step_group_id,
2609: COUNT(step_group_id) step_group_count
2647:
2648:
2649: /************************************ Validation 2 ***********************************************************/
2650: -- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT
2651: -- and not in the view IGS_TR_STEP_V.
2652: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2653:
2654: FOR rec_decrement_step_grp_lmt IN c_decrement_step_grp_lmt LOOP
2655: OPEN c_rowid(p_tracking_id, rec_decrement_step_grp_lmt.step_group_id );
2660:
2661:
2662:
2663: /************************************* Validation 3 ***********************************************************/
2664: -- Insert records into IGS_TR_STEP_GRP_LMT when tracking_id and step_group_id combination exists in IGS_TR_STEP_V
2665: -- but not in IGS_TR_STEP_GRP_LMT.
2666: -- Default the value of Step_group_limit to 1.
2667:
2668: FOR rec_insert_step_grp_lmt IN c_insert_step_grp_lmt LOOP
2677:
2678:
2679: /*************************************** Validation 4 ***********************************************************/
2680: -- Check the step group limit for a combination of Tracking_Id and Step_group_id in the table IGS_TR_STEP_GRP_LMT
2681: -- and the view IGS_TR_STEP_V.
2682: -- In case IGS_TR_STEP_GRP_LMT.STEP_GROUP_LIMIT is greater than the count of step_group_id's for tracking_id and step_group_id
2683: -- combination in IGS_TR_STEP_V view then set it equal to the count of step_group_id's in the IGS_TR_STEP_V.
2684:
2685: FOR rec_item_step IN c_item_step (p_tracking_id)
2679: /*************************************** Validation 4 ***********************************************************/
2680: -- Check the step group limit for a combination of Tracking_Id and Step_group_id in the table IGS_TR_STEP_GRP_LMT
2681: -- and the view IGS_TR_STEP_V.
2682: -- In case IGS_TR_STEP_GRP_LMT.STEP_GROUP_LIMIT is greater than the count of step_group_id's for tracking_id and step_group_id
2683: -- combination in IGS_TR_STEP_V view then set it equal to the count of step_group_id's in the IGS_TR_STEP_V.
2684:
2685: FOR rec_item_step IN c_item_step (p_tracking_id)
2686: LOOP
2687: OPEN c_grp_lmt (p_tracking_id, rec_item_step.step_group_id);
2692: FETCH c_rowid INTO lv_rowid;
2693: CLOSE c_rowid;
2694:
2695: -- Check whether Step Group limit is greater than the count of Tracking item steps for a step group id and tracking_id combination
2696: -- then set the step group limit equal to the count of step group id's in the igs)tr)step_v view for a step group id and tracking_id combination.
2697:
2698: IF ln_grp_lmt > rec_item_step.step_group_count THEN
2699: igs_tr_step_grp_lmt_pkg.update_row (
2700: X_ROWID => lv_rowid ,
2715:
2716: END sync_trk_item_grplmt;
2717:
2718:
2719: FUNCTION validate_completion_status ( p_tracking_id IN IGS_TR_STEP_V.TRACKING_ID%TYPE,
2720: p_tracking_status IN IGS_TR_ITEM_V.TRACKING_STATUS%TYPE,
2721: p_sequence_ind IN IGS_TR_ITEM_V.SEQUENCE_IND%TYPE,
2722: p_message_name OUT NOCOPY VARCHAR2
2723: )