[Home] [Help]
PACKAGE BODY: APPS.JA_JAINBOER_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINBOER_XMLP_PKG AS
2 /* $Header: JAINBOERB.pls 120.1 2007/12/25 16:14:15 dwkrishn noship $ */
3 FUNCTION CF_BOE_CLOSINGFORMULA(BOE_AMOUNT IN NUMBER
4 ,AMOUNT_APPLIED IN NUMBER
5 ,WRITE_OFF_AMOUNT IN NUMBER) RETURN VARCHAR2 IS
6 CLOSING_AMT NUMBER;
7 BEGIN
8 CLOSING_AMT := NVL(BOE_AMOUNT
9 ,0) - NVL(AMOUNT_APPLIED
10 ,0) - NVL(P_TEMP_AMT
11 ,0) - NVL(WRITE_OFF_AMOUNT
12 ,0);
13 IF CLOSING_AMT = 0 THEN
14 RETURN ('NIL');
15 ELSE
16 RETURN (TO_CHAR(CLOSING_AMT
17 ,'FM99999999999990D00'));
18 END IF;
19 RETURN NULL;
20 END CF_BOE_CLOSINGFORMULA;
21 FUNCTION CF_ACCOUNTFORMULA(CHART_OF_ACCOUNTS_ID IN NUMBER
22 ,CUSTOMS_WRITE_OFF_ACCOUNT IN NUMBER) RETURN VARCHAR2 IS
23 V_ACCOUNT VARCHAR2(1000);
24 BEGIN
25 JAI_CMN_GL_PKG.GET_ACCOUNT_NUMBER(CHART_OF_ACCOUNTS_ID
26 ,CUSTOMS_WRITE_OFF_ACCOUNT
27 ,V_ACCOUNT);
28 RETURN (V_ACCOUNT);
29 END CF_ACCOUNTFORMULA;
30 FUNCTION CF_TEMP_CALFORMULA(BOE_ID IN NUMBER
31 ,WRITE_OFF_AMOUNT IN NUMBER) RETURN NUMBER IS
32 BEGIN
33 IF P_TEMP_ID <> BOE_ID THEN
34 P_TEMP_AMT := BALANCE(BOE_ID);
35 P_TEMP_ID := BOE_ID;
36 P_TEMP_AMT1 := WRITE_OFF_AMOUNT;
37 ELSE
38 P_TEMP_AMT := P_TEMP_AMT + P_TEMP_AMT1;
39 P_TEMP_AMT1 := WRITE_OFF_AMOUNT;
40 END IF;
41 RETURN (1);
42 END CF_TEMP_CALFORMULA;
43 FUNCTION BALANCE(V_BOE_ID IN NUMBER) RETURN NUMBER IS
44 CURSOR FOR_INITIAL_AMOUNT IS
45 SELECT
46 SUM(NVL(WRITE_OFF_AMOUNT
47 ,0))
48 FROM
49 JAI_CMN_BOE_WRITEOFFS
50 WHERE BOE_ID = V_BOE_ID
51 AND TRUNC(WRITE_OFF_DATE) < TRUNC(P_START_DATE)
52 ORDER BY
53 WRITE_OFF_DATE;
54 V_AMOUNT NUMBER;
55 BEGIN
56 OPEN FOR_INITIAL_AMOUNT;
57 FETCH FOR_INITIAL_AMOUNT
58 INTO V_AMOUNT;
59 CLOSE FOR_INITIAL_AMOUNT;
60 RETURN (NVL(V_AMOUNT
61 ,0));
62 END BALANCE;
63 FUNCTION CF_SOB_NAMEFORMULA RETURN VARCHAR2 IS
64 CURSOR FOR_SOB_ID(COA_ID IN NUMBER) IS
65 SELECT
66 SET_OF_BOOKS_ID
67 FROM
68 ORG_ORGANIZATION_DEFINITIONS
69 WHERE CHART_OF_ACCOUNTS_ID = COA_ID;
70 CURSOR FOR_SOB_NAME(SOB_ID IN NUMBER) IS
71 SELECT
72 NAME
73 FROM
74 GL_SETS_OF_BOOKS
75 WHERE SET_OF_BOOKS_ID = SOB_ID;
76 V_SET_OF_BOOKS_ID NUMBER;
77 V_SOB_NAME VARCHAR2(100);
78 BEGIN
79 OPEN FOR_SOB_ID(P_CHART_OF_ACCOUNTS_ID);
80 FETCH FOR_SOB_ID
81 INTO V_SET_OF_BOOKS_ID;
82 CLOSE FOR_SOB_ID;
83 OPEN FOR_SOB_NAME(V_SET_OF_BOOKS_ID);
84 FETCH FOR_SOB_NAME
85 INTO V_SOB_NAME;
86 CLOSE FOR_SOB_NAME;
87 RETURN (V_SOB_NAME);
88 END CF_SOB_NAMEFORMULA;
89 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
90 CURSOR FOR_REPORT_DATA IS
91 SELECT
92 HDR.BOE_TYPE,
93 HDR.BOE_ID,
94 HDR.BOE_AMOUNT,
95 HDR.AMOUNT_APPLIED,
96 WRT.WRITE_OFF_AMOUNT,
97 WRT.WRITE_OFF_DATE,
98 WRT.WRITE_OFF_REASON,
99 ORG.ORGANIZATION_NAME,
100 ORG.ORGANIZATION_NAME HDR_ORG,
101 LOC.LOCATION_CODE,
102 JHOU.CUSTOMS_WRITE_OFF_ACCOUNT,
103 LOC.ADDRESS_LINE_1,
104 LOC.ADDRESS_LINE_2,
105 LOC.ADDRESS_LINE_3,
106 GCC.CHART_OF_ACCOUNTS_ID
107 FROM
108 JAI_CMN_BOE_HDRS HDR,
109 JAI_CMN_BOE_WRITEOFFS WRT,
110 ORG_ORGANIZATION_DEFINITIONS ORG,
111 HR_LOCATIONS LOC,
112 JAI_CMN_INVENTORY_ORGS JHOU,
113 GL_CODE_COMBINATIONS GCC
114 WHERE HDR.BOE_ID = WRT.BOE_ID
115 AND HDR.ORGANIZATION_ID = ORG.ORGANIZATION_ID
116 AND HDR.LOCATION_ID = LOC.LOCATION_ID
117 AND HDR.ORGANIZATION_ID = JHOU.ORGANIZATION_ID
118 AND HDR.LOCATION_ID = JHOU.LOCATION_ID
119 AND HDR.BOE_ID = NVL(P_BOE_NO
120 ,HDR.BOE_ID)
121 AND HDR.BOE_TYPE = NVL(P_BOE_TYPE
122 ,HDR.BOE_TYPE)
123 AND TRUNC(WRT.WRITE_OFF_DATE) BETWEEN TRUNC(P_START_DATE)
124 AND TRUNC(P_END_DATE)
125 AND GCC.CODE_COMBINATION_ID = JHOU.CUSTOMS_WRITE_OFF_ACCOUNT
126 ORDER BY
127 HDR.BOE_ID,
128 WRT.WRITE_OFF_DATE,
129 HDR.BOE_TYPE;
130 V_PREVIOUS_BOE_ID NUMBER := -1;
131 V_PR_ELIER_WRTEN_OFF_AMOUNT NUMBER := 0;
132 V_EARLIER_WRITTEN_OFF_AMOUNT NUMBER := 0;
133 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
134 SELECT
135 CONCURRENT_PROGRAM_ID,
136 NVL(ENABLE_TRACE
137 ,'N')
138 FROM
139 FND_CONCURRENT_REQUESTS
140 WHERE REQUEST_ID = P_REQUEST_ID;
141 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
142 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
143 BEGIN
144 /*SRW.MESSAGE(1275
145 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
146 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
147 P_START_DATE1 := TO_CHAR(P_START_DATE,'DD-MON-YYYY');
148 P_END_DATE1 := TO_CHAR(P_END_DATE,'DD-MON-YYYY');
149 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
150 BEGIN
151 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
152 FETCH C_PROGRAM_ID
153 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
154 CLOSE C_PROGRAM_ID;
155 /*SRW.MESSAGE(1275
156 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
157 IF V_ENABLE_TRACE = 'Y' THEN
158 EXECUTE IMMEDIATE
159 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
160 END IF;
161 EXCEPTION
162 WHEN OTHERS THEN
163 /*SRW.MESSAGE(1275
164 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
165 END;
166 FOR i IN FOR_REPORT_DATA LOOP
167 IF I.BOE_ID <> V_PREVIOUS_BOE_ID THEN
168 V_EARLIER_WRITTEN_OFF_AMOUNT := BALANCE(I.BOE_ID);
169 V_PR_ELIER_WRTEN_OFF_AMOUNT := I.WRITE_OFF_AMOUNT;
170 V_PREVIOUS_BOE_ID := I.BOE_ID;
171 ELSE
172 V_EARLIER_WRITTEN_OFF_AMOUNT := V_EARLIER_WRITTEN_OFF_AMOUNT + V_PR_ELIER_WRTEN_OFF_AMOUNT;
173 V_PR_ELIER_WRTEN_OFF_AMOUNT := I.WRITE_OFF_AMOUNT;
174 END IF;
175 INSERT INTO JAI_CMN_WRITEOFFS_T
176 (CUSTOMS_WRITE_OFF_ACCOUNT
177 ,ADDRESS_LINE_1
178 ,ADDRESS_LINE_2
179 ,ADDRESS_LINE_3
180 ,ORGANIZATION_NAME
181 ,CHART_OF_ACCOUNTS_ID
182 ,BOE_TYPE
183 ,BOE_ID
184 ,BOE_AMOUNT
185 ,LOCATION_CODE
186 ,AMOUNT_APPLIED
187 ,WRITE_OFF_AMOUNT
188 ,WRITE_OFF_DATE
189 ,WRITE_OFF_REASON
190 ,EARLIER_WRITTEN_OFF_AMOUNT
191 ,CREATED_BY
192 ,CREATION_DATE
193 ,LAST_UPDATED_BY
194 ,LAST_UPDATE_DATE)
195 VALUES (I.CUSTOMS_WRITE_OFF_ACCOUNT
196 ,I.ADDRESS_LINE_1
197 ,I.ADDRESS_LINE_2
198 ,I.ADDRESS_LINE_3
199 ,I.ORGANIZATION_NAME
200 ,I.CHART_OF_ACCOUNTS_ID
201 ,I.BOE_TYPE
202 ,I.BOE_ID
203 ,I.BOE_AMOUNT
204 ,I.LOCATION_CODE
205 ,I.AMOUNT_APPLIED
206 ,I.WRITE_OFF_AMOUNT
207 ,I.WRITE_OFF_DATE
208 ,I.WRITE_OFF_REASON
209 ,V_EARLIER_WRITTEN_OFF_AMOUNT
210 ,FND_GLOBAL.USER_ID
211 ,SYSDATE
212 ,FND_GLOBAL.USER_ID
213 ,SYSDATE);
214 END LOOP;
215 RETURN (TRUE);
216 END BEFOREREPORT;
217 FUNCTION AFTERREPORT RETURN BOOLEAN IS
218 BEGIN
219 DELETE FROM JAI_CMN_WRITEOFFS_T;
220 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
221 RETURN (TRUE);
222 END AFTERREPORT;
223 END JA_JAINBOER_XMLP_PKG;
224
225