1 PACKAGE QA_FLEX_UTIL AS
2 /* $Header: qltutlfb.pls 120.3.12000000.1 2007/01/19 07:20:02 appldev ship $ */
3
4 --
5 -- The following two functions mimic item() and locator(), but do not
6 -- generate exception if ID is not found. This emulates outer
7 -- join condition. Used by qa_results_full_v for 21 CFR Part 11
8 -- Compliance project (ERES). Tracking bug 3071511.
9 --
10 FUNCTION item2(p_org_id NUMBER, p_item_id NUMBER) RETURN VARCHAR2;
11 FUNCTION locator2(p_org_id NUMBER, p_locator_id NUMBER) RETURN VARCHAR2;
12
13 FUNCTION item(x_org_id number, x_item_id number) return varchar2;
14 FUNCTION get_item_id (x_org_id number, x_item VARCHAR2) return NUMBER;
15 -- anagarwa Wed Sep 19 17:49:08 PDT 2001
16 PROCEDURE get_item_category_val (p_org_id NUMBER,
17 p_item_val VARCHAR2 default null,
18 p_item_id NUMBER default null,
19 x_category_val OUT NOCOPY VARCHAR2,
20 x_category_id OUT NOCOPY NUMBER);
21
22 FUNCTION locator(x_org_id number, x_locator_id number) return varchar2;
23 FUNCTION get_locator_id (x_org_id number, x_locator VARCHAR2) return NUMBER;
24
25 --
26 -- The following are APIs that facilitate performance tuning. They
27 -- are mostly used in views to get rid of outer joins to complex tables.
28 --
29
30 --
31 -- Return project_number from pjm_projects_all_v given a project ID.
32 --
33 FUNCTION project_number(x_id number) RETURN varchar2;
34
35 --
36 -- Return Sales Order Number given a Sales Order ID.
37 --
38 FUNCTION sales_order(x_id number) RETURN varchar2;
39
40 --
41 -- Return RMA Number given a Sales Order ID.
42 --
43 FUNCTION rma_number(x_id number) RETURN number;
44
45 --
46 -- Return contract_number from oke_k_headers_full_v given a contract ID.
47 --
48 FUNCTION contract_number(x_id number) RETURN varchar2;
49
50 --
51 -- Return contract_line_number from oke_k_lines_full_v given a contract line ID.
52 --
53 FUNCTION contract_line_number(x_id number) RETURN varchar2;
54
55 --
56 -- Return deliverable_number from oke_k_deliverables_vl given a deliverable ID.
57 --
58 FUNCTION deliverable_number(x_id number) RETURN varchar2;
59
60
61 FUNCTION work_order (x_org_id NUMBER, x_work_order_id number)
62 RETURN VARCHAR2;
63
64 --
65 -- Return the result_column_name from qa_plan_chars given a plan_id
66 -- and a char_id. Return null if not found.
67 --
68 FUNCTION qpc_result_column_name(x_plan_id number, x_char_id number)
69 RETURN varchar2;
70
71 --
72 -- Return qpc_values_exist_flag (an integer) from qa_plan_chars given a
73 -- plan_id and a char_id. Return null if not found.
74 --
75 FUNCTION qpc_values_exist_flag(x_plan_id number, x_char_id number)
76 RETURN number;
77
78 cached_qpc_plan_id number := -1;
79 cached_qpc_char_id number := -1;
80 cached_qpc_result_column_name varchar2(60);
81 cached_qpc_values_exist_flag number;
82
83
84 --
85 -- Return plan_id from qa_criteria_headers given a criteria_id.
86 -- Return null if not found.
87 --
88 FUNCTION qch_plan_id(x_criteria_id number) RETURN number;
89
90 --
91 -- Return description from mtl_categories_kfv given category_id.
92 -- Return null if not found.
93 --
94 FUNCTION mtl_categories_description(x_category_id number) RETURN varchar2;
95
96
97 --
98 -- Derive project id given an LPN. Used internally.
99 -- bso Wed Mar 13 16:53:35 PST 2002
100 --
101 FUNCTION get_project_id_from_lpn(
102 p_org_id NUMBER,
103 p_lpn_id NUMBER) RETURN NUMBER;
104
105 --
106 -- Derive task id given an LPN. Used internally.
107 -- bso Wed Mar 13 16:53:35 PST 2002
108 --
109 FUNCTION get_task_id_from_lpn(
110 p_org_id NUMBER,
111 p_lpn_id NUMBER) RETURN NUMBER;
112
113
114
115 --
116 -- Derive project number given an LPN. Used by WMS/QA mobile integration.
117 -- See /qadev/qa/51.0/11.5.8/wms_pjm_dld.txt
118 -- bso Wed Mar 13 16:53:35 PST 2002
119 --
120 PROCEDURE get_project_number_from_lpn(
121 p_org_id NUMBER,
122 p_lpn_id NUMBER,
123 x_project_number OUT NOCOPY VARCHAR2);
124
125
126 --
127 -- Derive task number given an LPN. Used by WMS/QA mobile integration.
128 -- See /qadev/qa/51.0/11.5.8/wms_pjm_dld.txt
129 -- bso Wed Mar 13 16:53:35 PST 2002
130 --
131 PROCEDURE get_task_number_from_lpn(
132 p_org_id NUMBER,
133 p_lpn_id NUMBER,
134 x_task_number OUT NOCOPY VARCHAR2);
135
136
137 FUNCTION get_vendor_site_id(p_vendor_site VARCHAR2) RETURN NUMBER;
138
139 --
140 -- Return project ID from pjm_projects_all_v given a project number.
141 --
142 FUNCTION get_project_id(p_project_number VARCHAR2) RETURN NUMBER;
143
144 --
145 -- Return task ID from mtl_task_v given a project ID and task number.
146 --
147 FUNCTION get_task_id(p_project_id NUMBER,p_task_number VARCHAR2) RETURN NUMBER;
148
149
150 -- Bug 3096256.
151 -- This procedures returns the Subinventory of an LPN given the LPN_ID
152 -- from wms_license_plate_numbers. This procedure is called from
153 -- getSubinventoryFromLPN() method in ContextElementTable.java.
154 -- For RCV/WMS Enhancements. kabalakr Mon Aug 25 04:12:48 PDT 2003.
155
156 PROCEDURE get_subinventory_from_lpn(
157 p_lpn_id NUMBER,
158 x_subinventory OUT NOCOPY VARCHAR2);
159
160
161 -- Bug 3096256.
162 -- This procedures returns the Locator of an LPN given the LPN_ID
163 -- from wms_license_plate_numbers. This procedure is called from
164 -- getLocatorFromLPN() method in ContextElementTable.java.
165 -- For RCV/WMS Enhancements. kabalakr Mon Aug 25 04:12:48 PDT 2003.
166
167 PROCEDURE get_locator_from_lpn(
168 p_org_id NUMBER,
169 p_lpn_id NUMBER,
170 x_locator OUT NOCOPY VARCHAR2);
171
172 --dgupta: Start R12 EAM Integration. Bug 4345492
173 FUNCTION get_asset_group_name (org_id IN NUMBER, value IN NUMBER)
174 RETURN VARCHAR2;
175 --dgupta: End R12 EAM Integration. Bug 4345492
176
177 --pragma restrict_references (default,WNDS);
178
179
180 --
181 -- Bug 4958739. R12 Performance fixes.
182 -- New utility function for getting qa_lookup meaning.
183 -- srhariha. Mon Jan 30 01:25:38 PST 2006
184 --
185 FUNCTION get_qa_lookups_meaning (p_lookup_type IN VARCHAR2,
186 p_lookup_code IN VARCHAR2)
187 RETURN VARCHAR2;
188
189 --
190 -- Bug 5279941.
191 -- New utility function for getting the asset instance
192 -- name from the asset instance Number
193 -- ntungare Wed Jun 21 01:45:43 PDT 2006
194 --
195 FUNCTION get_asset_instance_name (p_asset_instance_number IN VARCHAR2)
196 RETURN VARCHAR2;
197
198 end QA_FLEX_UTIL;
[Home] [Help]