1 PACKAGE igf_sl_dl_gen_xml AS
2 /* $Header: IGFSL25S.pls 120.0 2005/06/01 13:19:20 appldev noship $ */
3
4 --
5 -- Process to generate direct loan XML output file
6 -- main is the entry point through concurrent program
7 --
8 -- main would validate loan records and insert into _LOR_LOC
9 -- table with document id. Then it would raise business event
10 -- to call the xml gateway routines to create xml document
11 -- this business event would have document id as a parameter
12 --
13 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
14 retcode OUT NOCOPY NUMBER,
15 p_award_year VARCHAR2,
16 p_source_id VARCHAR2,
17 p_report_id VARCHAR2,
18 p_attend_id VARCHAR2,
19 p_fund_id NUMBER,
20 p_fund_dummy NUMBER,
21 p_base_id NUMBER,
22 p_base_dummy NUMBER,
23 p_loan_id NUMBER,
24 p_loan_dummy NUMBER,
25 p_pgroup_id NUMBER);
26
27 --
28 -- Workfwlo Process to store XML output file
29 -- this process is initiated after xml gateway sucessfully
30 -- creates xml document
31 -- this process would store the generated document into
32 -- table after editing it and pass it for printing
33 --
34
35 PROCEDURE store_xml(itemtype IN VARCHAR2,
36 itemkey IN VARCHAR2,
37 actid IN NUMBER,
38 funcmode IN VARCHAR2,
39 resultout OUT NOCOPY VARCHAR2);
40 --
41 -- Process to print direct loan XML output file
42 --
43
44 PROCEDURE print_xml(errbuf OUT NOCOPY VARCHAR2,
45 retcode OUT NOCOPY NUMBER,
46 p_document_id_txt VARCHAR2);
47
48
49 PROCEDURE print_out_xml(p_xml_clob CLOB);
50 PROCEDURE edit_clob(p_document_id_txt VARCHAR2,p_xml_clob OUT NOCOPY CLOB, p_rowid OUT NOCOPY ROWID);
51 --
52 -- Cursor to pick up loan records
53 --
54 CURSOR cur_pick_loans (p_cal_type VARCHAR2, p_seq_number NUMBER,
55 p_base_id NUMBER,
56 p_report_id VARCHAR2,
57 p_attend_id VARCHAR2,
58 p_fund_id NUMBER,
59 p_loan_id NUMBER
60 )
61 IS
62 SELECT
63 lor.*, fcat.fed_fund_code, fmast.ci_cal_type,fmast.ci_sequence_number,
64 awd.base_id, awd.offered_amt,awd.accepted_amt,loan.loan_per_begin_date,loan.loan_per_end_date,
65 loan.loan_number,loan.award_id,loan.loan_status,loan.loan_chg_status,loan.loan_status_date,
66 loan.loan_chg_status_date, loan.active, loan.active_date
67 FROM
68 igf_sl_loans_all loan,
69 igf_sl_lor_all lor,
70 igf_aw_award_all awd,
71 igf_aw_fund_mast_all fmast,
72 igf_aw_fund_cat_all fcat
73 WHERE
74 fmast.ci_cal_type = p_cal_type AND
75 fmast.ci_sequence_number = p_seq_number AND
76 fcat.fed_fund_code IN ('DLP','DLS','DLU') AND
77 fmast.fund_code = fcat.fund_code AND
78 awd.fund_id = fmast.fund_id AND
79 loan.award_id = awd.award_id AND
80 loan.loan_id = lor.loan_id AND
81 fmast.fund_id = NVL(p_fund_id, fmast.fund_id) AND
82 awd.base_id = NVL(p_base_id, awd.base_id) AND
83 loan.loan_id = NVL(p_loan_id, loan.loan_id) AND
84 lor.atd_entity_id_txt = NVL(p_attend_id,atd_entity_id_txt) AND
85 lor.rep_entity_id_txt = NVL(p_report_id,rep_entity_id_txt) AND
86 loan.active = 'Y' AND
87 (loan.loan_status = 'G' OR loan.loan_chg_status = 'G' )
88 ORDER BY
89 lor.rep_entity_id_txt,
90 lor.atd_entity_id_txt,
91 awd.base_id,
92 fcat.fed_fund_code;
93
94 CURSOR cur_pick_loans_all_status (p_cal_type VARCHAR2, p_seq_number NUMBER,
95 p_base_id NUMBER,
96 p_report_id VARCHAR2,
97 p_attend_id VARCHAR2,
98 p_fund_id NUMBER,
99 p_loan_id NUMBER
100 )
101 IS
102 SELECT
103 lor.*, fcat.fed_fund_code, fmast.ci_cal_type,fmast.ci_sequence_number,
104 awd.base_id, awd.offered_amt,awd.accepted_amt,loan.loan_per_begin_date,loan.loan_per_end_date,
105 loan.loan_number,loan.award_id,loan.loan_status,loan.loan_chg_status,loan.loan_status_date,
106 loan.loan_chg_status_date, loan.active, loan.active_date
107 FROM
108 igf_sl_loans_all loan,
109 igf_sl_lor_all lor,
110 igf_aw_award_all awd,
111 igf_aw_fund_mast_all fmast,
112 igf_aw_fund_cat_all fcat
113 WHERE
114 fmast.ci_cal_type = p_cal_type AND
115 fmast.ci_sequence_number = p_seq_number AND
116 fcat.fed_fund_code IN ('DLP','DLS','DLU') AND
117 fmast.fund_code = fcat.fund_code AND
118 awd.fund_id = fmast.fund_id AND
119 loan.award_id = awd.award_id AND
120 loan.loan_id = lor.loan_id AND
121 fmast.fund_id = NVL(p_fund_id, fmast.fund_id) AND
122 awd.base_id = NVL(p_base_id, awd.base_id) AND
123 loan.loan_id = NVL(p_loan_id, loan.loan_id) AND
124 lor.atd_entity_id_txt = NVL(p_attend_id,atd_entity_id_txt) AND
125 lor.rep_entity_id_txt = NVL(p_report_id,rep_entity_id_txt) AND
126 loan.active = 'Y' AND
127 (loan.loan_status in ('G', 'N','R')
128 OR loan.loan_chg_status in ('G','N','R') )
129 ORDER BY
130 lor.rep_entity_id_txt,
131 lor.atd_entity_id_txt,
132 awd.base_id,
133 fcat.fed_fund_code;
134
135 END igf_sl_dl_gen_xml;
[Home] [Help]