[Home] [Help]
1: PACKAGE BODY igf_ap_inst_app AS
2: /* $Header: IGFAP50B.pls 120.0 2005/09/09 17:14:44 appldev noship $ */
3:
4:
5: PROCEDURE update_ToDo_status(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
6: p_application_code igf_ap_appl_status_all.application_code%TYPE,
7: x_return_status OUT NOCOPY VARCHAR2)
8: IS
9: ------------------------------------------------------------------
19: --Who When What
20: -------------------------------------------------------------------
21:
22: --Cursor to fetch to do item
23: CURSOR c_td_item (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
24: cp_application_code igf_ap_appl_status_all.application_code%TYPE)
25: IS
26: SELECT tdmst.todo_number
27: FROM igf_ap_td_item_inst_all tdinst, igf_ap_td_item_mst_all tdmst, Igf_ap_fa_base_rec_all baseRec
23: CURSOR c_td_item (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
24: cp_application_code igf_ap_appl_status_all.application_code%TYPE)
25: IS
26: SELECT tdmst.todo_number
27: FROM igf_ap_td_item_inst_all tdinst, igf_ap_td_item_mst_all tdmst, Igf_ap_fa_base_rec_all baseRec
28: WHERE tdmst.system_todo_type_code = 'INSTAPP'
29: AND baseRec.ci_cal_type = tdmst.ci_cal_type
30: AND baseRec.ci_sequence_number = tdmst.ci_sequence_number
31: AND baseRec.base_id = tdinst.base_id
70: igs_ge_msg_stack.add;
71:
72: END update_ToDo_status;
73:
74: PROCEDURE update_ToDo (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
75: p_application_code igf_ap_appl_status_all.application_code%TYPE,
76: p_status IN igf_ap_td_item_inst_all.status%TYPE,
77: x_return_status OUT NOCOPY VARCHAR2)
78: IS
89: --Who When What
90: -------------------------------------------------------------------
91:
92: --Cursor to fetch to do item
93: CURSOR c_td_item (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
94: cp_application_code igf_ap_appl_status_all.application_code%TYPE)
95: IS
96: SELECT tdmst.todo_number
97: FROM igf_ap_td_item_inst_all tdinst, igf_ap_td_item_mst_all tdmst, Igf_ap_fa_base_rec_all baseRec
93: CURSOR c_td_item (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
94: cp_application_code igf_ap_appl_status_all.application_code%TYPE)
95: IS
96: SELECT tdmst.todo_number
97: FROM igf_ap_td_item_inst_all tdinst, igf_ap_td_item_mst_all tdmst, Igf_ap_fa_base_rec_all baseRec
98: WHERE tdmst.system_todo_type_code = 'INSTAPP'
99: AND baseRec.ci_cal_type = tdmst.ci_cal_type
100: AND baseRec.ci_sequence_number = tdmst.ci_sequence_number
101: AND baseRec.base_id = tdinst.base_id
142: END update_ToDo;
143:
144:
145:
146: PROCEDURE update_app_status(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
147: p_application_code igf_ap_appl_status_all.application_code%TYPE,
148: p_application_status_code igf_ap_appl_status_all.application_status_code%TYPE,
149: x_return_status OUT NOCOPY VARCHAR2)
150: IS
161: --Who When What
162: -------------------------------------------------------------------
163:
164: -- cursor to get the row from application status table
165: CURSOR c_appl_status (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
166: cp_application_code igf_ap_appl_status_all.application_code%TYPE
167: ) IS
168: SELECT rowid
169: FROM IGF_AP_APPL_STATUS_ALL aps
202:
203: END update_app_status;
204:
205:
206: PROCEDURE update_Ant_Data_For_All_Terms (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
207: p_cal_type igs_ca_inst_all.cal_type%TYPE,
208: p_seq_number igs_ca_inst_all.sequence_number%TYPE,
209: p_ant_data_column VARCHAR2,
210: p_ant_data_value VARCHAR2,
238:
239: l_get_cal_typ_seq_num c_all_terms%ROWTYPE;
240:
241: -- to fetch all the anticipated value for the given base_id and award year
242: CURSOR c_get_ant_data(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
243: cp_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
244: cp_ld_seq_number igs_ca_inst_all.sequence_number%TYPE
245: )
246: IS
389: END update_Ant_Data_For_All_Terms;
390:
391:
392:
393: PROCEDURE update_Ant_Data_a_Term(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
394: p_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
395: p_ld_seq_number igs_ca_inst_all.sequence_number%TYPE,
396: p_ant_data_column VARCHAR2,
397: p_ant_data_value VARCHAR2,
413: -------------------------------------------------------------------
414:
415:
416: -- to fetch all the anticipated value for the given base_id and award year
417: CURSOR c_get_ant_data(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
418: cp_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
419: cp_ld_seq_number igs_ca_inst_all.sequence_number%TYPE
420: )
421: IS
554:
555: END update_Ant_Data_a_Term;
556:
557:
558: PROCEDURE raise_event_on_IA_submit (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
559: p_application_code igf_ap_appl_status_all.application_code%TYPE,
560: x_return_status OUT NOCOPY VARCHAR2)
561: IS
562: ------------------------------------------------------------------
573: -------------------------------------------------------------------
574:
575: -- Get person number
576: CURSOR c_person_number(
577: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
578: ) IS
579: SELECT hz.party_number
580: FROM hz_parties hz,
581: igf_ap_fa_base_rec_all fa
577: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
578: ) IS
579: SELECT hz.party_number
580: FROM hz_parties hz,
581: igf_ap_fa_base_rec_all fa
582: WHERE fa.person_id = hz.party_id
583: AND fa.base_id = cp_base_id;
584: l_person_number hz_parties.party_number%TYPE;
585:
585:
586:
587: -- Get award year alternate code
588: CURSOR c_award_year(
589: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
590: ) IS
591: SELECT ca.alternate_code
592: FROM igs_ca_inst_all ca,
593: igf_ap_fa_base_rec_all fa
589: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
590: ) IS
591: SELECT ca.alternate_code
592: FROM igs_ca_inst_all ca,
593: igf_ap_fa_base_rec_all fa
594: WHERE fa.base_id = cp_base_id
595: AND fa.ci_cal_type = ca.cal_type
596: AND fa.ci_sequence_number = ca.sequence_number;
597: l_alternate_code igs_ca_inst_all.alternate_code%TYPE;