[Home] [Help]
PACKAGE BODY: APPS.FV_PROMPT_PAY
Source
1 PACKAGE BODY fv_prompt_pay as
2 -- $Header: FVPPPSTB.pls 120.11 2006/10/11 09:35:07 arcgupta ship $
3 --==============================================================
4 g_module_name VARCHAR2(100) := 'fv.plsql.fv_prompt_pay.';
5
6 -- Error Code and Error Messages
7 v_error_code Number := 0 ;
8 v_error_mesg Varchar2(500) ;
9
10 -- Variables to hold passed parameters
11 v_set_of_books_id Number ;
12 v_from_date Date ;
13 v_to_date Date ;
14
15 ----------------------------------------------------------------------
16 -- MAIN
17 ----------------------------------------------------------------------
18 procedure main (
19 Errbuf OUT NOCOPY varchar2,
20 retcode OUT NOCOPY varchar2,
21 currrency in varchar2,
22 from_date in varchar2,
23 to_dt in varchar2,
24 brk1 in number,
25 brk2 in number DEFAULT NULL ,
26 brk3 in number DEFAULT NULL ,
27 brk4 in number DEFAULT NULL ,
28 agency1 in varchar2 DEFAULT NULL ,
29 agency2 in varchar2 DEFAULT NULL
30 )
31
32 IS
33 l_module_name VARCHAR2(200) := g_module_name || 'main';
34 l_org_id NUMBER(15);
35 l_currency_code VARCHAR2(15);
36 l_set_of_books_name VARCHAR2(30);
37 l_req_id NUMBER(15);
38
39 Begin
40
41 -- Initialize parameters to global variables
42 -- Get Current Org ID
43 l_org_id := MO_GLOBAL.get_current_org_id;
44
45 mo_utils.get_ledger_info(l_org_id,v_set_of_books_id ,l_set_of_books_name);
46
47
48
49 v_from_date := to_date ( from_date , 'YYYY/MM/DD HH24:MI:SS' );
50 v_to_date := to_date ( to_dt , 'YYYY/MM/DD HH24:MI:SS' ) ;
51
52 /* -- by ks
53 If v_error_code = 0 Then
54 Delete from fv_prompt_pay_temp ;
55 populate_temp_table ;
56 End If ;
57
58 If v_error_code = 0 Then
59 Commit ;
60 */
61
62
63
64 -- Get Currency Code by using Set of Books ID
65 select currency_code
66 into l_currency_code
67 from gl_ledgers_public_v
68 where ledger_id = v_set_of_books_id ;
69
70 -- Set Org_ID
71 fnd_request.set_org_id ( l_org_id );
72
73 -- Kick off the Prompt Payment Report
74 l_req_id := fnd_request.submit_request
75 (
76 application => 'FV' ,
77 program => 'FVPPPPST' ,
78 description => NULL ,
79 start_time => NULL ,
80 sub_request => FALSE ,
81 argument1 => v_set_of_books_id ,
82 argument2 => l_set_of_books_name ,
83 argument3 => l_currency_code ,
84 argument4 => from_date ,
85 argument5 => to_dt ,
86 argument6 => brk1 ,
87 argument7 => brk2 ,
88 argument8 => brk3 ,
89 argument9 => brk4 ,
90 argument10 => agency1 ,
91 argument11 => agency2);
92
93 if ( l_req_id <> 0 ) then
94 -- Set Oeg_ID
95 fnd_request.set_org_id ( l_org_id );
96 -- Kick off the Prompt Payment Exception Report
97 l_req_id := fnd_request.submit_request
98 (
99 application => 'FV' ,
100 program => 'FVPPPPEX' ,
101 description => NULL ,
102 start_time => NULL ,
103 sub_request => FALSE ,
104 argument1 => v_set_of_books_id ,
105 argument2 => l_set_of_books_name ,
106 argument3 => l_currency_code ,
107 argument4 => from_date ,
108 argument5 => to_dt ,
109 argument6 => agency1 ,
110 argument7 => agency2);
111 COMMIT;
112 Else
113 Rollback ;
114 End If ;
115
116
117 retcode := to_char(v_error_code) ;
118 errbuf := v_error_mesg ;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 v_error_code := SQLCODE ;
123 v_error_mesg := SQLERRM;
124 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', v_error_mesg) ;
125 RAISE;
126 End Main ;
127
128
129 ----------------------------------------------------------------------
130 -- populate_temp_table
131 ----------------------------------------------------------------------
132 Procedure populate_temp_table is
133 l_module_name VARCHAR2(200) := g_module_name || 'populate_temp_table';
134
135 Begin
136
137 Insert Into fv_prompt_pay_temp
138 ( invoice_id ,
139 pay_invoice_id,
140 pay_payment_number ,
141 pay_due_date ,
142 discount_amount_available ,
143 invoice_payment_id ,
144 discount_taken ,
145 check_date ,
146 invoice_amount,
147 invoice_type_lookup_code)
148 Select
149 A.invoice_id,
150 S.invoice_id ,
151 S.payment_num ,
152 S.due_date ,
153 S.discount_amount_available,
154 P.invoice_payment_id ,
155 nvl(P.discount_taken,0.00) ,
156 K.check_date ,
157 A.INVOICE_AMOUNT ,
158 A.Invoice_type_lookup_code
159 FROM
160 fv_terms_types T,
161 ap_payment_schedules S,
162 ap_invoice_payments P,
163 ap_checks K,
164 ap_invoices A
165 WHERE
166 A.set_of_books_id = v_set_of_books_id
167 AND A.payment_status_flag = 'Y'
168 AND T.term_id = A.terms_id
169 AND T.terms_type = 'PROMPT PAY'
170 AND S.invoice_id = A.invoice_id
171 AND S.due_date is not null
172 AND S.due_date =
173 ( SELECT max(U.due_date)
174 FROM ap_payment_schedules U
175 WHERE U.invoice_id = S.invoice_id)
176 AND P.invoice_id = S.invoice_id
177 AND P.payment_num = S.payment_num
178 AND P.invoice_payment_id =
179 ( SELECT I.invoice_payment_id
180 FROM ap_invoice_payments I,
181 ap_checks C
182 WHERE I.invoice_payment_id = P.invoice_payment_id
183 AND C.check_id = P.check_id
184 AND C.check_date =
185 ( SELECT max(H.check_date)
186 FROM ap_checks H
187 WHERE H.check_id = C.check_id ))
188 AND K.check_id = P.check_id
189 AND K.check_date between v_from_date and v_to_date
190 AND K.void_date is null ;
191 Exception
192 When NO_DATA_FOUND then
193 Null ;
194 WHEN OTHERS THEN
195 v_error_code := SQLCODE ;
196 v_error_mesg := SQLERRM;
197 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', v_error_mesg) ;
198 RAISE;
199 End ;
200
201
202 -----------------------------------------------------------------
203 -- End Of the Package
204 -----------------------------------------------------------------
205 End fv_Prompt_pay ;
206