[Home] [Help]
PACKAGE BODY: APPS.JTF_AK_QUERY
Source
1 PACKAGE BODY jtf_ak_query as
2 --$Header: jtfakqb.pls 120.1 2005/07/02 02:30:45 appldev ship $
3 FUNCTION execute_query (
4 p_max_rows IN NUMBER default NULL,
5 p_range_low IN NUMBER default 0,
6 p_range_high IN NUMBER default NULL,
7 p_appl_id IN NUMBER default fnd_global.resp_appl_id,
8 p_flow_name IN VARCHAR2,
9 p_parent_page_name IN VARCHAR2,
10 p_primary_key_name IN VARCHAR2,
11 p_key_value1 IN VARCHAR2,
12 p_key_value2 IN VARCHAR2 default NULL,
13 p_key_value3 IN VARCHAR2 default NULL,
14 p_key_value4 IN VARCHAR2 default NULL,
15 p_key_value5 IN VARCHAR2 default NULL,
16 p_key_value6 IN VARCHAR2 default NULL,
17 p_key_value7 IN VARCHAR2 default NULL,
18 p_key_value8 IN VARCHAR2 default NULL,
19 p_key_value9 IN VARCHAR2 default NULL,
20 p_key_value10 IN VARCHAR2 default NULL,
21 p_where_clause IN VARCHAR2 default NULL,
22 p_where_binds IN ak_query_pkg.bind_tab)
23 RETURN ak_query_pkg.results_table_type IS
24 --
25 -- Static CRM Navigator Variables
26 --
27 C_APPLICATION_ID NUMBER := fnd_global.resp_appl_id;
28 C_RESP_ID NUMBER := fnd_global.resp_id;
29 C_USER_ID NUMBER := fnd_global.user_id;
30
31 l_flow_code VARCHAR2(30);
32 l_page_application_id NUMBER(15);
33 l_page_code VARCHAR2(30);
34 l_parent_region_appl_id NUMBER(15);
35 l_parent_region_code VARCHAR2(30);
36 l_database_object_name VARCHAR2(30);
37 l_pk_exists NUMBER(15);
38 BEGIN
39
40 -- ====================================================================
41 -- Assumption : Developer needs to supply the following thru JTF tables
42 -- (APPLICATION ID is derived from FND_GLOBAL.RESP_APPL_ID)
43 -- FLOW NAME,
44 -- PARENT PAGE NAME,
45 -- PK NAME,
46 -- PK VALUE
47 -- for procedure to derive remaining AK query parameters.
48 -- ====================================================================
49 /*
50 here we commented out where we try to grab the flow code from the flow
51 name... we aren't dealing with names...only codes. i don't know why
52 the flow was referenced by the name, but the parent page and primary
53 key were referenced by the code. sounds a bit confused...
54
55 -- get flow id
56 select flow_code into l_flow_code
57 from ak_flows_vl
58 where flow_application_id = p_appl_id
59 and name = p_flow_name;*/
60 l_flow_code := p_flow_name;
61
62 -- get page info
63 SELECT fpr.page_application_id, fpr.page_code,
64 fpr.region_application_id, fpr.region_code,
65 fpr.database_object_name
66 INTO l_page_application_id, l_page_code,
67 l_parent_region_appl_id, l_parent_region_code,
68 l_database_object_name
69 FROM ak_flow_pages_vl fp,
70 ak_flow_page_regions_v fpr
71 WHERE fp.flow_application_id = p_appl_id
72 AND fp.flow_application_id = fpr.flow_application_id
73 AND fp.flow_code = l_flow_code
74 AND fp.flow_code = fpr.flow_code
75 AND fp.page_application_id = p_appl_id
76 AND fp.page_application_id = fpr.page_application_id
77 AND fp.page_code = p_parent_page_name
78 AND fp.page_code = fpr.page_code
79 AND fp.primary_region_code = fpr.region_code
80 AND fp.primary_region_appl_id = fpr.region_application_id;
81
82 -- check primary key name
83 -- and not checking primary key value
84 select 1 into l_pk_exists
85 from ak_unique_keys
86 where database_object_name = l_database_object_name
87 and unique_key_name = p_primary_key_name;
88
89 -- call ak_query_pkg.exec_query
90 ak_query_pkg.exec_query(
91 p_flow_appl_id => p_appl_id,
92 p_flow_code => l_flow_code,
93 p_parent_page_appl_id => l_page_application_id,
94 p_parent_page_code => l_page_code,
95 p_parent_region_appl_id => l_parent_region_appl_id,
96 p_parent_region_code => l_parent_region_code,
97 p_parent_primary_key_name => p_primary_key_name,
98 p_parent_key_value1 => p_key_value1,
99 p_parent_key_value2 => p_key_value2,
100 p_parent_key_value3 => p_key_value3,
101 p_parent_key_value4 => p_key_value4,
102 p_parent_key_value5 => p_key_value5,
103 p_parent_key_value6 => p_key_value6,
104 p_parent_key_value7 => p_key_value7,
105 p_parent_key_value8 => p_key_value8,
106 p_parent_key_value9 => p_key_value9,
107 p_parent_key_value10 => p_key_value10,
108 p_where_clause => p_where_clause,
109 p_responsibility_id => FND_GLOBAL.RESP_ID,
110 p_user_id => FND_GLOBAL.USER_ID,
111 p_return_parents => 'F',
112 p_return_children => 'T',
113 p_return_node_display_only=> 'F',
114 p_set_trace => 'F',
115 p_range_low => p_range_low,
116 p_range_high => p_range_high,
117 p_where_binds => p_where_binds,
118 p_max_rows => p_max_rows
119 );
120
121 RETURN ak_query_pkg.g_results_table;
122
123 END execute_query;
124 END jtf_ak_query;