1 PACKAGE AMS_Party_Mkt_Seg_Loader_PVT AS
2 /* $Header: amsvldrs.pls 120.1 2005/06/27 05:41:33 appldev ship $ */
3
4
5
6 /*****************************************************************************/
7 -- Define a PL sql table to hold the Discoverer SQL Query
8 -- Added by ptendulk on May03/2000
9
10 TYPE sql_rec_type IS TABLE OF VARCHAR2(2000)
11 INDEX BY BINARY_INTEGER;
12
13 /******************************************************************************/
14 --PL\SQL table to hold the Partyids returned by execution for dbms_sql
15 /******************************************************************************/
16
17 TYPE t_party_tab is TABLE OF NUMBER
18 INDEX BY BINARY_INTEGER;
19
20
21 -- Start of Comments
22 --
23 -- NAME
24 -- Refresh_Party_Market_Segment
25 --
26 -- PURPOSE
27 -- This procedure is created to as a concurrent program which
28 -- will call the load_party_mkt_seg and will return errors if any
29 --
30 -- NOTES
31 --
32 --
33 -- HISTORY
34 -- 05/02/1999 ptendulk created
35 -- End of Comments
36
37 PROCEDURE Refresh_Party_Market_Segment
38 (errbuf OUT NOCOPY VARCHAR2,
39 retcode OUT NOCOPY NUMBER,
40 p_cell_id IN NUMBER DEFAULT NULL);
41
42
43 /*****************************************************************************/
44 -- Procedure
45 -- load_party_mkt_seg
46 -- Purpose
47 -- load ams_party_market_segments
48 -- History
49 -- 01/16/2000 julou created
50 -- 05/02/2000 ptendulk Modified , Added Parameters for Handling errors
51 -- Parameters added : x_return_status,x_msg_data,x_msg_count
52 -------------------------------------------------------------------------------
53 PROCEDURE Load_Party_Mkt_Seg
54 ( p_cell_id IN NUMBER DEFAULT NULL,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2
58 );
59
60
61 -- Start of Comments
62 --
63 -- NAME
64 -- Validate_Sql
65 --
66 -- PURPOSE
67 -- This procedure is created to validate the discoverer sql created for
68 -- the Cells . It will follow the following steps :
69 -- 1. Check If the sql length is less than 32k , If it's less than 32k
70 -- process and execute it as native sql or use dynamic sql
71 -- 2. Check for the party id between SELECT and FROM of the SQL string
72 -- 3. Substitue the party id for every thing between select and from
73 -- 4. Execute the query
74 --
75 -- It will return the Parameters as
76 -- 1. x_query : This table will have the discoverer sql query
77 -- 2. x_sql_type : It will return 'NATIVE' if the sql is Native SQL
78 -- or it will return 'DYNAMIC'
79 -- NOTES
80 --
81 --
82 -- HISTORY
83 -- 05/02/1999 ptendulk created
84 -- End of Comments
85
86 PROCEDURE Validate_Sql
87 (p_workbook_name IN VARCHAR2,
88 p_workbook_owner IN VARCHAR2,
89 p_worksheet_name IN VARCHAR2,
90 p_cell_name IN VARCHAR2,
91 x_query OUT NOCOPY sql_rec_type,
92 x_sql_type OUT NOCOPY VARCHAR2,
93
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2);
97
98
99 /*****************************************************************************/
100 -- Procedure
101 -- Refresh_Segment_Size
102 --
103 -- Purpose
104 -- This procedure is created to as a concurrent program which
105 -- will call the update_segment_size and will return errors if any
106 --
107 -- Notes
108 --
109 --
110 -- History
111 -- 04/09/2001 yxliu created
112 -- 06/20/2001 yxliu moved from AMS_Cell_Pvt package
113 ------------------------------------------------------------------------------
114
115 PROCEDURE Refresh_Segment_Size
116 ( errbuf OUT NOCOPY VARCHAR2,
117 retcode OUT NOCOPY NUMBER,
118 p_cell_id IN NUMBER DEFAULT NULL
119 );
120
121
122 /*****************************************************************************
123 * NAME
124 * LOAD_PARTIES_FOR_MARKET_QUALIFIERS
125 *
126 * PURPOSE
127 * This procedure is a concurrent program to
128 * generate party list that matches a given territory's qualifiers and buying group
129 *
130 * NOTES
131 *
132 * HISTORY
133 * 10/04/2001 yzhao created
134 */
135
136 PROCEDURE LOAD_PARTY_MARKET_QUALIFIER
137 (errbuf OUT NOCOPY VARCHAR2,
138 retcode OUT NOCOPY NUMBER,
139 /* yzhao 07/17/2002 fix bug 2410322 - UPG1157:9I:AMS PACKAGE/PACKAGE BODY MISMATCHES
140 p_terr_id IN NUMBER,
141 p_bg_id IN NUMBER);
142 */
143 p_terr_id IN NUMBER := NULL,
144 p_bg_id IN NUMBER := NULL);
145
146
147 END AMS_Party_Mkt_Seg_Loader_PVT;