[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_SI_SM_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_PS_SI_SM_EXPORT_PKG AS
2 --$Header: JACNSMPB.pls 120.2 2011/01/14 07:46:38 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNSMPB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export accounting related Settlement method |
13 --| which has transactions with current legal entity in given |
14 --| accounting year for Public Sector |
15 --| |
16 --| PROCEDURE LIST |
17 --| PROCEDURE Add_Settlement_Method |
18 --| |
19 --| |
20 --| HISTORY |
21 --| 06/AUG/2010 Wuhua Liu reated |
22 --| 01/06/2011 Jianchao Chi Updated for the new solution to change |
23 --| the logic of Voucher Number and Legal |
24 --| Entity |
25 --+======================================================================*/
26
27 --==========================================================================
28 -- PROCEDURE NAME:
29 --
30 -- Add_Settlement_Method Public
31 --
32 -- DESCRIPTION:
33 --
34 -- This procedure is to export Settlement method for
35 -- public sector
36 --
37 -- PARAMETERS:
38 -- In: pn_legal_entity_id Legal Entity ID
39 -- pn_ledger_id Ledger ID
40 -- pn_accounting_year Accounting Year
41 --
42 -- DESIGN REFERENCES:
43 -- TDD_1213_FIN_GL_P_CNAOV2_SI.doc
44 --
45 -- CHANGE HISTORY:
46 --
47 -- 06-AUG-2010 Wuhua Liu created
48 -- 06-JAN-2011 Jianchao Chi Updated for CNAO V2 Upgrade,
49 -- for voucher number and legal entity
50
51 PROCEDURE Add_Settlement_Method(pn_legal_entity_id IN NUMBER,
52 pn_ledger_id IN NUMBER,
53 pn_accounting_year IN NUMBER) IS
54 lv_procedure_name VARCHAR2(40) := 'Add_Settlement_Method';
55 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
56 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
57 ln_row_count NUMBER := 0;
58
59 CURSOR settlement_method_cur(pn_legal_entity NUMBER,
60 pn_ledger NUMBER,
61 pn_account_year NUMBER) IS
62 SELECT DISTINCT settlement_method_number, settlement_method_name
63 FROM (SELECT ffv.flex_value settlement_method_number,
64 ffv.description settlement_method_name
65 FROM fnd_flex_values_vl ffv, fnd_flex_value_sets ffvs
66 WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
67 AND ffvs.flex_value_set_name = 'JA_CN_SETTLEMENT_METHOD')
68 --Only existing settlement method in Itemized Journal table should be exported
69 WHERE settlement_method_number IN
70 (SELECT jl.settlement_method_number
71 FROM ja_cn_journal_lines jl, ja_cn_voucher_number jcvn
72 --Update by Jianchao Chi, for cnaov2 upgrade, 06-Jan-2011
73 --Add ja_cn_voucher_number table, voucher number and legal entity
74 --are fetched from ja_cn_voucher_number
75 --The previous one was:
76 ----------------------------------------------------
77 --FROM ja_cn_journal_lines jl
78 --WHERE jl.legal_entity_id = pn_legal_entity
79 ----------------------------------------------------
80 --Add the following three condidions
81 WHERE jcvn.legal_entity_id = pn_legal_entity --Legal entity ID parameter
82 AND jcvn.je_header_id = jl.je_header_id
83 AND jcvn.je_line_number = jl.je_line_num
84 AND jl.ledger_id = pn_ledger -- Ledger ID Parameter
85 AND jl.period_name IN
86 (SELECT gps.period_name
87 FROM gl_period_statuses gps
88 WHERE gps.ledger_id = jl.ledger_id
89 AND gps.application_id = 101
90 AND gps.period_year = pn_account_year --Accounting Year parameter
91 ))
92 ORDER BY settlement_method_number;
93
94 BEGIN
95 --logging for debug
96 IF (ln_proc_level >= ln_dbg_level) THEN
97 FND_LOG.STRING(ln_proc_level,
98 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
99 '.begin',
100 'Enter procedure');
101 -- logging the parameters
102 FND_LOG.STRING(ln_proc_level,
103 lv_procedure_name || '.parameters',
104 'pn_ledger_id=' || pn_ledger_id || ',' ||
105 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
106 'pn_accounting_year=' || pn_accounting_year);
107 END IF; --(ln_proc_level >= ln_dbg_level)
108 FND_FILE.PUT_LINE(FND_FILE.LOG,
109 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
110 '.parameters ' || 'pn_ledger_id=' || pn_ledger_id || ',' ||
111 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
112 'pn_accounting_year=' || pn_accounting_year);
113 --Open and loop the Cursor to add the data to XML file
114 FOR v_row IN settlement_method_cur(pn_legal_entity_id,
115 pn_ledger_id,
116 pn_accounting_year) LOOP
117 ln_row_count := ln_row_count + 1;
118 JA_CN_UTILITY.Add_Sub_Root_Node('SETTLEMENT_METHOD',
119 JA_CN_UTILITY.GV_TAG_TYPE_START);
120 JA_CN_UTILITY.Add_Child_Node('SETTLEMENT_METHOD_NUMBER',
121 v_row.settlement_method_number);
122 JA_CN_UTILITY.Add_Child_Node('SETTLEMENT_METHOD_NAME',
123 v_row.settlement_method_name);
124 JA_CN_UTILITY.Add_Sub_Root_Node('SETTLEMENT_METHOD',
125 JA_CN_UTILITY.GV_TAG_TYPE_END);
126 END LOOP; --(v_row IN settlement_method_cur)
127
128 -- To judge if the Cursor fetchs data.
129 IF (ln_row_count = 0) THEN
130 JA_CN_UTILITY.Print_No_Data_Found_For_Log('SETTLEMENT_METHOD');
131 JA_CN_UTILITY.Add_Sub_Root_Node('SETTLEMENT_METHOD',
132 JA_CN_UTILITY.GV_TAG_TYPE_START);
133 JA_CN_UTILITY.Add_Child_Node('SETTLEMENT_METHOD_NUMBER', NULL);
134 JA_CN_UTILITY.Add_Child_Node('SETTLEMENT_METHOD_NAME', NULL);
135 JA_CN_UTILITY.Add_Sub_Root_Node('SETTLEMENT_METHOD',
136 JA_CN_UTILITY.GV_TAG_TYPE_END);
137 END IF; -- (ln_row_count = 0)
138 --logging for debug
139 IF (ln_proc_level >= ln_dbg_level) THEN
140 FND_LOG.STRING(ln_proc_level,
141 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
142 'Exit procedure');
143 END IF; -- (ln_proc_level>=ln_dbg_level)
144 FND_FILE.PUT_LINE(FND_FILE.LOG,
145 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
146 '.end');
147 --To handle the Exception
148 EXCEPTION
149 WHEN OTHERS THEN
150 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
151 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
152 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
153 '.Other_Exception ',
154 SQLCODE || SQLERRM);
155 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
156 FND_FILE.put_line(FND_FILE.log,
157 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
158 SQLCODE || SQLERRM);
159 RAISE;
160 END Add_Settlement_Method;
161 END JA_CN_PS_SI_SM_EXPORT_PKG;