4
1 PACKAGE IBY_FD_EXTRACT_GEN_PVT AUTHID CURRENT_USER AS
2 /* $Header: ibyfdxgs.pls 120.42 2011/05/09 11:29:17 asarada ship $ */
3
5 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_FD_EXTRACT_GEN_PVT';
6
7 -- Various parameters used by the extract-generating XML views
8 --
9
10 -- system security key- for registered instrument decryption
11 --
12 G_VP_SYS_KEY CONSTANT VARCHAR2(30) := 'SYS_KEY';
13
14 -- payment instruction id; for fv instruction level APIs
15 --
16 G_VP_INSTR_ID CONSTANT VARCHAR2(30) := 'PMT_INSTR_ID';
17
18 -- Federal Summary ECS dos Sequence number; for fv ECS summary
19 --
20 G_VP_FV_ECS_SEQ CONSTANT VARCHAR2(30) := 'FV_ECS_SEQ';
21
22 -- Format type
23 --
24 G_VP_FMT_TYPE CONSTANT VARCHAR2(30) := 'FMT_TYPE';
25
26
27 G_FORMAT_TYPE VARCHAR2(30);
28
29
30 /*Adding types for caching :- Citi Perf*/
31
32 TYPE p_party_contact_point IS RECORD (
33 table_name VARCHAR2(1000),
34 l_payee_party_id NUMBER,
35 l_email VARCHAR2(2000),
36 l_phone_cp_id NUMBER ,
37 l_fax_cp_id NUMBER ,
38 l_url VARCHAR2(2000)
39 );
40
41 TYPE party_contact_Tab_Type IS TABLE OF p_party_contact_point
42 INDEX BY VARCHAR2(2000);
43
44 party_contact_tab party_contact_Tab_Type;
45
46 TYPE p_site_contact_point IS RECORD (
47 table_name VARCHAR2(1000),
48 l_party_site_id NUMBER,
49 l_email VARCHAR2(2000),
50 l_phone_cp_id NUMBER ,
51 l_fax_cp_id NUMBER ,
52 l_url VARCHAR2(2000)
53 );
54
55 TYPE site_contact_Tab_Type IS TABLE OF p_site_contact_point
56 INDEX BY VARCHAR2(2000);
57
58 site_contact_tab site_contact_Tab_Type;
59
60
61 /*End of variables for Caching :- Citi Perf */
62
63 -- for payment format
64 PROCEDURE Create_Extract_1_0
65 (
66 p_payment_instruction_id IN NUMBER,
67 p_save_extract_flag IN VARCHAR2,
68 p_format_type IN VARCHAR2,
69 p_is_reprint_flag IN VARCHAR2,
70 p_sys_key IN iby_security_pkg.des3_key_type,
71 x_extract_doc OUT NOCOPY CLOB
72 );
73
74 -- directly for aux formats
75 -- indirectly for payment format
76 PROCEDURE Create_Extract_1_0
77 (
78 p_payment_instruction_id IN NUMBER,
79 p_save_extract_flag IN VARCHAR2,
80 p_format_type IN VARCHAR2,
81 p_sys_key IN iby_security_pkg.des3_key_type,
82 x_extract_doc OUT NOCOPY CLOB
83 );
84
85
86 -- for separate remittance advice
87 PROCEDURE Create_Extract_1_0
88 (
89 p_payment_instruction_id IN NUMBER,
90 p_save_extract_flag IN VARCHAR2,
91 p_format_type IN VARCHAR2,
92 p_delivery_method IN VARCHAR2,
93 p_payment_id IN NUMBER,
94 p_sys_key IN iby_security_pkg.des3_key_type,
95 x_extract_doc OUT NOCOPY CLOB,
96 p_from_pmt_ref IN NUMBER,
97 p_to_pmt_ref IN NUMBER
98 );
99
100
101 -- for positive pay - bug 5028143
102 PROCEDURE Create_Pos_Pay_Extract_1_0
103 (
104 p_payment_instruction_id IN NUMBER,
105 p_payment_profile_id IN NUMBER,
106 p_from_date IN VARCHAR2,
107 p_to_date IN VARCHAR2,
108 p_sys_key IN iby_security_pkg.des3_key_type,
109 x_extract_doc OUT NOCOPY CLOB
110 );
111
112 -- for LKQ POS PAY issue
113 PROCEDURE Create_Pos_Pay_Extract_2_0
114 (
115 p_payment_instruction_id IN NUMBER,
116
117 p_format_name IN VARCHAR2,
118 p_internal_bank_account_name IN VARCHAR2,
119 p_from_date IN VARCHAR2,
120 p_to_date IN VARCHAR2,
121 p_payment_status IN VARCHAR2,
122 p_reselect IN VARCHAR2,
123
124 p_sys_key IN iby_security_pkg.des3_key_type,
125 x_extract_doc OUT NOCOPY CLOB
126 );
127
128 PROCEDURE Create_PPR_Extract_1_0
129 (
130 p_payment_service_request_id IN NUMBER,
131 p_sys_key IN iby_security_pkg.des3_key_type,
132 x_extract_doc OUT NOCOPY CLOB
133 );
134
135
136
137 FUNCTION Get_FP_TaxRegistration(p_legal_entity_id IN NUMBER)
138 RETURN VARCHAR2;
139
140 FUNCTION Get_Payee_LegalRegistration(p_vendor_id IN NUMBER,
141 p_vendor_site_id IN NUMBER,
142 p_vendor_site_country IN VARCHAR2)
143 RETURN VARCHAR2;
144
145
146 FUNCTION Get_Payee_TaxRegistration(p_party_id IN NUMBER,
147 p_supplier_site_id IN NUMBER) -- bug# 7412315
148 RETURN VARCHAR2;
149
150
151 FUNCTION Get_PayerContact(p_party_id IN NUMBER)
152 RETURN XMLTYPE;
153
154 FUNCTION Get_PayeeContact(p_payment_id IN NUMBER)
155 RETURN XMLTYPE;
156 /*Overloaded Function*/
157 FUNCTION Get_PayeeContact(p_payment_id IN NUMBER
158 ,p_remit_to_location_id IN iby_payments_all.remit_to_location_id%TYPE
159 ,p_party_site_id IN iby_payments_all.party_site_id%TYPE
160 ,p_payee_party_id IN iby_payments_all.payee_party_id%TYPE)
161 RETURN XMLTYPE;
162
163 FUNCTION format_hr_address(p_hr_location_id IN NUMBER,
164 p_style_code IN VARCHAR2 DEFAULT NULL)
165 RETURN VARCHAR2;
166
167
168 FUNCTION format_hz_address(p_hz_location_id IN NUMBER,
169 p_style_code IN VARCHAR2 DEFAULT NULL)
170 RETURN VARCHAR2;
171
172
173 FUNCTION Get_Pmt_DocPayableCount(p_payment_id IN NUMBER)
174 RETURN NUMBER;
175
176 FUNCTION Get_Ins_PayerInstrAgg(p_payment_instruction_id IN NUMBER)
177 RETURN XMLTYPE;
178
179
180 FUNCTION Get_Payer(p_legal_entity_id IN NUMBER)
181 RETURN XMLTYPE;
182
183 FUNCTION Get_PayerBankAccount(p_bank_account_id IN NUMBER)
184 RETURN XMLTYPE;
185
186
187 FUNCTION Get_Payer_Denorm(p_payment_id IN NUMBER)
188 RETURN XMLTYPE;
189
190 FUNCTION Get_PayerBankAccount_Denorm(p_payment_id IN NUMBER)
191 RETURN XMLTYPE;
192
193
194 FUNCTION Get_PayerIns_Denorm(p_payment_instruction_id IN NUMBER)
195 RETURN XMLTYPE;
196
197 FUNCTION Get_PayerBankAccountIns_Denorm(p_payment_instruction_id IN NUMBER)
198 RETURN XMLTYPE;
199
200
201 FUNCTION Get_Ins_FVFieldsAgg(p_payment_instruction_id IN NUMBER)
202 RETURN XMLTYPE;
203
204 FUNCTION Get_Ins_AccountSettingsAgg(p_bep_account_id IN NUMBER)
205 RETURN XMLTYPE;
206
207 FUNCTION Get_Pmt_DocPayableAgg(p_payment_id IN NUMBER)
208 RETURN XMLTYPE;
209
210 FUNCTION Get_Payee(p_payment_id IN NUMBER)
211 RETURN XMLTYPE;
212
213
214 --Overloaded Function
215 FUNCTION Get_Payee(p_payment_id IN NUMBER, p_pmt_func IN VARCHAR2)
216 RETURN XMLTYPE;
217
218 /* TPP - Start */
219 FUNCTION Get_InvPayee(p_payment_id IN NUMBER)
220 RETURN XMLTYPE;
221
222 FUNCTION get_rel_add_info(
223 payee_party_id IN NUMBER,
224 supplier_site_id IN NUMBER,
225 inv_payee_party_id IN NUMBER,
226 inv_supplier_site_id IN NUMBER)
227 RETURN VARCHAR2;
228
229 FUNCTION get_relship_id(
230 payee_party_id IN NUMBER,
231 supplier_site_id IN NUMBER,
232 inv_payee_party_id IN NUMBER,
233 inv_supplier_site_id IN NUMBER)
234 RETURN NUMBER;
235 /* TPP - End */
236
237 FUNCTION Get_PayeeBankAccount(p_payment_id IN NUMBER, p_external_bank_account_id IN NUMBER)
238 RETURN XMLTYPE;
239
240 FUNCTION Get_PayeeBankAccount_Denorm(p_payment_id IN NUMBER, p_external_bank_account_id IN NUMBER)
241 RETURN XMLTYPE;
242
243 FUNCTION Get_PayeeBankAccount_Denorm(p_payment_id IN NUMBER
244 , p_external_bank_account_id IN NUMBER
245 ,p_pmt_func IN VARCHAR2)
246 RETURN XMLTYPE;
247
248 FUNCTION Get_Doc_DocLineAgg(p_document_payable_id IN NUMBER)
249 RETURN XMLTYPE;
250 -- Overloaded function Citi Perf issues.
251 FUNCTION Get_Doc_DocLineAgg(p_document_payable_id IN NUMBER,
252 p_call_app_doc_unique_ref2 IN ap_invoices_all.invoice_id%TYPE,
253 p_doc_currency_code IN iby_docs_payable_all.document_currency_code%TYPE,
254 p_calling_app_id IN iby_docs_payable_all.calling_app_id%TYPE)
255 RETURN XMLTYPE;
256 --Overloaded function Citi perf
257
258 FUNCTION Get_SRA_Attribute(p_payment_id IN NUMBER, p_attribute_type IN NUMBER)
259 RETURN VARCHAR2;
260
261 FUNCTION get_FEIN(payment_instruction_id IN NUMBER)
262 RETURN VARCHAR2;
263
264 FUNCTION get_Abbreviated_Agency_Code(payment_instruction_id IN NUMBER)
265 RETURN VARCHAR2;
266
267 FUNCTION get_Allotment_Code(payment_id IN NUMBER)
268 RETURN VARCHAR2;
269
270 FUNCTION TOP_Offset_Eligibility_Flag(payment_id IN NUMBER)
271 RETURN VARCHAR2;
272
273 FUNCTION get_SPS_PMT_TS(payment_id IN NUMBER)
274 RETURN VARCHAR2;
275
276
277 FUNCTION Get_Bordero_Bank_Ref(p_doc_payable_id IN NUMBER)
278 RETURN VARCHAR2;
279
280 FUNCTION Get_Bordero_Int_Amt(p_doc_payable_id IN NUMBER)
281 RETURN Number;
282
283 FUNCTION Get_Bordero_Abatement(p_doc_payable_id IN NUMBER)
284 RETURN Number;
285
286
287 FUNCTION Get_Payment_Amount_Text(payment_id IN NUMBER)
288 RETURN VARCHAR2;
289
290 FUNCTION Get_Payment_Amount_Withheld(payment_id IN NUMBER)
291 RETURN NUMBER;
292
293
294 -- Payment process request extract functions
295 FUNCTION Get_Ppr_PmtAgg(p_payment_service_request_id IN NUMBER)
296 RETURN XMLTYPE;
297
298 FUNCTION Get_Ppr_PmtCount(p_payment_service_request_id IN NUMBER)
299 RETURN NUMBER;
300
301 FUNCTION Get_Ppr_PreBuildDocAgg(p_payment_service_request_id IN NUMBER)
302 RETURN XMLTYPE;
303
304 FUNCTION Get_Ppr_PreBuildDocCount(p_payment_service_request_id IN NUMBER)
305 RETURN NUMBER;
306
307 FUNCTION Get_Pmt_PmtErrAgg(p_payment_id IN NUMBER)
308 RETURN XMLTYPE;
309
310 FUNCTION Get_Doc_DocErrAgg(p_document_payable_id IN NUMBER)
311 RETURN XMLTYPE;
312
313 PROCEDURE Update_Pmt_SRA_Attr_Prt
314 (
315 p_payment_instruction_id IN NUMBER
316 );
317
318 PROCEDURE Update_Pmt_SRA_Attr_Ele
319 (
320 p_payment_id IN NUMBER,
321 p_delivery_method IN VARCHAR2,
322 p_recipient_email IN VARCHAR2,
323 p_recipient_fax IN VARCHAR2
324 );
325
326 PROCEDURE initialize;
327 FUNCTION Get_Hz_Address(p_location_id IN NUMBER)
328 RETURN XMLTYPE;
329
330 FUNCTION Get_Account_Address(p_location_id IN NUMBER, p_country IN VARCHAR2)
331 RETURN XMLTYPE;
332
333 FUNCTION Get_Hr_Address(p_location_id IN NUMBER)
334 RETURN XMLTYPE;
335
336 FUNCTION Get_Ins_TotalAmt(p_payment_instruction_id IN NUMBER)
337 RETURN NUMBER;
338
339 FUNCTION Get_Expense_Rpt_CC_Num(p_document_payable_id IN NUMBER)
340 RETURN VARCHAR2;
341
342 FUNCTION Get_CC_Num_For_Issuer_Pmt_Agg(p_document_payable_id IN NUMBER)
343 RETURN XMLTYPE;
344
345 FUNCTION Replace_Special_Characters(p_base_string IN varchar2)
346 RETURN VARCHAR2;
347
348 /* Bug 9266772*/
349 FUNCTION Get_Intermediary_Bank_Accts(p_bank_acct_id IN NUMBER)
350 RETURN XMLTYPE;
351 /* Bug 9266772*/
352
353 /*Bug 11923557 */
354 FUNCTION get_PayeeBankAccountAssignment(p_pmt_id IN Number,p_ext_bank_acct_id IN Number)
355 RETURN XMLTYPE;
356 /*Bug 11923557 */
357 END IBY_FD_EXTRACT_GEN_PVT;
358
359
[Home] [Help]