[Home] [Help]
55: -- Interface table that are pending for processing and the parent
56: -- Inquiry Application record's status is completed ('1')and the parent
57: -- Interface Record has status of Completed ('1') or Warning ('4')
58:
59: CURSOR c_inq_pkg (cp_interface_run_id igr_i_pkg_int.interface_run_id%TYPE)IS
60: SELECT pkg.rowid,pkg.*
61: FROM igr_i_pkg_int pkg
62: WHERE pkg.interface_run_id = cp_interface_run_id
63: AND pkg.status = '2';
57: -- Interface Record has status of Completed ('1') or Warning ('4')
58:
59: CURSOR c_inq_pkg (cp_interface_run_id igr_i_pkg_int.interface_run_id%TYPE)IS
60: SELECT pkg.rowid,pkg.*
61: FROM igr_i_pkg_int pkg
62: WHERE pkg.interface_run_id = cp_interface_run_id
63: AND pkg.status = '2';
64:
65: -------------Local Procedure Get_Meaning-----------------------------------------------
700: )
701: );
702:
703: --Cursor to get the inquiry_type_id and package_reduct_ind from IGR_I_APPL_INT table.
704: CURSOR c_appl_int (cp_interface_inq_appl_id igr_i_pkg_int.interface_inq_appl_id%TYPE) IS
705: SELECT inquiry_type_id, pkg_reduct_ind
706: FROM igr_i_appl_int
707: WHERE interface_inq_appl_id = cp_interface_inq_appl_id;
708:
720: FETCH c_package_item INTO l_exists;
721: IF c_package_item%NOTFOUND THEN
722: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E321', 8405);
723: UPDATE
724: igr_i_pkg_int
725: SET
726: status = '3', -- 'Error'
727: error_code = 'E321',
728: error_text = l_error_text
783:
784: IF lb_validation THEN
785:
786: -- call the TBH to insert the values from the
787: -- Interface table igr_i_pkg_int.
788:
789: igr_i_a_pkgitm_pkg.insert_row(
790: x_rowid => lv_rowid,
791: x_person_id => p_inq_pkg_rec.person_id,
821:
822: IF lv_msg_data IS NOT NULL THEN
823: /* Return status of Null should be treated as Success */
824: IF NVL(lv_return_status,'S') = 'S' THEN
825: UPDATE igr_i_pkg_int
826: SET status = '4',
827: error_code = 'E702',
828: error_text = lv_msg_data
829: WHERE rowid = p_inq_pkg_rec.rowid;
828: error_text = lv_msg_data
829: WHERE rowid = p_inq_pkg_rec.rowid;
830: ELSE
831: ROLLBACK TO inqpkg_save;
832: UPDATE igr_i_pkg_int
833: SET status = '3',
834: error_code = 'E322',
835: error_text = lv_msg_data
836: WHERE rowid = p_inq_pkg_rec.rowid;
837: END IF;
838: ELSE
839: /* Return status of Null should be treated as Success */
840: IF NVL(lv_return_status,'S') = 'S' THEN
841: UPDATE igr_i_pkg_int
842: SET status = '1',
843: error_code = NULL,
844: error_text = NULL
845: WHERE rowid = p_inq_pkg_rec.rowid;
845: WHERE rowid = p_inq_pkg_rec.rowid;
846: ELSE
847: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
848: ROLLBACK TO inqpkg_save;
849: UPDATE igr_i_pkg_int
850: SET status = '3',
851: error_code = 'E322',
852: error_text = l_error_text
853: WHERE rowid = p_inq_pkg_rec.rowid;
871: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
872: END IF;
873:
874: ROLLBACK TO inqpkg_save;
875: UPDATE igr_i_pkg_int
876: SET error_code = 'E322',
877: status = '3',
878: error_text = l_error_text
879: WHERE interface_inq_pkg_id = p_inq_pkg_rec.interface_inq_pkg_id;
900:
901: BEGIN -- prc_inq_pkg
902: /*-----------------------------------------------------------------------------
903: This procedure is used to import the data from the interface table
904: igr_i_pkg_int to the System table
905:
906: 1. This procedure will loop through all the records in the table igr_i_pkg_int
907: with the STATUS = 2 -'Pending AND the parent IGS_AD_INTERFACE RECORD has
908: been successfully imported (Status = 1 - 'Completed')
902: /*-----------------------------------------------------------------------------
903: This procedure is used to import the data from the interface table
904: igr_i_pkg_int to the System table
905:
906: 1. This procedure will loop through all the records in the table igr_i_pkg_int
907: with the STATUS = 2 -'Pending AND the parent IGS_AD_INTERFACE RECORD has
908: been successfully imported (Status = 1 - 'Completed')
909: -------------------------------------------------------------------------------*/
910: IF (l_request_id IS NULL) THEN