1 PACKAGE PA_OUTPUT_TAX as
2 /* $Header: PAXOTAXS.pls 120.6 2007/02/08 00:04:36 rmarcel ship $ */
3
4 --
5 -- This package will populate the VAT Tax Id ,Tax Exempt Flag and related
6 -- attributes of PA_DRAFT_INVOICE_ITEMS.
7 -- Procedure : GET_DEFAULT_TAX_INFO
8 -- Parameter :
9 -- P_Project_Id - Project Id
10 -- P_Draft_Inv_Num - Draft Invoice Number
11 -- P_Customer_Id - Customer Id
12 -- P_Bill_to_site_use_id - Bill to site Use id
13 -- P_Ship_to_site_use_id - Ship to Site Use id
14 -- P_Sets_of_books_id - Sets of Books Id
15 -- P_Event_id - Event Id
16 -- P_Expenditure_item_id - Expenditure Item Id
17 -- P_User_Id - User Id
18 -- P_Request_id - Request Id
19 -- X_Output_vat_tax_id - Output Vat Tax Identifier
20 -- X_Output_tax_exempt_flag - Output Tax Exemption Flag
21 -- X_Output_tax_exempt_number - Output Tax Exemption Number
22 -- X_Output_exempt_reason_code
23 -- - Output Tax exempt reason code
24 -- P_invoice_date - Invoice Date , added for bug 5484859
25 --
26 -- Note :
27 -- This procedure will call arp_tax package .
28 --
29
30 PROCEDURE GET_DEFAULT_TAX_INFO
31 ( P_Project_Id IN NUMBER ,
32 P_Draft_Inv_Num IN NUMBER ,
33 P_Customer_Id IN NUMBER ,
34 P_Bill_to_site_use_id IN NUMBER ,
35 P_Ship_to_site_use_id IN NUMBER ,
36 P_Sets_of_books_id IN NUMBER ,
37 P_Event_id IN NUMBER default NULL,
38 P_Expenditure_item_id IN NUMBER default NULL,
39 P_User_Id IN NUMBER ,
40 P_Request_id IN NUMBER ,
41 X_Output_tax_exempt_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
42 X_Output_tax_exempt_number OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
43 X_Output_exempt_reason_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
44 X_Output_tax_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
45 P_invoice_date IN DATE default sysdate); /* bug 5484859 */
46
47
48 PROCEDURE GET_DEFAULT_TAX_INFO
49 ( P_Project_Id IN NUMBER ,
50 P_Draft_Inv_Num IN NUMBER ,
51 P_Customer_Id IN NUMBER ,
52 P_Bill_to_site_use_id IN NUMBER ,
53 P_Ship_to_site_use_id IN NUMBER ,
54 P_Sets_of_books_id IN NUMBER ,
55 P_Event_id IN NUMBER default NULL,
56 P_Expenditure_item_id IN NUMBER default NULL,
57 P_User_Id IN NUMBER ,
58 P_Request_id IN NUMBER ,
59 X_Output_tax_exempt_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
60 X_Output_tax_exempt_number OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
61 X_Output_exempt_reason_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
62 X_Output_tax_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
63 Pbill_to_customer_id IN NUMBER,
64 Pship_to_customer_id IN NUMBER,
65 P_draft_inv_num_credited IN NUMBER DEFAULT NULL,
66 P_invoice_date IN DATE default sysdate); /* bug 5484859 */
67
68
69 -- Procedure : MARK_CUST_REV_DIST_LINES
70 -- Usage : This procedure will update the Customer Revenue
71 -- distribution lines with Tax info and mark it for
72 -- Invoice generation.
73 -- Parameter
74 -- P_Project_Id - Project Id
75 -- P_Draft_Inv_Num - Draft Invoice Number
76 -- P_Customer_Id - Customer Id
77 -- P_Bill_to_site_use_id - Bill to site Use id
78 -- P_Ship_to_site_use_id - Ship to Site Use id
79 -- P_Sets_of_books_id - Sets of Books Id
80 -- P_Expenditure_item_id - Expenditure Item Id (PL/SQL Table)
81 -- P_Line_num - Expenditure Line Number(PL/SQL Table)
82 -- P_User_Id - User Id
83 -- P_Request_id - Request Id
84 -- P_No_of_rec - No of input records
85 -- X_Rec_upd - No of Records Updated
86 /*Overloaded the procedure for customer account relation enhancement
87 bug 2760630 */
88 PROCEDURE MARK_CUST_REV_DIST_LINES (
89 P_Project_Id IN NUMBER ,
90 P_Draft_Inv_Num IN NUMBER ,
91 P_Customer_Id IN NUMBER ,
92 p_agreement_id IN NUMBER,
93 P_Bill_to_site_use_id IN NUMBER ,
94 P_Ship_to_site_use_id IN NUMBER ,
95 P_Sets_of_books_id IN NUMBER ,
96 P_Expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
97 P_Line_num IN PA_PLSQL_DATATYPES.IdTabTyp,
98 P_User_Id IN NUMBER ,
99 P_Request_id IN NUMBER ,
100 P_No_of_rec IN NUMBER ,
101 X_Rec_upd OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
102 P_bill_trans_currency_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
103 P_bill_trans_invoice_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
104 P_bill_trans_bill_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
105 P_invproc_invoice_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
106 P_invproc_bill_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
107 p_retention_percentage IN VARCHAR2,
108 P_status_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
109 p_invoice_date IN VARCHAR2,
110 x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
111 );
112
113 PROCEDURE MARK_CUST_REV_DIST_LINES (
114 P_Project_Id IN NUMBER ,
115 P_Draft_Inv_Num IN NUMBER ,
116 P_Customer_Id IN NUMBER ,
117 p_agreement_id IN NUMBER,
118 P_Bill_to_site_use_id IN NUMBER ,
119 P_Ship_to_site_use_id IN NUMBER ,
120 P_Sets_of_books_id IN NUMBER ,
121 P_Expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
122 P_Line_num IN PA_PLSQL_DATATYPES.IdTabTyp,
123 P_User_Id IN NUMBER ,
124 P_Request_id IN NUMBER ,
125 P_No_of_rec IN NUMBER ,
126 X_Rec_upd OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
127 P_bill_trans_currency_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
128 P_bill_trans_invoice_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
129 P_bill_trans_bill_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
130 P_invproc_invoice_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
131 P_invproc_bill_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
132 p_retention_percentage IN VARCHAR2,
133 P_status_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
134 p_invoice_date IN VARCHAR2,
135 Pbill_to_customer_id IN NUMBER,
136 Pship_to_customer_id IN NUMBER,
137 P_shared_funds_consumption IN NUMBER, /* Federal */
138 P_expenditure_item_date IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Federal */
139 x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
140 );
141
142 -- Function : IS_AR_INSTALLED
143 -- Usage : This function will return Y if AR is installed other
144 -- wise return N.
145 -- Parameter :
146 -- P_Check_prod_installed -Flag to check only the product installation.
147 -- P_Check_org_installed -Flag to check the product installation
148 -- in a particular org.
149
150 Function IS_AR_INSTALLED( P_Check_prod_installed in varchar2 default 'N',
151 P_Check_org_installed in varchar2 default 'N')
152 return Varchar2;
153
154 -- Function : GET_DRAFT_INVOICE_TAX_AMT
155 -- Usage : This function will return 0 if invoice is not inter
156 -- faced to AR, otherwise return tax amount for that
157 -- invoice.
158 -- Parameter :
159 -- P_Trx_Id -Customer Transaction Identifier
160
161 Function GET_DRAFT_INVOICE_TAX_AMT( P_Trx_Id in NUMBER )
162 return Number;
163
164 --
165 -- This procedure will populate the VAT Tax Id ,Tax Exempt Flag and related
166 -- attributes of Expenditure Items in array.
167 -- Procedure : GET_DEFAULT_TAX_INFO
168 -- Parameter :
169 -- P_Project_Id - Project Id
170 -- P_Customer_Id - Customer Id
171 -- P_Bill_to_site_use_id - Bill to site Use id
172 -- P_Ship_to_site_use_id - Ship to Site Use id
173 -- P_Sets_of_books_id - Sets of Books Id
174 -- P_Expenditure_item_id - Expenditure Item Id
175 -- P_User_Id - User Id
176 -- P_Request_id - Request Id
177 -- P_Compute_flag - Whether Computation is required or not
178 -- P_Error_code - Error Code
179 -- X_Output_vat_tax_id - Output Vat Tax Identifier
180 -- X_Output_tax_exempt_flag - Output Tax Exemption Flag
181 -- X_Output_tax_exempt_number - Output Tax Exemption Number
182 -- X_Output_exempt_reason_code
183 -- - Output Tax exempt reason code
184 --
185 -- Note :
186 -- This procedure will call GET_DEFAULT_TAX_INFO procedure.
187 --
188 PROCEDURE GET_DEFAULT_TAX_INFO_ARR
189 ( P_Project_Id IN number ,
190 P_Customer_Id IN number ,
191 P_Bill_to_site_use_id IN number ,
192 P_Ship_to_site_use_id IN number ,
193 P_Set_of_books_id IN number ,
194 P_Expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
195 P_User_Id IN NUMBER ,
196 P_Request_id IN NUMBER ,
197 P_No_of_records IN NUMBER ,
198 P_Compute_flag IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
199 P_Error_Code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
200 X_Output_tax_code OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
201 X_Output_tax_exempt_flag OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
202 X_Output_tax_exempt_number OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
203 X_Output_exempt_reason_code OUT NOCOPY PA_PLSQL_DATATYPES.Char80TabTyp,
204 Pbill_to_customer_id IN NUMBER DEFAULT NULL,
205 Pship_to_customer_id IN NUMBER DEFAULT NULL);
206
207 FUNCTION TAXID_2_CODE_CONV (p_org_id in number,
208 p_tax_id in number)
209 return varchar2;
210 pragma RESTRICT_REFERENCES (TAXID_2_CODE_CONV, WNDS);
211
212 procedure get_legal_entity_id (p_customer_id IN NUMBER,
213 p_org_id IN NUMBER,
214 p_transaction_type_id IN NUMBER,
215 p_batch_source_id IN NUMBER,
216 x_legal_entity_id OUT NOCOPY NUMBER,
217 x_return_status OUT NOCOPY VARCHAR2);
218
219 procedure get_btch_src_trans_type ( p_project_id IN NUMBER,
220 p_draft_invoice_num IN NUMBER,
221 p_draft_inv_num_credited IN NUMBER,
222 x_transaction_type_id out NOCOPY number,
223 x_batch_source_id out NOCOPY number,
224 x_return_status OUT NOCOPY varchar2);
225
226 END PA_OUTPUT_TAX;