1 PACKAGE jai_cmn_rgm_processing_pkg AS
2 /* $Header: jai_cmn_rgm_prc.pls 120.7.12020000.3 2013/03/25 02:57:12 zxin ship $ */
3
4 /*----------------------------------------------------------------------------------------------------------------------------
5 CHANGE HISTORY for FILENAME: jai_rgm_trx_processing_pkg_s.sql
6 S.No dd/mm/yyyy Author and Details
7 ------------------------------------------------------------------------------------------------------------------------------
8 1 26/07/2004 Vijay Shankar for Bug# 4068823, Version:115.0
9
10 Package that Starts Service Tax Processing of both AP and AR transactions based on the inputs
11 provided through request of "India - Service Tax Processing" concurrent
12 2 12/04/2005 Brathod, for Bug# 4286646, Version 115.1
13 Issue :-
14 Because of change in Valueset from JA_IN_DATE to FND_STANDARD_DATE Concurrent was resulting
15 in error because JA_IN_DATE uses normal date format while FND_STANDARD_DATE uses NLS_DATE format
16 and it is passed as character value.
17 Fix :-
18 Procedure signature modified to convert p_trx_from_date, p_trx_from_date from date to
19 pv_trx_from_date, pv_trx_from_date varchar2.
20 DEPENDANCY:
21 -----------
22 IN60106 + 4068823
23
24 3. 08-Jun-2005 Version 116.1 jai_cmn_rgm_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
25 as required for CASE COMPLAINCE.
26
27 4. 05-Jul-2006 Aiyer for the bug 5369250, Version 120.3
28 Issue:-
29 --------
30 The concurrent failes with the following error :-
31 "FDPSTP failed due to ORA-01861: literal does not match format string ORA-06512: at line 1 "
32
33 Reason:-
34 ---------
35 The procedure PROCESS had a parameters p_override_invoice_date of type date , however the concurrent program
36 passes it in the canonical format and hence the failure.
37
38 Fix:-
39 -----------
40 Modified the procedure process.
41 Changed the datatype of p_override_invoice_date from date to varchar2 as this parameter.
42 Also added the new parameter ld_override_invoice_date . The value in p_override_invoice_date would be converted to date format and
43 stored in the local variable ld_override_invoice_date.
44
45 Dependency due to this fix:-
46 None
47
48 5. 02-Apr-2010 Allen Yang for bug bug 9485355 (12.1.3 non-shippable Enhancement)
49 added a cursor variable MainRec_Cur and two parameters for procedure 'process'.
50
51 6. 24-May-2011 Xiao for POT change, reg bug#12533434.
52 Fixed: Add one more procedure populate_repository to populate the transactions on accrual basis.
53 ----------------------------------------------------------------------------------------------------------------------------*/
54
55 g_debug VARCHAR2(1); --File.Sql.35 by Brathod
56 gv_called_from_dflt CONSTANT VARCHAR2(10) := 'Batch'; -- File.Sql.35 by Brathod
57
58 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
59 TYPE MainRecord IS RECORD (delivery_id JAI_RGM_INVOICE_GEN_T.delivery_id%TYPE
60 , delivery_date JAI_RGM_INVOICE_GEN_T.delivery_date%TYPE
61 , organization_id JAI_RGM_INVOICE_GEN_T.organization_id%TYPE
62 , location_id JAI_RGM_INVOICE_GEN_T.location_id%TYPE
63 , vat_invoice_no JAI_RGM_INVOICE_GEN_T.vat_invoice_no%TYPE
64 , party_id JAI_RGM_INVOICE_GEN_T.party_id%TYPE
65 , party_site_id JAI_RGM_INVOICE_GEN_T.party_site_id%TYPE
66 , party_type JAI_RGM_INVOICE_GEN_T.party_type%TYPE
67 , vat_inv_gen_status JAI_RGM_INVOICE_GEN_T.vat_inv_gen_status%TYPE
68 , vat_acct_status JAI_RGM_INVOICE_GEN_T.vat_acct_status%TYPE
69 , order_line_id JAI_RGM_INVOICE_GEN_T.order_line_id%TYPE
70 , order_number JAI_RGM_INVOICE_GEN_T.order_number%TYPE);
71
72 TYPE MainRec_Cur IS REF CURSOR;
73 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
74
75 PROCEDURE process_batch(
76 errbuf OUT NOCOPY VARCHAR2,
77 retcode OUT NOCOPY VARCHAR2,
78 p_regime_id IN NUMBER,
79 p_rgm_registration_num IN VARCHAR2,
80 pv_trx_from_date IN VARCHAR2,
81 pv_trx_till_date IN VARCHAR2,
82 p_called_from IN VARCHAR2, -- DEFAULT 'Batch' File.Sql.35 by Brathod
83 p_debug IN VARCHAR2, -- DEFAULT 'Y' File.Sql.35 by Brathod
84 p_trace_switch IN VARCHAR2, -- DEFAULT 'N' File.Sql.35 by Brathod
85 p_organization_id IN NUMBER default null --Added by kunkumar for bug#6066813
86 );
87
88 PROCEDURE process_payments(
89 p_regime_id IN NUMBER,
90 p_organization_type IN VARCHAR2,
91 p_trx_from_date IN DATE,
92 p_trx_to_date IN DATE,
93 p_org_id IN NUMBER,
94 p_batch_id IN NUMBER,
95 p_debug IN VARCHAR2,
96 p_process_flag OUT NOCOPY VARCHAR2,
97 p_process_message OUT NOCOPY VARCHAR2,
98 p_organization_id IN NUMBER default null --Added by kunkumar for bug#6066813
99 );
100
101 PROCEDURE insert_request_details(
102 p_batch_id OUT NOCOPY NUMBER,
103 p_regime_id IN NUMBER,
104 p_rgm_registration_num IN VARCHAR2,
105 p_trx_from_date IN DATE,
106 p_trx_till_date IN DATE
107 );
108
109 FUNCTION get_item_line_id(
110 p_invoice_id IN NUMBER,
111 p_po_distribution_id IN NUMBER,
112 p_rcv_transaction_id IN NUMBER
113 ) RETURN NUMBER;
114
115 /*
116 CREATED BY : ssumaith
117 CREATED DATE : 15-MAR-2005
118 ENHANCEMENT BUG : 4245053
119 PURPOSE : wrapper program to interpret the input parameters and suitably call program to
120 generate vat imvoice number and pass accounting during shipment
121 CALLED FROM : Concurrent program JAIVATP
122 */
123 PROCEDURE process (
124 retcode OUT NOCOPY VARCHAR2,
125 errbuf OUT NOCOPY VARCHAR2,
126 p_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE,
127 p_registration_num JAI_RGM_TRX_RECORDS.REGIME_PRIMARY_REGNO%TYPE,
128 p_organization_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE,
129 p_location_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
130 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
131 p_order_number_from OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE,
132 p_order_number_to OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE,
133 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
134 p_delivery_id_from JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE,
135 p_delivery_id_to JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE,
136 pv_delivery_date_from VARCHAR2, --DATE, Harshita for Bug 4918870
137 pv_delivery_date_to VARCHAR2, --DATE, Harshita for Bug 4918870
138 p_process_action VARCHAR2,
139 p_single_invoice_num VARCHAR2,
140 p_override_invoice_date VARCHAR2 , /* aiyer for the bug 5369250 */
141 p_debug VARCHAR2
142 -- code ported from BL12.1.3 by zhiwei.xin on 25-MAR-2013 begin
143 ,p_called_from VARCHAR2 DEFAULT NULL -- added by zhiwei.xin on 8-NOV-2012 for VAT Invoice Generation/Accounting
144 -- code ported from BL12.1.3 by zhiwei.xin on 25-MAR-2013 end.
145 );
146
147 --Add by Xiao for POT change, reg bug#12533434, begin
148 ------------------------------------------------------------
149 PROCEDURE populate_repository(
150 p_batch_id IN NUMBER,
151 p_regime_id IN NUMBER,
152 p_organization_type IN VARCHAR2,
153 p_from_date IN DATE,
154 p_to_date IN DATE,
155 p_org_id IN NUMBER,
156 p_organization_id IN NUMBER,
157 p_process_flag OUT NOCOPY VARCHAR2,
158 p_process_message OUT NOCOPY VARCHAR2);
159 ------------------------------------------------------------
160 --Add by Xiao for POT change, reg bug#12533434, end
161
162 --Add by Xiao for POT Phase III changes, reg bug#12895841, begin
163 ------------------------------------------------------------
164 PROCEDURE claim_reversal_trx_lines(
165 pv_source IN VARCHAR2,
166 pn_organization_id IN NUMBER,
167 pn_location_id IN NUMBER,
168 pn_invoice_id IN NUMBER,
169 pn_line_number IN NUMBER,
170 pd_date IN DATE,
171 pn_adjusted_amount IN NUMBER,
172 pv_action IN VARCHAR2,
173 pn_claim_payment_id IN NUMBER DEFAULT 0);
174 ------------------------------------------------------------
175 --Add by Xiao for POT Phase III changes, reg bug#12895841, end
176
177 -- code ported from BL12.1.3 by zhiwei.xin on 24-JAN-2013 begin
178 --Added by Qiong for POT reversal account begin
179 -----------------------------------------------
180 FUNCTION get_reversal_account_name(
181 p_regime_id IN NUMBER,
182 p_organization_type IN VARCHAR2,
183 p_organization_id IN NUMBER,
184 p_location_id IN NUMBER
185 ) RETURN VARCHAR2;
186 ------------------------------------------------
187 --Added by Qiong for POT reversal account end
188 -- code ported from BL12.1.3 by zhiwei.xin on 24-JAN-2013 end.
189
190 END jai_cmn_rgm_processing_pkg;
[Home] [Help]