1 PACKAGE BODY JA_JAINPRRG_XMLP_PKG AS
2 /* $Header: JAINPRRGB.pls 120.1 2007/12/25 16:25:53 dwkrishn noship $ */
3 FUNCTION CF_PO_NUM_N_RELEASEFORMULA(PO_NUMBER IN VARCHAR2
4 ,PO_RELEASE_NUM IN NUMBER) RETURN CHAR IS
5 BEGIN
6 RETURN PO_NUMBER || '(' || PO_RELEASE_NUM || ')';
7 END CF_PO_NUM_N_RELEASEFORMULA;
8
9 FUNCTION CF_TOTAL_AMOUNTFORMULA(CS_LINE_AMOUNT IN NUMBER
10 ,CS_EXCISE IN NUMBER
11 ,CS_CVD IN NUMBER
12 ,CS_ADDCVD IN NUMBER
13 ,CS_CUSTOMS IN NUMBER
14 ,CS_CST IN NUMBER
15 ,CS_LST IN NUMBER
16 ,CS_FREIGHT IN NUMBER
17 ,CS_OCTROI IN NUMBER
18 ,CS_OTHERS IN NUMBER) RETURN NUMBER IS
19 BEGIN
20 RETURN (CS_LINE_AMOUNT + CS_EXCISE + CS_CVD + CS_ADDCVD + CS_CUSTOMS + CS_CST + CS_LST + CS_FREIGHT + CS_OCTROI + CS_OTHERS);
21 END CF_TOTAL_AMOUNTFORMULA;
22
23 PROCEDURE P_ORG_ADDR1VALIDTRIGGER IS
24 V_LOCATION_ID NUMBER;
25 V_SET_OF_BOOKS_ID NUMBER;
26 CURSOR C_ORG_ADDR1(P_ORGANIZATION_ID IN NUMBER) IS
27 SELECT
28 NAME,
29 LOCATION_ID
30 FROM
31 HR_ORGANIZATION_UNITS
32 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
33 CURSOR C_ADDRESS(P_LOCATION_ID IN NUMBER) IS
34 SELECT
35 ADDRESS_LINE_1 || ',' || ADDRESS_LINE_2 || ',' || ADDRESS_LINE_3 || ',' || TOWN_OR_CITY || ',' || COUNTRY || ',' || POSTAL_CODE || ',' || TELEPHONE_NUMBER_1 || ',' || TELEPHONE_NUMBER_2 || ',' || TELEPHONE_NUMBER_3
36 FROM
37 HR_LOCATIONS
38 WHERE LOCATION_ID = P_LOCATION_ID;
39 CURSOR C_SOB_ID(P_ORG_ID IN NUMBER) IS
40 SELECT
41 TO_NUMBER(SET_OF_BOOKS_ID)
42 FROM
43 HR_OPERATING_UNITS
44 WHERE ORGANIZATION_ID = P_ORG_ID;
45 CURSOR C_SOB_NAME(P_SOB_ID IN NUMBER) IS
46 SELECT
47 NAME
48 FROM
49 GL_SETS_OF_BOOKS
50 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
51 BEGIN
52 OPEN C_ORG_ADDR1(P_ORG_ID);
53 FETCH C_ORG_ADDR1
54 INTO P_ORG_NAME,V_LOCATION_ID;
55 CLOSE C_ORG_ADDR1;
56 OPEN C_ADDRESS(V_LOCATION_ID);
57 FETCH C_ADDRESS
58 INTO P_ORG_ADDR;
59 CLOSE C_ADDRESS;
60 OPEN C_SOB_ID(P_ORG_ID);
61 FETCH C_SOB_ID
62 INTO V_SET_OF_BOOKS_ID;
63 CLOSE C_SOB_ID;
64 OPEN C_SOB_NAME(V_SET_OF_BOOKS_ID);
65 FETCH C_SOB_NAME
66 INTO P_SOB_NAME;
67 CLOSE C_SOB_NAME;
68
69 END P_ORG_ADDR1VALIDTRIGGER;
70
71 FUNCTION CF_VENDOR_NAMEFORMULA(VENDOR_ID IN NUMBER) RETURN CHAR IS
72 V_VENDOR_NAME PO_VENDORS.VENDOR_NAME%TYPE;
73 CURSOR C_VENDOR_NAME(P_VENDOR_ID IN NUMBER) IS
74 SELECT
75 VENDOR_NAME
76 FROM
77 PO_VENDORS
78 WHERE VENDOR_ID = P_VENDOR_ID;
79 BEGIN
80 OPEN C_VENDOR_NAME(VENDOR_ID);
81 FETCH C_VENDOR_NAME
82 INTO V_VENDOR_NAME;
83 CLOSE C_VENDOR_NAME;
84 RETURN V_VENDOR_NAME;
85 END CF_VENDOR_NAMEFORMULA;
86
87 FUNCTION CF_VENDOR_SITE_CODEFORMULA(VENDOR_SITE_ID IN NUMBER) RETURN CHAR IS
88 V_VENDOR_SITE_CODE PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE%TYPE;
89 CURSOR C_VENDOR_SITE_CODE(P_VENDOR_SITE_ID IN NUMBER) IS
90 SELECT
91 VENDOR_SITE_CODE
92 FROM
93 PO_VENDOR_SITES_ALL
94 WHERE VENDOR_SITE_ID = P_VENDOR_SITE_ID;
95 BEGIN
96 OPEN C_VENDOR_SITE_CODE(VENDOR_SITE_ID);
97 FETCH C_VENDOR_SITE_CODE
98 INTO V_VENDOR_SITE_CODE;
99 CLOSE C_VENDOR_SITE_CODE;
100 RETURN V_VENDOR_SITE_CODE;
101 END CF_VENDOR_SITE_CODEFORMULA;
102
103 FUNCTION AFTERPFORM RETURN BOOLEAN IS
104 V_ERROR_MESSAGE VARCHAR2(500);
105 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
106 SELECT
107 CONCURRENT_PROGRAM_ID,
108 NVL(ENABLE_TRACE
109 ,'N')
110 FROM
111 FND_CONCURRENT_REQUESTS
112 WHERE REQUEST_ID = P_REQUEST_ID;
113 CURSOR GET_AUDSID IS
114 SELECT
115 A.SID,
116 A.SERIAL#,
117 B.SPID
118 FROM
119 V$SESSION A,
120 V$PROCESS B
121 WHERE AUDSID = USERENV('SESSIONID')
122 AND A.PADDR = B.ADDR;
123 CURSOR GET_DBNAME IS
124 SELECT
125 NAME
126 FROM
127 V$DATABASE;
128 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
129 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
130 AUDSID NUMBER := USERENV('SESSIONID');
131 SID NUMBER;
132 SERIAL NUMBER;
133 SPID VARCHAR2(9);
134 NAME1 VARCHAR2(25);
135 BEGIN
136 /*SRW.MESSAGE(1275
137 ,'Report Version is 120.3 Last modified date is 22/11/2006')*/NULL;
138 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
139 FETCH C_PROGRAM_ID
140 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
141 CLOSE C_PROGRAM_ID;
142 /*SRW.MESSAGE(1274
143 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
144 IF V_ENABLE_TRACE = 'Y' THEN
145 OPEN GET_AUDSID;
146 FETCH GET_AUDSID
147 INTO SID,SERIAL,SPID;
148 CLOSE GET_AUDSID;
149 OPEN GET_DBNAME;
150 FETCH GET_DBNAME
151 INTO NAME1;
152 CLOSE GET_DBNAME;
153 /*SRW.MESSAGE(1274
154 ,'TraceFile Name = ' || LOWER(NAME1) || '_ora_' || SPID || '.trc')*/NULL;
155 EXECUTE IMMEDIATE
156 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
157 END IF;
158 JAI_AP_RPT_PRRG_PKG.PROCESS_REPORT(P_FROM_DATE
159 ,P_TO_DATE
160 ,P_VENDOR_ID
161 ,P_VENDOR_SITE_ID
162 ,P_ORG_ID
163 ,P_RUN_NO
164 ,V_ERROR_MESSAGE);
165 /*SRW.MESSAGE(1274
166 ,'Runno:' || P_RUN_NO)*/NULL;
167 IF V_ERROR_MESSAGE IS NOT NULL THEN
168 /*SRW.MESSAGE(1274
169 ,'Error in jai_ap_rpt_prrg_pkg.process_report:' || V_ERROR_MESSAGE)*/NULL;
170 END IF;
171 RETURN (TRUE);
172 EXCEPTION
173 WHEN OTHERS THEN
174 /*SRW.MESSAGE(1274
175 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
176 RETURN TRUE;
177 END AFTERPFORM;
178
179 FUNCTION AFTERREPORT RETURN BOOLEAN IS
180 BEGIN
181 IF P_COMMIT_DATA = 'Y' THEN
182 COMMIT;
183 ELSE
184 ROLLBACK;
185 END IF;
186 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
187 RETURN (TRUE);
188 END AFTERREPORT;
189
190 FUNCTION G_INVOICEGROUPFILTER RETURN BOOLEAN IS
191 BEGIN
192 P_LINE_NO := P_LINE_NO + 1;
193 CP_SERIAL := P_LINE_NO;
194 RETURN (TRUE);
195 END G_INVOICEGROUPFILTER;
196
197 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
198 BEGIN
199 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
200 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
201 lp_run_no:=p_run_no;
202 P_ORG_ADDR1VALIDTRIGGER;
203 LP_ORG_ADDR:=P_ORG_ADDR;
204 LP_ORG_NAME:=P_ORG_NAME;
205 LP_SOB_NAME:=P_SOB_NAME;
206 LP_FROM_DATE:=to_char(P_FROM_DATE,'DD-MON-YYYY');
207 LP_TO_DATE:=to_char(P_TO_DATE,'DD-MON-YYYY');
208 RETURN (TRUE);
209 END BEFOREREPORT;
210
211 FUNCTION CP_SERIAL_P RETURN NUMBER IS
212 BEGIN
213 RETURN CP_SERIAL;
214 END CP_SERIAL_P;
215
216 END JA_JAINPRRG_XMLP_PKG;
217
218
[Home] [Help]