[Home] [Help]
PACKAGE BODY: APPS.PN_EXP_PAYMENT_ITEMS_PKG
Source
1 PACKAGE BODY PN_EXP_PAYMENT_ITEMS_PKG AS
2 -- $Header: PNTEXPIB.pls 120.3 2006/08/11 11:41:34 sdmahesh ship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : lock_row
6 -- INVOKED FROM : lock_row procedure
7 -- PURPOSE : locks the row
8 -- HISTORY :
9 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_PAYMENT_ITEMS,PN_PAYMENT_SCHEDULES
10 -- with _ALL table.
11 -------------------------------------------------------------------------------
12 PROCEDURE LOCK_ROW (
13 X_PAYMENT_ITEM_ID IN NUMBER,
14 X_EXPORT_TO_AP_FLAG IN VARCHAR2,
15 X_EXPORT_TO_AR_FLAG IN VARCHAR2,
16 X_EXPORT_CURRENCY_CODE IN VARCHAR2,
17 X_RATE IN NUMBER,
18 X_PAYMENT_SCHEDULE_ID IN NUMBER,
19 X_PERIOD_NAME IN VARCHAR2,
20 X_DUE_DATE IN DATE,
21 X_AP_INVOICE_NUM IN VARCHAR2,
22 X_GROUPING_RULE_ID IN NUMBER
23 ) IS
24
25 CURSOR c1 IS
26 SELECT EXPORT_TO_AP_FLAG,
27 EXPORT_TO_AR_FLAG,
28 EXPORT_CURRENCY_CODE,
29 RATE,
30 DUE_DATE,
31 AP_INVOICE_NUM,
32 GROUPING_RULE_ID
33 FROM PN_PAYMENT_ITEMS_ALL
34 WHERE PAYMENT_ITEM_ID = X_PAYMENT_ITEM_ID
35 FOR UPDATE OF PAYMENT_ITEM_ID NOWAIT;
36
37 CURSOR c2 IS
38 SELECT PERIOD_NAME
39 FROM PN_PAYMENT_SCHEDULES_ALL
40 WHERE PAYMENT_SCHEDULE_ID = X_PAYMENT_SCHEDULE_ID
41 FOR UPDATE OF PAYMENT_SCHEDULE_ID NOWAIT;
42
43 tlinfo c1%ROWTYPE;
44 tlinfo2 c2%ROWTYPE;
45
46 BEGIN
47 OPEN c1;
48 FETCH c1 INTO tlinfo;
49 IF (c1%NOTFOUND) THEN
50 CLOSE c1;
51 RETURN;
52 END IF;
53 CLOSE c1;
54
55 OPEN c2;
56 FETCH c2 INTO tlinfo2;
57 IF (c2%NOTFOUND) THEN
58 CLOSE c2;
59 RETURN;
60 END IF;
61 CLOSE c2;
62
63 IF (((tlinfo.EXPORT_TO_AP_FLAG = X_EXPORT_TO_AP_FLAG)
64 OR ((tlinfo.EXPORT_TO_AP_FLAG IS NULL)
65 AND (X_EXPORT_TO_AP_FLAG IS NULL)))
66 AND ((tlinfo.EXPORT_TO_AR_FLAG = X_EXPORT_TO_AR_FLAG)
67 OR ((tlinfo.EXPORT_TO_AR_FLAG IS NULL)
68 AND (X_EXPORT_TO_AR_FLAG IS NULL)))
69 AND ((tlinfo.EXPORT_CURRENCY_CODE = X_EXPORT_CURRENCY_CODE)
70 OR ((tlinfo.EXPORT_CURRENCY_CODE IS NULL)
71 AND (X_EXPORT_CURRENCY_CODE IS NULL)))
72 AND ((tlinfo.RATE = X_RATE)
73 OR ((tlinfo.RATE IS NULL)
74 AND (X_RATE IS NULL)))
75 AND ((tlinfo2.PERIOD_NAME = X_PERIOD_NAME)
76 OR ((tlinfo2.PERIOD_NAME IS NULL)
77 AND (X_PERIOD_NAME IS NULL)))
78 AND ((tlinfo.DUE_DATE = X_DUE_DATE)
79 OR ((tlinfo.DUE_DATE IS NULL)
80 AND (X_DUE_DATE IS NULL)))
81 AND ((tlinfo.AP_INVOICE_NUM = X_AP_INVOICE_NUM)
82 OR ((tlinfo.AP_INVOICE_NUM IS NULL)
83 AND (X_AP_INVOICE_NUM IS NULL)))
84 AND ((tlinfo.GROUPING_RULE_ID = X_GROUPING_RULE_ID)
85 OR ((tlinfo.GROUPING_RULE_ID IS NULL)
86 AND (X_GROUPING_RULE_ID IS NULL)))
87 ) THEN
88 NULL;
89 ELSE
90 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
91 app_exception.raISe_exception;
92 END IF;
93
94 RETURN;
95 END LOCK_ROW;
96
97 --------------------------------------------------------------------------------
98 -- PROCEDURE: UPDATE ROW
99 -- PURPOSE: row handler for pn_payment_item from the export to AP/AR perspective
100 -- INVOKED: from table_handler program unit in PNTXPPMT.fmb
101 -- HISTORY:
102 -- 28-MAR-02 ftanudja o added parameters x_norm_itm_id, x_norm_exp_amt,
103 -- x_export_amount. updated table with new value for
104 -- normalized items
105 -- 10-DEC-03 atuppad o Added code for the updating of 3 columns: DUE_DATE,
106 -- GROUPING_RULE_ID and AP_INVOICE_NUM
107 -- 15-JUL-05 hareesha o Bug 4284035 - Replaced PN_PAYMENT_ITEMS with _ALL table
108 -- 24-NOV-05 Kiran o round amount before insert/update into term/item
109 -- 10-AUG-06 sdmahesh o Bug 5283912
110 -- Updated accounted_amount in pn_payment_items_all as
111 -- actual_amount * x_rate
112 --------------------------------------------------------------------------------
113
114 PROCEDURE UPDATE_ROW (
115 X_PAYMENT_ITEM_ID IN NUMBER,
116 X_EXPORT_TO_AP_FLAG IN VARCHAR2,
117 X_EXPORT_TO_AR_FLAG IN VARCHAR2,
118 X_EXPORT_AMOUNT IN NUMBER,
119 X_NORM_ITM_ID IN NUMBER,
120 X_NORM_EXP_AMT IN NUMBER,
121 X_EXPORT_CURRENCY_CODE IN VARCHAR2,
122 X_RATE IN NUMBER,
123 X_LAST_UPDATE_DATE IN DATE,
124 X_LAST_UPDATED_BY IN NUMBER,
125 X_LAST_UPDATE_LOGIN IN NUMBER,
126 X_PAYMENT_SCHEDULE_ID IN NUMBER,
127 X_PERIOD_NAME IN VARCHAR2,
128 X_DUE_DATE IN DATE,
129 X_AP_INVOICE_NUM IN VARCHAR2,
130 X_GROUPING_RULE_ID IN NUMBER
131 )
132 IS
133 l_info_text VARCHAR2(100);
134 l_precision NUMBER;
135 l_ext_precision NUMBER;
136 l_min_acct_unit NUMBER;
137 l_export_amt NUMBER;
138 l_norm_export_amt NUMBER;
139
140 BEGIN
141
142 IF x_export_currency_code IS NOT NULL THEN
143 fnd_currency.get_info( currency_code => x_export_currency_code
144 ,precision => l_precision
145 ,ext_precision => l_ext_precision
146 ,min_acct_unit => l_min_acct_unit);
147 END IF;
148
149 IF l_precision IS NOT NULL THEN
150 l_export_amt := ROUND(x_export_amount, l_precision);
151 l_norm_export_amt := ROUND(x_norm_exp_amt, l_precision);
152 ELSE
153 l_export_amt := x_export_amount;
154 l_norm_export_amt := x_norm_exp_amt;
155 END IF;
156
157 l_info_text := 'updating normalized item amount';
158 IF X_NORM_ITM_ID IS NOT NULL THEN
159 UPDATE PN_PAYMENT_ITEMS_ALL
160 SET export_currency_amount = l_norm_export_amt,
161 export_currency_code = x_export_currency_code,
162 rate = x_rate,
163 accounted_amount = actual_amount * x_rate,
164 last_update_date = x_last_update_date,
165 last_updated_by = x_last_updated_by,
166 last_update_login = x_last_update_login
167 WHERE payment_item_id = x_norm_itm_id;
168 END IF;
169
170 l_info_text := 'updating cash item data';
171 UPDATE PN_PAYMENT_ITEMS_ALL
172 SET EXPORT_TO_AP_FLAG = X_EXPORT_TO_AP_FLAG,
173 EXPORT_TO_AR_FLAG = X_EXPORT_TO_AR_FLAG,
174 EXPORT_CURRENCY_AMOUNT = l_export_amt,
175 EXPORT_CURRENCY_CODE = X_EXPORT_CURRENCY_CODE,
176 RATE = X_RATE,
177 ACCOUNTED_AMOUNT = ACTUAL_AMOUNT * X_RATE,
178 DUE_DATE = X_DUE_DATE,
179 AP_INVOICE_NUM = X_AP_INVOICE_NUM,
180 GROUPING_RULE_ID = X_GROUPING_RULE_ID,
181 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
182 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
183 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
184 WHERE PAYMENT_ITEM_ID = X_PAYMENT_ITEM_ID;
185
186 l_info_text := 'updating associated payment schedule';
187 UPDATE PN_PAYMENT_SCHEDULES_ALL
188 SET PERIOD_NAME = X_PERIOD_NAME
189 WHERE PAYMENT_SCHEDULE_ID = X_PAYMENT_SCHEDULE_ID;
190
191 IF (SQL%NOTFOUND) THEN
192 RAISE NO_DATA_FOUND;
193 END IF;
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 raISe_application_error(-20001,'Error while ' || l_info_text || to_char(sqlcode));
198 app_exception.raISe_exception;
199 END update_row;
200
201 END pn_exp_payment_items_pkg;