1 package body IGI_EXP_BALANCE AS
2 -- $Header: igistpeb.pls 120.5 2008/02/08 11:53:17 dvjoshi ship $
3 /* P_MODE = T for Transmission Unit Level or manual batch payment
4 D for Dialogue Unit Level
5 N = for manual payment without batch
6 */
7
8 FUNCTION AR_BALANCE_WARNING (P_MODE IN VARCHAR2,
9 P_CHECKRUN_NAME IN VARCHAR2,
10 P_TRANSMISSION_UNIT_ID IN NUMBER,
11 P_DIALOGUE_UNIT_ID IN NUMBER,
12 P_CUSTOMER_ID IN NUMBER,
13 p_CUSTOMER_NAME OUT NOCOPY VARCHAR2)
14 return BOOLEAN is
15
16 l_ar_amount NUMBER;
17 l_customer_id NUMBER;
18 l_customer_name VARCHAR2(50);
19
20 CURSOR trans_unit_customer IS
21 SELECT distinct third_party_id
22 FROM igi_exp_dial_unit_def
23 WHERE trans_unit_id = p_transmission_unit_id;
24
25
26 CURSOR dial_unit_customer IS
27 SELECT third_party_id
28 FROM igi_exp_dial_unit_def
29 WHERE dial_unit_id = p_dialogue_unit_id;
30
31 CURSOR ap_pay_bat_sel_inv_customer IS
32 SELECT distinct asic.vendor_id
33 FROM ap_selected_invoice_checks asic
34 WHERE asic.checkrun_name = p_checkrun_name;
35
36 CURSOR ar_balance IS
37 SELECT NVL(SUM(aps.amount_due_remaining),0)
38 FROM ra_customer_trx rct,
39 ar_payment_schedules aps
40 WHERE aps.customer_trx_id = rct.customer_trx_id
41 AND rct.bill_to_customer_id = l_customer_id
42 AND aps.class = 'INV'
43 AND aps.due_date <= trunc(sysdate);
44
45 CURSOR ar_customer_name IS
46 SELECT PARTY_NAME
47 FROM HZ_CUST_ACCOUNTS acct,HZ_PARTIES party
48 WHERE acct.PARTY_ID=party.PARTY_ID
49 AND acct.cust_account_id = l_customer_id;
50
51 /*
52 SELECT rc.customer_name
53 FROM ra_customers rc
54 WHERE rc.customer_id = l_customer_id;
55 */
56 BEGIN
57
58 l_ar_amount := 0;
59
60 -- Transmission unit payment through Workflow --
61 IF (p_transmission_unit_id is not null)
62 AND (p_mode = 'T')
63 THEN
64 OPEN trans_unit_customer;
65 LOOP
66 FETCH trans_unit_customer INTO l_customer_id;
67 EXIT WHEN trans_unit_customer%NOTFOUND;
68 OPEN ar_balance;
69 FETCH ar_balance INTO l_ar_amount;
70 EXIT WHEN l_ar_amount <> 0;
71 CLOSE ar_balance;
72 END LOOP;
73 CLOSE trans_unit_customer;
74
75 ELSE
76 -- Manual Mode for payment batches --
77 IF (p_checkrun_name is not null)
78 AND (p_transmission_unit_id is null)
79 AND (p_mode = 'T')
80 THEN
81 OPEN ap_pay_bat_sel_inv_customer;
82 FETCH ap_pay_bat_sel_inv_customer INTO l_customer_id;
83 OPEN ar_balance;
84 FETCH ar_balance INTO l_ar_amount;
85 CLOSE ar_balance;
86 CLOSE ap_pay_bat_sel_inv_customer;
87 ELSE
88 -- Dialogue unit payment --
89 IF (p_dialogue_unit_id is not null)
90 AND (p_mode = 'D')
91 THEN
92 OPEN dial_unit_customer;
93 FETCH dial_unit_customer INTO l_customer_id;
94 OPEN ar_balance;
95 FETCH ar_balance INTO l_ar_amount;
96 CLOSE ar_balance;
97 CLOSE dial_unit_customer;
98 ELSE
99 -- Manual Mode for payment without batch --
100 IF (p_customer_id is null)
101 AND (p_mode = 'N')
102 THEN
103 l_customer_id := p_customer_id;
104 OPEN ar_balance;
105 FETCH ar_balance INTO l_ar_amount;
106 CLOSE ar_balance;
107 END IF;
108 END IF;
109 END IF;
110 END IF;
111
112 IF l_ar_amount = 0
113 THEN
114 OPEN ar_customer_name;
115 FETCH ar_customer_name INTO l_customer_name;
116 CLOSE ar_customer_name;
117 p_customer_name := l_customer_name;
118 return TRUE;
119 ELSE
120 return FALSE;
121 END IF;
122
123 END AR_BALANCE_WARNING;
124
125
126 END IGI_EXP_BALANCE;
[Home] [Help]