eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JA_JAINBBR_XMLP_PKG

Source


1 PACKAGE BODY JA_JAINBBR_XMLP_PKG AS
2 /* $Header: JAINBBRB.pls 120.1 2007/12/25 16:13:44 dwkrishn noship $ */
3   FUNCTION CF_CLOSING_BALANCEFORMULA(CF_CLOSING_BALANCE IN NUMBER
4                                     ,CF_OPEN_BALANCE IN NUMBER
5                                     ,RECEIPTS IN VARCHAR2
6                                     ,PAYMENTS IN VARCHAR2) RETURN NUMBER IS
7   BEGIN
8     RETURN (NVL(CF_CLOSING_BALANCE
9               ,CF_OPEN_BALANCE) + RECEIPTS - PAYMENTS);
10   END CF_CLOSING_BALANCEFORMULA;
11 
12   FUNCTION CF_BALANCEFORMULA(RECEIPTS IN VARCHAR2
13                             ,PAYMENTS IN VARCHAR2) RETURN NUMBER IS
14   BEGIN
15     RETURN (NVL(RECEIPTS
16               ,0) - NVL(PAYMENTS
17               ,0));
18   END CF_BALANCEFORMULA;
19 
20   FUNCTION CF_ACCOUNT_CODEFORMULA(ACCOUNT_CODE IN NUMBER) RETURN VARCHAR2 IS
21     V_ACCOUNT VARCHAR2(1000);
22   BEGIN
23     JAI_CMN_GL_PKG.GET_ACCOUNT_NUMBER(P_CHART_OF_ACCTS_ID
24                                      ,ACCOUNT_CODE
25                                      ,V_ACCOUNT);
26     RETURN (V_ACCOUNT);
27   END CF_ACCOUNT_CODEFORMULA;
28 
29   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
30     CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
31       SELECT
32         CONCURRENT_PROGRAM_ID,
33         NVL(ENABLE_TRACE
34            ,'N')
35       FROM
36         FND_CONCURRENT_REQUESTS
37       WHERE REQUEST_ID = P_REQUEST_ID;
38     V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
39     V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
40   BEGIN
41     /*SRW.MESSAGE(1275
42                ,'Report Version -> 115.2, Last Updated Date -> 17/11/2004')*/NULL;
43     BEGIN
44       OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
45       FETCH C_PROGRAM_ID
46        INTO V_PROGRAM_ID,V_ENABLE_TRACE;
47       CLOSE C_PROGRAM_ID;
48       /*SRW.MESSAGE(1275
49                  ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
50       IF V_ENABLE_TRACE = 'Y' THEN
51         EXECUTE IMMEDIATE
52           'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
53       END IF;
54     EXCEPTION
55       WHEN OTHERS THEN
56         /*SRW.MESSAGE(1275
57                    ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
58     END;
59     P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
60     /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
61     LP_SET_OF_BOOKS_ID := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
62     IF P_BOOK_TYPE = 'C' THEN
63       P_DUMMY := 'Cash';
64     ELSE
65       P_DUMMY := 'Bank';
66     END IF;
67     LP_START_DATE:=to_char(P_START_DATE,'DD-MON-YYYY');
68     LP_END_DATE:=to_char(P_END_DATE,'DD-MON-YYYY');
69     LP1_START_DATE:=to_char(P_START_DATE,'DD-MON-YYYY HH24:MI:SS');
70     LP1_END_DATE:=to_char(P_END_DATE,'DD-MON-YYYY HH24:MI:SS');
71 
72     RETURN (TRUE);
73   END BEFOREREPORT;
74 
75   FUNCTION CF_1FORMULA RETURN VARCHAR2 IS
76     CURSOR FOR_SOB_ID(COA_ID IN NUMBER) IS
77       SELECT
78         SET_OF_BOOKS_ID
79       FROM
80         ORG_ORGANIZATION_DEFINITIONS
81       WHERE CHART_OF_ACCOUNTS_ID = COA_ID;
82     CURSOR FOR_SOB_NAME(SOB_ID IN NUMBER) IS
83       SELECT
84         NAME
85       FROM
86         GL_SETS_OF_BOOKS
87       WHERE SET_OF_BOOKS_ID = SOB_ID;
88     V_SET_OF_BOOKS_ID NUMBER;
89     V_SOB_NAME VARCHAR2(100);
90   BEGIN
91     OPEN FOR_SOB_ID(P_CHART_OF_ACCTS_ID);
92     FETCH FOR_SOB_ID
93      INTO V_SET_OF_BOOKS_ID;
94     CLOSE FOR_SOB_ID;
95     OPEN FOR_SOB_NAME(V_SET_OF_BOOKS_ID);
96     FETCH FOR_SOB_NAME
97      INTO V_SOB_NAME;
98     CLOSE FOR_SOB_NAME;
99     RETURN (V_SOB_NAME);
100   END CF_1FORMULA;
101 
102   FUNCTION CF_ACCT_DESCFORMULA (account_code IN NUMBER) RETURN VARCHAR2 IS
103     CURSOR GET_APP_COLUMN_NAME(CP_ID_FLEX_CODE IN FND_SEGMENT_ATTRIBUTE_VALUES.ID_FLEX_CODE%TYPE,CP_SEG_ATT_TYPE IN FND_SEGMENT_ATTRIBUTE_VALUES.SEGMENT_ATTRIBUTE_TYPE%TYPE) IS
104       SELECT
105         DISTINCT
106         APPLICATION_COLUMN_NAME
107       FROM
108         FND_SEGMENT_ATTRIBUTE_VALUES
109       WHERE APPLICATION_ID = 101
110         AND ID_FLEX_CODE = CP_ID_FLEX_CODE
111         AND ID_FLEX_NUM = P_CHART_OF_ACCTS_ID
112         AND SEGMENT_ATTRIBUTE_TYPE = CP_SEG_ATT_TYPE
113         AND ATTRIBUTE_VALUE = 'Y';
114     CURSOR FLEX_VAL_SET_ID(V_COLUMN_NAME IN VARCHAR2,CP_ID_FLEX_CODE IN FND_SEGMENT_ATTRIBUTE_VALUES.ID_FLEX_CODE%TYPE) IS
115       SELECT
116         A.FLEX_VALUE_SET_ID
117       FROM
118         FND_ID_FLEX_SEGMENTS A
119       WHERE A.APPLICATION_COLUMN_NAME = V_COLUMN_NAME
120         AND A.APPLICATION_ID = 101
121         AND A.ID_FLEX_CODE = CP_ID_FLEX_CODE
122         AND A.ID_FLEX_NUM = P_CHART_OF_ACCTS_ID;
123     V_COLUMN_NAME VARCHAR2(30);
124     V_COLUMN_VALUE VARCHAR2(30);
125     V_FLEX_ID NUMBER;
126     V_DESCRIPTION VARCHAR2(100);
127     CURSOR GET_DESCRIPTION IS
128       SELECT
129         SUBSTR(DESCRIPTION
130               ,1
131               ,50)
132       FROM
133         FND_FLEX_VALUES_VL
134       WHERE FLEX_VALUE_SET_ID = V_FLEX_ID
135         AND FLEX_VALUE = V_COLUMN_VALUE;
136   BEGIN
137     OPEN GET_APP_COLUMN_NAME('GL#','GL_ACCOUNT');
138     FETCH GET_APP_COLUMN_NAME
139      INTO V_COLUMN_NAME;
140     CLOSE GET_APP_COLUMN_NAME;
141     IF V_COLUMN_NAME IS NULL THEN
142       V_COLUMN_NAME := 'SEGMENT3';
143     END IF;
144     OPEN FLEX_VAL_SET_ID(V_COLUMN_NAME,'GL#');
145     FETCH FLEX_VAL_SET_ID
146      INTO V_FLEX_ID;
147     CLOSE FLEX_VAL_SET_ID;
148     /*EXECUTE IMMEDIATE
149       'select ' || V_COLUMN_NAME || ' into :p_column_value from gl_code_combinations
150       		where chart_of_accounts_id = :P_CHART_OF_ACCTS_ID AND code_combination_id = :account_code';*/
151     EXECUTE IMMEDIATE
152       'select ' || V_COLUMN_NAME || ' from gl_code_combinations
153       		where chart_of_accounts_id = :P_CHART_OF_ACCTS_ID AND code_combination_id = :account_code'
154 	        INTO p_column_value
155 		USING P_CHART_OF_ACCTS_ID,account_code  ;
156     V_COLUMN_VALUE := P_COLUMN_VALUE;
157     OPEN GET_DESCRIPTION;
158     FETCH GET_DESCRIPTION
159      INTO V_DESCRIPTION;
160     CLOSE GET_DESCRIPTION;
161     RETURN (V_DESCRIPTION);
162   END CF_ACCT_DESCFORMULA;
163 
164   FUNCTION CF_P_BOOKFORMULA RETURN VARCHAR2 IS
165     V_BOOK_TYPE VARCHAR2(20);
166   BEGIN
167     IF P_BOOK_TYPE = 'B' THEN
168       V_BOOK_TYPE := 'Bank Book';
169     ELSE
170       V_BOOK_TYPE := 'Cash Book';
171     END IF;
172     RETURN (V_BOOK_TYPE);
173   END CF_P_BOOKFORMULA;
174 
175   FUNCTION JA_IN_CAL_BAL(P_BASE_DATE IN DATE
176                         ,P_BALANCE_TYPE IN VARCHAR2
177                         ,P_OPEN_CLOSE IN VARCHAR2) RETURN NUMBER IS
178     V_BASE_DATE DATE;
179     V_AMOUNT1 NUMBER;
180     V_AMOUNT2 NUMBER;
181     V_AMOUNT3 NUMBER;
182     V_BALANCE NUMBER;
183     V_SET_OF_BOOKS_ID NUMBER;
184     LV_PAY_SOURCE GL_JE_HEADERS.JE_SOURCE%TYPE;
185     LV_RCV_SOURCE GL_JE_HEADERS.JE_SOURCE%TYPE;
186     LV_PAY_SOURCE1 GL_JE_HEADERS.JE_SOURCE%TYPE;
187     LV_RCV_SOURCE1 GL_JE_HEADERS.JE_SOURCE%TYPE;
188     LV_CLEAR_STATUS AR_CASH_RECEIPT_HISTORY_ALL.STATUS%TYPE;
189     LV_REMIT_STATUS AR_CASH_RECEIPT_HISTORY_ALL.STATUS%TYPE;
190     LV_CONFIRM_STATUS AR_CASH_RECEIPT_HISTORY_ALL.STATUS%TYPE;
191     LV_REV_STATUS AR_CASH_RECEIPT_HISTORY_ALL.STATUS%TYPE;
192     LV_NEGOT_LOOKUP AP_CHECKS_ALL.STATUS_LOOKUP_CODE%TYPE;
193     LV_CLEAR_LOOKUP AP_CHECKS_ALL.STATUS_LOOKUP_CODE%TYPE;
194     LV_VOIDED_LOOKUP AP_CHECKS_ALL.STATUS_LOOKUP_CODE%TYPE;
195     LV_REC_UNACC_LOOKUP AP_CHECKS_ALL.STATUS_LOOKUP_CODE%TYPE;
196     LV_REC_LOOKUP AP_CHECKS_ALL.STATUS_LOOKUP_CODE%TYPE;
197     LV_CLEAR_UNACC_LOOKUP AP_CHECKS_ALL.STATUS_LOOKUP_CODE%TYPE;
198   BEGIN
199     LV_PAY_SOURCE := 'Payables India';
200     LV_RCV_SOURCE := 'Receivables India';
201     LV_PAY_SOURCE1 := 'Payables';
202     LV_RCV_SOURCE1 := 'Receivables';
203     LV_CLEAR_STATUS := 'CLEARED';
204     LV_REMIT_STATUS := 'REMITTED';
205     LV_CONFIRM_STATUS := 'CONFIRMED';
206     LV_REV_STATUS := 'REVERSED';
207     LV_NEGOT_LOOKUP := 'NEGOTIABLE';
208     LV_CLEAR_LOOKUP := 'CLEARED';
209     LV_VOIDED_LOOKUP := 'VOIDED';
210     LV_REC_UNACC_LOOKUP := 'RECONCILED UNACCOUNTED';
211     LV_REC_LOOKUP := 'RECONCILED';
212     LV_CLEAR_UNACC_LOOKUP := 'CLEARED BUT UNACCOUNTED';
213     IF P_OPEN_CLOSE = 'OP' THEN
214       V_BASE_DATE := P_BASE_DATE;
215     END IF;
216     SELECT
217       DECODE(P_BALANCE_TYPE
218             ,'CR'
219             ,SUM(ACCOUNTED_DR)
220             ,'DR'
221             ,SUM(ACCOUNTED_CR)
222             ,0)
223     INTO V_AMOUNT2
224     FROM
225       GL_JE_HEADERS GLH,
226       GL_JE_LINES GLL,
227       CE_BANK_ACCOUNTS CBA
228     WHERE GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
229       AND GLH.LEDGER_ID = GLL.LEDGER_ID
230       AND GLL.LEDGER_ID = LP_SET_OF_BOOKS_ID
231       AND CBA.ASSET_CODE_COMBINATION_ID = GLL.CODE_COMBINATION_ID
232       AND CBA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID
233       AND GLH.JE_SOURCE NOT IN ( LV_PAY_SOURCE , LV_RCV_SOURCE , LV_PAY_SOURCE1 , LV_RCV_SOURCE1 )
234       AND GLH.DEFAULT_EFFECTIVE_DATE < V_BASE_DATE
235       AND ( CBA.ACCOUNT_OWNER_ORG_ID IS NULL
236     OR CBA.ACCOUNT_OWNER_ORG_ID = P_ORG_ID );
237     IF P_BALANCE_TYPE = 'CR' THEN
238       SELECT
239         SUM(DECODE(ACRH.STATUS
240                   ,'REVERSED'
241                   ,(ACRH.AMOUNT * NVL(ACRH.EXCHANGE_RATE
242                      ,1)) * -1
243                   ,NVL(ACRH.AMOUNT
244                      ,0) * NVL(ACRH.EXCHANGE_RATE
245                      ,1)))
246       INTO V_AMOUNT1
247       FROM
248         AR_CASH_RECEIPT_HISTORY_ALL ACRH,
249         AR_CASH_RECEIPTS_ALL ACR,
250         HZ_PARTIES HP,
251         HZ_CUST_ACCOUNTS HCA,
252         CE_BANK_ACCOUNTS CBA
253       WHERE ACRH.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
254         AND ACR.REMITTANCE_BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
255         AND HCA.PARTY_ID = hp.party_id (+)
256         AND ACR.PAY_FROM_CUSTOMER = hca.cust_account_id (+)
257         AND ACRH.STATUS IN ( LV_CLEAR_STATUS , LV_REMIT_STATUS , LV_CONFIRM_STATUS , LV_REV_STATUS )
258         AND ( ( ACRH.STATUS = LV_REV_STATUS
259         AND ACR.REVERSAL_DATE is not null )
260       OR ( ACRH.CASH_RECEIPT_HISTORY_ID IN (
261         SELECT
262           MIN(INCRH.CASH_RECEIPT_HISTORY_ID)
263         FROM
264           AR_CASH_RECEIPT_HISTORY_ALL INCRH
265         WHERE INCRH.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
266           AND INCRH.STATUS <> LV_REV_STATUS ) ) )
267         AND CBA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID
268         AND ACRH.GL_DATE < V_BASE_DATE
269         AND ( ACR.ORG_ID IS NULL
270       OR ACR.ORG_ID = P_ORG_ID );
271       SELECT
272         SUM(API.INVOICE_AMOUNT * NVL(API.EXCHANGE_RATE
273                ,1))
274       INTO V_AMOUNT3
275       FROM
276         AP_INVOICE_DISTRIBUTIONS_ALL APID,
277         AP_INVOICE_LINES_ALL APLA,
278         AP_INVOICES_ALL API,
279         PO_VENDORS POV,
280         CE_BANK_ACCOUNTS CBA
281       WHERE API.INVOICE_ID = APID.INVOICE_ID
282         AND APLA.INVOICE_ID = APID.INVOICE_ID
283         AND APLA.LINE_NUMBER = APID.INVOICE_LINE_NUMBER
284         AND API.VENDOR_ID = POV.VENDOR_ID
285         AND CBA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID
286         AND CBA.ASSET_CODE_COMBINATION_ID = APID.DIST_CODE_COMBINATION_ID
287         AND APID.MATCH_STATUS_FLAG = 'A'
288         AND APID.ACCOUNTING_DATE < V_BASE_DATE
289         AND ( CBA.ACCOUNT_OWNER_ORG_ID IS NULL
290       OR CBA.ACCOUNT_OWNER_ORG_ID = P_ORG_ID );
291       V_BALANCE := NVL(V_AMOUNT1
292                       ,0) + NVL(V_AMOUNT2
293                       ,0) + NVL(V_AMOUNT3
294                       ,0);
295     ELSIF P_BALANCE_TYPE = 'DR' THEN
296       SELECT
297         SUM(NVL(AIP.AMOUNT
298                ,0) * NVL(AIP.EXCHANGE_RATE
299                ,1))
300       INTO V_AMOUNT1
301       FROM
302         AP_INVOICE_PAYMENTS_ALL AIP,
303         AP_CHECKS_ALL APC,
304         CE_BANK_ACCOUNTS CBA
305       WHERE AIP.CHECK_ID = APC.CHECK_ID
306         AND APC.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
307         AND APC.STATUS_LOOKUP_CODE IN ( LV_NEGOT_LOOKUP , LV_CLEAR_LOOKUP , LV_VOIDED_LOOKUP , LV_REC_UNACC_LOOKUP , LV_REC_LOOKUP , LV_CLEAR_UNACC_LOOKUP )
308         AND CBA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID
309         AND AIP.ACCOUNTING_DATE < V_BASE_DATE
310         AND ( CBA.ACCOUNT_OWNER_ORG_ID IS NULL
311       OR CBA.ACCOUNT_OWNER_ORG_ID = P_ORG_ID );
312       V_BALANCE := NVL(V_AMOUNT1
313                       ,0) + NVL(V_AMOUNT2
314                       ,0);
315     END IF;
316     RETURN V_BALANCE;
317   END JA_IN_CAL_BAL;
318 
319   FUNCTION AFTERREPORT RETURN BOOLEAN IS
320   BEGIN
321     /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
322     RETURN (TRUE);
323   END AFTERREPORT;
324 
325   FUNCTION CF_OPEN_BAL_DRFORMULA RETURN NUMBER IS
326     AMT NUMBER;
327     AMT1 NUMBER;
328   BEGIN
329     RETURN (JA_IN_CAL_BAL(P_START_DATE
330                         ,'DR'
331                         ,'OP'));
332   END CF_OPEN_BAL_DRFORMULA;
333 
334   FUNCTION CF_OPEN_BAL_CRFORMULA RETURN NUMBER IS
335     AMT NUMBER;
336     AMT1 NUMBER;
337   BEGIN
338     RETURN (JA_IN_CAL_BAL(P_START_DATE
339                         ,'CR'
340                         ,'OP'));
341   END CF_OPEN_BAL_CRFORMULA;
342 
343   FUNCTION CF_OPEN_BALANCEFORMULA(CF_OPEN_BAL_CR IN NUMBER
344                                  ,CF_OPEN_BAL_DR IN NUMBER) RETURN NUMBER IS
345   BEGIN
346     RETURN (NVL(CF_OPEN_BAL_CR
347               ,0) - NVL(CF_OPEN_BAL_DR
348               ,0));
349   END CF_OPEN_BALANCEFORMULA;
350 
351 END JA_JAINBBR_XMLP_PKG;
352