1 PACKAGE ZX_VTX_USER_PKG AUTHID CURRENT_USER AS
2 /* $Header: zxvtxuserpkgs.pls 120.9 2006/09/14 21:15:54 svaze ship $ */
3
4 /*These are two global variables which are used to hold the transaction date and transaction type id
5 Since these are derived at the header level and are reused at the header level they have been made
6 global*/
7
8 g_trx_date ZX_LINES_DET_FACTORS.TRX_DATE%TYPE;
9 g_trx_type_id ZX_LINES_DET_FACTORS.RECEIVABLES_TRX_TYPE_ID%TYPE;
10 g_line_negation BOOLEAN;
11 g_trx_line_id ZX_LINES_DET_FACTORS.TRX_ID%TYPE;
12 G_MESSAGES_TBL ZX_TAX_PARTNER_PKG.messages_tbl_type;
13 err_count number :=0;
14
15 /*The following are the nested table declartions to collect all the records
16 fetch from the main cursor defined in Derive_Line_Ext_Attr procedure .*/
17
18 TYPE org_id_table IS TABLE OF ZX_LINES_DET_FACTORS.Internal_Organization_Id%TYPE
19 INDEX BY BINARY_INTEGER;
20 TYPE application_id_table IS TABLE OF ZX_LINES_DET_FACTORS.Application_Id%TYPE
21 INDEX BY BINARY_INTEGER;
22 TYPE entity_code_table IS TABLE OF ZX_LINES_DET_FACTORS.entity_code%TYPE
23 INDEX BY BINARY_INTEGER;
24 TYPE event_class_code_table IS TABLE OF ZX_LINES_DET_FACTORS.event_class_code%TYPE
25 INDEX BY BINARY_INTEGER;
26 TYPE trx_id_table IS TABLE OF ZX_LINES_DET_FACTORS.trx_id%TYPE
27 INDEX BY BINARY_INTEGER;
28 TYPE trx_provider_id_table IS TABLE OF ZX_USER_PROC_INPUT_V.Tax_Provider_Id%TYPE
29 INDEX BY BINARY_INTEGER;
30 TYPE tax_regime_code_table IS TABLE OF ZX_USER_PROC_INPUT_V.Tax_Regime_Code%TYPE
31 INDEX BY BINARY_INTEGER;
32 TYPE trx_line_type_table IS TABLE OF ZX_LINES_DET_FACTORS.Trx_Line_Type%TYPE
33 INDEX BY BINARY_INTEGER;
34 TYPE trx_line_id_table IS TABLE OF ZX_LINES_DET_FACTORS.trx_line_id%TYPE
35 INDEX BY BINARY_INTEGER;
36 TYPE product_id_table IS TABLE OF ZX_LINES_DET_FACTORS.product_id%TYPE
37 INDEX BY BINARY_INTEGER;
38 TYPE product_org_id_table IS TABLE OF ZX_LINES_DET_FACTORS.product_org_id%TYPE
39 INDEX BY BINARY_INTEGER;
40 TYPE ship_to_party_tx_prf_id_table IS TABLE OF ZX_LINES_DET_FACTORS.ship_to_party_tax_prof_id%TYPE
41 INDEX BY BINARY_INTEGER;
42 TYPE exempt_cert_number_table IS TABLE OF ZX_LINES_DET_FACTORS.exempt_certificate_number%TYPE
43 INDEX BY BINARY_INTEGER;
44 TYPE exempt_control_flag_table IS TABLE OF ZX_LINES_DET_FACTORS.Exemption_Control_Flag%TYPE
45 INDEX BY BINARY_INTEGER;
46 TYPE exempt_reason_code_table IS TABLE OF ZX_LINES_DET_FACTORS.exempt_reason_code%TYPE
47 INDEX BY BINARY_INTEGER;
48 TYPE ship_to_site_tax_prof_table IS TABLE OF ZX_LINES_DET_FACTORS.ship_to_site_tax_prof_id%TYPE
49 INDEX BY BINARY_INTEGER;
50 TYPE ship_from_prty_tx_prf_id_table IS TABLE OF ZX_LINES_DET_FACTORS.Ship_from_Party_Tax_Prof_Id%TYPE
51 INDEX BY BINARY_INTEGER;
52 TYPE ship_to_location_id_table IS TABLE OF ZX_LINES_DET_FACTORS.ship_to_location_id%TYPE
53 INDEX BY BINARY_INTEGER;
54 TYPE ship_to_address_id_table IS TABLE OF ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE
55 INDEX BY BINARY_INTEGER;
56 TYPE ship_to_party_id_table IS TABLE OF ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE
57 INDEX BY BINARY_INTEGER;
58 TYPE arp_trx_line_type_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE1%TYPE
59 INDEX BY BINARY_INTEGER;
60 TYPE arp_product_code_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE2%TYPE
61 INDEX BY BINARY_INTEGER;
62 TYPE arp_exempt_cert_number_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE3%TYPE
63 INDEX BY BINARY_INTEGER;
64 TYPE arp_state_exempt_reason_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE4%TYPE
65 INDEX BY BINARY_INTEGER;
66 TYPE arp_county_exempt_reason_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE5%TYPE
67 INDEX BY BINARY_INTEGER;
68 TYPE arp_city_exempt_reason_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE6%TYPE
69 INDEX BY BINARY_INTEGER;
70 TYPE arp_district_exempt_rs_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE7%TYPE
71 INDEX BY BINARY_INTEGER;
72 TYPE arp_audit_flag_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE8%TYPE
73 INDEX BY BINARY_INTEGER;
74 TYPE arp_ship_to_add_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE9%TYPE
75 INDEX BY BINARY_INTEGER;
76 TYPE arp_ship_from_add_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE10%TYPE
77 INDEX BY BINARY_INTEGER;
78 TYPE arp_poa_add_code_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE11%TYPE
79 INDEX BY BINARY_INTEGER;
80 TYPE arp_customer_code_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE12%TYPE
81 INDEX BY BINARY_INTEGER;
82 TYPE arp_customer_class_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE13%TYPE
83 INDEX BY BINARY_INTEGER;
84 TYPE arp_company_code_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE14%TYPE
85 INDEX BY BINARY_INTEGER;
86 TYPE arp_division_code_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE15%TYPE
87 INDEX BY BINARY_INTEGER;
88 TYPE arp_transaction_date_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_DATE_ATTRIBUTE1%TYPE
89 INDEX BY BINARY_INTEGER;
90 TYPE arp_state_exempt_percent_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE1%TYPE
91 INDEX BY BINARY_INTEGER;
92 TYPE arp_county_exempt_pct_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE2%TYPE
93 INDEX BY BINARY_INTEGER;
94 TYPE arp_city_exempt_pct_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE3%TYPE
95 INDEX BY BINARY_INTEGER;
96 TYPE arp_district_exempt_pct_table IS TABLE OF ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE4%TYPE
97 INDEX BY BINARY_INTEGER;
98 TYPE ship_to_site_use_table IS TABLE OF ZX_LINES_DET_FACTORS.SHIP_TO_CUST_ACCT_SITE_USE_ID%TYPE
99 INDEX BY BINARY_INTEGER;
100 TYPE bill_to_site_use_table IS TABLE OF ZX_LINES_DET_FACTORS.BILL_TO_CUST_ACCT_SITE_USE_ID%TYPE
101 INDEX BY BINARY_INTEGER;
102 TYPE bill_to_site_tax_prof_table IS TABLE OF ZX_LINES_DET_FACTORS.BILL_TO_PARTY_TAX_PROF_ID%TYPE
103 INDEX BY BINARY_INTEGER;
104 TYPE bill_to_location_id_table IS TABLE OF ZX_LINES_DET_FACTORS.BILL_TO_LOCATION_ID%TYPE
105 INDEX BY BINARY_INTEGER;
106 TYPE bill_to_party_tax_id_table IS TABLE OF ZX_LINES_DET_FACTORS.BILL_TO_PARTY_TAX_PROF_ID%TYPE
107 INDEX BY BINARY_INTEGER;
108 TYPE bill_third_pty_acct_id_table IS TABLE OF ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%TYPE
109 INDEX BY BINARY_INTEGER;
110 TYPE hq_site_tax_prof_id_tab IS TABLE OF ZX_LINES_DET_FACTORS.TRADING_HQ_SITE_TAX_PROF_ID%TYPE
111 INDEX BY BINARY_INTEGER;
112 TYPE hq_party_tax_prof_id_tab IS TABLE OF ZX_LINES_DET_FACTORS.TRADING_HQ_PARTY_TAX_PROF_ID%TYPE
113 INDEX BY BINARY_INTEGER;
114 TYPE line_level_action_table IS TABLE OF ZX_LINES_DET_FACTORS.LINE_LEVEL_ACTION%TYPE
115 INDEX BY BINARY_INTEGER;
116 TYPE exemption_control_flag_table IS TABLE OF ZX_LINES_DET_FACTORS.EXEMPTION_CONTROL_FLAG%TYPE
117 INDEX BY BINARY_INTEGER;
118 TYPE adjusted_doc_trx_id_table IS TABLE OF ZX_LINES_DET_FACTORS.ADJUSTED_DOC_TRX_ID%TYPE
119 INDEX BY BINARY_INTEGER;
120 TYPE line_amount_table IS TABLE OF ZX_LINES_DET_FACTORS.LINE_AMT%TYPE
121 INDEX BY BINARY_INTEGER;
122 TYPE adj_doc_appl_id_table IS TABLE OF ZX_LINES_DET_FACTORS.ADJUSTED_DOC_APPLICATION_ID%TYPE
123 INDEX BY BINARY_INTEGER;
124 TYPE adj_doc_entity_code_table IS TABLE OF ZX_LINES_DET_FACTORS.ADJUSTED_DOC_ENTITY_CODE%TYPE
125 INDEX BY BINARY_INTEGER;
126 TYPE adj_evnt_cls_code_table IS TABLE OF ZX_LINES_DET_FACTORS.ADJUSTED_DOC_EVENT_CLASS_CODE%TYPE
127 INDEX BY BINARY_INTEGER;
128 TYPE adj_doc_line_id_table IS TABLE OF ZX_LINES_DET_FACTORS.ADJUSTED_DOC_LINE_ID%TYPE
129 INDEX BY BINARY_INTEGER;
130 TYPE adj_doc_trx_level_type_table IS TABLE OF ZX_LINES_DET_FACTORS.ADJUSTED_DOC_TRX_LEVEL_TYPE%TYPE
131 INDEX BY BINARY_INTEGER;
132 TYPE ship_third_pty_site_table IS TABLE OF ZX_LINES_DET_FACTORS.SHIP_THIRD_PTY_ACCT_SITE_ID%TYPE
133 INDEX BY BINARY_INTEGER;
134 TYPE bill_third_pty_site_table IS TABLE OF ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_SITE_ID%TYPE
135 INDEX BY BINARY_INTEGER;
136
137 TYPE exemptions_info_rec IS RECORD (
138 certificate_number ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE3%TYPE,
139 state_exempt_reason ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE4%TYPE,
140 county_exempt_reason ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE5%TYPE,
141 city_exempt_reason ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE6%TYPE,
142 district_exempt_reason ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE7%TYPE,
143 state_exempt_pct ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE1%TYPE,
144 county_exempt_pct ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE2%TYPE,
145 city_exempt_pct ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE3%TYPE,
146 district_exempt_pct ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE4%TYPE,
147 default_exempt_pct ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE4%TYPE
148 );
149 TYPE exemptions_info_table IS TABLE OF exemptions_info_rec
150 INDEX BY BINARY_INTEGER;
151
152 --The following are the variable declarations for all the nested tables above(one per nested table)
153
154 internal_org_id_tab org_id_table ;
155 application_id_tab application_id_table;
156 entity_code_tab entity_code_table;
157 event_class_code_tab event_class_code_table;
158 trx_id_tab trx_id_table;
159 tax_provider_id_tab trx_provider_id_table;
160 tax_regime_code_tab tax_regime_code_table;
161 trx_line_type_tab trx_line_type_table;
162 trx_line_id_tab trx_line_id_table;
163 product_id_tab product_id_table;
164 Product_Org_Id_tab product_org_id_table;
165 ship_to_tx_id_tab ship_to_party_tx_prf_id_table;
166 ship_from_tx_id_tab ship_from_prty_tx_prf_id_table;
167 cert_num_tab exempt_cert_number_table;
168 exmpt_rsn_code_tab exempt_reason_code_table;
169 exemption_control_flag_tab exemption_control_flag_table;
170
171 ship_to_site_tax_prof_tab ship_to_site_tax_prof_table;
172 ship_to_loc_id_tab ship_to_location_id_table;
173 exmpt_control_flg_tab exempt_control_flag_table;
174 arp_trx_line_type_tab arp_trx_line_type_table ;
175 arp_product_code_tab arp_product_code_table;
176 arp_audit_flag_tab arp_audit_flag_table;
177 arp_ship_to_add_tab arp_ship_to_add_table;
178 arp_ship_from_add_tab arp_ship_from_add_table;
179 arp_poa_add_code_tab arp_poa_add_code_table;
180 arp_customer_code_tab arp_customer_code_table;
181 arp_customer_class_tab arp_customer_class_table;
182 arp_company_code_tab arp_company_code_table;
183 arp_division_code_tab arp_division_code_table;
184 arp_transaction_date_tab arp_transaction_date_table;
185 ship_to_address_id_tab ship_to_address_id_table;
186 ship_to_party_id_tab ship_to_party_id_table;
187
188 arp_state_exempt_reason_tab arp_state_exempt_reason_table ;
189 arp_county_exempt_reason_tab arp_county_exempt_reason_table;
190 arp_city_exempt_reason_tab arp_city_exempt_reason_table;
191 arp_district_exempt_rs_tab arp_district_exempt_rs_table;
192 arp_state_exempt_percent_tab arp_state_exempt_percent_table;
193 arp_county_exempt_pct_tab arp_county_exempt_pct_table;
194 arp_city_exempt_pct_tab arp_city_exempt_pct_table;
195 arp_district_exempt_pct_tab arp_district_exempt_pct_table;
196 ship_to_site_use_tab ship_to_site_use_table;
197 bill_to_site_use_tab bill_to_site_use_table;
198 bill_to_site_tax_prof_tab bill_to_site_tax_prof_table;
199 bill_to_party_tax_id_tab bill_to_party_tax_id_table;
200 bill_to_location_id_tab bill_to_location_id_table;
201 trad_hq_site_tax_prof_id_tab hq_site_tax_prof_id_tab;
202 trad_hq_party_tax_prof_id_tab hq_party_tax_prof_id_tab;
203 bill_third_pty_acct_id_tab bill_third_pty_acct_id_table;
204 line_level_action_tab line_level_action_table;
205 adjusted_doc_trx_id_tab adjusted_doc_trx_id_table;
206 line_amount_tab line_amount_table;
207 exemptions_info_tab exemptions_info_table;
208 adj_doc_appl_id_tab adj_doc_appl_id_table;
209 adj_doc_entity_code_tab adj_doc_entity_code_table;
210 adj_evnt_cls_code_tab adj_evnt_cls_code_table;
211 adj_doc_line_id_tab adj_doc_line_id_table;
212 adj_doc_trx_level_type_tab adj_doc_trx_level_type_table;
213 ship_third_pty_site_tab ship_third_pty_site_table;
214 bill_third_pty_site_tab bill_third_pty_site_table;
215
216
217
218
219
220 PROCEDURE Derive_Hdr_Ext_Attr(
221 x_error_status OUT NOCOPY VARCHAR2,
222 x_messages_tbl OUT NOCOPY ZX_TAX_PARTNER_PKG.messages_tbl_type);
223 PROCEDURE Derive_Line_Ext_Attr(
224 x_error_status OUT NOCOPY VARCHAR2,
225 x_messages_tbl OUT NOCOPY ZX_TAX_PARTNER_PKG.messages_tbl_type);
226
227 END ZX_VTX_USER_PKG;
[Home] [Help]