1 PACKAGE BODY JA_JAINVATR_XMLP_PKG AS
2 /* $Header: JAINVATRB.pls 120.1 2007/12/25 16:33:28 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
5 SELECT
6 CONCURRENT_PROGRAM_ID,
7 NVL(ENABLE_TRACE
8 ,'N')
9 FROM
10 FND_CONCURRENT_REQUESTS
11 WHERE REQUEST_ID = P_REQUEST_ID;
12 CURSOR GET_AUDSID IS
13 SELECT
14 A.SID,
15 A.SERIAL#,
16 NVL(B.SPID
17 ,'')
18 FROM
19 V$SESSION A,
20 V$PROCESS B
21 WHERE AUDSID = USERENV('SESSIONID')
22 AND A.PADDR = B.ADDR;
23 CURSOR GET_DBNAME IS
24 SELECT
25 NVL(NAME
26 ,'')
27 FROM
28 V$DATABASE;
29 CURSOR LCU_TAX_TYPES IS
30 SELECT
31 DISTINCT
32 JORRV.ATTRIBUTE_CODE
33 FROM
34 JAI_RGM_ORG_REGNS_V JORRV
35 WHERE JORRV.REGIME_CODE = 'VAT'
36 AND JORRV.REGISTRATION_TYPE = 'TAX_TYPES';
37 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
38 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
39 AUDSID NUMBER := USERENV('SESSIONID');
40 SID NUMBER;
41 SERIAL NUMBER;
42 SPID VARCHAR2(9);
43 NAME1 VARCHAR2(25);
44 LC_TAX_TYPES VARCHAR2(4000);
45 BEGIN
46 BEGIN
47 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
48 FETCH C_PROGRAM_ID
49 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
50 CLOSE C_PROGRAM_ID;
51 /*SRW.MESSAGE(1275
52 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
53 IF V_ENABLE_TRACE = 'Y' THEN
54 EXECUTE IMMEDIATE
55 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
56 OPEN GET_AUDSID;
57 FETCH GET_AUDSID
58 INTO SID,SERIAL,SPID;
59 CLOSE GET_AUDSID;
60 OPEN GET_DBNAME;
61 FETCH GET_DBNAME
62 INTO NAME1;
63 CLOSE GET_DBNAME;
64 /*SRW.MESSAGE(1275
65 ,'TraceFile Name = ' || NVL(LOWER(NAME1)
66 ,'') || '_ora_' || NVL(SPID
67 ,'') || '.trc')*/NULL;
68 END IF;
69 EXCEPTION
70 WHEN OTHERS THEN
71 /*SRW.MESSAGE(1275
72 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
73 END;
74 /*SRW.MESSAGE(007
75 ,'The Report Version is 120.0 and the Last Modified Date is 24/11/2006')*/NULL;
76 P_FROM_DATE := FND_DATE.CANONICAL_TO_DATE(P_CAN_DATE_FROM);
77 P_TO_DATE := FND_DATE.CANONICAL_TO_DATE(P_CAN_DATE_TO);
78 FOR lr_tax_types IN LCU_TAX_TYPES LOOP
79 LC_TAX_TYPES := LC_TAX_TYPES || '''' || LR_TAX_TYPES.ATTRIBUTE_CODE || ''',';
80 END LOOP;
81 IF (LC_TAX_TYPES IS NOT NULL) THEN
82 LC_TAX_TYPES := ' AND jitx.tax_type IN ( ' || RTRIM(LC_TAX_TYPES
83 ,',') || ' )';
84 END IF;
85 P_REGIME_TAXES := LC_TAX_TYPES;
86 /*SRW.MESSAGE(007
87 ,'Tax Types = ' || P_REGIME_TAXES)*/NULL;
88 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
89 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
90 RETURN (TRUE);
91 END BEFOREREPORT;
92
93 FUNCTION CF_TURNOVER_ISO_SHIPMENTSFORMU(A_ORGANIZATION_ID IN NUMBER
94 ,A_LOCATION_ID IN NUMBER) RETURN NUMBER IS
95 LN_ISO_SHIPMENT_AMT NUMBER;
96 CURSOR LCU_ISO_SHIPMENT_AMOUNT IS
97 SELECT
98 ROUND(SUM(JISPL.SELLING_PRICE * JISPL.QUANTITY)
99 ,2)
100 FROM
101 JAI_OM_WSH_LINES_ALL JISPL,
102 OE_ORDER_HEADERS_ALL OOHA
103 WHERE OOHA.ORDER_SOURCE_ID = 10
104 AND JISPL.ORDER_HEADER_ID = OOHA.HEADER_ID
105 AND JISPL.ORDER_TYPE_ID = OOHA.ORDER_TYPE_ID
106 AND JISPL.ORGANIZATION_ID = A_ORGANIZATION_ID
107 AND JISPL.LOCATION_ID = A_LOCATION_ID
108 AND TRUNC(JISPL.CREATION_DATE) BETWEEN P_FROM_DATE
109 AND P_TO_DATE;
110 BEGIN
111 OPEN LCU_ISO_SHIPMENT_AMOUNT;
112 FETCH LCU_ISO_SHIPMENT_AMOUNT
113 INTO LN_ISO_SHIPMENT_AMT;
114 CLOSE LCU_ISO_SHIPMENT_AMOUNT;
115 RETURN (NVL(LN_ISO_SHIPMENT_AMT
116 ,0));
117 END CF_TURNOVER_ISO_SHIPMENTSFORMU;
118
119 FUNCTION CF_TURNOVER_ISO_RECEIPTSFORMUL(A_ORGANIZATION_ID IN NUMBER
120 ,A_LOCATION_ID IN NUMBER) RETURN NUMBER IS
121 LN_ISO_RECEIPTS_AMT NUMBER;
122 CURSOR LCU_ISO_RECEIPTS_AMOUNT IS
123 SELECT
124 SUM(PRLA.UNIT_PRICE * RT.PRIMARY_QUANTITY)
125 FROM
126 PO_REQUISITION_LINES_ALL PRLA,
127 RCV_TRANSACTIONS RT
128 WHERE PRLA.REQUISITION_LINE_ID = RT.REQUISITION_LINE_ID
129 AND PRLA.DESTINATION_ORGANIZATION_ID = A_ORGANIZATION_ID
130 AND PRLA.DELIVER_TO_LOCATION_ID = A_LOCATION_ID
131 AND RT.SOURCE_DOCUMENT_CODE = 'REQ'
132 AND RT.TRANSACTION_DATE BETWEEN P_FROM_DATE
133 AND P_TO_DATE;
134 BEGIN
135 OPEN LCU_ISO_RECEIPTS_AMOUNT;
136 FETCH LCU_ISO_RECEIPTS_AMOUNT
137 INTO LN_ISO_RECEIPTS_AMT;
138 CLOSE LCU_ISO_RECEIPTS_AMOUNT;
139 RETURN (NVL(LN_ISO_RECEIPTS_AMT
140 ,0));
141 END CF_TURNOVER_ISO_RECEIPTSFORMUL;
142
143 FUNCTION CF_SALES_TOTAL_BASE_AMTFORMULA(CS_SALES_NEX_BASE_AMT IN NUMBER
144 ,CS_SALES_CST_BASE_AMT IN NUMBER
145 ,CS_SALES_EX_BASE_AMT IN NUMBER
146 ,CF_TURNOVER_ISO_SHIPMENTS IN NUMBER) RETURN NUMBER IS
147 BEGIN
148 RETURN (CS_SALES_NEX_BASE_AMT + CS_SALES_CST_BASE_AMT + CS_SALES_EX_BASE_AMT + CF_TURNOVER_ISO_SHIPMENTS);
149 END CF_SALES_TOTAL_BASE_AMTFORMULA;
150
151 FUNCTION CF_SALES_TOTAL_TAX_AMTFORMULA(CS_SALES_NEX_TAX_AMT IN NUMBER
152 ,CS_SALES_CST_TAX_AMT IN NUMBER
153 ,CS_SALES_VATREV_TAX_AMT IN NUMBER) RETURN NUMBER IS
154 BEGIN
155 RETURN (CS_SALES_NEX_TAX_AMT + CS_SALES_CST_TAX_AMT + CS_SALES_VATREV_TAX_AMT);
156 END CF_SALES_TOTAL_TAX_AMTFORMULA;
157
158 FUNCTION CF_PUR_TOTAL_BASE_AMTFORMULA(CS_PUR_NCG_UNREG_BASE_AMT IN NUMBER
159 ,CS_PUR_NCG_REG_BASE_AMT IN NUMBER
160 ,CS_PUR_NCG_CST_BASE_AMT IN NUMBER
161 ,CF_TURNOVER_ISO_RECEIPTS IN NUMBER
162 ,CS_PUR_CG_BASE_AMT IN NUMBER) RETURN NUMBER IS
163 BEGIN
164 RETURN (CS_PUR_NCG_UNREG_BASE_AMT + CS_PUR_NCG_REG_BASE_AMT + CS_PUR_NCG_CST_BASE_AMT + CF_TURNOVER_ISO_RECEIPTS + CS_PUR_CG_BASE_AMT);
165 END CF_PUR_TOTAL_BASE_AMTFORMULA;
166
167 FUNCTION CF_PUR_TOTAL_TAX_AMTFORMULA(CS_PUR_NCG_UNREG_TAX_AMT IN NUMBER
168 ,CS_PUR_NCG_REG_TAX_AMT IN NUMBER
169 ,CS_PUR_NCG_CST_TAX_AMT IN NUMBER
170 ,CS_PUR_CG_TAX_AMT IN NUMBER) RETURN NUMBER IS
171 BEGIN
172 RETURN (CS_PUR_NCG_UNREG_TAX_AMT + CS_PUR_NCG_REG_TAX_AMT + CS_PUR_NCG_CST_TAX_AMT + CS_PUR_CG_TAX_AMT);
173 END CF_PUR_TOTAL_TAX_AMTFORMULA;
174
175 FUNCTION CF_PUR_CG_TOTAL_TAX_CLAIMEDFOR(A_ORGANIZATION_ID IN NUMBER
176 ,A_LOCATION_ID IN NUMBER) RETURN NUMBER IS
177 LN_CG_TAX_CLAIMED NUMBER;
178 CURSOR LCU_CG_TAX_CLAIMED IS
179 SELECT
180 SUM(JRRL.RECOVERED_AMOUNT)
181 FROM
182 JAI_RCV_RGM_LINES JRRL
183 WHERE JRRL.REGIME_ITEM_CLASS = 'CG'
184 AND JRRL.ORGANIZATION_ID = A_ORGANIZATION_ID
185 AND JRRL.LOCATION_ID = A_LOCATION_ID
186 AND TRUNC(JRRL.RECEIPT_DATE) BETWEEN P_FROM_DATE
187 AND P_TO_DATE;
188 BEGIN
189 OPEN LCU_CG_TAX_CLAIMED;
190 FETCH LCU_CG_TAX_CLAIMED
191 INTO LN_CG_TAX_CLAIMED;
192 CLOSE LCU_CG_TAX_CLAIMED;
193 RETURN (NVL(LN_CG_TAX_CLAIMED
194 ,0));
195 END CF_PUR_CG_TOTAL_TAX_CLAIMEDFOR;
196
197 FUNCTION CF_TAX_SUMMARYFORMULA(CF_SALES_TOTAL_TAX_AMT IN NUMBER
198 ,CF_INPUTTAX_CLAIMABLE IN NUMBER
199 ,CF_PUR_CG_TOTAL_TAX_CLAIMED IN NUMBER) RETURN NUMBER IS
200 BEGIN
201 RETURN (CF_SALES_TOTAL_TAX_AMT - CF_INPUTTAX_CLAIMABLE - CF_PUR_CG_TOTAL_TAX_CLAIMED);
202 END CF_TAX_SUMMARYFORMULA;
203
204 FUNCTION CF_INPUTTAX_CLAIMABLEFORMULA(CF_PUR_TOTAL_TAX_AMT IN NUMBER
205 ,CS_TAX_INTERSTATE_PUR IN NUMBER) RETURN NUMBER IS
206 BEGIN
207 RETURN (NVL(CF_PUR_TOTAL_TAX_AMT
208 ,0) - NVL(CS_TAX_INTERSTATE_PUR
209 ,0));
210 END CF_INPUTTAX_CLAIMABLEFORMULA;
211
212 FUNCTION AFTERREPORT RETURN BOOLEAN IS
213 BEGIN
214 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
215 RETURN (TRUE);
216 END AFTERREPORT;
217
218 END JA_JAINVATR_XMLP_PKG;
219
220
[Home] [Help]