1 PACKAGE EC_OUTBOUND_STAGE AS
2 -- $Header: ECOSTGS.pls 120.2 2005/09/29 11:03:17 arsriniv ship $
3
4 /**
5 Global Variables
6 **/
7
8 counter number :=0;
9 i_document_number number :=0;
10
11 u_file_handle utl_file.file_type;
12
13 /**
14 Global Common Key Constants: these can be changed if the common key specifications
15 change or become parameters in future versions.
16 **/
17 g_rec_num_ln number := 4;
18 g_rec_num_fl varchar2(1) := '0';
19
20 g_rec_lcd_ln number := 2;
21 g_rec_lcd_fl varchar2(1) := fnd_global.local_chr(32); -- space
22
23 g_rec_lql_ln number := 3;
24 g_rec_lql_fl varchar2(1) := fnd_global.local_chr(32); -- space
25
26 g_rec_appd_fl varchar2(1) := fnd_global.local_chr(32); -- space
27
28 g_rec_ckey_ln number := 91;
29 g_rec_ckey_fl varchar2(1) := fnd_global.local_chr(32); -- space
30
31 g_tp_ckey_ln number := 25;
32 g_tp_ckey_fl varchar2(1) := fnd_global.local_chr(32); -- space
33
34 /**
35 The reference key lengths can be changed to a length of 13
36 so that at least 5 levels of reference keys can fit in a common key
37 of length 91 and tp code length of 25:
38 **/
39 -- g_ref_ckey_ln number := 13;
40 g_ref_ckey_ln number := 22;
41 g_ref_ckey_fl varchar2(1) := fnd_global.local_chr(32); -- space
42
43 /**
44 Record Type for storing the Information Specific to a Level
45 i.e. Start record Number, Document id , Document Number , Key Column name etc.
46 **/
47 TYPE start_rec is RECORD
48 (
49 start_record_number ece_interface_columns.record_number%TYPE,
50 total_records number(15),
51 Document_Id number(15),
52 Line_Number number(15),
53 Parent_Stage_Id number(15),
54 Select_Cursor number(15),
55 Transaction_type ece_interface_tables.transaction_type%TYPE,
56 Run_Id number(15),
57 Document_Number varchar2(200),
58 Status varchar2(40),
59 Stage_Id number(15),
60 Key_Column_Name ece_interface_tables.key_column_name%TYPE,
61 key_Column_Position number(4),
62 Key_Column_Staging ece_interface_columns.staging_column%TYPE,
63 primary_address_type ece_interface_tables.primary_address_type%TYPE,
64 tp_code_staging ece_interface_columns.staging_column%TYPE
65 );
66
67 TYPE Level_Info IS TABLE OF start_rec INDEX by BINARY_INTEGER;
68
69 /**
70 Record Type for storing the Information Specific to a Record Number
71 i.e. record Number, position , width, and SQL select statment etc.
72 **/
73 TYPE all_rec is RECORD
74 (
75 record_number ece_interface_columns.record_number%TYPE,
76 external_level ece_external_levels.external_level%TYPE,
77 start_record_number ece_interface_columns.record_number%TYPE,
78 counter number(15),
79 select_stmt varchar2(32000)
80 );
81
82 TYPE Record_Info IS TABLE OF all_rec INDEX by BINARY_INTEGER;
83
84 /**
85 This is the Main Staging Program.
86 For a given transaction, and the Outbound File information i.e. File name
87 and File Path , it extracts the Staging table data into the Flat File. There is no
88 checking done for the data, and it is extracted according to the Mapping
89 information seeded for a transaction.
90 **/
91 PROCEDURE Get_Data
92 (
93 i_transaction_type IN varchar2,
94 i_file_name IN varchar2,
95 i_file_path IN varchar2,
96 i_map_id IN number,
97 i_run_id IN number
98 );
99
100 /**
101 This procedures fetches the staging data in the proper hierarchecal order by recursively
102 calling itself using the current records STAGE_ID = PARENT_STAGE_ID. It also calls the
103 procedures to format the common key and populate the flat file with the stage data.
104 Calling this procedure recursively guarantees that the flat file will be formatted in order
105 as long as the relationship between the STAGE_ID and the PARENT_STAGE_ID is populated
106 correctly by the OUTBOUND ENGINE regardless of the order they were populated.
107 **/
108 procedure Fetch_Stage_Data
109 (
110 i_transaction_type in varchar2,
111 i_run_id in number,
112 i_parent_stage_id IN number,
113 i_stage_cursor IN OUT NOCOPY number,
114 i_common_key IN OUT NOCOPY varchar2
115 );
116
117 /**
118 This procedures loads the mapping information between the Flat File
119 and the Staging table. This information is seeded in the ECE_INTERFACE_TABLES
120 and ECE_INTERFACE_COLUMNS. The mapping information is loaded into the Local Body
121 PL/SQL table variable for a given transaction Type and its level. This PL/SQL table
122 loaded with Mapping information is visible only to the functions and procedures
123 defined within this package.
124 **/
125 procedure Populate_Flatfile_Mapping
126 (
127 i_transaction_type in varchar2,
128 i_level in number,
129 i_map_id IN number
130 );
131 /**
132 This procedure formats the main body of a SELECT statement for each record number of
133 a given transaction and saves the result in a local PL/SQL table for later parsing.
134 This procedure is called once for each record number regardless of the number of
135 columns in the staging table in order to save on the number of PL/SQL string operations
136 required
137 **/
138 PROCEDURE Get_Select_Stmt
139 (
140 i_current_level IN NUMBER,
141 i_record_num IN number,
142 i_file_pos IN number,
143 i_next_file_pos IN OUT NOCOPY number,
144 i_total_rec_unit IN number
145 );
146
147 /**
148 The Data is extracted from the Staging table using loaded in the Local PL/SQL table.
149 This procedures uses Transaction Level and cursor handle as parameters to parse a SQL
150 statement.
151 The Cursor handle is passed as 0 in the First call , and the subsequent calls
152 use the Cursor Handle returned by the Procedure. This helps in avoiding the
153 expensive parsing of the SQL Statement again and again for the Same level.
154 **/
155 procedure Select_From_Stage_Table
156 (
157 i_level IN NUMBER,
158 i_stage_id IN NUMBER,
159 i_select_cursor IN OUT NOCOPY NUMBER,
160 i_common_key IN OUT NOCOPY VARCHAR2
161 );
162 /**
163 This procedure formats the common key for each level of a given transaction. It takes the previous common key
164 string and formats it according to the level before concatenting the new KEY COLUMN on to the end of it.
165 NOTE: all common key variables (eg: length, fill character) are defined as global variables and can be
166 changed if the common key specifications change or become parameters in future versions.
167 Additionally, the call to this procedure can be commented out if NO common key is desired. This provides a
168 modest increase in perfomance and a decrease in flat file size.
169 **/
170 procedure Select_Common_Key
171 (
172 i_level IN NUMBER,
173 i_tp_code IN VARCHAR2,
174 i_key_column IN VARCHAR2,
175 i_common_key IN OUT NOCOPY VARCHAR2
176 );
177
178 end EC_OUTBOUND_STAGE;
[Home] [Help]