[Home] [Help]
PACKAGE BODY: APPS.CE_XML_HDRS_INF_PKG
Source
1 PACKAGE BODY CE_XML_HDRS_INF_PKG as
2 /* $Header: cexmlhib.pls 120.4 2005/08/30 16:00:41 lkwan noship $ */
3
4 FUNCTION body_revision RETURN VARCHAR2 IS
5 BEGIN
6
7 RETURN '$Revision: 120.4 $';
8
9 END body_revision;
10
11 FUNCTION spec_revision RETURN VARCHAR2 IS
12 BEGIN
13
14 RETURN G_spec_revision;
15
16 END spec_revision;
17
18 PROCEDURE Ifx_Row( X_Statement_Number VARCHAR2,
19 X_Bank_Branch_Name IN OUT NOCOPY VARCHAR2,
20 X_Bank_Account_Num VARCHAR2,
21 X_Statement_Date DATE,
22 X_Check_Digits VARCHAR2,
23 X_Control_Begin_Balance NUMBER,
24 X_Control_End_Balance NUMBER,
25 X_Control_Total_Dr NUMBER,
26 X_Control_Total_Cr NUMBER,
27 X_Control_Dr_Line_Count NUMBER,
28 X_Control_Cr_Line_Count NUMBER,
29 X_Control_Line_Count NUMBER,
30 X_Record_Status_Flag VARCHAR2,
31 X_Currency_Code IN OUT NOCOPY VARCHAR2,
32 X_Created_By IN OUT NOCOPY NUMBER,
33 X_Creation_Date IN OUT NOCOPY DATE,
34 X_Last_Updated_By IN OUT NOCOPY NUMBER,
35 X_Last_Update_Date IN OUT NOCOPY DATE,
36 X_Attribute_Category VARCHAR2,
37 X_Attribute1 VARCHAR2,
38 X_Attribute10 VARCHAR2,
39 X_Attribute11 VARCHAR2,
40 X_Attribute12 VARCHAR2,
41 X_Attribute13 VARCHAR2,
42 X_Attribute14 VARCHAR2,
43 X_Attribute15 VARCHAR2,
44 X_Attribute2 VARCHAR2,
45 X_Attribute3 VARCHAR2,
46 X_Attribute4 VARCHAR2,
47 X_Attribute5 VARCHAR2,
48 X_Attribute6 VARCHAR2,
49 X_Attribute7 VARCHAR2,
50 X_Attribute8 VARCHAR2,
51 X_Attribute9 VARCHAR2,
52 X_Org_Id IN OUT NOCOPY NUMBER,
53 X_Bank_Name IN OUT NOCOPY VARCHAR2,
54 X_Int_Calc_Balance NUMBER,
55 X_Cashflow_Balance NUMBER) IS
56
57 l_temp NUMBER;
58 l_temp2 NUMBER;
59 l_temp3 NUMBER;
60 l_temp4 NUMBER;
61 l_org_id CE_BANK_ACCOUNTS.ACCOUNT_OWNER_org_id%TYPE;
62 l_bank_name CE_BANK_BRANCHES_V.bank_name%TYPE;
63 l_bank_branch_name CE_BANK_BRANCHES_V.bank_branch_name%TYPE;
64 l_curr_code CE_BANK_ACCOUNTS_V.currency_code%TYPE;
65
66 l_seq NUMBER;
67
68 BEGIN
69
70 DELETE FROM ce_statement_headers_int
71 WHERE bank_account_num = ltrim(rtrim(X_Bank_Account_Num,' '),' ')
72 AND statement_number = ltrim(rtrim(X_Statement_Number,' '),' ');
73
74 SELECT count(BA.ACCOUNT_OWNER_org_id),
75 count(BB.bank_name),
76 count(BB.bank_branch_name),
77 count(BA.currency_code)
78 INTO l_temp, l_temp2, l_temp3, l_temp4
79 FROM CE_BANK_ACCOUNTS BA,
80 CE_BANK_BRANCHES_V BB
81 WHERE BA.bank_account_num = X_Bank_Account_Num
82 AND BB.branch_party_id = BA.bank_branch_id;
83
84 IF (l_temp = 1) THEN
85 SELECT ACCOUNT_OWNER_org_id
86 INTO l_org_id
87 FROM CE_BANK_ACCOUNTS
88 WHERE bank_account_num = X_Bank_Account_Num;
89 ELSE
90 l_org_id := TO_NUMBER(null);
91 END IF;
92
93 IF (X_Bank_Name is null) THEN
94 IF (l_temp2 = 1) THEN
95 SELECT bb.bank_name
96 INTO l_bank_name
97 FROM CE_BANK_ACCOUNTS BA,
98 CE_BANK_BRANCHES_V BB
99 WHERE BA.bank_account_num = X_Bank_Account_Num
100 AND BB.branch_party_id = BA.bank_branch_id;
101 ELSE
102 l_bank_name := '';
103 END IF;
104 ELSE
105 l_bank_name := X_Bank_Name;
106 END IF;
107
108 IF (X_Bank_Branch_Name is null) THEN
109 IF (l_temp3 = 1) THEN
110 SELECT bb.bank_branch_name
111 INTO l_bank_branch_name
112 FROM CE_BANK_ACCOUNTS BA,
113 CE_BANK_BRANCHES_V BB
114 WHERE BA.bank_account_num = X_Bank_Account_Num
115 AND BB.branch_party_id = BA.bank_branch_id;
116 ELSE
117 l_bank_branch_name := '';
118 END IF;
119 ELSE
120 l_bank_branch_name := ltrim(rtrim(X_Bank_Branch_Name,' '),' ');
121 END IF;
122
123 IF (X_Currency_Code is null) THEN
124 IF (l_temp2 = 1) THEN
125 SELECT currency_code
126 INTO l_curr_code
127 FROM CE_BANK_ACCOUNTS
128 WHERE bank_account_num = X_Bank_Account_Num;
129 ELSE
130 l_curr_code := '';
131 END IF;
132 ELSE
133 l_curr_code := ltrim(rtrim(X_Currency_Code,' '),' ');
134 END IF;
135
136 INSERT INTO CE_STATEMENT_HEADERS_INT(
137 statement_number,
138 bank_branch_name,
139 bank_account_num,
140 statement_date,
141 check_digits,
142 control_begin_balance,
143 control_end_balance,
144 control_total_dr,
145 control_total_cr,
146 control_dr_line_count,
147 control_cr_line_count,
148 control_line_count,
149 record_status_flag,
150 currency_code,
151 created_by,
152 creation_date,
153 last_updated_by,
154 last_update_date,
155 attribute_category,
156 attribute1,
157 attribute10,
158 attribute11,
159 attribute12,
160 attribute13,
161 attribute14,
162 attribute15,
163 attribute2,
164 attribute3,
165 attribute4,
166 attribute5,
167 attribute6,
168 attribute7,
169 attribute8,
170 attribute9,
171 bank_name,
172 org_id,
173 int_calc_balance,
174 cashflow_balance
175 ) VALUES (
176 ltrim(rtrim(X_Statement_Number,' '),' '),
177 l_bank_branch_name,
178 ltrim(rtrim(X_Bank_Account_Num,' '),' '),
179 ltrim(rtrim(X_Statement_Date,' '),' '),
180 X_Check_Digits,
181 X_Control_Begin_Balance,
182 X_Control_End_Balance,
183 X_Control_Total_Dr,
184 X_Control_Total_Cr,
185 X_Control_Dr_Line_Count,
186 X_Control_Cr_Line_Count,
187 X_Control_Line_Count,
188 X_Record_Status_Flag,
189 l_curr_code,
190 -1,
191 sysdate,
192 -1,
193 sysdate,
194 X_Attribute_Category,
195 X_Attribute1,
196 X_Attribute10,
197 X_Attribute11,
198 X_Attribute12,
199 X_Attribute13,
200 X_Attribute14,
201 X_Attribute15,
202 X_Attribute2,
203 X_Attribute3,
204 X_Attribute4,
205 X_Attribute5,
206 X_Attribute6,
207 X_Attribute7,
208 X_Attribute8,
209 X_Attribute9,
210 l_bank_name,
211 l_org_id,
212 X_Int_Calc_Balance,
213 X_Cashflow_Balance
214 );
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 fnd_message.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
219 fnd_message.set_token('PROCEDURE', 'CE_XML_HDRS_INF_PKG.IFX_ROW');
220 RAISE;
221 END Ifx_row;
222
223 END CE_XML_HDRS_INF_PKG;