1 PACKAGE BODY ARP_CR_UTIL AS
2 /*$Header: ARRUTILB.pls 120.4 2003/11/04 20:43:40 orashid ship $*/
3 --
4 --
5 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7 PROCEDURE get_dist_ccid( P_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
8 P_source_table IN ar_distributions.source_table%TYPE,
9 P_source_type IN ar_distributions.source_type%TYPE,
10 P_rma_rec IN ar_receipt_method_accounts%ROWTYPE,
11 P_ccid OUT NOCOPY ar_distributions.code_combination_id%TYPE)
12 IS
13 l_ccid ar_distributions.code_combination_id%TYPE;
14 --
15 CURSOR ar_get_crh_ccid_C IS
16 SELECT dist.code_combination_id ccid
17 FROM ar_distributions dist,
18 ar_cash_receipt_history crh
19 WHERE dist.source_table = 'CRH'
20 AND dist.source_id = crh.cash_receipt_history_id
21 AND dist.source_type = P_source_type
22 AND crh.cash_receipt_id = P_cr_id
23 ORDER BY LINE_ID desc;
24 --
25 BEGIN
26 --
27 IF PG_DEBUG in ('Y', 'C') THEN
28 arp_standard.debug( '<<<<<<< arp_cr_util.get_dist_ccid' );
29 END IF;
30 --
31 OPEN ar_get_crh_ccid_C;
32 FETCH ar_get_crh_ccid_C INTO l_ccid;
33 CLOSE ar_get_crh_ccid_C;
34
35 IF ( P_rma_rec.receipt_method_id IS NOT NULL ) AND
36 ( l_ccid IS NULL )
37 THEN
38 IF ( P_source_type = 'CASH' )
39 THEN
40 l_ccid := P_rma_rec.cash_ccid;
41 ELSE
42 IF P_source_type = 'BANK_CHARGES'
43 THEN
44 l_ccid := P_rma_rec.bank_charges_ccid;
45 ELSE
46 IF P_source_type = 'REMITTANCE'
47 THEN
48 l_ccid := P_rma_rec.remittance_ccid;
49 ELSE
50 IF P_source_type = 'SHORT_TERM_DEBT'
51 THEN
52 l_ccid := P_rma_rec.short_term_debt_ccid;
53 ELSE
54 IF P_source_type = 'FACTOR'
55 THEN
56 l_ccid := P_rma_rec.factor_ccid;
57 ELSE
58 IF P_source_type = 'CONFIRMATION'
59 THEN
60 l_ccid := P_rma_rec.receipt_clearing_ccid;
61 END IF;
62 END IF;
63 END IF;
64 END IF;
65 END IF;
66 END IF;
67 END IF;
68 --
69 P_ccid := l_ccid;
70 --
71 IF PG_DEBUG in ('Y', 'C') THEN
72 arp_standard.debug( '<<<<<<< arp_cr_util.get_dist_ccid' );
73 END IF;
74 --
75 EXCEPTION
76 WHEN OTHERS THEN
77 IF PG_DEBUG in ('Y', 'C') THEN
78 arp_standard.debug( 'EXCEPTION arp_cr_util.get_dist_ccid' );
79 END IF;
80 RAISE;
81 END get_dist_ccid;
82 --
83 --
84 --
85 PROCEDURE get_creation_info( P_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
86 P_remit_bank_account_id IN ar_cash_receipts.remit_bank_acct_use_id%type,
87 P_history_status OUT NOCOPY ar_cash_receipt_history.status%TYPE,
88 P_source_type OUT NOCOPY ar_distributions.source_type%TYPE,
89 P_ccid OUT NOCOPY ar_distributions.code_combination_id%TYPE,
90 P_override_remit_account_flag OUT NOCOPY ar_cash_receipts.override_remit_account_flag%TYPE) IS
91 l_history_status ar_cash_receipt_history.status%TYPE;
92 l_source_type ar_distributions.source_type%TYPE;
93 l_ccid ar_distributions.code_combination_id%TYPE;
94 l_override_remit_account_flag ar_cash_receipts.override_remit_account_flag%TYPE;
95 --
96 BEGIN
97 --
98 IF PG_DEBUG in ('Y', 'C') THEN
99 arp_standard.debug( '<<<<<<< arp_cr_util.get_creation_info' );
100 END IF;
101 --
102 SELECT rc.creation_status,
103 decode( rc.creation_status,
104 'APPROVED', null,
105 'CONFIRMED','CONFIRMATION',
106 'REMITTED', 'REMITTANCE',
107 'CLEARED', 'CASH' ),
108 decode(rc.creation_status,
109 'APPROVED',null,
110 'CONFIRMED', rma.receipt_clearing_ccid,
111 'REMITTED', rma.remittance_ccid,
112 'CLEARED', rma.cash_ccid),
113 nvl(rma.override_remit_account_flag,'Y')
114 INTO l_history_status,
115 l_source_type,
116 l_ccid,
117 l_override_remit_account_flag
118 FROM ar_receipt_classes rc,
119 ar_receipt_methods rm,
120 ar_receipt_method_accounts rma
121 WHERE rm.receipt_class_id = rc.receipt_class_id
122 AND rm.receipt_method_id = p_receipt_method_id
123 AND rma.receipt_method_id = rm.receipt_method_id
124 AND rma.remit_bank_acct_use_id = P_remit_bank_account_id;
125 --
126 P_history_status := l_history_status;
127 P_source_type := l_source_type;
128 P_ccid := l_ccid;
129 P_override_remit_account_flag := l_override_remit_account_flag;
130 --
131 IF PG_DEBUG in ('Y', 'C') THEN
132 arp_standard.debug( '<<<<<<< arp_cr_util.get_creation_info' );
133 END IF;
134 --
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 IF PG_DEBUG in ('Y', 'C') THEN
139 arp_standard.debug( 'EXCEPTION arp_cr_util.get_creation_info' );
140 END IF;
141 RAISE;
142 END get_creation_info;
143 --
144
145
146 PROCEDURE get_batch_id( p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
147 p_batch_id OUT NOCOPY ar_batches.batch_id%TYPE) IS
148 --
149 BEGIN
150 --
151 IF PG_DEBUG in ('Y', 'C') THEN
152 arp_standard.debug( 'arp_cr_util.get_batch_id()+' );
153 END IF;
154 --
155 -- We select 'max' this is because if returns NULL, we do not
156 -- want it to raise a return null exception.
157 -- Note: there's only one record per cash receipt in history table has
158 -- first_postable_flag ='Y'
159 --
160 SELECT max(crh.batch_id)
161 INTO p_batch_id
162 FROM ar_cash_receipt_history crh,
163 ar_batches bat
164 WHERE crh.cash_receipt_id = p_cr_id
165 AND crh.first_posted_record_flag = 'Y'
166 AND bat.batch_id = crh.batch_id
167 AND bat.type = 'MANUAL';
168 --
169 IF PG_DEBUG in ('Y', 'C') THEN
170 arp_standard.debug( 'arp_cr_util.get_batch_id()-' );
171 END IF;
172 --
173 EXCEPTION
174 WHEN OTHERS THEN
175 IF PG_DEBUG in ('Y', 'C') THEN
176 arp_standard.debug( 'EXCEPTION arp_cr_util.get_batch_id' );
177 END IF;
178 RAISE;
179 END get_batch_id;
180
181 END ARP_CR_UTIL;
[Home] [Help]