eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.IGS_AD_IMP_013 dependencies on IGS_AD_ACADHIS_INT_ALL

Line 873: l_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;

869: ) AS
870:
871: -- Added to overcome snapshot-old error {Rollback segment Error }
872:
873: l_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
874: l_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
875: l_count_interface_acadhis_id NUMBER;
876: l_total_records_prcessed NUMBER;
877: CURSOR acad_hist(cp_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE,

Line 874: l_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;

870:
871: -- Added to overcome snapshot-old error {Rollback segment Error }
872:
873: l_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
874: l_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
875: l_count_interface_acadhis_id NUMBER;
876: l_total_records_prcessed NUMBER;
877: CURSOR acad_hist(cp_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE,
878: cp_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE)

Line 877: CURSOR acad_hist(cp_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE,

873: l_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
874: l_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
875: l_count_interface_acadhis_id NUMBER;
876: l_total_records_prcessed NUMBER;
877: CURSOR acad_hist(cp_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE,
878: cp_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE)
879: IS
880: -- Institution does not match so creating
881: SELECT cst_insert dmlmode, rowid, a.*

Line 878: cp_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE)

874: l_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
875: l_count_interface_acadhis_id NUMBER;
876: l_total_records_prcessed NUMBER;
877: CURSOR acad_hist(cp_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE,
878: cp_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE)
879: IS
880: -- Institution does not match so creating
881: SELECT cst_insert dmlmode, rowid, a.*
882: FROM IGS_AD_ACADHIS_INT_ALL a

Line 882: FROM IGS_AD_ACADHIS_INT_ALL a

878: cp_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE)
879: IS
880: -- Institution does not match so creating
881: SELECT cst_insert dmlmode, rowid, a.*
882: FROM IGS_AD_ACADHIS_INT_ALL a
883: WHERE a.interface_run_id = p_interface_run_id
884: AND a.status = '2'
885: AND ( NOT EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
886: WHERE h1.party_id = a.person_id

Line 897: FROM IGS_AD_ACADHIS_INT_ALL a

893:
894: --Exact match
895: UNION ALL
896: SELECT cst_update dmlmode, rowid, a.*
897: FROM IGS_AD_ACADHIS_INT_ALL a
898: WHERE a.interface_run_id = p_interface_run_id
899: AND a.status = '2'
900: AND ( p_rule = 'I' OR (p_rule = 'R' AND a.match_ind = cst_mi_val_21))
901: AND ( EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2

Line 922: FROM IGS_AD_ACADHIS_INT_ALL a

918: -- ( matching instituion code but dates do not match and no partial match
919: -- ( both start date and end date for all OSS matching records is NULL))
920: UNION ALL
921: SELECT cst_first_row dmlmode, rowid, a.*
922: FROM IGS_AD_ACADHIS_INT_ALL a
923: WHERE a.interface_run_id = p_interface_run_id
924: AND a.status = '2'
925: AND UPDATE_EDUCATION_ID IS NULL
926: AND NVL(a.start_date,a.end_date) IS NOT NULL

Line 945: FROM IGS_AD_ACADHIS_INT_ALL a

941: AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id
942: -- Partial match finds single record, hence update if discrepancy rule is 'I'/'R-21' - per bug 3417941
943: UNION ALL
944: SELECT cst_partial_update dmlmode, rowid, a.*
945: FROM IGS_AD_ACADHIS_INT_ALL a
946: WHERE a.interface_run_id = p_interface_run_id
947: AND a.status = '2'
948: AND UPDATE_EDUCATION_ID IS NULL
949: AND (p_rule = 'I' OR (p_rule = 'R' AND a.match_ind = cst_mi_val_21))

Line 1382: IGS_AD_ACADHIS_INT_ALL

1378: END IF;
1379:
1380:
1381: UPDATE
1382: IGS_AD_ACADHIS_INT_ALL
1383: SET
1384: error_code = l_error_Code,
1385: error_text = l_error_text,
1386: status = cst_s_val_3,

Line 1397: UPDATE IGS_AD_ACADHIS_INT_ALL

1393: RETURN;
1394: END;
1395:
1396: IF l_return_status IN ('E','U') THEN
1397: UPDATE IGS_AD_ACADHIS_INT_ALL
1398: SET error_code = 'E322',
1399: error_text = l_msg_data,
1400: status = '3',
1401: match_ind = DECODE (

Line 1411: UPDATE IGS_AD_ACADHIS_INT_ALL

1407: --log detail
1408: ELSE
1409: -- BUG 2385289 BY RRENGARA ON 24-MAY-2002
1410: -- updated education_id after successful insert
1411: UPDATE IGS_AD_ACADHIS_INT_ALL
1412: SET status = cst_s_val_1,
1413: error_code = cst_ec_val_NULL,
1414: education_id = l_education_id
1415: WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;

Line 1418: UPDATE IGS_AD_ACADHIS_INT_ALL

1414: education_id = l_education_id
1415: WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1416: END IF;
1417: ELSE -- validation fails
1418: UPDATE IGS_AD_ACADHIS_INT_ALL
1419: SET error_code = l_error_code,
1420: error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405),
1421: status = l_status,
1422: match_ind = DECODE (

Line 1554: IGS_AD_ACADHIS_INT_ALL

1550:
1551:
1552:
1553: UPDATE
1554: IGS_AD_ACADHIS_INT_ALL
1555: SET
1556: error_code = l_error_Code,
1557: error_text =l_error_text,
1558: status = '3',

Line 1570: UPDATE IGS_AD_ACADHIS_INT_ALL

1566: END;
1567:
1568:
1569: IF l_return_status IN ('E','U') THEN
1570: UPDATE IGS_AD_ACADHIS_INT_ALL
1571: SET error_code = 'E014',
1572: status = '3',
1573: error_text = l_msg_data,
1574: match_ind = DECODE (

Line 1583: UPDATE IGS_AD_ACADHIS_INT_ALL

1579: --log detail
1580: ELSE
1581: -- BUG 2385289 BY RRENGARA ON 24-MAY-2002
1582: -- updated education_id after successful update
1583: UPDATE IGS_AD_ACADHIS_INT_ALL
1584: SET match_ind = decode ( person_history_rec.dmlmode,
1585: cst_partial_update, cst_mi_val_12,
1586: decode ( person_history_rec.match_ind ,
1587: NULL, cst_mi_val_18,

Line 1594: UPDATE IGS_AD_ACADHIS_INT_ALL

1590: education_id = c_null_hdlg_acad_hist_cur_rec.Education_Id
1591: WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1592: END IF;
1593: ELSE
1594: UPDATE IGS_AD_ACADHIS_INT_ALL
1595: SET status = cst_s_val_3,
1596: error_code = l_error_code,
1597: error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405),
1598: match_ind = DECODE (

Line 1619: UPDATE IGS_AD_ACADHIS_INT_ALL acad

1615:
1616: l_prog_label := 'igs.plsql.igs_ad_imp_013.prc_pe_acad_hist';
1617:
1618: --If given invalid update education ID then error out.
1619: UPDATE IGS_AD_ACADHIS_INT_ALL acad
1620: SET
1621: status = '3', error_code = 'E711',
1622: error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E711', 8405)
1623: WHERE update_education_id IS NOT NULL

Line 1631: UPDATE IGS_AD_ACADHIS_INT_ALL

1627: ) ;
1628: COMMIT;
1629:
1630: IF p_rule IN ('E', 'I') THEN
1631: UPDATE IGS_AD_ACADHIS_INT_ALL
1632: SET
1633: status = '3'
1634: , error_code = 'E700'
1635: ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)

Line 1644: UPDATE IGS_AD_ACADHIS_INT_ALL

1640: COMMIT;
1641:
1642: -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
1643: IF p_rule = 'R' THEN
1644: UPDATE IGS_AD_ACADHIS_INT_ALL
1645: SET
1646: status = '1', error_code = NULL
1647: WHERE interface_run_id = p_interface_run_id
1648: AND status = '2'

Line 1655: UPDATE IGS_AD_ACADHIS_INT_ALL a

1651: COMMIT;
1652:
1653: -- 3. Set STATUS to 3 for interface records with multiple matching duplicate system records for RULE = I
1654: IF p_rule = 'I' THEN
1655: UPDATE IGS_AD_ACADHIS_INT_ALL a
1656: SET
1657: status = '3'
1658: , match_ind = '13'
1659: WHERE interface_run_id = p_interface_run_id

Line 1682: UPDATE IGS_AD_ACADHIS_INT_ALL a

1678: COMMIT;
1679: -- 4. Set STATUS to 3 for interface records with multiple matching duplicate system record for RULE = R
1680: -- and either MATCH IND IN (15, 21) OR IS NULL
1681: IF p_rule = 'R' THEN
1682: UPDATE IGS_AD_ACADHIS_INT_ALL a
1683: SET
1684: status = '3'
1685: , match_ind = '13'
1686: WHERE interface_run_id = p_interface_run_id

Line 1709: UPDATE IGS_AD_ACADHIS_INT_ALL a

1705: END IF;
1706: COMMIT;
1707: -- 5. Set STATUS to 1 and MATCH IND to 19 for interface records with RULE = E matching OSS record(s)
1708: IF p_rule = 'E' THEN
1709: UPDATE IGS_AD_ACADHIS_INT_ALL a
1710: SET
1711: status = '1'
1712: , match_ind = '19'
1713: , education_id = update_education_id

Line 1717: UPDATE IGS_AD_ACADHIS_INT_ALL a

1713: , education_id = update_education_id
1714: WHERE update_education_id IS NOT NULL;
1715: COMMIT;
1716:
1717: UPDATE IGS_AD_ACADHIS_INT_ALL a
1718: SET
1719: status = '3'
1720: , match_ind = '19'
1721: ,error_code = 'E708'

Line 1743: UPDATE IGS_AD_ACADHIS_INT_ALL a

1739: WHERE interface_acadhis_id = a.interface_acadhis_id
1740: AND status = '2');
1741: COMMIT;
1742:
1743: UPDATE IGS_AD_ACADHIS_INT_ALL a
1744: SET
1745: status = '1'
1746: , match_ind = '19'
1747: , education_id =

Line 1778: UPDATE IGS_AD_ACADHIS_INT_ALL a

1774: TO_DATE('01-01-0001','DD-MM-YYYY')))
1775: );
1776:
1777: -- Partial match finds single record, hence success if discrepancy rule is 'E' - per bug 3417941
1778: UPDATE IGS_AD_ACADHIS_INT_ALL a
1779: SET
1780: status = '1'
1781: , match_ind = '19'
1782: , education_id =

Line 1837: FROM IGS_AD_ACADHIS_INT_ALL

1833: **********************************************************************************/
1834:
1835: SELECT COUNT(interface_acadhis_id)
1836: INTO l_count_interface_acadhis_id
1837: FROM IGS_AD_ACADHIS_INT_ALL
1838: WHERE interface_run_id = p_interface_run_id
1839: AND status =2 ;
1840:
1841: l_total_records_prcessed := 0;

Line 1849: FROM IGS_AD_ACADHIS_INT_ALL

1845:
1846: SELECT
1847: MIN(interface_acadhis_id) , MAX(interface_acadhis_id)
1848: INTO l_min_interface_acadhis_id , l_max_interface_acadhis_id
1849: FROM IGS_AD_ACADHIS_INT_ALL
1850: WHERE interface_run_id = p_interface_run_id
1851: AND status =2
1852: AND rownum < =100;
1853:

Line 1890: UPDATE IGS_AD_ACADHIS_INT_ALL acad

1886: -- 7. Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching OSS record(s)
1887: -- in ALL updateable column values
1888:
1889: IF p_rule = 'R' THEN
1890: UPDATE IGS_AD_ACADHIS_INT_ALL acad
1891: SET
1892: status = '1'
1893: , match_ind = '23'
1894: WHERE interface_run_id = p_interface_run_id

Line 1947: UPDATE IGS_AD_ACADHIS_INT_ALL acad

1943: COMMIT;
1944: -- Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and MATCH IND <> 21, 25, ones failed discrepancy check
1945:
1946: IF p_rule = 'R' THEN
1947: UPDATE IGS_AD_ACADHIS_INT_ALL acad
1948: SET
1949: status = '3'
1950: , match_ind = '20'
1951: , dup_acad_history_id = ( SELECT hz_acad_hist_id FROM igs_Ad_Hz_Acad_Hist

Line 1961: UPDATE IGS_AD_ACADHIS_INT_ALL acad

1957: AND update_Education_id IS NOT NULL;
1958:
1959: COMMIT;
1960:
1961: UPDATE IGS_AD_ACADHIS_INT_ALL acad
1962: SET
1963: status = '3'
1964: , match_ind = '20'
1965: , dup_acad_history_id = ( SELECT hz_acad_hist_id FROM igs_Ad_Hz_Acad_Hist

Line 2005: UPDATE IGS_AD_ACADHIS_INT_ALL acad

2001: END IF;
2002: COMMIT;
2003: -- Multiple Partial (do not need to compare dates as date are already compared
2004: -- and only partial matching records are in status '2'
2005: UPDATE IGS_AD_ACADHIS_INT_ALL acad
2006: SET
2007: status = '3'
2008: , match_ind = '14'
2009: WHERE interface_run_id = p_interface_run_id

Line 2019: UPDATE IGS_AD_ACADHIS_INT_ALL acad

2015: );
2016: COMMIT;
2017:
2018: -- Partial match finds single record, hence '20,3' for discrepancy rule 'R' - per bug 3417941
2019: UPDATE IGS_AD_ACADHIS_INT_ALL acad
2020: SET
2021: status = '3'
2022: , match_ind = '20'
2023: WHERE interface_run_id = p_interface_run_id

Line 2034: UPDATE IGS_AD_ACADHIS_INT_ALL acad

2030: COMMIT;
2031:
2032: --Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
2033: IF p_rule = 'R' THEN
2034: UPDATE IGS_AD_ACADHIS_INT_ALL acad
2035: SET
2036: status = '3'
2037: , error_code = 'E700'
2038: ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)