1 PACKAGE jai_cmn_utils_pkg AS
2 /* $Header: jai_cmn_utils.pls 120.3 2007/04/12 12:41:09 ssawant 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
81 ***************************************************************************************************************/
82 /************************************************************************************************************************
83
84 Created By : Sanjikum
85
86 Creation Date : 28-Oct-2004
87
88 Enhancement Bug : 3964409
89
90 Purpose : This procedure updates the JAI_CMN_RG_SLNOS table.
91 1) If there are no record existing, then it inserts the record into
92 JAI_CMN_RG_SLNOS and then updates the same.
93 2) If there are some records existing, but they are not for current year,
94 then it updates the record again
95
96 Dependency :
97
98 Change History :
99
100
101 Future Dependencies For the release Of this Object:-
102 (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/
103 A datamodel change )
104
105 ----------------------------------------------------------------------------------------------------------------------------------------------------
106 Current Version Current Bug Dependent Files Version Author Date Remarks
107 Of File On Bug/Patchset Dependent On
108 ja_in_create_3964409_apps.sql
109 ----------------------------------------------------------------------------------------------------------------------------------------------------
110 115.0 3964409 1. ja_in_create_3964409_apps.sql 115.0 Sanjikum 28/10/2004
111 2. ja_in_create_3964409_ja.sql 115.0 Sanjikum 28/10/2004
112 3. ja_in_datafix_po_3964409.sql 115.0 Sanjikum 28/10/2004
113
114 ******************************************************************************************************************************/
115
116 FUNCTION check_jai_exists(p_calling_object VARCHAR2 ,
117 p_inventory_orgn_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE DEFAULT NULL ,
118 p_org_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE DEFAULT NULL ,
119 p_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL ,
120 p_value_string VARCHAR2 DEFAULT NULL ,
121 p_format_string VARCHAR2 DEFAULT NULL
122 ) RETURN BOOLEAN;
123
124
125
126 /************************************************************************************************************************
127
128 Created By : rallamse
129 Creation Date : 06-Dec-2005
130 Enhancement Bug : 4773914
131 Purpose : This function get_operating_unit returns operating unit based on inventory organization id.
132 Dependency : This introduces a dependacy
133 Change History :
134
135
136 Future Dependencies For the release Of this Object:-
137 (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/
138 A datamodel change )
139
140 ******************************************************************************************************************************/
141 FUNCTION get_operating_unit (
142 p_calling_object VARCHAR2 ,
143 p_inventory_orgn_id ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID%TYPE
144 ) RETURN NUMBER;
145
146 PROCEDURE update_rg_slno(
147 pn_organization_id IN NUMBER,
148 pn_location_id IN NUMBER,
149 pv_register_type IN VARCHAR2,
150 pn_fin_year IN NUMBER,
151 pn_txn_amt IN NUMBER,
152 pn_slno OUT NOCOPY NUMBER,
153 pn_opening_balance OUT NOCOPY NUMBER,
154 pn_closing_balance OUT NOCOPY NUMBER
155 );
156
157 FUNCTION currency_conversion(
158 c_set_of_books_id In Number,
159 c_from_currency_code In varchar2,
160 c_conversion_date in date,
161 c_conversion_type in varchar2,
162 c_conversion_rate in number
163 ) return number ;
164
165 PROCEDURE print_log(
166 filename VARCHAR2,
167 text_to_write VARCHAR2
168 ) ;
169 --As part OF R12 Inititive Inventory conversion the OPM code IS commented
170 /*FUNCTION opm_uom_version(
171 from_uom varchar2,
172 to_uom varchar2,
173 p_item_id number
174 ) RETURN NUMBER;
175
176 FUNCTION get_opm_assessable_value
177 (p_item_id number,
178 p_qty number,
179 p_exted_price number,
180 P_Cust_Id Number Default 0
181 ) RETURN NUMBER ;*/
182
183
184 procedure get_le_info
185 (
186 p_api_version IN NUMBER ,
187 p_init_msg_list IN VARCHAR2 ,
188 p_commit IN VARCHAR2 ,
189 p_ledger_id IN NUMBER ,
190 p_bsv IN VARCHAR2 ,
191 p_org_id IN NUMBER ,
192 x_return_status OUT NOCOPY VARCHAR2 ,
193 x_msg_count OUT NOCOPY NUMBER ,
194 x_msg_data OUT NOCOPY VARCHAR2 ,
195 x_legal_entity_id OUT NOCOPY NUMBER ,
196 x_legal_entity_name OUT NOCOPY VARCHAR2
197 );
198
199 FUNCTION validate_po_type(p_po_type IN VARCHAR2 DEFAULT NULL,
200 p_style_id IN NUMBER DEFAULT NULL,
201 p_po_header_id IN NUMBER DEFAULT NULL
202 ) RETURN BOOLEAN;
203 /*Bug5039365 by ssawant*/
204 FUNCTION return_valid_date( p_validate_text VARCHAR2 ) RETURN DATE ;
205
206 END jai_cmn_utils_pkg;