1 PACKAGE asg_apply AUTHID DEFINER AS
2 /*$Header: asgaplys.pls 120.4.12020000.2 2013/04/09 11:55:23 saradhak ship $*/
3
4 -- DESCRIPTION
5 -- This package processes upload data. It also contains ltos of helper
6 -- routines meant to apply the changes efficiently.
7 --
8 -- HISTORY
9 -- 15-mar-2011 saradhak Added synchronous_process_upload api
10 -- 12-aug-2009 saradhak Added process_mobile_queries api
11 -- 15-sep-2004 ssabesan Changes for delivery notification
12 -- 28-nov-2002 ssabesan Added NOCOPY in function definition
13 -- 14-aug-2002 rsripada Added globals to store conc program's user-id etc
14 -- 29-may-2002 rsripada Streamlined some of the procedures
15 -- 24-may-2002 rsripada Added sequence processing support
16 -- 25-apr-2002 rsripada Added deferred transaction support etc
17 -- 22-feb-2002 rsripada Finalized api specifications
18 -- 19-feb-2002 rsripada Created
19
20 -- Table to store the list of usernames or publication-items
21 TYPE vc2_tbl_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
22 -- Table to store the list of tranids
23 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
24
25 g_empty_vc2_tbl vc2_tbl_type; -- Should always be empty!
26 g_empty_num_tbl num_tbl_type; -- Should always be empty!
27
28 g_current_tranid NUMBER;
29 g_user_name VARCHAR2(100);
30 g_only_deferred_trans VARCHAR2(1);
31
32 g_conc_userid NUMBER := 5;
33 g_conc_respid NUMBER := 20420;
34 g_conc_appid NUMBER := 1;
35
36 -- Logging procedure
37 PROCEDURE log(debug_msg IN VARCHAR2,
38 log_level IN NUMBER := FND_LOG.LEVEL_STATEMENT);
39
40 -- Sort the publication item list by weight stored in
41 -- asg_pub_item table
42 PROCEDURE sort_by_weight(p_pub_name IN VARCHAR2,
43 x_pub_items_tbl IN OUT NOCOPY vc2_tbl_type);
44
45 -- Returns the list of all clients with the specified inq record types
46 -- dirty for unprocessed new records
47 -- deferred for processed but deferred records.
48 -- x_return_status should be checked for FND_API.G_RET_STS_SUCCESS
49 -- before the clients list processed.
50 PROCEDURE get_all_clients(p_dirty IN VARCHAR2 := 'Y',
51 p_deferred IN VARCHAR2 := 'N',
52 x_clients_tbl OUT NOCOPY vc2_tbl_type,
53 x_return_status OUT NOCOPY VARCHAR2);
54
55 PROCEDURE get_all_tranids(p_user_name IN VARCHAR2,
56 x_tranids_tbl OUT NOCOPY num_tbl_type,
57 x_return_status OUT NOCOPY VARCHAR2);
58
59 -- get the names of all publication items that have
60 -- records for the specified tran_id and pubname
61 PROCEDURE get_all_pub_items(p_user_name IN VARCHAR2,
62 p_tranid IN NUMBER,
63 p_pubname IN VARCHAR2,
64 x_pubitems_tbl OUT NOCOPY vc2_tbl_type,
65 x_return_status OUT NOCOPY VARCHAR2);
66
67 -- get the names of all publication items that have
68 -- records for the specified tran_id
69 PROCEDURE get_all_pub_items(p_user_name IN VARCHAR2,
70 p_tranid IN NUMBER,
71 x_pubitems_tbl OUT NOCOPY vc2_tbl_type,
72 x_return_status OUT NOCOPY VARCHAR2);
73
74 -- get the names of all publication items that have only dirty
75 -- records for the specified tran_id
76 PROCEDURE get_all_dirty_pub_items(p_user_name IN VARCHAR2,
77 p_tranid IN NUMBER,
78 p_pubname IN VARCHAR2,
79 x_pubitems_tbl OUT NOCOPY vc2_tbl_type,
80 x_return_status OUT NOCOPY VARCHAR2);
81
82 -- Will set x_return_status to FND_API.G_RET_STS_ERROR if no tranid exists
83 -- Returns both dirty and deferred tranids
84 PROCEDURE get_first_tranid(p_user_name IN VARCHAR2,
85 x_tranid OUT NOCOPY NUMBER,
86 x_return_status OUT NOCOPY VARCHAR2);
87
88 -- Will set x_return_status to FND_API.G_RET_STS_ERROR if no tranid exists
89 -- Returns both dirty and deferred tranids
90 PROCEDURE get_next_tranid(p_user_name IN VARCHAR2,
91 p_curr_tranid IN NUMBER,
92 x_tranid OUT NOCOPY NUMBER,
93 x_return_status OUT NOCOPY VARCHAR2);
94
95 -- Procedure to delete a row
96 PROCEDURE delete_row(p_user_name IN VARCHAR2,
97 p_tranid IN NUMBER,
98 p_pubitem IN VARCHAR2,
99 p_sequence IN NUMBER,
100 x_return_status OUT NOCOPY VARCHAR2);
101
102 -- Procedure to purge all the dirty INQ records for
103 -- the specified user/transid/publication-item(s)
104 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
105 p_tranid IN NUMBER,
106 p_pubitems_tbl IN vc2_tbl_type,
107 x_return_status OUT NOCOPY VARCHAR2);
108
109 -- Procedure to purge all the dirty INQ records for
110 -- the specified user/transid
111 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
112 p_tranid IN NUMBER,
113 x_return_status OUT NOCOPY VARCHAR2);
114
115 -- Procedure to purge all the dirty INQ records for
116 -- the specified user
117 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
118 x_return_status OUT NOCOPY VARCHAR2);
119
120 -- Signal the beginning of inq processing for an user
121 -- returns FND_API.G_FALSE is no inq processing is necessary for this user
122 PROCEDURE begin_client_apply(p_user_name IN VARCHAR2,
123 x_begin_client_apply OUT NOCOPY VARCHAR2,
124 x_return_status OUT NOCOPY VARCHAR2);
125
126 -- Signal the end of inq processing for an user
127 -- All dirty records processed in this session that are not removed from
128 -- inq will be marked as deferred.
129 PROCEDURE end_client_apply(p_user_name IN VARCHAR2,
130 x_return_status OUT NOCOPY VARCHAR2);
131
132 -- Should be called before any user's transactions are processed
133 -- returns FND_API.G_FALSE if no user has dirty/deferred data in inq.
134 PROCEDURE begin_apply(x_begin_apply OUT NOCOPY VARCHAR2,
135 x_return_status OUT NOCOPY VARCHAR2);
136
137 -- Should be called at the end of the apply for all clients in that
138 -- session. Always returns FND_API.G_RET_STS_SUCCESS.
139 PROCEDURE end_apply(x_return_status OUT NOCOPY VARCHAR2);
140
141 -- Procedure to update the upload information
142 PROCEDURE setup_inq_info(p_user_name IN VARCHAR2,
143 p_tranid IN NUMBER,
144 x_return_status OUT NOCOPY VARCHAR2);
145
146 -- Procedure to process synchronous mobile queries from client
147 PROCEDURE process_mobile_queries(p_user_name IN VARCHAR2,
148 p_tranid IN NUMBER,
149 x_return_status OUT NOCOPY VARCHAR2);
150
151 -- Procedure to process sequence updates from client
152 PROCEDURE process_sequences(p_user_name IN VARCHAR2,
153 p_tranid IN NUMBER,
154 x_return_status OUT NOCOPY VARCHAR2);
155
156 -- Main procedure to process all upload transactions
157 -- Will be used by the concurrent program to process all users.
158 PROCEDURE process_upload(errbuf OUT NOCOPY VARCHAR2,
159 RETCODE OUT NOCOPY VARCHAR2,
160 p_mode IN VARCHAR2 );
161
162 function is_conc_program_running
163 return varchar2;
164
165 --12.1
166 PROCEDURE get_compacted_tranid(p_user_name IN VARCHAR2,
167 p_tranid IN NUMBER,
168 x_compacted_tranid OUT NOCOPY NUMBER,
169 x_return_status OUT NOCOPY VARCHAR2);
170
171 --12.1
172 PROCEDURE process_auto_sync(p_user_name IN VARCHAR2,
173 p_tranid IN NUMBER,
174 x_compacted_tranid OUT NOCOPY NUMBER,
175 x_return_status OUT NOCOPY VARCHAR2);
176
177 PROCEDURE synchronous_process_upload(curr_user IN VARCHAR2);
178
179 END asg_apply;