APPS.IGS_AD_IMP_026 dependencies on IGS_PE_VISIT_HISTRY

Line 535: FROM igs_pe_visit_histry_v

531: l_app VARCHAR2(50);
532:
533: CURSOR visit_histry_date(cp_visa_rec visa_dtls%ROWTYPE) IS
534: SELECT 'X'
535: FROM igs_pe_visit_histry_v
536: WHERE person_id = cp_visa_rec.person_id AND
537: visa_type = cp_visa_rec.visa_type AND
538: visa_issue_date = cp_visa_rec.visa_issue_date AND
539: visit_end_date NOT BETWEEN cp_visa_rec.visa_issue_date AND (cp_visa_rec.visa_expiry_date+30);

Line 1780: CURSOR chk_duplicate(cp_port_of_entry igs_pe_visit_histry.port_of_entry%TYPE,

1776: || ssaleem 27-AUG-2003 Moved the Validate Record to the package level
1777: ||
1778: */
1779:
1780: CURSOR chk_duplicate(cp_port_of_entry igs_pe_visit_histry.port_of_entry%TYPE,
1781: cp_cntry_entry_form_num igs_pe_visit_histry.cntry_entry_form_num%TYPE) IS
1782: SELECT rowid,vh.*
1783: FROM igs_pe_visit_histry vh
1784: WHERE port_of_entry = cp_port_of_entry AND

Line 1781: cp_cntry_entry_form_num igs_pe_visit_histry.cntry_entry_form_num%TYPE) IS

1777: ||
1778: */
1779:
1780: CURSOR chk_duplicate(cp_port_of_entry igs_pe_visit_histry.port_of_entry%TYPE,
1781: cp_cntry_entry_form_num igs_pe_visit_histry.cntry_entry_form_num%TYPE) IS
1782: SELECT rowid,vh.*
1783: FROM igs_pe_visit_histry vh
1784: WHERE port_of_entry = cp_port_of_entry AND
1785: cntry_entry_form_num = cp_cntry_entry_form_num;

Line 1783: FROM igs_pe_visit_histry vh

1779:
1780: CURSOR chk_duplicate(cp_port_of_entry igs_pe_visit_histry.port_of_entry%TYPE,
1781: cp_cntry_entry_form_num igs_pe_visit_histry.cntry_entry_form_num%TYPE) IS
1782: SELECT rowid,vh.*
1783: FROM igs_pe_visit_histry vh
1784: WHERE port_of_entry = cp_port_of_entry AND
1785: cntry_entry_form_num = cp_cntry_entry_form_num;
1786:
1787:

Line 1823: igs_pe_visit_histry_pkg.insert_row(

1819: BEGIN
1820:
1821: SAVEPOINT before_insert;
1822:
1823: igs_pe_visit_histry_pkg.insert_row(
1824: X_ROWID => l_rowid,
1825: X_PORT_OF_ENTRY => visit_rec.port_of_entry,
1826: X_CNTRY_ENTRY_FORM_NUM => visit_rec.cntry_entry_form_num ,
1827: X_VISA_ID => visit_rec.visa_id ,

Line 1919: l_visit_end_date igs_pe_visit_histry.visit_end_date%TYPE;

1915:
1916: l_error VARCHAR2(30);
1917: l_message_name VARCHAR2(30);
1918: l_app VARCHAR2(50);
1919: l_visit_end_date igs_pe_visit_histry.visit_end_date%TYPE;
1920:
1921: BEGIN
1922:
1923: SAVEPOINT before_update;

Line 1933: igs_pe_visit_histry_pkg.update_row(

1929: l_visit_end_date := NULL;
1930: END IF;
1931: END IF;
1932:
1933: igs_pe_visit_histry_pkg.update_row(
1934: X_ROWID => dup_visit_rec.rowid,
1935: X_PORT_OF_ENTRY => NVL(visit_rec.port_of_entry,dup_visit_rec.port_of_entry),
1936: X_CNTRY_ENTRY_FORM_NUM => NVL(visit_rec.cntry_entry_form_num,dup_visit_rec.cntry_entry_form_num),
1937: X_VISA_ID => NVL(visit_rec.visa_id ,dup_visit_rec.visa_id),

Line 2064: FROM igs_pe_visit_histry vs

2060: match_ind='19'
2061: WHERE interface_run_id=l_interface_run_id
2062: AND STATUS = '2'
2063: AND EXISTS( SELECT vs.rowid
2064: FROM igs_pe_visit_histry vs
2065: WHERE vs.port_of_entry = UPPER(vh.port_of_entry) AND
2066: UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num))
2067: AND EXISTS( SELECT vi.rowid
2068: FROM igs_pe_visa_int vi

Line 2107: FROM igs_pe_visit_histry vs ,

2103: FROM igs_pe_visa_int vi
2104: WHERE vi.interface_visa_id = vh.interface_visa_id AND
2105: vi.status = '1')
2106: AND EXISTS( SELECT vs.rowid
2107: FROM igs_pe_visit_histry vs ,
2108: igs_pe_visa pev
2109: WHERE vs.visa_id = pev.visa_id AND
2110: vs.port_of_entry = UPPER(vh.port_of_entry) AND
2111: UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num) AND

Line 2141: FROM igs_pe_visit_histry vs

2137: UPDATE igs_pe_vst_hist_int vh
2138: SET status = '3',
2139: match_ind='20',
2140: (dup_port_of_entry,dup_cntry_entry_form_num) = (SELECT port_of_entry,cntry_entry_form_num
2141: FROM igs_pe_visit_histry vs
2142: WHERE vs.port_of_entry = UPPER(vh.port_of_entry) AND
2143: UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num))
2144: WHERE interface_run_id=l_interface_run_id AND
2145: status = '2' AND

Line 2152: FROM igs_pe_visit_histry

2148: WHERE vsi.interface_visa_id = vh.interface_visa_id AND
2149: vsi.status = '1') AND
2150: match_ind IS NULL AND
2151: EXISTS (SELECT rowid
2152: FROM igs_pe_visit_histry
2153: WHERE port_of_entry = UPPER(vh.port_of_entry) AND
2154: UPPER(cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num) );
2155: END IF;
2156: