1 PACKAGE IEX_DUNNING_PUB AUTHID CURRENT_USER AS
2 /* $Header: iexpduns.pls 120.18.12020000.2 2013/02/15 13:06:27 snuthala ship $ */
3
4
5 -- clchang added new column XDO_TEMPLATE_ID for 11.5.11
6
7 TYPE AG_DN_XREF_REC_TYPE IS RECORD (
8 AG_DN_XREF_ID NUMBER ,
9 aging_bucket_id NUMBER ,
10 aging_bucket_line_id NUMBER ,
11 callback_flag VARCHAR2(1) ,
12 callback_days NUMBER ,
13 FM_METHOD VARCHAR2(10) ,
14 template_id NUMBER ,
15 xdo_template_id NUMBER ,
16 score_RANGE_LOW NUMBER ,
17 score_RANGE_HIGH NUMBER ,
18 DUNNING_LEVEL VARCHAR2(30) ,
19 OBJECT_VERSION_NUMBER NUMBER ,
20 LAST_UPDATE_DATE DATE ,
21 LAST_UPDATED_BY NUMBER ,
22 CREATION_DATE DATE ,
23 CREATED_BY NUMBER ,
24 LAST_UPDATE_LOGIN NUMBER );
25
26 TYPE AG_DN_XREF_TBL_TYPE is Table of AG_DN_XREF_REC_TYPE
27 index by binary_integer;
28
29
30 TYPE AG_DN_XREF_ID_TBL_TYPE is Table of NUMBER index by binary_integer;
31
32 G_MISS_AG_DN_XREF_REC IEX_DUNNING_PUB.AG_DN_XREF_REC_TYPE;
33 G_MISS_AG_DN_XREF_TBL IEX_DUNNING_PUB.AG_DN_XREF_TBL_TYPE;
34 G_MISS_AG_DN_XREF_ID_TBL IEX_DUNNING_PUB.AG_DN_XREF_ID_TBL_TYPE;
35
36
37 -- clchang added new column XML_REQUEST_ID, XML_TEMPLATE_ID for 11.5.11
38 TYPE DUNNING_REC_TYPE IS RECORD (
39 DUNNING_ID NUMBER ,
40 TEMPLATE_ID NUMBER ,
41 callback_yn VARCHAR2(1) ,
42 callback_date DATE ,
43 STATUS VARCHAR2(240) ,
44 CAMPAIGN_SCHED_ID NUMBER ,
45 DELINQUENCY_ID NUMBER ,
46 FFM_REQUEST_ID NUMBER ,
47 XML_REQUEST_ID NUMBER ,
48 XML_TEMPLATE_ID NUMBER ,
49 OBJECT_ID NUMBER ,
50 OBJECT_TYPE VARCHAR2(30) ,
51 DUNNING_LEVEL VARCHAR2(30) ,
52 DUNNING_OBJECT_ID NUMBER ,
53 DUNNING_METHOD VARCHAR2(30) ,
54 AMOUNT_DUE_REMAINING NUMBER ,
55 CURRENCY_CODE VARCHAR2(15) ,
56 LAST_UPDATE_DATE DATE ,
57 LAST_UPDATED_BY NUMBER ,
58 CREATION_DATE DATE ,
59 CREATED_BY NUMBER ,
60 LAST_UPDATE_LOGIN NUMBER ,
61 FINANCIAL_CHARGE NUMBER ,
62 LETTER_NAME VARCHAR2(30) ,
63 INTEREST_AMT NUMBER ,
64 dunning_plan_id number ,
65 contact_destination varchar2(240) ,
66 contact_party_id number ,
67 REQUEST_ID NUMBER , -- added by gnramasa for bug 5661324 14-Mar-07
68 DELIVERY_STATUS VARCHAR2(30) , -- added by gnramasa for bug 5661324 14-Mar-07
69 PARENT_DUNNING_ID NUMBER , -- added by gnramasa for bug 5661324 14-Mar-07
70 DUNNING_MODE VARCHAR2(10) , -- added by gnramasa for bug 8489610 14-May-09
71 CONFIRMATION_MODE VARCHAR2(10) , -- added by gnramasa for bug 8489610 14-May-09
72 org_id number , -- added for bug 9151851
73 AG_DN_XREF_ID NUMBER , -- added by gnramasa for bug 9326376 2-Feb-10
74 CORRESPONDENCE_DATE DATE , -- added by gnramasa for bug 9326376 2-Feb-10
75 ADDT_XML_TEMPLATE_ID NUMBER , -- added by gnramasa for bug 9970624 4-Aug-10
76 ADDT_DELIVERY_STATUS VARCHAR2(30) , -- added by gnramasa for bug 9970624 4-Aug-10
77 AS_OF_DATE DATE
78 );
79
80 G_MISS_DUNNING_REC IEX_DUNNING_PUB.DUNNING_REC_TYPE;
81
82 TYPE DUNNING_TBL_TYPE is Table of DUNNING_REC_TYPE
83 index by binary_integer;
84
85 TYPE DelId_NumList is Table of Number;
86
87
88 Procedure Create_AG_DN_XREF
89 (p_api_version IN NUMBER := 1.0,
90 p_init_msg_list IN VARCHAR2 ,
91 p_commit IN VARCHAR2 ,
92 P_AG_DN_XREF_TBL IN IEX_DUNNING_PUB.AG_DN_XREF_TBL_TYPE ,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_msg_count OUT NOCOPY NUMBER,
95 x_msg_data OUT NOCOPY VARCHAR2,
96 x_AG_DN_XREF_ID_TBL OUT NOCOPY IEX_DUNNING_PUB.AG_DN_XREF_ID_TBL_TYPE);
97
98
99 Procedure Update_AG_DN_XREF
100 (p_api_version IN NUMBER := 1.0,
101 p_init_msg_list IN VARCHAR2 ,
102 p_commit IN VARCHAR2 ,
103 P_AG_DN_XREF_TBL IN IEX_DUNNING_PUB.AG_DN_XREF_TBL_TYPE ,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_msg_data OUT NOCOPY VARCHAR2);
107
108
109
110 Procedure Delete_AG_DN_XREF
111 (p_api_version IN NUMBER := 1.0,
112 p_init_msg_list IN VARCHAR2 ,
113 p_commit IN VARCHAR2 ,
114 P_AG_DN_XREF_ID IN NUMBER,
115 x_return_status OUT NOCOPY VARCHAR2,
116 x_msg_count OUT NOCOPY NUMBER,
117 x_msg_data OUT NOCOPY VARCHAR2);
118
119
120
121
122 Procedure Send_Dunning
123 (p_api_version IN NUMBER := 1.0,
124 p_init_msg_list IN VARCHAR2 ,
125 p_commit IN VARCHAR2 ,
126 p_running_level IN VARCHAR2,
127 p_parent_request_id IN NUMBER, -- added by gnramasa for bug 5661324 14-Mar-07
128 p_dunning_plan_id in number,
129 p_correspondence_date IN DATE, -- added by gnramasa for bug 9326376 2-Feb-10
130 p_dunning_mode IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
131 p_process_err_rec_only IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
132 p_no_of_workers IN number := 1, -- added by gnramasa for bug 8489610 14-May-09
133 p_single_staged_letter IN VARCHAR2 DEFAULT 'N', -- added by gnramasa for bug 9326376 2-Feb-10
134 p_customer_name_low IN VARCHAR2,
135 p_customer_name_high IN VARCHAR2,
136 p_account_number_low IN VARCHAR2,
137 p_account_number_high IN VARCHAR2,
138 p_billto_location_low IN VARCHAR2,
139 p_billto_location_high IN VARCHAR2,
140 p_order_output_by IN VARCHAR2,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2);
144
145
146
147 /*=====================================================================
148 clchang updated 10/02/2002 - no CloseDunning in 115.9
149
150 PROCEDURE Close_Dunning
151 (p_api_version IN NUMBER,
152 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
153 p_commit IN VARCHAR2 := FND_API.G_FALSE,
154 --p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE
155 -- := IEX_DELINQUENCY_PUB.G_MISS_DELINQUENCY_TBL,
156 p_delinquencies_tbl IN DelId_NumList,
157 p_security_check IN VARCHAR2,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2);
161 *======================================================================*/
162
163
164 Procedure Daily_Dunning
165 (p_api_version IN NUMBER :=1.0,
166 p_init_msg_list IN VARCHAR2 ,
167 p_commit IN VARCHAR2 ,
168 --p_dunning_tbl IN IEX_DUNNING_PUB.DUNNING_TBL_TYPE,
169 p_running_level IN VARCHAR2,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER,
172 x_msg_data OUT NOCOPY VARCHAR2);
173
174
175 PROCEDURE CALLBACK_CONCUR(
176 ERRBUF OUT NOCOPY VARCHAR2,
177 RETCODE OUT NOCOPY VARCHAR2,
178 P_ORG_ID IN NUMBER DEFAULT NULL); --Added for MOAC
179
180
181 PROCEDURE SEND_DUNNING_CONCUR(
182 ERRBUF OUT NOCOPY VARCHAR2,
183 RETCODE OUT NOCOPY VARCHAR2,
184 dunning_plan_id NUMBER,
185 p_staged_dunning_dummy IN VARCHAR2, -- added by gnramasa for bug 9326376 2-Feb-10
186 p_correspondence_date IN VARCHAR2, -- added by gnramasa for bug 9326376 2-Feb-10
187 p_parent_request_id IN NUMBER, -- added by gnramasa for bug 5661324 14-Mar-07
188 p_dunning_mode IN VARCHAR2 DEFAULT 'FINAL', -- added by gnramasa for bug 8489610 14-May-09
189 p_process_err_dummy IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
190 p_process_err_rec_only IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
191 p_no_of_workers IN number := 1, -- added by gnramasa for bug 8489610 14-May-09
192 p_single_staged_letter IN VARCHAR2 DEFAULT 'N', -- added by gnramasa for bug 9326376 2-Feb-10
193 p_coll_bus_level_dummy IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
194 p_customer_name_low IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
195 p_customer_name_high IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
196 --p_account_number_dummy IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
197 p_account_number_low IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
198 p_account_number_high IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
199 p_billto_location_dummy IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
200 p_billto_location_low IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
201 p_billto_location_high IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
202 p_order_output_by IN VARCHAR2);
203
204 --Added for bug 9582646 gnramasa 5th May 10
205 PROCEDURE STG_DUNNING_MIG_CONCUR(
206 ERRBUF OUT NOCOPY VARCHAR2,
207 RETCODE OUT NOCOPY VARCHAR2,
208 p_migration_mode IN VARCHAR2 DEFAULT 'FINAL');
209
210
211 END IEX_DUNNING_PUB;