1 PACKAGE MSD_DEM_SR_UTIL AUTHID CURRENT_USER AS
2 /* $Header: msddemsus.pls 120.8.12020000.3 2012/10/16 10:06:33 nallkuma ship $ */
3
4 /*** CONSTANTS ***/
5
6
7 /*** PROCEDURES ***
8 * EXECUTE_REMOTE_QUERY
9 */
10
11 /*
12 * This procedure executes a query passed from a remote database.
13 */
14
15 procedure EXECUTE_REMOTE_QUERY(query IN VARCHAR2);
16
17
18 /*** FUNCTIONS ***
19 * SET_CUSTOMER_ATTRIBUTE
20 * GET_CATEGORY_SET_ID
21 * GET_CONVERSION_TYPE
22 * GET_MASTER_ORGANIZATION
23 * GET_CUSTOMER_ATTRIBUTE
24 * GET_NULL_PK
25 * GET_NULL_CODE
26 * GET_NULL_DESC
27 * GET_DEFAULT_CODE
28 * UOM_CONV
29 * IS_ITEM_OPTIONAL_FOR_LVL
30 * IS_PRODUCT_FAMILY_FORECASTABLE
31 * CONFIG_ITEM_EXISTS
32 * CONVERT_GLOBAL_AMT
33 * GET_ZONE_ATTR
34 * GET_SR_ZONE_DESC
35 * GET_SR_ZONE_PK
36 * IS_TXN_DEPOT_REPAIR
37 * GET_SERVICE_REQ_ORG_ID
38 * FIND_PARENT_ITEM
39 * FIND_BASE_MODEL
40 * IS_ITEM_OPTIONAL_FOR_FACT
41 * GET_SPF_MASTER_ORGANIZATION
42 */
43
44
45
46 /*
47 * Usability Enhancements. Bug # 3509147.
48 * This function sets the value of profile MSD_CUSTOMER_ATTRIBUTE to NONE
49 * if collecting for the first time
50 */
51 FUNCTION SET_CUSTOMER_ATTRIBUTE (
52 p_profile_code IN VARCHAR2,
53 p_profile_value IN VARCHAR2,
54 p_profile_level IN VARCHAR2)
55 RETURN NUMBER;
56
57 /*
58 * This function gets the value of the source profile MSD_DEM_CATEGORY_SET_NAME
59 */
60 FUNCTION GET_CATEGORY_SET_ID
61 RETURN NUMBER;
62
63 /*
64 * This function gets the value of the source profile MSD_DEM_CONVERSION_TYPE
65 */
66 FUNCTION GET_CONVERSION_TYPE
67 RETURN VARCHAR2;
68
69 /*
70 * This function gets the ID of the master organization in the source instance.
71 */
72 FUNCTION GET_MASTER_ORGANIZATION
73 RETURN NUMBER;
74
75 /*
76 * This function gets the value of the source profile MSD_DEM_CUSTOMER_ATTRIBUTE
77 */
78 FUNCTION GET_CUSTOMER_ATTRIBUTE
79 RETURN VARCHAR2;
80
81
82 function get_null_pk return number;
83
84 function get_null_code return VARCHAR2;
85
86 function get_null_desc return VARCHAR2;
87
88 function get_default_code return VARCHAR2;
89
90 function uom_conv (uom_code varchar2,
91 item_id number) return number;
92
93 FUNCTION IS_ITEM_OPTIONAL_FOR_LVL(p_component_item_id in NUMBER) RETURN NUMBER;
94
95
96 FUNCTION IS_PRODUCT_FAMILY_FORECASTABLE (p_org_id in NUMBER,
97 p_inventory_item_id in NUMBER,
98 p_check_optional in NUMBER) RETURN NUMBER;
99
100 FUNCTION CONFIG_ITEM_EXISTS ( p_header_id IN NUMBER,
101 p_org_id IN NUMBER,
102 p_ato_line_id IN NUMBER) RETURN NUMBER;
103
104 function convert_global_amt(p_curr_code in varchar2, p_date in date) return number;
105
106 FUNCTION get_zone_attr return varchar2; --jarora
107
108 FUNCTION get_sr_zone_desc ( p_location_id IN NUMBER,
109 p_zone_attr IN VARCHAR2) RETURN VARCHAR2; --jarora
110
111 FUNCTION get_sr_zone_pk ( p_location_id IN NUMBER,
112 p_zone_attr IN VARCHAR2) RETURN NUMBER; --jarora
113
114 FUNCTION is_txn_depot_repair(p_txn_source_id IN NUMBER) return VARCHAR2; --jarora
115
116 FUNCTION get_service_req_org_id (p_txn_source_id IN NUMBER) return NUMBER; --jarora
117
118 /* This function checks if data has to be collected for a given customer(party).
119 * Returns 1 (true) if all the customer accounts associated with the customer are enabled,
120 * returns 2 (false) if any of the customer accounts associated is disabled.
121 */
122 FUNCTION get_data_for_customer(p_party_id in number, p_cust_attribute in varchar2) return NUMBER; --syenamar
123
124 /*
125 * This function gets the parent item for the given item. If profile MSD_DEM: Calculate
126 * Planning Percentage is set to 'Yes, for "Consume & Derive" Options only' then it gets
127 * nearest parent model.
128 */
129 FUNCTION FIND_PARENT_ITEM ( p_link_to_line_id IN NUMBER,
130 p_include_class IN VARCHAR2 )
131 RETURN NUMBER;
132
133
134 /*
135 * This function get the inventory_item_id of the base model in the configuration.
136 */
137 FUNCTION FIND_BASE_MODEL ( p_top_model_line_id IN NUMBER )
138 RETURN NUMBER;
139
140
141 /*
142 * This function is called when item has ato_forecast_control = NONE.
143 * First, check whether the given component is optional component in the BOM or not.
144 * If so, then find the parent's component sequence id and then check whether the
145 * parent is either ((optional with forecast control = none) or (consume and derive))
146 */
147 FUNCTION IS_ITEM_OPTIONAL_FOR_FACT ( p_component_item_id IN NUMBER,
148 p_component_sequence_id IN NUMBER,
149 p_parent_line_id IN NUMBER )
150 RETURN NUMBER;
151
152
153 /*
154 * This function gets the value of the source profile MSD_SPF_MASTER_ORG
155 */
156 FUNCTION GET_SPF_MASTER_ORGANIZATION
157 RETURN VARCHAR2;
158
159 /*
160 * This function gets the sr_pk of base model from SR associated to given ISO line
161 */
162 FUNCTION GET_BASE_ITEM_IN_SR_FOR_LINE ( p_order_line_id in number)
163 RETURN NUMBER;
164
165
166 --Bug 14683310--kkhatri--12.2.1
167 --Bug 13716734--kkahtri-12.3
168 /* Returns whether sub inventory is bad or good */
169 FUNCTION GET_SUB_INV_TYPE ( p_order_line_id number, p_attribute_col varchar2)
170 RETURN number;
171
172 /*
173 * This function gets the transit type flag for a given visit type
174 */
175 FUNCTION GET_TRANSIT_TYPE_FLAG (p_visit_type_code IN VARCHAR2)
176 RETURN VARCHAR2;
177
178 END MSD_DEM_SR_UTIL;
[Home] [Help]