1 PACKAGE EC_INBOUND_STAGE AS
2 -- $Header: ECISTGS.pls 120.4 2005/09/29 10:38:28 arsriniv ship $
3 /*#
4 * This package contains routines to copy data from the incoming flat file to the staging tables.
5 * @rep:scope internal
6 * @rep:product EC
7 * @rep:lifecycle active
8 * @rep:displayname Inbound Staging Program
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY EC_INBOUND
11 */
12
13 /**
14 Global Variables
15 **/
16 g_record_num_start number := 92;
17 g_common_key_length number := 100;
18 g_record_num_length number := 4;
19 /* bug2110652 */
20 g_source_charset varchar2(30);
21
22 /**
23 Record Type for storing the Information Specific to a Level
24 i.e. Start record Number, Document id , Document Number , Key Column name etc.
25 **/
26 TYPE start_rec is RECORD
27 (
28 start_record_number number(15),
29 Document_Id number(15),
30 Line_Number number(15),
31 Parent_Stage_Id number(15),
32 Insert_Cursor number(15),
33 Transaction_type varchar2(40),
34 Run_Id number(15),
35 Document_Number varchar2(200),
36 Status varchar2(40),
37 Stage_Id number(15),
38 Key_Column_Name varchar2(80),
39 key_column_position number(4),
40 primary_address_type ece_interface_tables.primary_address_type%TYPE,
41 tp_code ece_tp_headers.tp_code%TYPE
42 );
43
44 -- TYPE Stage_Record_Type is TABLE of stage_record index by BINARY_INTEGER; -- mguthrie
45 TYPE Level_Info is TABLE of start_rec INDEX by BINARY_INTEGER;
46
47 /**
48 This is the Main Staging Program.
49 For a given transaction , and the Inbound File information i.e. File name
50 and File Path , it loads the Flat File into the Staging table. There is no
51 checking done for the data , and is loaded according to the Mapping
52 information seeded for a transaction.
53 **/
54 /*#
55 * This is the main procedure to copy data from the incoming flat file to the staging tables.
56 * No validation is performed on the data. The data is loaded according to the seeded map provided for the transaction.
57 * @param i_transaction_type Transaction Type
58 * @param i_file_name Flat File Name
59 * @param i_file_path File Path of the Inbound File
60 * @param i_map_id Map Id of the Map Used
61 * @param i_run_id Run Id of the Inbound Process
62 * @rep:scope internal
63 * @rep:lifecycle active
64 * @rep:displayname Load Incoming Data To Staging Tables
65 * @rep:compatibility S
66 */
67
68 procedure Load_Data
69 (
70 i_transaction_type IN varchar2,
71 i_file_name IN varchar2,
72 i_file_path IN varchar2,
73 i_map_id IN number, -- mguthrie
74 i_run_id OUT NOCOPY number
75 );
76
77 /**
78 This Function returns the Boolean True or False for a match between the
79 Record Number read from the File and the Record Number seeded for the
80 transaction. If the match is found , then it returns back the number of
81 Data elements present , and the Cursor Position in the line upto which
82 the Data has been read.
83 **/
84
85 function match_record_num
86 (
87 i_current_level IN number,
88 i_record_num IN number,
89 i_file_pos OUT NOCOPY number,
90 i_next_file_pos IN OUT NOCOPY number,
91 i_total_rec_unit OUT NOCOPY number
92 ) return boolean;
93
94 /**
95 After a successful match of record number between the Line Read from
96 FlatFile and the seeded data , the Line is loaded into the PL/SQL
97 table. The PL/SQL table is defined as a Local variable in the Body
98 of the package and is accessible to the Functions and Procedures
99 inside the package body only.
100 **/
101 procedure load_data_from_file
102 (
103 i_file_pos IN number,
104 i_total_rec_unit IN number,
105 c_current_line IN OUT NOCOPY varchar2 -- Added OUT NOCOPY as per bug:4555935
106 );
107
108 /**
109 This procedures loads the mapping information between the Flat File
110 and the Staging table. This information is seeded in the ECE_INTERFACE_TABLES
111 and ECE_INTERFACE_COLUMNS. The mapping information is loaded into the Local Body
112 PL/SQL table variable for a given transaction Type and its level. This PL/SQL table
113 loaded with Mapping information is visible only to the functions and procedures
114 defined within this package.
115 **/
116 procedure populate_flatfile_mapping
117 (
118 i_transaction_type in varchar2,
119 i_level in number,
120 i_map_id IN number -- mguthrie
121 );
122
123 /**
124 The Data loaded in the Local PL/SQL table is inserted into the Staging table.
125 This procedures takes Transaction Level and the Cursor handle as the parameter.
126 The Cursor handle is passed as 0 in the First call , and the subsequent calls
127 uses the Cursor Handle returned by the Procedure. This helps in avoiding the
128 expensive parsing of the SQL Statement again and again for the Same level.
129 **/
130 procedure Insert_Into_Stage_Table
131 (
132 i_level IN NUMBER,
133 i_map_id IN NUMBER, -- mguthrie
134 i_insert_cursor IN OUT NOCOPY NUMBER
135 );
136
137 procedure find_pos
138 (
139 i_level IN number,
140 i_search_text IN varchar2,
141 o_pos OUT NOCOPY NUMBER,
142 i_required IN BOOLEAN DEFAULT TRUE
143 );
144
145 procedure get_tp_code
146 (
147 i_translator_code in varchar2,
148 i_location_code in varchar2,
149 i_address_type in varchar2,
150 i_transaction_type in varchar2,
151 i_tp_code OUT NOCOPY varchar2
152 );
153
154 end EC_INBOUND_STAGE;
[Home] [Help]