[Home] [Help]
PACKAGE BODY: APPS.JA_CN_APAR_COMMON_PKG
Source
1 PACKAGE BODY JA_CN_APAR_COMMON_PKG AS
2 --$Header: JACNPRCB.pls 120.0 2010/05/13 09:17:38 wuwu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNPRCB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To export document type and transaction type | |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Document_Type |
16 --| PROCEDURE Add_Transaction_Type |
17 --| |
18 --| |
19 --| HISTORY |
20 --| 01-Apr-2010 Chaoqun Wu created |
21 --+======================================================================*/
22
23 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_APAR_COMMON_PROG';
24
25 --==========================================================================
26 -- PROCEDURE NAME:
27 --
28 -- Add_Document_Type Public
29 --
30 -- DESCRIPTION:
31 --
32 -- This procedure is to export document type
33 --
34 -- PARAMETERS:
35 -- NULL
36 --
37 -- DESIGN REFERENCES:
38 -- APAR_Chaoqun.doc
39 --
40 -- CHANGE HISTORY:
41 -- 01-Apr-2010 Chaoqun Wu created
42 --==========================================================================
43
44 PROCEDURE Add_Document_Type
45 IS
46 lv_procedure_name VARCHAR2(40) := 'Add_Document_Type';
47 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
48 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
49 NO_DATA EXCEPTION;
50
51 --Cursor for getting document type
52 --
53 CURSOR doc_type_cur
54 IS
55 SELECT flv.lookup_code Doc_Type_Num
56 , flv.meaning Doc_Num
57 FROM fnd_lookup_values flv
58 WHERE flv.lookup_type = 'JA_CN_DOCUMENT_TYPE'
59 AND flv.language = USERENV('LANG');
60
61 BEGIN
62 --logging for debug
63 IF (ln_proc_level >= ln_dbg_level)
64 THEN
65 FND_LOG.STRING(ln_proc_level,
66 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
67 '.begin',
68 'Enter procedure');
69 END IF; --ln_proc_level>=ln_dbg_level
70
71 FOR v_row IN doc_type_cur
72 LOOP
73 Ja_Cn_Utility.Add_Sub_Root_Node('DOCUMENT_TYPE'
74 ,Ja_Cn_Utility.GV_TAG_TYPE_START
75 ,Ja_Cn_Utility.GV_MODULE_APAR
76 );
77 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_TYPE_NUMBER'
78 ,v_row.Doc_Type_Num
79 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
80 ,Ja_Cn_Utility.GV_REQUIRED_YES
81 ,Ja_Cn_Utility.GV_MODULE_APAR
82 );
83 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_NUMBER'
84 ,v_row.Doc_Num
85 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
86 ,Ja_Cn_Utility.GV_REQUIRED_YES
87 ,Ja_Cn_Utility.GV_MODULE_APAR
88 );
89 Ja_Cn_Utility.Add_Sub_Root_Node('DOCUMENT_TYPE'
90 ,Ja_Cn_Utility.GV_TAG_TYPE_END
91 ,Ja_Cn_Utility.GV_MODULE_APAR
92 );
93 END LOOP;
94
95 --logging for debug
96 IF (ln_proc_level >= ln_dbg_level)
97 THEN
98 FND_LOG.STRING(ln_proc_level,
99 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
100 'Exit procedure');
101 END IF; -- (ln_proc_level>=ln_dbg_level)
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
106 THEN
107 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
108 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
109 '.Other_Exception ',
110 SQLCODE || SQLERRM);
111 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
112 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
113 RAISE;
114 END Add_Document_Type;
115
116 --==========================================================================
117 -- PROCEDURE NAME:
118 --
119 -- Add_Transaction_Type Public
120 --
121 -- DESCRIPTION:
122 --
123 -- This procedure is to export transaction type
124 --
125 -- PARAMETERS:
126 -- In: pn_legal_entity_id NUMBER identifier of legal entity
127 -- pn_ledger_id NUMBER identifier of ledger
128 -- pv_accounting_year VARCHAR2 accounting year
129 --
130 -- DESIGN REFERENCES:
131 -- APAR_Chaoqun.doc
132 --
133 -- CHANGE HISTORY:
134 -- 01-Apr-2010 Chaoqun Wu created
135 --==========================================================================
136
137 PROCEDURE Add_Transaction_Type
138 (pn_legal_entity_id IN NUMBER
139 ,pn_ledger_id IN NUMBER
140 ,pv_accounting_year IN VARCHAR2
141 )
142 IS
143 lv_procedure_name VARCHAR2(40) := 'Add_Transaction_Type';
144 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
145 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
146
147 --Cursor for getting document type
148 --
149 CURSOR tran_type_cur
150 IS
151 SELECT Tran_Type_Num
152 , Tran_Type_Name
153 FROM(
154 --Transanction type for document type 'Receivable'
155 SELECT TO_CHAR(rctt.cust_trx_type_id) Tran_Type_Num
156 , rctt.NAME Tran_Type_Name
157 , 'RECEIVABLES' Source
158 FROM Ra_Cust_Trx_Types_All rctt
159 WHERE EXISTS (SELECT hro.ORGANIZATION_ID
160 FROM hr_operating_units hro, xle_entity_profiles xfi
161 WHERE xfi.legal_entity_id = hro.default_legal_context_id
162 AND hro.set_of_books_id = pn_ledger_id --parameter: pn_ledger_id
163 AND xfi.legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
164 AND rctt.Org_Id = hro.ORGANIZATION_ID
165 AND NVL(rctt.Legal_Entity_Id, pn_legal_entity_id) = pn_legal_entity_id) --parameter: pn_legal_entity_id
166 AND rctt.start_date <=
167 (SELECT MAX(GP.END_DATE)
168 FROM GL_PERIODS GP, GL_LEDGERS LED
169 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
170 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
171 AND GP.PERIOD_year = pv_accounting_year --parameter: pv_accounting_year
172 AND led.ledger_id = pn_ledger_id) --parameter: pn_ledger_id
173 AND (rctt.end_date IS NULL OR
174 rctt.end_date >=
175 (SELECT MIN(GP.START_DATE)
176 FROM GL_PERIODS GP, GL_LEDGERS LED
177 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
178 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
179 AND GP.PERIOD_year = pv_accounting_year --parameter: pv_accounting_year
180 AND led.ledger_id = pn_ledger_id)) --parameter: pn_ledger_id
181 UNION
182 --Transaction type for document type 'Receipt'
183 SELECT flv.lookup_code Tran_Type_Num
184 , flv.meaning Tran_Type_Name
185 , 'RECEIPT' Source
186 FROM fnd_lookup_values flv
187 WHERE flv.lookup_type = 'JA_CN_REC_TRAN_TYPE'
188 AND flv.language = USERENV('LANG')
189 UNION
190 --Transaction type for document type 'Payable'
191 SELECT alit.LOOKUP_CODE Tran_Type_Num
192 , alit.DISPLAYED_FIELD Tran_Type_Name
193 , 'PAYABLE' Source
194 FROM Ap_Lc_Invoice_Types_V alit
195 WHERE alit.LOOKUP_CODE NOT IN
196 (--'PAYMENT REQUEST', 'INVOICE REQUEST',
197 'CREDIT MEMO REQUEST')
198 UNION
199 --Transaction type for document type 'Payment'
200 SELECT alc.lookup_code Tran_Type_Num
201 , alc.displayed_field Tran_Type_Name
202 , 'PAYMENT' Source
203 FROM ap_lookup_codes alc
204 WHERE alc.lookup_type = 'PAYMENT TYPE'
205 AND alc.lookup_code NOT IN ('A', 'N')
206 ) ORDER BY Source;
207
208 BEGIN
209 --logging for debug
210 IF (ln_proc_level >= ln_dbg_level)
211 THEN
212 FND_LOG.STRING(ln_proc_level,
213 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
214 '.begin',
215 'Enter procedure');
216 -- logging the parameters
217 FND_LOG.STRING(ln_proc_level,
218 lv_procedure_name ||
219 '.parameters',
220 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
221 'pn_ledger_id=' || pn_ledger_id || ',' ||
222 'pv_accounting_year=' || pv_accounting_year);
223 END IF; --ln_proc_level>=ln_dbg_level
224
225 FOR v_row IN tran_type_cur
226 LOOP
227 Ja_Cn_Utility.Add_Sub_Root_Node('TRANSACTION_TYPE'
228 ,Ja_Cn_Utility.GV_TAG_TYPE_START
229 ,Ja_Cn_Utility.GV_MODULE_APAR
230 );
231 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_TYPE_NUMBER'
232 ,v_row.Tran_Type_Num
233 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
234 ,Ja_Cn_Utility.GV_REQUIRED_YES
235 ,Ja_Cn_Utility.GV_MODULE_APAR
236 );
237 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_TYPE_NAME'
238 ,v_row.Tran_Type_Name
239 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
240 ,Ja_Cn_Utility.GV_REQUIRED_YES
241 ,Ja_Cn_Utility.GV_MODULE_APAR
242 );
243 Ja_Cn_Utility.Add_Sub_Root_Node('TRANSACTION_TYPE'
244 ,Ja_Cn_Utility.GV_TAG_TYPE_END
245 ,Ja_Cn_Utility.GV_MODULE_APAR
246 );
247 END LOOP;
248
249 --logging for debug
250 IF (ln_proc_level >= ln_dbg_level)
251 THEN
252 FND_LOG.STRING(ln_proc_level,
253 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
254 'Exit procedure');
255 END IF; -- (ln_proc_level>=ln_dbg_level)
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
260 THEN
261 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
262 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
263 '.Other_Exception ',
264 SQLCODE || SQLERRM);
265 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
266 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
267 RAISE;
268 END Add_Transaction_Type;
269
270 END JA_CN_APAR_COMMON_PKG;