1 PACKAGE jai_cmn_utils_pkg AS
2 /* $Header: jai_cmn_utils.pls 120.7.12020000.3 2013/03/18 07:37:39 qimeng ship $ */
3
4
5 /********************************************************************************************************
6 FILENAME : ja_in_util_pkg_s.sql
7
8 Created By : ssumaith
9
10 Created Date : 29-Nov-2004
11
12 Bug : 4033992
13
14 Purpose : Check whether The India Localization functionality should be used or not.
15
16 Called from : All india Localization triggers on base APPS tables
17
18 --------------------------------------------------------------------------------------------------------
19 CHANGE HISTORY:
20 --------------------------------------------------------------------------------------------------------
21 S.No Date Author and Details
22 --------------------------------------------------------------------------------------------------------
23 1. 2004/11/29 ssumaith - bug# 4033992 - File version 115.0
24
25 created the package spec for the common utility which will be used to check
26 if India Localization funtionality is being used.
27
28 This function check_jai_exists is to be called from all India localization triggers.
29 The parameter - p_calling_object is a mandatory one and will have the name of the
30 trigger which calls the package.
31 The other parameters are optional , but one of them needs to be passed.
32 The second parameter is inventory_organization_id
33 The Third Parameter is Operating_unit
34 The fouth Parameter is Set_of_books_id
35 The fifth and sixth parameters are for future use.
36 The fifth parameter - p_value_string has the values passed seperated by colons
37 The sixth parameter - p_format_string has the corresponding labels seperated by colons,
38 which inform what each value is.
39
40 Example call to the package can be :
41
42 JA_IN_UTIL.CHECK_JAI_EXISTS(
43 P_CALLING_OBJECT => 'TRIGGER NAME' ,
44 P_ORG_ID => :New.org_id ,
45 P_Value_string => 'OM:OE_ORDER_LINES_ALL' ,
46 p_format_string => 'MODULE NAME:TABLE NAME'
47 );
48
49
50 2. 08-Jun-2005 Version 116.1 jai_cmn_utils -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
51 as required for CASE COMPLAINCE.
52
53 3. 14-Jun-2005 rchandan for bug#4428980, Version 116.2
54 Modified the object to remove literals from DML statements and CURSORS.
55 This activity is done as a part of R12 initiatives.
56 As part OF R12 Inititive Inventory conversion the OPM code IS commented
57
58 4 06-Jul-2005 rallamse for bug# PADDR Elimination
59 1. Removed the procedures ja_in_put_locator , ja_in_set_locator and ja_in_get_locator
60 from both the specification and body.
61
62 5. 12-Jul-2005 Sanjikum for Bug#4483042, Version 117.2
63 1) Added a new function validate_po_type
64
65 5. 06-Dec-2005 rallamse for Bug#4773914, Version 120.2
66 1) Added a new function get_operating_unit
67
68 6. 26-FEB-2007 SSAWANT , File version 120.3
69 Forward porting the change in 11.5 bug 4903380 to R12 bug no 5039365.
70
71 Added a function return_valid_date. This function would take varchar2 as input. If
72 this input is a date then it would return the same otherwise it would return NULL.
73 This function is currently used in JAINASST.rdf and JAINYEDE.rdf.
74
75 Dependency
76 ----------
77 Yes
78
79
80 7. 09-Mar-2010 Jia for GL Drilldown ER
81 Add a new function if_IL_drilldown that is used to Enable/Disable drilldown button
82 according OFI journal source and journal categories.
83
84 8. 15-JUN-2012 Chong for ZX ER
85 Add a new function WRITE_FND_LOG that is used to write FND log by given level.
86
87 ***************************************************************************************************************/
88 /************************************************************************************************************************
89
90 Created By : Sanjikum
91
92 Creation Date : 28-Oct-2004
93
94 Enhancement Bug : 3964409
95
96 Purpose : This procedure updates the JAI_CMN_RG_SLNOS table.
97 1) If there are no record existing, then it inserts the record into
98 JAI_CMN_RG_SLNOS and then updates the same.
99 2) If there are some records existing, but they are not for current year,
100 then it updates the record again
101
102 Dependency :
103
104 Change History :
105
106
107 Future Dependencies For the release Of this Object:-
108 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
109 A datamodel change )
110
111 ----------------------------------------------------------------------------------------------------------------------------------------------------
112 Current Version Current Bug Dependent Files Version Author Date Remarks
113 Of File On Bug/Patchset Dependent On
114 ja_in_create_3964409_apps.sql
115 ----------------------------------------------------------------------------------------------------------------------------------------------------
116 115.0 3964409 1. ja_in_create_3964409_apps.sql 115.0 Sanjikum 28/10/2004
117 2. ja_in_create_3964409_ja.sql 115.0 Sanjikum 28/10/2004
118 3. ja_in_datafix_po_3964409.sql 115.0 Sanjikum 28/10/2004
119
120 ******************************************************************************************************************************/
121
122 FUNCTION check_jai_exists(p_calling_object VARCHAR2 ,
123 p_inventory_orgn_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE DEFAULT NULL ,
124 p_org_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE DEFAULT NULL ,
125 p_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL ,
126 p_value_string VARCHAR2 DEFAULT NULL ,
127 p_format_string VARCHAR2 DEFAULT NULL
128 ) RETURN BOOLEAN;
129
130
131
132 /************************************************************************************************************************
133
134 Created By : rallamse
135 Creation Date : 06-Dec-2005
136 Enhancement Bug : 4773914
137 Purpose : This function get_operating_unit returns operating unit based on inventory organization id.
138 Dependency : This introduces a dependacy
139 Change History :
140
141
142 Future Dependencies For the release Of this Object:-
143 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
144 A datamodel change )
145
146 ******************************************************************************************************************************/
147 FUNCTION get_operating_unit (
148 p_calling_object VARCHAR2 ,
149 p_inventory_orgn_id ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID%TYPE
150 ) RETURN NUMBER;
151
152 PROCEDURE update_rg_slno(
153 pn_organization_id IN NUMBER,
154 pn_location_id IN NUMBER,
155 pv_register_type IN VARCHAR2,
156 pn_fin_year IN NUMBER,
157 pn_txn_amt IN NUMBER,
158 pn_slno OUT NOCOPY NUMBER,
159 pn_opening_balance OUT NOCOPY NUMBER,
160 pn_closing_balance OUT NOCOPY NUMBER
161 );
162
163 FUNCTION currency_conversion(
164 c_set_of_books_id In Number,
165 c_from_currency_code In varchar2,
166 c_conversion_date in date,
167 c_conversion_type in varchar2,
168 c_conversion_rate in number
169 ) return number ;
170
171 PROCEDURE print_log(
172 filename VARCHAR2,
173 text_to_write VARCHAR2
174 ) ;
175 --As part OF R12 Inititive Inventory conversion the OPM code IS commented
176 /*FUNCTION opm_uom_version(
177 from_uom varchar2,
178 to_uom varchar2,
179 p_item_id number
180 ) RETURN NUMBER;
181
182 FUNCTION get_opm_assessable_value
183 (p_item_id number,
184 p_qty number,
185 p_exted_price number,
186 P_Cust_Id Number Default 0
187 ) RETURN NUMBER ;*/
188
189
190 procedure get_le_info
191 (
192 p_api_version IN NUMBER ,
193 p_init_msg_list IN VARCHAR2 ,
194 p_commit IN VARCHAR2 ,
195 p_ledger_id IN NUMBER ,
196 p_bsv IN VARCHAR2 ,
197 p_org_id IN NUMBER ,
198 x_return_status OUT NOCOPY VARCHAR2 ,
199 x_msg_count OUT NOCOPY NUMBER ,
200 x_msg_data OUT NOCOPY VARCHAR2 ,
201 x_legal_entity_id OUT NOCOPY NUMBER ,
202 x_legal_entity_name OUT NOCOPY VARCHAR2
203 );
204
205 FUNCTION validate_po_type(p_po_type IN VARCHAR2 DEFAULT NULL,
206 p_style_id IN NUMBER DEFAULT NULL,
207 p_po_header_id IN NUMBER DEFAULT NULL
208 ) RETURN BOOLEAN;
209 /*Bug5039365 by ssawant*/
210 FUNCTION return_valid_date( p_validate_text VARCHAR2 ) RETURN DATE ;
211
212 --==========================================================================
213 -- FUNCTION NAME:
214 --
215 -- if_IL_drilldown Public
216 --
217 -- DESCRIPTION:
218 --
219 -- This function is used to Enable/Disable drilldown buttion
220 -- according OFI journal source and journal categories.
221 --
222 -- PARAMETERS:
223 -- In: pn_je_source Identifier of journal source
224 -- pn_je_category Identifier of journal category
225 --
226 --
227 -- DESIGN REFERENCES:
228 -- FDD_R12_1_4_GL_Drilldown_V0.4.docx
229 --
230 -- CHANGE HISTORY:
231 --
232 -- 09-Mar-2010 Jia Li created
233 --==========================================================================
234 FUNCTION if_IL_drilldown (
235 pn_je_source VARCHAR2
236 , pn_je_category VARCHAR2
237 ) RETURN BOOLEAN;
238
239 PROCEDURE WRITE_FND_LOG_MSG(
240 module IN VARCHAR2,
241 message IN VARCHAR2
242 );
243
244
245 --==========================================================================
246 -- FUNCTION NAME:
247 -- WRITE_FND_LOG Public
248 --
249 -- DESCRIPTION:
250 -- Write FND log by given log level.
251 --
252 -- PARAMETERS:
253 -- In: pn_message_level Give log level
254 -- pv_module module name
255 -- pv_message fnd log message contents
256 --
257 -- DESIGN REFERENCES:
258 --
259 -- CHANGE HISTORY:
260 -- 15-JUN-2012 Chong created
261 --==========================================================================
262 PROCEDURE WRITE_FND_LOG(
263 pn_message_level IN NUMBER DEFAUlt 1, --FND log level, default 1: statument level
264 pv_module IN VARCHAR2,
265 pv_message IN VARCHAR2
266 );
267
268 --==========================================================================
269 -- FUNCTION NAME:
270 -- IS_ACCRUAL_BASIS Public
271 --
272 -- DESCRIPTION:
273 -- Compare given gl date with service tax accrual date, if given gl date
274 -- before service tax accrual date, then return false, else return true.
275 --
276 -- PARAMETERS:
277 -- In: pn_regime_id
278 -- pn_organization_id
279 -- pn_location_id
280 -- pd_gl_date
281 --
282 -- DESIGN REFERENCES:
283 --
284 -- CHANGE HISTORY:
285 -- 10-JUN-2012 Qinglei created
286 --==========================================================================
287 FUNCTION IS_ACCRUAL_BASIS(pn_regime_id NUMBER,
288 pn_organization_id NUMBER,
289 pn_location_id NUMBER,
290 pd_gl_date DATE) RETURN BOOLEAN;
291
292
293 END jai_cmn_utils_pkg;