[Home] [Help]
PACKAGE BODY: APPS.JA_CN_SM_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_SM_EXPORT_PKG AS
2 --$Header: JACNSMEB.pls 120.3 2011/01/14 07:41:50 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================|
8 --| FILENAME |
9 --| JACNSMEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| This package contains the following PL/SQL tables/procedures |
14 --| to export the accounting related Settlement Method which have |
15 --| transaction with current legal entity in given accounting year. |
16 --| |
17 --| PROCEDURE LIST |
18 --| Add_Settlement_Method |
19 --| |
20 --| HISTORY |
21 --| 11-Mar-2010 Wei Huang Created |
22 --| 19-May-2010 Wei Huang Updated procedure Add_Settlement_Method |
23 --| 05-Jan-2011 Jianchao Chi Updated for the new solution to change |
24 --| the logic of Voucher Number and Legal |
25 --| Entity |
26 --| |
27 --+======================================================================*/
28 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_SM_EXPORT_PKG';
29
30 --==========================================================================
31 -- PROCEDURE NAME:
32 --
33 -- Add_Settlement_Method Public
34 --
35 -- DESCRIPTION:
36 --
37 -- This procedure is to export the accounting related Settlement Method
38 -- which have transaction with current legal entity in given accounting
39 -- year.
40 --
41 -- PARAMETERS:
42 -- In: pn_ledger_id Legder ID
43 -- pn_legal_entity_id LEGAL_ENTITY_ID
44 -- pv_accounting_year Accounting Year
45 --
46 -- Out:
47 --
48 -- DESIGN REFERENCES:
49 --
50 --
51 -- CHANGE HISTORY:
52 -- 11-Mar-2010 Wei Huang Created
53 -- 19-May-2010 Wei Huang Updated the logic of handling NO_DATA Exception
54 -- 05-Jan-2011 Jianchao Chi Updated for change the logic of legal entity
55 --
56 --===========================================================================
57 PROCEDURE Add_Settlement_Method(pn_ledger_id IN NUMBER,
58 pn_legal_entity_id IN NUMBER,
59 pv_accounting_year IN VARCHAR2) IS
60 lv_procedure_name VARCHAR2(40) := 'Add_Settlement_Method';
61 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
62 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
63 --ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
64 NO_DATA EXCEPTION;
65 ln_row_count NUMBER := 0;
66
67 CURSOR settlement_method_cur IS
68 SELECT DISTINCT Settlement_Method_Number, Settlement_Method_Name
69 FROM (SELECT --for AP
70 iby1.PAYMENT_METHOD_CODE AS Settlement_Method_Number,
71 iby1.PAYMENT_METHOD_NAME AS Settlement_Method_Name
72 FROM iby_payment_methods_vl iby1
73 UNION
74 SELECT --for AR
75 to_char(arm.RECEIPT_METHOD_ID) AS Settlement_Method_Number,
76 arm.name AS Settlement_Method_Name
77 FROM AR_RECEIPT_METHODS arm
78 UNION
79 SELECT --for GL/AGIS
80 ffv.FLEX_VALUE AS Settlement_Method_Number,
81 ffv.DESCRIPTION AS Settlement_Method_Name
82 FROM FND_FLEX_VALUES_VL ffv, FND_FLEX_VALUE_SETS ffvs
83 WHERE ffv.FLEX_VALUE_SET_ID = ffvs.flex_value_set_id
84 AND ffvs.flex_value_set_name = 'JA_CN_SETTLEMENT_METHOD')
85 --Only existing settlement method in Itemized Journal table should be exported
86 WHERE Settlement_Method_Number IN
87 (SELECT jl.Settlement_Method_Number
88 FROM JA_CN_JOURNAL_LINES jl, ja_cn_voucher_number jcvn
89 --Update by Jianchao Chi, for cnaov2 upgrade 06-JAN-2011
90 --Add ja_cn_voucher_number table, voucher number and legal entity
91 --are fetched from ja_cn_voucher_number
92 --The new one is
93 ----------------------------------------------------
94 --AND jcvn.je_header_id = jl.je_header_id
95 --AND jcvn.je_line_number = jl.je_line_num
96 --AND jcvn.legal_entity_id = pn_le_id;
97 ----------------------------------------------------
98 WHERE jcvn.legal_entity_id = pn_legal_entity_id --Change from "jl.legal_entity_id = pn_legal_entity_id"
99 AND jl.ledger_id = pn_ledger_id
100 AND jcvn.je_header_id = jl.je_header_id ----Add these two conditions
101 AND jcvn.je_line_number = jl.je_line_num
102 AND jl.period_name IN
103 (SELECT gps.period_name
104 FROM GL_PERIOD_STATUSES gps
105 WHERE gps.ledger_id = jl.ledger_id
106 AND gps.application_id = 101
107 and gps.period_year = pv_accounting_year))
108 ORDER BY Settlement_Method_Number;
109
110 BEGIN
111 --logging for debug
112 IF (ln_proc_level >= ln_dbg_level) THEN
113 FND_LOG.STRING(ln_proc_level,
114 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
115 '.begin',
116 'Enter procedure');
117 -- logging the parameters
118 FND_LOG.STRING(ln_proc_level,
119 lv_procedure_name || '.parameters',
120 'pn_ledger_id=' || pn_ledger_id || ',' ||
121 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
122 'pv_accounting_year=' || pv_accounting_year);
123 END IF; --ln_proc_level >= ln_dbg_level
124 FND_FILE.put_line(FND_FILE.log,
125 lv_procedure_name || '.parameters:' ||
126 'pn_ledger_id=' || pn_ledger_id || ',' ||
127 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
128 'pv_accounting_year=' || pv_accounting_year);
129
130 --Open and loop the Cursor to add the data to XML file
131 FOR v_row IN settlement_method_cur LOOP
132 ln_row_count := ln_row_count + 1;
133 Ja_Cn_Utility.Add_Sub_Root_Node('SETTLEMENT_METHOD',
134 Ja_Cn_Utility.GV_TAG_TYPE_START);
135 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NUMBER',
136 v_row.settlement_method_number);
137 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NAME',
138 v_row.settlement_method_name);
139 Ja_Cn_Utility.Add_Sub_Root_Node('SETTLEMENT_METHOD',
140 Ja_Cn_Utility.GV_TAG_TYPE_END);
141 END LOOP;
142
143 -- To judge if the Cursor fetchs data. If the row count is 0 and the
144 -- minOccurs=0 specified in xml schema, then need to handle it and raise exception
145 IF (ln_row_count = 0) THEN
146 Ja_Cn_Utility.Print_No_Data_Found_For_Log('SETTLEMENT_METHOD');
147
148 Ja_Cn_Utility.Add_Sub_Root_Node('SETTLEMENT_METHOD',
149 Ja_Cn_Utility.GV_TAG_TYPE_START);
150 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NUMBER', NULL);
151 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NAME', NULL);
152 Ja_Cn_Utility.Add_Sub_Root_Node('SETTLEMENT_METHOD',
153 Ja_Cn_Utility.GV_TAG_TYPE_END);
154 END IF;
155
156 --logging for debug
157 IF (ln_proc_level >= ln_dbg_level) THEN
158 FND_LOG.STRING(ln_proc_level,
159 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
160 'Exit procedure');
161 END IF; -- (ln_proc_level>=ln_dbg_level)
162
163 --To handle the No Data Found Exception
164 EXCEPTION
165 WHEN OTHERS THEN
166 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
167 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
168 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
169 '.Other_Exception ',
170 SQLCODE || SQLERRM);
171 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
172 FND_FILE.put_line(FND_FILE.log,
173 lv_procedure_name || SQLCODE || SQLERRM);
174 RAISE;
175
176 END Add_Settlement_Method;
177
178 END JA_CN_SM_EXPORT_PKG;