1 package jai_ar_rgm_processing_pkg
2 /* $Header: jai_ar_rgm_proc.pls 120.6 2011/11/28 10:23:38 qioliu ship $ */
3
4 /***********************************************************************************************************************************************
5 Created By : aiyer
6 Created Date : 27-jan-2005
7 Enhancement Bug : 4146634
8 Purpose : Process the Service Tax AR records (Invoices,Credit memo's and Cash Receipts Applications) and populate
9 the jai_rgm_trx_refs and jai_rgms_trx_records appropriately.
10 Called From : jai_rgm_trx_processing.process_batch
11
12 Dependency Due To The Current Bug :
13 This object has been newly created with AUTHID CURRENT_USER as a part of the service tax enhancement.
14 Needs to be always released along with the bug 4146708.
15
16 Change History: -
17 =================
18 1 20-Feb-2005 aiyer - Bug # 4193633 - File Version# 115.1
19 Issue
20 The tax earned and unearned discount are not getting apportioned properly of service type of taxes and hence the India - Service Tax concurrent
21 ends up in a warning for records with these issues
22
23 Fix
24 The procedure get_ar_tax_disc_accnt has been modified for the fix of this bug.
25 Please refer the change history of the package body for the details of this bug
26
27 Dependency Due To This Bug:
28 Dependency exists due to specification change of the current procedure.
29 Always sent the following packages together:-
30
31 1. jai_rgm_process_ar_taxes_pkg_s.sql (115.1)
32 2. jai_rgm_process_ar_taxes_pkg_b.sql (115.1)
33 3. jai_rgm_trx_recording_pkg_s.sql version (115.1)
34 4. jai_rgm_trx_recording_pkg_b.sql version (115.1)
35
36
37 2. 08-Jun-2005 Version 116.1 jai_ar_rgm_proc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
38 as required for CASE COMPLAINCE.
39
40
41 3. 14-May-2005 CSahoo for bug#5879769. File Version 120.3
42 Forward porting of 11i BUG#5694855
43 SERVICE TAX BY INVENTORY ORGANIZATION AND SERVICE TYPE SOLUTION
44
45 4. 14-Mar-2011 ER 11821537
46 Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
47 Service Tax Liability arises on creation of Invoice and not on Receipt of Payment from Customer
48 i.e. Accounting changes to Accrual basis from Cash Basis
49 Fix: Added procedure populate_repository
50 5. 21-Nov-2011 Qiong for Advanced Receipts
51
52 Future Dependencies For the release Of this Object:-
53 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
54 A datamodel change )
55
56 ----------------------------------------------------------------------------------------------------------------------------------------------------
57 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
58 Of File On Bug/Patchset
59 jai_rgm_process_ar_taxes_pkg_b.sql
60 ----------------------------------------------------------------------------------------------------------------------------------------------------
61 115.1 4146634 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug
62 4146708 for SERVICE/CESS enhancement release
63
64 ----------------------------------------------------------------------------------------------------------------------------------------------------
65 ********************************************************************************************************************************************************/
66
67 AS
68
69 procedure get_regime_info ( p_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE ,
70 p_tax_type_code JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE ,
71 p_regime_id OUT NOCOPY JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
72 p_error_flag OUT NOCOPY VARCHAR2 ,
73 p_error_message OUT NOCOPY VARCHAR2
74 );
75
76 procedure get_ar_tax_disc_accnt ( p_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE ,
77 p_org_id AR_RECEIVABLES_TRX_ALL.ORG_ID%TYPE ,
78 p_total_disc_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ,
82 p_unearned_disc_ccid OUT NOCOPY AR_RECEIVABLES_TRX_ALL.CODE_COMBINATION_ID%TYPE ,
79 p_tax_ediscounted OUT NOCOPY AR_RECEIVABLE_APPLICATIONS_ALL.TAX_EDISCOUNTED%TYPE ,
80 p_earned_disc_ccid OUT NOCOPY AR_RECEIVABLES_TRX_ALL.CODE_COMBINATION_ID%TYPE ,
81 p_tax_uediscounted OUT NOCOPY AR_RECEIVABLE_APPLICATIONS_ALL.TAX_UEDISCOUNTED%TYPE ,
83 p_process_flag OUT NOCOPY VARCHAR2 ,
84 p_process_message OUT NOCOPY VARCHAR2
85 );
86
87 procedure populate_inv_cm_references ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
88 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
89 p_from_date IN DATE ,
90 p_to_date IN DATE ,
91 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
92 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
93 p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
94 p_process_flag OUT NOCOPY VARCHAR2 ,
95 p_process_message OUT NOCOPY VARCHAR2,
96 p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL
97 );
98
99 procedure delete_non_existant_cm ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
100 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
101 p_from_date IN DATE ,
102 p_to_date IN DATE ,
103 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
104 p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
105 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
106 p_process_flag OUT NOCOPY VARCHAR2 ,
107 p_process_message OUT NOCOPY VARCHAR2
108 ,p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL );
109
110 procedure populate_cm_app ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
111 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
112 p_from_date IN DATE ,
113 p_to_date IN DATE ,
114 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
115 p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
116 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
117 p_process_flag OUT NOCOPY VARCHAR2 ,
118 p_process_message OUT NOCOPY VARCHAR2
119 ,p_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL );
120
121 procedure populate_receipt_records ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
122 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
123 p_from_date IN DATE ,
124 p_to_date IN DATE ,
125 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
126 p_source IN varchar2, --File.Sql.35 Cbabu JAI_CONSTANTS.SOURCE_AR%TYPE ,
127 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
128 p_process_flag OUT NOCOPY VARCHAR2 ,
129 p_process_message OUT NOCOPY VARCHAR2
130 ,p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL);
131 /*Bug 11821537 - Start*/
132 PROCEDURE populate_repository (p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
133 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
134 p_from_date IN DATE ,
135 p_to_date IN DATE ,
136 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
137 p_source IN varchar2 ,
138 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
139 p_process_flag OUT NOCOPY VARCHAR2 ,
140 p_process_message OUT NOCOPY VARCHAR2 ,
141 p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL) ;
142 /*Bug 11821537 - End*/
143 procedure process_records ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
144 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
145 p_from_date IN DATE ,
146 p_to_date IN DATE ,
147 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
148 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
149 p_process_flag OUT NOCOPY VARCHAR2 ,
150 p_process_message OUT NOCOPY VARCHAR2 ,
151 p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL /*5879769*/
152 );
153 /*Added by Wenqiong for Advanced Receipt on Service Taxes Begin*/
154 PROCEDURE process_st_advance_receipts (
155 p_event IN VARCHAR2,
156 p_acra IN AR_CASH_RECEIPTS_ALL%ROWTYPE Default NULL,
157 p_araa IN AR_RECEIVABLE_APPLICATIONS_ALL%ROWTYPE Default NULL,
158 p_process_flag OUT NOCOPY VARCHAR2,
159 p_process_message OUT NOCOPY VARCHAR2
160 );
161 PROCEDURE generate_documents (
162 p_acra IN AR_CASH_RECEIPTS_ALL%ROWTYPE,
163 p_araa IN AR_RECEIVABLE_APPLICATIONS_ALL%ROWTYPE Default NULL,
164 p_document_type IN VARCHAR2 ,
165 p_process_flag OUT NOCOPY VARCHAR2 ,
166 p_process_message OUT NOCOPY VARCHAR2
167 );
168 PROCEDURE generate_cm_dm(p_rila IN ra_interface_lines_all%ROWTYPE,
169 p_rida IN ra_interface_distributions_all%ROWTYPE,
170 pn_regime_id IN NUMBER,
171 pn_organization_id IN NUMBER,
172 pn_location_id IN NUMBER,
173 pv_tax_type IN VARCHAR2,
174 p_process_flag OUT NOCOPY VARCHAR2 ,
175 p_process_message OUT NOCOPY VARCHAR2
176 );
177 PROCEDURE generate_gl_entry(
178 p_jacr IN JAI_AR_CASH_RECEIPTS_ALL%ROWTYPE ,
179 p_acra IN AR_CASH_RECEIPTS_ALL%ROWTYPE ,
180 p_araa IN AR_RECEIVABLE_APPLICATIONS_ALL%ROWTYPE Default Null ,
181 p_document_type VARCHAR2 ,
182 pn_regime_id IN NUMBER,
183 pn_organization_id IN NUMBER,
184 pn_location_id IN NUMBER,
185 pv_tax_type IN VARCHAR2,
186 pn_tran_tax_amount IN NUMBER,
187 pn_func_tax_amount IN NUMBER,
188 p_process_flag OUT NOCOPY VARCHAR2 ,
189 p_process_message OUT NOCOPY VARCHAR2
190 ) ;
191 PROCEDURE validate_receipts(p_acra IN ar_cash_receipts_all%ROWTYPE,
192 p_document_type IN VARCHAR2,
193 p_process_flag OUT NOCOPY VARCHAR2,
194 p_process_message OUT NOCOPY VARCHAR2);
195 PROCEDURE validate_app_unapp(p_araa IN ar_receivable_applications_all%ROWTYPE,
196 p_document_type OUT NOCOPY VARCHAR2,
197 p_process_flag OUT NOCOPY VARCHAR2,
198 p_process_message OUT NOCOPY VARCHAR2);
199 /*Added by Wenqiong for Advanced Receipt on Service Taxes End*/
200
201 END jai_ar_rgm_processing_pkg ;