1 PACKAGE BODY JA_JAINSTCR_XMLP_PKG AS
2 /* $Header: JAINSTCRB.pls 120.1 2007/12/25 16:30:14 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 IF P_ORGANIZATION_ID IS NOT NULL THEN
6 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and dtl.organization_id = :p_organization_id';
7 END IF;
8 IF P_LOCATION_ID IS NOT NULL THEN
9 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and dtl.location_id = :p_location_id';
10 END IF;
11 IF P_PARTY_ID IS NOT NULL THEN
12 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and hdr.party_id = :p_party_id';
13 END IF;
14 IF P_PARTY_SITE_ID IS NOT NULL THEN
15 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and hdr.party_site_id = :p_party_site_id';
16 END IF;
17 IF P_MATCH_TYPE = 'P' THEN
18 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and dtl.tax_target_amount > dtl.matched_amount and dtl.matched_amount > 0 ';
19 ELSIF P_MATCH_TYPE = 'U' THEN
20 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and dtl.matched_amount is null ';
21 ELSIF P_MATCH_TYPE = 'F' THEN
22 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and dtl.tax_target_amount = dtl.matched_amount ';
23 ELSIF P_MATCH_TYPE = 'M' THEN
24 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and dtl.matched_amount > 0 ';
25 END IF;
26 /*SRW.MESSAGE(1275
27 ,'Where Clause Built is ' || P_WHERE_CLAUSE)*/NULL;
28 RETURN (TRUE);
29 END AFTERPFORM;
30
31 FUNCTION CF_INVOICE_AMOUNTFORMULA(INVOICE_ID1 IN NUMBER) RETURN NUMBER IS
32 CURSOR C_INV_AMOUNT(CP_ACCT_CLASS IN RA_CUST_TRX_LINE_GL_DIST_ALL.ACCOUNT_CLASS%TYPE) IS
33 SELECT
34 AMOUNT
35 FROM
36 RA_CUST_TRX_LINE_GL_DIST_ALL
37 WHERE CUSTOMER_TRX_ID = INVOICE_ID1
38 AND ACCOUNT_CLASS = CP_ACCT_CLASS - 'REC'
39 AND LATEST_REC_FLAG = 'Y';
40 V_INVOICE_AMOUNT NUMBER;
41 BEGIN
42 OPEN C_INV_AMOUNT('REC');
43 FETCH C_INV_AMOUNT
44 INTO V_INVOICE_AMOUNT;
45 CLOSE C_INV_AMOUNT;
46 RETURN (V_INVOICE_AMOUNT);
47 EXCEPTION
48 WHEN OTHERS THEN
49 RETURN (0);
50 END CF_INVOICE_AMOUNTFORMULA;
51
52 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
53 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
54 SELECT
55 CONCURRENT_PROGRAM_ID,
56 NVL(ENABLE_TRACE
57 ,'N')
58 FROM
59 FND_CONCURRENT_REQUESTS
60 WHERE REQUEST_ID = P_REQUEST_ID;
61 CURSOR GET_AUDSID IS
62 SELECT
63 A.SID,
64 A.SERIAL#,
65 B.SPID
66 FROM
67 V$SESSION A,
68 V$PROCESS B
69 WHERE AUDSID = USERENV('SESSIONID')
70 AND A.PADDR = B.ADDR;
71 CURSOR GET_DBNAME IS
72 SELECT
73 NAME
74 FROM
75 V$DATABASE;
76 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
77 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
78 V_AUDSID NUMBER := USERENV('SESSIONID');
79 V_SID NUMBER;
80 V_SERIAL NUMBER;
81 V_SPID VARCHAR2(9);
82 V_DBNAME VARCHAR2(25);
83 BEGIN
84 /*SRW.MESSAGE(1275
85 ,'Report Version is 120.3 Last modified date is 20/07/2006')*/NULL;
86 BEGIN
87 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
88 CP_MATCH_TYPE := LTRIM(RTRIM(P_MATCH_TYPE));
89 CP_FROM_DATE := TO_CHAR(P_FROM_DATE,'DD-MON-YY');
90 CP_TO_DATE := TO_CHAR(P_TO_DATE,'DD-MON-YY');
91
92 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
93 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
94 FETCH C_PROGRAM_ID
95 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
96 CLOSE C_PROGRAM_ID;
97 /*SRW.MESSAGE(1275
98 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
99 IF V_ENABLE_TRACE = 'Y' THEN
100 OPEN GET_AUDSID;
101 FETCH GET_AUDSID
102 INTO V_SID,V_SERIAL,V_SPID;
103 CLOSE GET_AUDSID;
104 OPEN GET_DBNAME;
105 FETCH GET_DBNAME
106 INTO V_DBNAME;
107 CLOSE GET_DBNAME;
108 /*SRW.MESSAGE(1275
109 ,'TraceFile Name = ' || LOWER(V_DBNAME) || '_ora_' || V_SPID || '.trc')*/NULL;
110 EXECUTE IMMEDIATE
111 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
112 END IF;
113 RETURN (TRUE);
114 EXCEPTION
115 WHEN OTHERS THEN
116 /*SRW.MESSAGE(1275
117 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
118 END;
119 END BEFOREREPORT;
120
121 FUNCTION CF_ORDER_DATEFORMULA(HEADER_ID IN NUMBER
122 ,ORDER_FLAG1 IN VARCHAR2) RETURN CHAR IS
123 CURSOR C_ORDER_DATE IS
124 SELECT
125 ORDERED_DATE
126 FROM
127 OE_ORDER_HEADERS_ALL
128 WHERE HEADER_ID = CF_ORDER_DATEFORMULA.HEADER_ID;
129 V_ORDER_DATE DATE;
130 BEGIN
131 IF ORDER_FLAG1 = 'O' THEN
132 OPEN C_ORDER_DATE;
133 FETCH C_ORDER_DATE
134 INTO V_ORDER_DATE;
135 CLOSE C_ORDER_DATE;
136 RETURN (TO_CHAR(V_ORDER_DATE));
137 ELSE
138 RETURN ('N/A');
139 END IF;
140 EXCEPTION
141 WHEN OTHERS THEN
142 RETURN (NULL);
143 END CF_ORDER_DATEFORMULA;
144
145 FUNCTION CF_FORM_NUMBERFORMULA(FORM_ID IN NUMBER) RETURN CHAR IS
146 CURSOR C_FORM_NUMBER IS
147 SELECT
148 FORM_NUMBER
149 FROM
150 JAI_CMN_ST_FORMS
151 WHERE FORM_ID = CF_FORM_NUMBERFORMULA.FORM_ID;
152 V_FORM_NUMBER JAI_CMN_ST_FORMS.FORM_NUMBER%TYPE;
153 BEGIN
154 OPEN C_FORM_NUMBER;
155 FETCH C_FORM_NUMBER
156 INTO V_FORM_NUMBER;
157 CLOSE C_FORM_NUMBER;
158 RETURN (V_FORM_NUMBER);
159 EXCEPTION
160 WHEN OTHERS THEN
161 RETURN (NULL);
162 END CF_FORM_NUMBERFORMULA;
163
164 FUNCTION CF_FORM_DATEFORMULA(FORM_ID IN NUMBER) RETURN DATE IS
165 CURSOR C_FORM_DATE IS
166 SELECT
167 FORM_DATE
168 FROM
169 JAI_CMN_ST_FORMS
170 WHERE FORM_ID = CF_FORM_DATEFORMULA.FORM_ID;
171 V_FORM_DATE JAI_CMN_ST_FORMS.FORM_DATE%TYPE;
172 BEGIN
173 OPEN C_FORM_DATE;
174 FETCH C_FORM_DATE
175 INTO V_FORM_DATE;
176 CLOSE C_FORM_DATE;
177 RETURN (V_FORM_DATE);
178 EXCEPTION
179 WHEN OTHERS THEN
180 RETURN (NULL);
181 END CF_FORM_DATEFORMULA;
182
183 FUNCTION DISPLAY_YES_NO RETURN BOOLEAN IS
184 V_MATCH_TYPE VARCHAR2(20);
185 BEGIN
186 V_MATCH_TYPE := LTRIM(RTRIM(P_MATCH_TYPE));
187 IF V_MATCH_TYPE = 'F' OR V_MATCH_TYPE = 'P' OR V_MATCH_TYPE = 'M' THEN
188 RETURN (TRUE);
189 ELSE
190 RETURN (FALSE);
191 END IF;
192 END DISPLAY_YES_NO;
193
194 FUNCTION G_ORGN_INFOGROUPFILTER RETURN BOOLEAN IS
195 BEGIN
196 P_REC_EXISTS := P_REC_EXISTS + 1;
197 RETURN (TRUE);
198 END G_ORGN_INFOGROUPFILTER;
199
200 FUNCTION AFTERREPORT RETURN BOOLEAN IS
201 BEGIN
202 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
203 RETURN (TRUE);
204 END AFTERREPORT;
205
206 END JA_JAINSTCR_XMLP_PKG;
207
208
[Home] [Help]