eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.IGI_DUNN_POST_UPG_PKG

Source


1 PACKAGE IGI_DUNN_POST_UPG_PKG as
2   -- $Header: igidunks.pls 120.9 2008/02/19 09:26:50 mbremkum ship $
3 
4    CURSOR c_bkts IS
5       SELECT distinct adls.dunning_letter_set_id dls_id,
6              adls.name
7       FROM   ar_dunning_letter_sets      adls,
8              igi_dun_letter_sets         idls
9       WHERE  idls.dunning_letter_set_id  = adls.dunning_letter_set_id
10       AND    idls.use_dunning_flag       = 'Y'
11       AND NOT EXISTS (SELECT 'Y'
12                       FROM    ar_aging_buckets aab
13                       WHERE   aab.description = adls.name);
14 
15    CURSOR c_bkts_lines (p_dls_id VARCHAR2) IS
16       SELECT adls.dunning_letter_set_id,
17              adlsl.dunning_line_num,
18              adlsl.range_of_days_from,
19              adlsl.range_of_days_to
20       FROM   ar_dunning_letter_set_lines        adlsl,
21              igi_dun_letter_sets                idls,
22              ar_dunning_letter_sets             adls
23       WHERE  adls.dunning_letter_set_id = p_dls_id
24       AND    adls.dunning_letter_set_id = idls.dunning_letter_set_id
25       AND    adlsl.dunning_letter_set_id = adls.dunning_letter_set_id;
26 
27    CURSOR c_aging_bkts_site IS
28       SELECT distinct adls.dunning_letter_set_id dls_id,
29              adls.name,
30              idclsl.currency_code ccy_code,
31 	     idls.charge_per_invoice_flag charge_type
32       FROM   igi_dun_cust_letter_set_lines    idclsl,
33              ar_dunning_letter_sets      adls,
34              igi_dun_letter_sets         idls
35       WHERE  decode(idls.charge_per_invoice_flag, 'Y', idclsl.invoice_charge_amount, 'N', idclsl.letter_charge_amount) IS NOT NULL
36       AND    idclsl.dunning_letter_set_id = adls.dunning_letter_set_id
37       AND    idls.dunning_letter_set_id  = adls.dunning_letter_set_id
38       AND    idls.use_dunning_flag       = 'Y'
39       AND NOT EXISTS (SELECT 'Y'
40                       FROM    ar_charge_schedules acs
41                       WHERE   acs.schedule_name = adls.name || '_' || idclsl.currency_code || '_' || idclsl.customer_profile_id);
42 
43    CURSOR c_aging_bkts IS
44       SELECT distinct adls.dunning_letter_set_id dls_id,
45              adls.name,
46              idlsl.currency_code ccy_code,
47 	     idls.charge_per_invoice_flag
48       FROM   igi_dun_letter_set_lines    idlsl,
49              ar_dunning_letter_sets      adls,
50              igi_dun_letter_sets         idls
51       WHERE  decode(idls.charge_per_invoice_flag, 'Y', idlsl.invoice_charge_amount, 'N', idlsl.letter_charge_amount) IS NOT NULL
52       AND    idlsl.dunning_letter_set_id = adls.dunning_letter_set_id
53       AND    idls.dunning_letter_set_id  = adls.dunning_letter_set_id
54       AND    idls.use_dunning_flag       = 'Y'
55       AND NOT EXISTS (SELECT 'Y'
56                       FROM    ar_charge_schedules acs
57                       WHERE   acs.schedule_name = adls.name || '_' || idlsl.currency_code);
58 
59 
60    CURSOR c_aging_bkt_lines (p_dls_id VARCHAR2, p_ccy_code VARCHAR2) IS
61       SELECT adls.dunning_letter_set_id,
62              adls.name,
63              idlsl.currency_code,
64              adlsl.dunning_letter_id,
65              adlsl.dunning_line_num,
66              adlsl.range_of_days_from,
67              adlsl.range_of_days_to,
68              idlsl.currency_code ccy_code,
69              idlsl.letter_charge_amount,
70              idlsl.invoice_charge_amount,
71 	     (SELECT charge_per_invoice_flag FROM igi_dun_letter_sets WHERE dunning_letter_set_id = p_dls_id) charge_type
72       FROM   ar_dunning_letter_set_lines        adlsl,
73              igi_dun_letter_set_lines           idlsl,
74              ar_dunning_letter_sets             adls
75       WHERE  adlsl.dunning_letter_set_id = idlsl.dunning_letter_set_id
76       AND    adlsl.dunning_line_num      = idlsl.dunning_line_num
77       AND    adlsl.dunning_letter_id     = idlsl.dunning_letter_id
78       AND    adlsl.dunning_letter_set_id = adls.dunning_letter_set_id
79       AND    (idlsl.letter_charge_amount IS NOT NULL OR
80               idlsl.invoice_charge_amount IS NOT NULL)
81       AND    adls.dunning_letter_set_id  = p_dls_id
82       AND    idlsl.currency_code         = p_ccy_code;
83 
84    /*Added for creating Charge Schedules where charge amounts were changed at customer level - mbremkum*/
85 
86    CURSOR c_override_dunning_letter (p_dls_id VARCHAR2, p_ccy_code VARCHAR2, p_charge_type VARCHAR2) IS
87    SELECT idlsl.dunning_letter_set_id dls_id, idlsl.currency_code ccy_code, idclsl.customer_profile_id
88 	FROM igi_dun_letter_set_lines idlsl, igi_dun_cust_letter_set_lines idclsl, igi_dun_letter_sets idls
89 	WHERE decode(p_charge_type, 'N', (nvl(idclsl.letter_charge_amount, -99) - nvl(idlsl.letter_charge_amount,-99)),
90 	 'Y', (nvl(idclsl.invoice_charge_amount,-99) - nvl(idlsl.invoice_charge_amount,-99))) <> 0
91 	 AND idls.dunning_letter_set_id = idlsl.dunning_letter_set_id
92 	 AND idls.use_dunning_flag = 'Y'
93 	 AND idls.charge_per_invoice_flag = p_charge_type
94 	 AND idlsl.dunning_letter_set_id = idclsl.dunning_letter_set_id
95 	 AND idlsl.currency_code = idclsl.currency_code
96 	 AND idlsl.dunning_letter_set_id = p_dls_id
97 	 AND idlsl.currency_code = p_ccy_code
98 	 AND idclsl.dunning_line_num = idlsl.dunning_line_num;
99 
100    CURSOR c_override_dunning_letter_uu (p_dls_id VARCHAR2, p_ccy_code VARCHAR2, p_charge_type VARCHAR2) IS
101    SELECT idlsl.dunning_letter_set_id dls_id, idlsl.currency_code ccy_code, idclsl.customer_profile_id
102 	FROM igi_dun_letter_set_lines idlsl, igi_dun_cust_letter_set_lines idclsl, igi_dun_letter_sets idls
103 	WHERE decode(p_charge_type, 'Y', (nvl(idclsl.letter_charge_amount, -99) - nvl(idlsl.letter_charge_amount,-99)),
104 	 'N', (nvl(idclsl.invoice_charge_amount,-99) - nvl(idlsl.invoice_charge_amount,-99))) <> 0
105 	 AND idls.dunning_letter_set_id = idlsl.dunning_letter_set_id
106 	 AND idlsl.dunning_letter_set_id = idclsl.dunning_letter_set_id
107 	 AND idlsl.currency_code = idclsl.currency_code
108 	 AND idls.use_dunning_flag = 'Y'
109 	 AND idls.charge_per_invoice_flag = p_charge_type
110 	 AND idlsl.dunning_letter_set_id = p_dls_id
111 	 AND idlsl.currency_code = p_ccy_code
112 	 AND idclsl.dunning_line_num = idlsl.dunning_line_num;
113 
114    CURSOR c_aging_bkt_lines_site (p_dls_id VARCHAR2, p_ccy_code VARCHAR2, p_customer_profile_id NUMBER) IS
115 	SELECT adls.dunning_letter_set_id,
116 	     adls.name,
117 	     adlsl.dunning_letter_id,
118 	     adlsl.dunning_line_num,
119 	     adlsl.range_of_days_from,
120 	     adlsl.range_of_days_to,
121 	     idclsl.currency_code ccy_code,
122 	     idclsl.letter_charge_amount,
123 	     idclsl.invoice_charge_amount,
124 	     idclsl.customer_profile_id,
125 	     idclsl.customer_profile_class_id,
126 	     idclsl.site_use_id,
127 	     (SELECT charge_per_invoice_flag FROM igi_dun_letter_sets WHERE dunning_letter_set_id = p_dls_id) charge_type
128 	FROM   ar_dunning_letter_set_lines        adlsl,
129 	     igi_dun_cust_letter_set_lines      idclsl,
130 	     ar_dunning_letter_sets             adls
131 	WHERE  adlsl.dunning_letter_set_id = adls.dunning_letter_set_id
132 	AND    adlsl.dunning_letter_set_id = idclsl.dunning_letter_set_id
133 	AND    adlsl.dunning_line_num      = idclsl.dunning_line_num
134 	AND    adlsl.dunning_letter_id     = idclsl.dunning_letter_id
135 	AND    idclsl.currency_code        = p_ccy_code
136 	AND    adls.dunning_letter_set_id = p_dls_id
137 	AND    idclsl.customer_profile_id = p_customer_profile_id
138 	ORDER BY adls.dunning_letter_set_id;
139 
140    CURSOR c_aging_bkts_uu IS
141         SELECT distinct adls.dunning_letter_set_id dls_id,
142              adls.name,
143              idlsl.currency_code ccy_code,
144              charge_per_invoice_flag charge_type
145 	FROM igi_dun_letter_set_lines           idlsl,
146              ar_dunning_letter_sets             adls,
147 	     igi_dun_letter_sets		idls
148 	WHERE decode(charge_per_invoice_flag, 'Y', letter_charge_amount, 'N', invoice_charge_amount) IS NOT NULL
149 	AND    idlsl.dunning_letter_set_id = adls.dunning_letter_set_id
150         AND    idls.dunning_letter_set_id  = adls.dunning_letter_set_id
151         AND    idls.use_dunning_flag       = 'Y'
152 	AND NOT EXISTS (SELECT 'Y'
153                       FROM    ar_charge_schedules acs
154                       WHERE   acs.schedule_name = adls.name || '_' || idlsl.currency_code || '_' || decode(idls.charge_per_invoice_flag, 'Y', 'PER_LETTER', 'N', 'PER_INVOICE'));
155 
156 
157    CURSOR c_aging_bkts_uu_site IS
158         SELECT distinct adls.dunning_letter_set_id dls_id,
159              adls.name,
160              idlsl.currency_code ccy_code,
161              charge_per_invoice_flag charge_type
162 	FROM igi_dun_cust_letter_set_lines           idlsl,
163              ar_dunning_letter_sets             adls,
164 	     igi_dun_letter_sets		idls
165 	WHERE decode(charge_per_invoice_flag, 'Y', letter_charge_amount, 'N', invoice_charge_amount) IS NOT NULL
166 	AND    idlsl.dunning_letter_set_id = adls.dunning_letter_set_id
167         AND    idls.dunning_letter_set_id  = adls.dunning_letter_set_id
168         AND    idls.use_dunning_flag       = 'Y'
169 	AND NOT EXISTS (SELECT 'Y'
170                       FROM    ar_charge_schedules acs
171                       WHERE   acs.schedule_name = adls.name || '_' || idlsl.currency_code || '_'|| idlsl.customer_profile_id  || '_' || decode(idls.charge_per_invoice_flag, 'Y', 'PER_LETTER', 'N', 'PER_INVOICE'));
172 
173 
174 	l_aging_bucket_line_id ar_aging_bucket_lines_b.aging_bucket_line_id%TYPE;
175 	l_schedule_header_id   ar_charge_schedule_hdrs.schedule_id%TYPE;
176 	l_aging_bucket_id      ar_aging_buckets.aging_bucket_id%TYPE;
177 	l_schedule_id          ar_charge_schedules.schedule_id%TYPE;
178 
179 
180 
181 PROCEDURE DUNNING_UPG(ERRBUF OUT NOCOPY VARCHAR2,
182 		      RETCODE OUT NOCOPY  VARCHAR2);
183 
184   END IGI_DUNN_POST_UPG_PKG;