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