[Home] [Help]
PACKAGE BODY: APPS.JA_CN_JC_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_JC_EXPORT_PKG AS
2 --$Header: JACNJCAB.pls 120.4 2011/01/14 07:03:01 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNJCAB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export Journal Categories |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Journal_Categories |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 01/20/2010 Chongwu Li Created |
20 --| 01/05/2011 Jianchao Chi Updated for the new solution to change |
21 --| the logic of Voucher Number and Legal |
22 --| Entity |
23 --+======================================================================*/
24 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_JC_EXPORT_PKG';
25
26 --==========================================================================
27 -- PROCEDURE NAME:
28 --
29 -- Add_Journal_Categories Public
30 --
31 -- DESCRIPTION:
32 --
33 -- This procedure is to export Journal Categories
34 --
35 -- PARAMETERS:
36 -- In: pn_legal_entity_id legal_entity ID
37 -- pn_ledger_id ledger ID
38 -- pn_accounting_year accounting year
39 -- DESIGN REFERENCES:
40 -- CNAO_V2_GL_TD.doc
41 --
42 -- CHANGE HISTORY:
43 --
44 -- 20-JAN-2010 Chongwu Li Created
45 -- 05-JAN-2011 Jianchao Chi Updated for the new solution to change
46 -- the logic of Voucher Number and Legal Entity
47 --===========================================================================
48
49 PROCEDURE Add_Journal_Categories(pn_legal_entity_id NUMBER,
50 pn_ledger_id NUMBER,
51 pn_accounting_year NUMBER) IS
52 lv_procedure_name VARCHAR2(40) := 'Add_Journal_Categories';
53 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
54 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
55 ln_row_count NUMBER;
56 --NO_DATA EXCEPTION;
57
58 -- to get journal categories
59 CURSOR journal_categories_cur(ln_legal_entity_id NUMBER,
60 ln_accounting_year NUMBER) IS
61 SELECT DISTINCT gjc.user_je_category_name,
62 gjc.je_category_key,
63 gjc.je_category_name
64 FROM gl_je_categories gjc,
65 ja_cn_journal_lines gcgl,
66 ja_cn_voucher_number jcvn
67 --Update by Jianchao Chi, for cnaov2 upgrade 05-JAN-2011
68 --Add ja_cn_voucher_number table, voucher number and legal entity
69 --are fetched from ja_cn_voucher_number
70 --The new one is
71 ----------------------------------------------------
72 --AND jcvn.je_header_id = jl.je_header_id
73 --AND jcvn.je_line_number = jl.je_line_num
74 --AND jcvn.legal_entity_id = ln_legal_entity_id;
75 ----------------------------------------------------
76 WHERE gjc.je_category_name = gcgl.je_category
77 --Add the follow two conditions by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
78 AND jcvn.je_header_id = gcgl.je_header_id
79 AND jcvn.je_line_number = gcgl.je_line_num
80 AND gcgl.period_name IN
81 (SELECT gp.period_name
82 FROM gl_periods gp, gl_ledgers gl
83 WHERE gp.period_year = ln_accounting_year
84 AND gp.period_set_name = gl.period_set_name)
85 --Comment by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
86 --AND jcjl.legal_entity_id = ln_legal_entity_id
87 AND jcvn.legal_entity_id = ln_legal_entity_id
88 ORDER BY gjc.user_je_category_name;
89
90 BEGIN
91 --logging for debug
92 IF (ln_proc_level >= ln_dbg_level) THEN
93 FND_LOG.STRING(ln_proc_level,
94 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
95 '.begin',
96 'Enter procedure');
97 FND_LOG.STRING(ln_proc_level,
98 lv_procedure_name || '.parameters',
99 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
100 'pn_ledger_id=' || pn_ledger_id || ',' ||
101 'pn_accounting_year=' || pn_accounting_year);
102
103 END IF; --ln_proc_level>=ln_dbg_level
104 FND_FILE.put_line(FND_FILE.log,
105 lv_procedure_name || '.parameters:' ||
106 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
107 'pn_ledger_id=' || pn_ledger_id || ',' ||
108 'pn_accounting_year=' || pn_accounting_year);
109 ln_row_count := 0;
110 FOR v_row IN journal_categories_cur(pn_legal_entity_id,
111 pn_accounting_year) LOOP
112 ln_row_count := ln_row_count + 1;
113 Ja_Cn_Utility.Add_Sub_Root_Node('JOURNAL_CATEGORY',
114 Ja_Cn_Utility.GV_TAG_TYPE_START);
115 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER',
116 v_row.je_category_name);
117 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NAME',
118 v_row.User_Je_Category_Name);
119 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_SHORT_NAME',
120 v_row.Je_Category_Key);
121 Ja_Cn_Utility.Add_Sub_Root_Node('JOURNAL_CATEGORY',
122 Ja_Cn_Utility.GV_TAG_TYPE_END);
123 END LOOP;
124
125 IF (ln_row_count = 0) THEN
126 Ja_Cn_Utility.Print_No_Data_Found_For_Log('JOURNAL_CATEGORY');
127 Ja_Cn_Utility.Add_Sub_Root_Node('JOURNAL_CATEGORY',
128 Ja_Cn_Utility.GV_TAG_TYPE_START);
129 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER', NULL);
130 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NAME', NULL);
131 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_SHORT_NAME', NULL);
132 Ja_Cn_Utility.Add_Sub_Root_Node('JOURNAL_CATEGORY',
133 Ja_Cn_Utility.GV_TAG_TYPE_END);
134 END IF; --ln_row_count = 0
135
136 --logging for debug
137 IF (ln_proc_level >= ln_dbg_level) THEN
138 FND_LOG.STRING(ln_proc_level,
139 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
140 'Exit procedure');
141 END IF; -- (ln_proc_level>=ln_dbg_level)
142 EXCEPTION
143 WHEN OTHERS THEN
144 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
145 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
146 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
147 '.Other_Exception ',
148 SQLCODE || SQLERRM);
149 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
150 FND_FILE.put_line(FND_FILE.log,
151 lv_procedure_name || SQLCODE || SQLERRM);
152 RAISE;
153 END Add_Journal_Categories;
154
155 END JA_CN_JC_EXPORT_PKG;
156