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.

APPS.IGR_IMP_003 dependencies on IGR_I_APPL

Line 17: CURSOR c_inquiry_dtls (cp_interface_run_id igr_i_appl_int.interface_run_id%TYPE)

13: ---------------------------------------------------------------------------------------------------------------------------*/
14:
15: /** Main Cursor to iterate in Inquiry details records **/
16:
17: CURSOR c_inquiry_dtls (cp_interface_run_id igr_i_appl_int.interface_run_id%TYPE)
18: IS
19: SELECT a.rowid,a.*
20: FROM igr_i_appl_int a
21: WHERE a.interface_run_id = cp_interface_run_id

Line 20: FROM igr_i_appl_int a

16:
17: CURSOR c_inquiry_dtls (cp_interface_run_id igr_i_appl_int.interface_run_id%TYPE)
18: IS
19: SELECT a.rowid,a.*
20: FROM igr_i_appl_int a
21: WHERE a.interface_run_id = cp_interface_run_id
22: AND a.status = '2';
23:
24: CURSOR c_inquiry_lines (cp_interface_run_id igr_i_lines_int.interface_run_id%TYPE)

Line 99: lv_enquiry_appl_number igr_i_appl.enquiry_appl_number%TYPE;

95: ***************************************************************/
96:
97: ----------------Variable Declarations-------------------------------------
98: lv_enq_appl_rowid ROWID;
99: lv_enquiry_appl_number igr_i_appl.enquiry_appl_number%TYPE;
100: ln_org_id igr_i_appl.org_id%TYPE ;
101: l_sales_lead_id igr_i_appl_v.sales_lead_id%TYPE;
102:
103: lv_msg_data VARCHAR2(4000);

Line 100: ln_org_id igr_i_appl.org_id%TYPE ;

96:
97: ----------------Variable Declarations-------------------------------------
98: lv_enq_appl_rowid ROWID;
99: lv_enquiry_appl_number igr_i_appl.enquiry_appl_number%TYPE;
100: ln_org_id igr_i_appl.org_id%TYPE ;
101: l_sales_lead_id igr_i_appl_v.sales_lead_id%TYPE;
102:
103: lv_msg_data VARCHAR2(4000);
104: lv_return_status VARCHAR2(1);

Line 101: l_sales_lead_id igr_i_appl_v.sales_lead_id%TYPE;

97: ----------------Variable Declarations-------------------------------------
98: lv_enq_appl_rowid ROWID;
99: lv_enquiry_appl_number igr_i_appl.enquiry_appl_number%TYPE;
100: ln_org_id igr_i_appl.org_id%TYPE ;
101: l_sales_lead_id igr_i_appl_v.sales_lead_id%TYPE;
102:
103: lv_msg_data VARCHAR2(4000);
104: lv_return_status VARCHAR2(1);
105: lv_msg_count NUMBER;

Line 237: UPDATE igr_i_appl_int

233: END IF;
234:
235: IF (lv_person_type_code IS NULL AND p_old_person_type_code = cst_OTHER) THEN
236: BEGIN
237: UPDATE igr_i_appl_int
238: SET status = '3',
239: error_code = 'E731'
240: WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
241: END;

Line 250: UPDATE igr_i_appl_int

246: CLOSE c_sys_person_type;
247:
248: IF (lv_funnel_status IS NOT NULL AND l_sys_person_type <> cst_PROSPECT) THEN
249: BEGIN
250: UPDATE igr_i_appl_int
251: SET status = '3',
252: error_code = 'E732' /* Invalid Person Type */
253: WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
254: END;

Line 349: UPDATE igr_i_appl_int

345:
346: IF lv_msg_data IS NOT NULL THEN
347: /* Return status of Null should be treated as Success */
348: IF NVL(lv_return_status,'S') = 'S' THEN
349: UPDATE igr_i_appl_int
350: SET status = '4',
351: error_code = 'E702',
352: error_text = lv_msg_data
353: WHERE rowid = p_inquiry_dtls_rec.rowid;

Line 356: UPDATE igr_i_appl_int

352: error_text = lv_msg_data
353: WHERE rowid = p_inquiry_dtls_rec.rowid;
354: ELSE
355: ROLLBACK TO perinq_save;
356: UPDATE igr_i_appl_int
357: SET status = '3',
358: error_code = 'E322',
359: error_text = lv_msg_data
360: WHERE rowid = p_inquiry_dtls_rec.rowid;

Line 365: UPDATE igr_i_appl_int

361: END IF;
362: ELSE
363: /* Return status of Null should be treated as Success */
364: IF NVL(lv_return_status,'S') = 'S' THEN
365: UPDATE igr_i_appl_int
366: SET status = '1',
367: error_code = NULL,
368: error_text = NULL,
369: enquiry_appl_number= lv_enquiry_appl_number,

Line 375: UPDATE igr_i_appl_int

371: WHERE rowid = p_inquiry_dtls_rec.rowid;
372: ELSE
373: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
374: ROLLBACK TO perinq_save;
375: UPDATE igr_i_appl_int
376: SET status = '3',
377: error_code = 'E322',
378: error_text = l_error_text
379: WHERE rowid = p_inquiry_dtls_rec.rowid;

Line 387: /* If insert is NOT successful update status to 3 in table igr_i_appl_int */

383:
384: EXCEPTION
385: WHEN OTHERS THEN
386: l_sqlerrm := SQLERRM;
387: /* If insert is NOT successful update status to 3 in table igr_i_appl_int */
388: p_status := '3';
389:
390: ROLLBACK TO perinq_save;
391:

Line 393: igr_i_appl_int

389:
390: ROLLBACK TO perinq_save;
391:
392: UPDATE
393: igr_i_appl_int
394: SET
395: status = '3',
396: error_code = 'E322' ,
397: error_text = NVL(lv_msg_data,l_sqlerrm)

Line 438: CURSOR c_inquiry_status (cp_inquiry_status igr_i_appl_int.inquiry_status%TYPE) IS

434: FROM igs_pe_person_base_v
435: WHERE person_id =p_person_id ;
436:
437: /*cursor to select inquiry status*/
438: CURSOR c_inquiry_status (cp_inquiry_status igr_i_appl_int.inquiry_status%TYPE) IS
439: SELECT
440: 'X'
441: FROM
442: igr_i_status_v

Line 448: CURSOR c_inquiry_source_type (cp_inquiry_source_type igr_i_appl_int.inquiry_source_type%TYPE) IS

444: s_enquiry_status = cp_inquiry_status
445: AND dsp_closed_ind = 'N';
446:
447: /*cursor to select inquiry source type */
448: CURSOR c_inquiry_source_type (cp_inquiry_source_type igr_i_appl_int.inquiry_source_type%TYPE) IS
449: SELECT
450: 'X'
451: FROM
452: fnd_lookup_values

Line 462: CURSOR c_inquiry_type_id (cp_inquiry_type_id igr_i_appl_int.inquiry_type_id%TYPE) IS

458: AND VIEW_APPLICATION_ID = 279
459: AND SECURITY_GROUP_ID = 0;
460:
461: /*cursor to select inquiry entry status id */
462: CURSOR c_inquiry_type_id (cp_inquiry_type_id igr_i_appl_int.inquiry_type_id%TYPE) IS
463: SELECT
464: 'X'
465: FROM
466: igr_i_inquiry_types

Line 472: CURSOR c_inquiry_entry_level_id (cp_inquiry_entry_level_id igr_i_appl_int.inquiry_entry_level_id%TYPE) IS

468: enabled_flag ='Y' AND
469: inquiry_type_id = cp_inquiry_type_id;
470:
471: /*cursor to select inquiry entry level id */
472: CURSOR c_inquiry_entry_level_id (cp_inquiry_entry_level_id igr_i_appl_int.inquiry_entry_level_id%TYPE) IS
473: SELECT
474: inq_entry_level_id
475: FROM
476: igr_i_entry_lvls_v

Line 483: CURSOR c_registering_person_id (cp_registering_person_id igr_i_appl_int.registering_person_id%TYPE) IS

479: AND inq_entry_level_id = cp_inquiry_entry_level_id;
480:
481:
482: /*cursor to select registering person id */
483: CURSOR c_registering_person_id (cp_registering_person_id igr_i_appl_int.registering_person_id%TYPE) IS
484: SELECT
485: 'X'
486: FROM
487: igs_pe_person_base_v

Line 492: CURSOR c_edu_goal_id (cp_edu_goal_id igr_i_appl_int.edu_goal_id%TYPE) IS

488: WHERE
489: person_id = cp_registering_person_id;
490:
491: /*cursor to select education goal */
492: CURSOR c_edu_goal_id (cp_edu_goal_id igr_i_appl_int.edu_goal_id%TYPE) IS
493: SELECT
494: 'X'
495: FROM
496: igs_ad_code_classes cc

Line 503: CURSOR c_inq_school_of_interest_id (cp_inq_school_of_interest_id igr_i_appl_int.inquiry_school_of_interest_id%TYPE) IS

499: AND NVL(cc.closed_ind,'N') = 'N'
500: AND cc.code_id = cp_edu_goal_id;
501:
502: /*cursor to select inquiry school of interest id */
503: CURSOR c_inq_school_of_interest_id (cp_inq_school_of_interest_id igr_i_appl_int.inquiry_school_of_interest_id%TYPE) IS
504: SELECT
505: 'X'
506: FROM
507: igs_ad_schl_aply_to

Line 513: CURSOR c_learn_source_id (cp_learn_source_id igr_i_appl_int.learn_source_id%TYPE) IS

509: closed_ind = 'N'
510: AND sch_apl_to_id = cp_inq_school_of_interest_id;
511:
512: /*cursor to select learn source id */
513: CURSOR c_learn_source_id (cp_learn_source_id igr_i_appl_int.learn_source_id%TYPE) IS
514: SELECT
515: 'X'
516: FROM
517: igs_ad_code_classes cc

Line 525: CURSOR c_influence_source_id (cp_influence_source_id igr_i_appl_int.influence_source_id%TYPE) IS

521: AND cc.code_id = cp_learn_source_id;
522:
523:
524: /*cursor to select influence source id */
525: CURSOR c_influence_source_id (cp_influence_source_id igr_i_appl_int.influence_source_id%TYPE) IS
526: SELECT
527: 'X'
528: FROM
529: igs_ad_code_classes cc

Line 537: CURSOR c_acad_cal_type_ci (p_acad_cal_type igr_i_appl_int.acad_cal_type%TYPE,

533: AND cc.code_id = cp_influence_source_id;
534:
535:
536: /*cursor to select academic cal type and ci sequence number */
537: CURSOR c_acad_cal_type_ci (p_acad_cal_type igr_i_appl_int.acad_cal_type%TYPE,
538: p_acad_ci_sequence_number igr_i_appl_int.acad_ci_sequence_number%TYPE) IS
539:
540: SELECT
541: 'X'

Line 538: p_acad_ci_sequence_number igr_i_appl_int.acad_ci_sequence_number%TYPE) IS

534:
535:
536: /*cursor to select academic cal type and ci sequence number */
537: CURSOR c_acad_cal_type_ci (p_acad_cal_type igr_i_appl_int.acad_cal_type%TYPE,
538: p_acad_ci_sequence_number igr_i_appl_int.acad_ci_sequence_number%TYPE) IS
539:
540: SELECT
541: 'X'
542: FROM

Line 568: CURSOR c_adm_cal_type_ci (p_adm_cal_type igr_i_appl_int.adm_cal_type%TYPE,

564: AND lkupv1.lookup_type = 'CALENDAR_STATUS';
565:
566:
567: /*cursor to select admission cal type and ci sequence number */
568: CURSOR c_adm_cal_type_ci (p_adm_cal_type igr_i_appl_int.adm_cal_type%TYPE,
569: p_adm_ci_sequence_number igr_i_appl_int.adm_ci_sequence_number%TYPE) IS
570: SELECT
571: 'X'
572: FROM

Line 569: p_adm_ci_sequence_number igr_i_appl_int.adm_ci_sequence_number%TYPE) IS

565:
566:
567: /*cursor to select admission cal type and ci sequence number */
568: CURSOR c_adm_cal_type_ci (p_adm_cal_type igr_i_appl_int.adm_cal_type%TYPE,
569: p_adm_ci_sequence_number igr_i_appl_int.adm_ci_sequence_number%TYPE) IS
570: SELECT
571: 'X'
572: FROM
573: igs_ca_inst_alt_v ciav1,

Line 602: CURSOR c_source_promotion (cp_source_promotion_id igr_i_appl_int.source_promotion_id%TYPE) IS

598: party_id = cp_party_id;
599:
600: -- Validation for the source_promotion_id added as a part of the Capture Campaign event
601:
602: CURSOR c_source_promotion (cp_source_promotion_id igr_i_appl_int.source_promotion_id%TYPE) IS
603: SELECT
604: 'X'
605: FROM
606: ams_p_source_codes_v sc,

Line 624: igr_i_appl_int

620: /* Validation for inquiry date */
621: IF( p_inquiry_dtls_rec.inquiry_dt> SYSDATE) THEN
622: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E349', 8405);
623: UPDATE
624: igr_i_appl_int
625: SET
626: status = '3',
627: error_code = 'E349' , /* Error code for validation to check if system date is greater than system date */
628: error_text = l_error_text

Line 645: igr_i_appl_int

641: IF ((l_birth_date IS NOT NULL) AND (l_birth_date > p_inquiry_dtls_rec.inquiry_dt)) THEN
642:
643: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E583', 8405);
644: UPDATE
645: igr_i_appl_int
646: SET
647: status = '3',
648: error_code = 'E583' , /* Error code for validation to check if the person is deceased */
649: error_text = l_error_text

Line 663: igr_i_appl_int

659: CLOSE c_deceased;
660: IF lv_deceased_ind = 'Y' THEN
661: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E528', 8405);
662: UPDATE
663: igr_i_appl_int
664: SET
665: status = '3',
666: error_code = 'E528' , /* Error code for validation to check if the person is deceased */
667: error_text = l_error_text

Line 681: igr_i_appl_int

677: FETCH c_inquiry_status INTO lv_exist;
678: IF c_inquiry_status%NOTFOUND THEN
679: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E302', 8405);
680: UPDATE
681: igr_i_appl_int
682: SET
683: status = '3',
684: error_code = 'E302' , /* Error code for validation for field Inquiry Status */
685: error_text = l_error_text

Line 705: igr_i_appl_int

701: FETCH c_inquiry_source_type INTO lv_exist;
702: IF c_inquiry_source_type%NOTFOUND THEN
703: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E303', 8405);
704: UPDATE
705: igr_i_appl_int
706: SET
707: status = '3',
708: error_code = 'E303', /*Error code for validation for field Inquiry Source Type*/
709: error_text = l_error_text

Line 728: igr_i_appl_int

724: FETCH c_inquiry_type_id INTO lv_exist;
725: IF c_inquiry_type_id%NOTFOUND THEN
726: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E313', 8405);
727: UPDATE
728: igr_i_appl_int
729: SET
730: status = '3',
731: error_code = 'E313', --Error code for validation for field Inquiry Type Id
732: error_text = l_error_text

Line 750: UPDATE igr_i_appl_int

746: /* validation for Package items Reduction indicator */
747: IF p_inquiry_dtls_rec.pkg_reduct_ind IS NOT NULL THEN
748: IF p_inquiry_dtls_rec.pkg_reduct_ind NOT IN ('Y','N') THEN
749: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E360', 8405);
750: UPDATE igr_i_appl_int
751: SET status = '3',
752: error_code = 'E360' ,/*Error code for validation for field Package items Reduction Ind */
753: error_text = l_error_text
754: WHERE

Line 768: igr_i_appl_int

764: FETCH c_inquiry_entry_level_id INTO lv_exist;
765: IF c_inquiry_entry_level_id%NOTFOUND THEN
766: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E305', 8405);
767: UPDATE
768: igr_i_appl_int
769: SET
770: status = '3',
771: error_code = 'E305' ,/*Error code for validation for field Inquiry Entry Level Id*/
772: error_text = l_error_text

Line 794: UPDATE igr_i_appl_int

790:
791: IF c_registering_person_id%NOTFOUND THEN
792: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E306', 8405);
793:
794: UPDATE igr_i_appl_int
795: SET status = '3',
796: error_code = 'E306' , /*Error code for validation for field Registering person Id*/
797: error_text = l_error_text
798: WHERE

Line 814: UPDATE igr_i_appl_int

810: /* validation for override process indicator */
811: IF p_inquiry_dtls_rec.override_process_ind IS NOT NULL THEN
812: IF p_inquiry_dtls_rec.override_process_ind NOT IN ('Y','N') THEN
813: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E307', 8405);
814: UPDATE igr_i_appl_int
815: SET status = '3',
816: error_code = 'E307' ,/*Error code for validation for field Override Process Ind */
817: error_text = l_error_text
818: WHERE

Line 832: UPDATE igr_i_appl_int

828: FETCH c_edu_goal_id INTO lv_exist;
829:
830: IF c_edu_goal_id%NOTFOUND THEN
831: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E308', 8405);
832: UPDATE igr_i_appl_int
833: SET status = '3',
834: error_code = 'E308', /*Error code for validation for field Education Goal Id*/
835: error_text = l_error_text
836: WHERE

Line 854: UPDATE igr_i_appl_int

850: OPEN c_inq_school_of_interest_id (p_inquiry_dtls_rec.inquiry_school_of_interest_id);
851: FETCH c_inq_school_of_interest_id INTO lv_exist;
852: IF c_inq_school_of_interest_id%NOTFOUND THEN
853: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E309', 8405);
854: UPDATE igr_i_appl_int
855: SET status = '3',
856: error_code = 'E309' ,/*Error code for validation for field Inquiry School of Interest Id*/
857: error_text = l_error_text
858: WHERE

Line 878: UPDATE igr_i_appl_int

874: OPEN c_learn_source_id (p_inquiry_dtls_rec.learn_source_id);
875: FETCH c_learn_source_id INTO lv_exist;
876: IF c_learn_source_id%NOTFOUND THEN
877: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E310', 8405);
878: UPDATE igr_i_appl_int
879: SET status = '3',
880: error_code = 'E310' ,/*Error code for validation for field Learn Source Id*/
881: error_text = l_error_text
882: WHERE

Line 900: UPDATE igr_i_appl_int

896: OPEN c_influence_source_id (p_inquiry_dtls_rec.influence_source_id);
897: FETCH c_influence_source_id INTO lv_exist;
898: IF c_influence_source_id%NOTFOUND THEN
899: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E311', 8405);
900: UPDATE igr_i_appl_int
901: SET status = '3',
902: error_code = 'E311' ,/*Error code for validation for field Influence Source Id*/
903: error_text = l_error_text
904: WHERE

Line 944: UPDATE igr_i_appl_int

940: P_DESC_FLEX_NAME => 'IGR_S_INQUIRY_FLEX'
941: ) THEN
942:
943: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E008', 8405);
944: UPDATE igr_i_appl_int
945: SET status = '3',
946: error_code = 'E008' ,/*Using this Error code for validation for field Flex Field as no error code specified for this*/
947: error_text = l_error_text
948: WHERE

Line 965: UPDATE igr_i_appl_int

961:
962: FETCH c_acad_cal_type_ci INTO lv_exist;
963: IF c_acad_cal_type_ci%NOTFOUND THEN
964: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E301', 8405);
965: UPDATE igr_i_appl_int
966: SET status = '3',
967: error_code = 'E301' ,/*Using this Error code for validation for field Acad Cal type ci seq number as no specific error code specified for this*/
968: error_text = l_error_text
969: WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;

Line 990: UPDATE igr_i_appl_int

986:
987: FETCH c_adm_cal_type_ci INTO lv_exist;
988: IF c_adm_cal_type_ci%NOTFOUND THEN
989: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E301', 8405);
990: UPDATE igr_i_appl_int
991: SET status = '3',
992: error_code = 'E301' ,/* Using this Error code for validation for field Adm Cal type ci seq number as no specific error code specified*/
993: error_text = l_error_text
994: WHERE

Line 1014: igr_i_appl_int

1010:
1011: IF c_source_promotion%NOTFOUND THEN
1012: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E584', 8405);
1013: UPDATE
1014: igr_i_appl_int
1015: SET
1016: status = '3',
1017: error_code = 'E584' , /* Error code if source promotion ID is not correct */
1018: error_text = l_error_text