[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CR_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_CR_EXPORT_PKG AS
2 --$Header: JACNCREB.pls 120.3 2010/05/20 02:45:42 weihuang noship $
3 --+=======================================================================+
4 --| Copyright (c) 2010 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================|
8 --| FILENAME |
9 --| JACNCREB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| This package contains the following PL/SQL tables/procedures |
14 --| to export customers which have transaction with current legal |
15 --| entity in given accounting year. |
16 --| |
17 --| TYPE LIEST |
18 --| |
19 --| |
20 --| PROCEDURE LIST |
21 --| Add_Customer |
22 --| |
23 --| HISTORY |
24 --| 04-Mar-2010 Wei Huang Created |
25 --| 12-Apr-2010 Wei Huang Updated procedure Add_Customer |
26 --| 19-May-2010 Wei Huang Updated procedure Add_Customer |
27 --| |
28 --+======================================================================*/
29 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_CR_EXPORT_PKG';
30
31 --==========================================================================
32 -- PROCEDURE NAME:
33 --
34 -- Add_Customer Public
35 --
36 -- DESCRIPTION:
37 --
38 -- This procedure is to export customers which have transaction
39 -- with current legal entity in given accounting year.
40 --
41 -- PARAMETERS:
42 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
43 -- pv_accounting_year Accounting Year
44 --
45 -- Out:
46 --
47 -- DESIGN REFERENCES:
48 --
49 --
50 -- CHANGE HISTORY:
51 -- 04-Mar-2010 Wei Huang Created
52 -- 12-Apr-2010 Wei Huang Updated the SQL of getting customer
53 -- information from AR Receipts
54 -- 19-May-2010 Wei Huang Updated the logic of handling NO_DATA Exception
55 --
56 --===========================================================================
57 PROCEDURE Add_Customer
58 (pn_legal_entity_id IN NUMBER
59 ,pv_accounting_year IN VARCHAR2
60 )
61 IS
62 lv_procedure_name VARCHAR2(40) := 'Add_Customer';
63 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
64 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
65 NO_DATA EXCEPTION;
66 ln_row_count NUMBER := 0;
67
68 CURSOR customer_cur
69 IS
70 SELECT DISTINCT Customer_Number,Customer_Name,Customer_Short_Name
71 FROM
72 (
73 --from AR Transction
74 SELECT
75 hp.PARTY_NUMBER Customer_Number,
76 hp.PARTY_NAME Customer_Name,
77 hp.PARTY_NAME Customer_Short_Name
78 FROM HZ_PARTIES hp ,HZ_CUST_ACCOUNTS hca
79 ,RA_CUSTOMER_TRX rcta ,RA_CUST_TRX_LINE_GL_DIST_ALL gd
80 WHERE hp.PARTY_ID = hca.PARTY_ID
81 AND hca.CUST_ACCOUNT_ID = rcta.BILL_TO_CUSTOMER_ID
82 AND rcta.CUSTOMER_TRX_ID = gd.CUSTOMER_TRX_ID
83 AND rcta.legal_entity_id = pn_legal_entity_id
84 AND rcta.COMPLETE_FLAG = 'Y'
85 AND to_char(gd.GL_DATE,'YYYY') = pv_accounting_year
86 AND gd.ACCOUNT_CLASS = 'REC'
87 AND gd.LATEST_REC_FLAG = 'Y'
88 UNION
89 -- from AR Receipts
90 SELECT
91 PARTY.PARTY_NUMBER Customer_Number,
92 PARTY.PARTY_NAME Customer_Name,
93 PARTY.PARTY_NAME Customer_Short_Name
94 FROM
95 HZ_PARTIES PARTY,
96 HZ_CUST_ACCOUNTS CUST,
97 AR_CASH_RECEIPTS_ALL CR,
98 AR_CASH_RECEIPT_HISTORY_ALL CRH_REM,
99 AR_BATCHES_ALL REM_BAT,
100 AR_BATCHES_ALL BAT,
101 AR_BATCHES_ALL BAT_BR,
102 AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED
103 WHERE
104 CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID (+)
105 AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
106 AND CRH_REM.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
107 AND CRH_REM.ORG_ID (+) = CR.ORG_ID
108 AND NOT EXISTS
109 ( SELECT CASH_RECEIPT_HISTORY_ID
110 FROM AR_CASH_RECEIPT_HISTORY_ALL CRH3
111 WHERE CRH3.STATUS = 'REMITTED'
112 AND CRH3.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
113 AND CRH3.CASH_RECEIPT_HISTORY_ID < CRH_REM.CASH_RECEIPT_HISTORY_ID)
114 AND CRH_REM.STATUS(+) = 'REMITTED'
115 AND CRH_REM.BATCH_ID = REM_BAT.BATCH_ID (+)
116 AND CRH_REM.ORG_ID = REM_BAT.ORG_ID (+)
117 AND REM_BAT.TYPE (+) = 'REMITTANCE'
118 AND CRH_FIRST_POSTED.BATCH_ID = BAT.BATCH_ID (+)
119 AND CRH_FIRST_POSTED.ORG_ID = BAT.ORG_ID (+)
120 AND BAT.TYPE (+) = 'MANUAL'
121 AND CRH_FIRST_POSTED.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
122 AND CRH_FIRST_POSTED.ORG_ID(+) = CR.ORG_ID
123 AND CRH_FIRST_POSTED.FIRST_POSTED_RECORD_FLAG(+) = 'Y'
124 AND CRH_FIRST_POSTED.BATCH_ID = BAT_BR.BATCH_ID (+)
125 AND CRH_FIRST_POSTED.ORG_ID = BAT_BR.ORG_ID (+)
126 AND BAT_BR.TYPE (+) = 'BR'
127 AND PARTY.PARTY_NUMBER IS NOT NULL
128 AND CR.Legal_Entity_Id = pn_legal_entity_id
129 AND CR.Status IN ('APP','UNAPP','UNID') --Applied, Unapplied, Unidentified
130 AND to_char(CRH_FIRST_POSTED.gl_date,'YYYY') = pv_accounting_year
131 )
132 ORDER BY Customer_Number;
133
134 BEGIN
135 --logging for debug
136 IF (ln_proc_level >= ln_dbg_level)
137 THEN
138 FND_LOG.STRING(ln_proc_level,
139 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
140 '.begin',
141 'Enter procedure');
142 -- logging the parameters
143 FND_LOG.STRING(ln_proc_level,
144 lv_procedure_name ||
145 '.parameters',
146 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
147 'pv_accounting_year=' || pv_accounting_year);
148 END IF; --ln_proc_level >= ln_dbg_level
149 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
150 '.parameters:' ||
151 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
152 'pv_accounting_year=' || pv_accounting_year);
153
154 --Open and loop the Cursor to add the data to XML file
155 FOR v_row IN customer_cur
156 LOOP
157 ln_row_count := ln_row_count + 1;
158 Ja_Cn_Utility.Add_Sub_Root_Node('CUSTOMER_RECORD',
159 Ja_Cn_Utility.GV_TAG_TYPE_START);
160 Ja_Cn_Utility.Add_Child_Node('CUSTOMER_NUMBER',
161 v_row.customer_number);
162 Ja_Cn_Utility.Add_Child_Node('CUSTOMER_NAME',
163 v_row.customer_name);
164 Ja_Cn_Utility.Add_Child_Node('CUSTOMER_SHORT_NAME',
165 v_row.customer_short_name);
166 Ja_Cn_Utility.Add_Sub_Root_Node('CUSTOMER_RECORD',
167 Ja_Cn_Utility.GV_TAG_TYPE_END);
168 END LOOP;
169
170 -- To judge if the Cursor fetchs data. If the row count is 0 and the
171 -- minOccurs=0 specified in xml schema, then need to handle it and raise exception
172 IF (ln_row_count = 0)
173 THEN
174 Ja_Cn_Utility.Print_No_Data_Found_For_Log('CUSTOMER_RECORD');
175
176 Ja_Cn_Utility.Add_Sub_Root_Node('CUSTOMER_RECORD',
177 Ja_Cn_Utility.GV_TAG_TYPE_START);
178 Ja_Cn_Utility.Add_Child_Node('CUSTOMER_NUMBER',
179 NULL);
180 Ja_Cn_Utility.Add_Child_Node('CUSTOMER_NAME',
181 NULL);
182 Ja_Cn_Utility.Add_Child_Node('CUSTOMER_SHORT_NAME',
183 NULL);
184 Ja_Cn_Utility.Add_Sub_Root_Node('CUSTOMER_RECORD',
185 Ja_Cn_Utility.GV_TAG_TYPE_END);
186
187 END IF;
188
189 --logging for debug
190 IF (ln_proc_level >= ln_dbg_level)
191 THEN
192 FND_LOG.STRING(ln_proc_level,
193 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
194 'Exit procedure');
195 END IF; -- (ln_proc_level>=ln_dbg_level)
196
197 --To handle the No Data Found Exception
198 EXCEPTION
199 WHEN OTHERS THEN
200 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
201 THEN
202 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
203 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
204 '.Other_Exception ',
205 SQLCODE || SQLERRM);
206 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
207 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
208 RAISE;
209
210 END Add_Customer;
211
212 END JA_CN_CR_EXPORT_PKG;