1: PACKAGE BODY OKL_STREAMS_RECON_PVT AS
2: /* $Header: OKLRSCRB.pls 120.10 2007/06/21 14:34:24 varangan noship $ */
3:
4: SUBTYPE error_message_type IS Okl_Accounting_Util.ERROR_MESSAGE_TYPE;
5:
29: p_retcode OUT NOCOPY NUMBER,
30: p_contract_number IN okc_k_headers_b.contract_number%TYPE DEFAULT NULL,
31: p_end_date IN VARCHAR2 DEFAULT NULL)
32: IS
33: l_api_name CONSTANT VARCHAR2(40):= 'OKL_STREAMS_RECON_REPORT';
34: l_api_version CONSTANT NUMBER := 1.0;
35: p_api_version CONSTANT NUMBER := 1.0;
36: l_init_msg_list VARCHAR2(3) := okl_api.g_true;
37: l_msg_count NUMBER;
150: p_end_date IN okc_k_headers_b.end_date%TYPE)
151: IS
152: SELECT DISTINCT chrb.currency_code
153: FROM okl_strm_elements ele,
154: okl_streams stm,
155: okl_strm_type_b sty,
156: okl_k_headers khr,
157: okc_k_headers_b chrb,
158: okc_k_lines_b kle,
183: p_end_date IN okc_k_headers_b.end_date%TYPE)
184: IS
185: SELECT SUM(ele.amount) amount
186: FROM okl_strm_elements ele,
187: okl_streams stm,
188: okl_strm_type_b sty,
189: okl_k_headers khr,
190: okc_k_headers_b chrb,
191: okc_k_lines_b kle,
217: IS
218: SELECT SUM(ste.amount) amount
219: FROM okl_strm_type_v sty,
220: okl_strm_elements ste,
221: okl_streams stm,
222: okc_statuses_b khs,
223: okc_statuses_b kls,
224: okl_k_headers khl,
225: okc_k_lines_b kle,
252: IS
253: SELECT SUM(ste.amount) amount
254: FROM okl_strm_type_v sty,
255: okl_strm_elements ste,
256: okl_streams stm,
257: okc_statuses_b khs,
258: okc_statuses_b kls,
259: okl_k_headers khl,
260: okc_k_lines_b kle,
288: IS
289: SELECT SUM(ste.amount) amount
290: FROM okl_strm_type_v sty,
291: okl_strm_elements ste,
292: okl_streams stm,
293: okc_statuses_b khs,
294: okc_statuses_b kls,
295: okl_k_headers khl,
296: okc_k_lines_b kle,
328: pdt.name product_name,
329: SUM(ste.amount) amount
330: FROM okl_strm_type_v sty,
331: okl_strm_elements ste,
332: okl_streams stm,
333: okc_statuses_b khs,
334: okc_statuses_b kls,
335: okl_products pdt,
336: fnd_lookup_values fnd,
378: 'N' cancelled_streams,
379: 'N' unbilled_streams,
380: SUM(ele.amount) amount
381: FROM okl_strm_elements ele,
382: okl_streams stm,
383: okl_strm_type_b sty,
384: okl_k_headers khr,
385: okc_k_headers_b chrb,
386: okc_k_lines_b kle,
414: 'N' unbilled_streams,
415: SUM(ste.amount) billed_streams
416: FROM okl_strm_type_v sty,
417: okl_strm_elements ste,
418: okl_streams stm,
419: okc_statuses_b khs,
420: okc_statuses_b kls,
421: okl_k_headers khl,
422: okc_k_lines_b kle,
450: 'N' unbilled_streams,
451: SUM(ste.amount)
452: FROM okl_strm_type_v sty,
453: okl_strm_elements ste,
454: okl_streams stm,
455: okc_statuses_b khs,
456: okc_statuses_b kls,
457: okl_k_headers khl,
458: okc_k_lines_b kle,
488: 'Y' unbilled_streams,
489: SUM(ste.amount)
490: FROM okl_strm_type_v sty,
491: okl_strm_elements ste,
492: okl_streams stm,
493: okc_statuses_b khs,
494: okc_statuses_b kls,
495: okl_k_headers khl,
496: okc_k_lines_b kle,
1027: FUNCTION xml_recon_qry RETURN BOOLEAN
1028: IS
1029: p_errbuf VARCHAR2(1000) := NULL;
1030: p_retcode NUMBER;
1031: l_api_name CONSTANT VARCHAR2(40):= 'OKL_STREAMS_RECON_REPORT';
1032: l_api_version CONSTANT NUMBER := 1.0;
1033: p_api_version CONSTANT NUMBER := 1.0;
1034: l_init_msg_list VARCHAR2(3) := okl_api.g_true;
1035: l_msg_count NUMBER;
1183: p_end_date IN okc_k_headers_b.end_date%TYPE)
1184: IS
1185: SELECT DISTINCT chrb.currency_code
1186: FROM okl_strm_elements ele,
1187: okl_streams stm,
1188: okl_strm_type_b sty,
1189: okl_k_headers khr,
1190: okc_k_headers_b chrb,
1191: okc_k_lines_b kle,
1216: p_end_date IN okc_k_headers_b.end_date%TYPE)
1217: IS
1218: SELECT SUM(ele.amount) amount
1219: FROM okl_strm_elements ele,
1220: okl_streams stm,
1221: okl_strm_type_b sty,
1222: okl_k_headers khr,
1223: okc_k_headers_b chrb,
1224: okc_k_lines_b kle,
1250: IS
1251: SELECT SUM(ste.amount) amount
1252: FROM okl_strm_type_v sty,
1253: okl_strm_elements ste,
1254: okl_streams stm,
1255: okc_statuses_b khs,
1256: okc_statuses_b kls,
1257: okl_k_headers khl,
1258: okc_k_lines_b kle,
1285: IS
1286: SELECT SUM(ste.amount) amount
1287: FROM okl_strm_type_v sty,
1288: okl_strm_elements ste,
1289: okl_streams stm,
1290: okc_statuses_b khs,
1291: okc_statuses_b kls,
1292: okl_k_headers khl,
1293: okc_k_lines_b kle,
1321: IS
1322: SELECT SUM(ste.amount) amount
1323: FROM okl_strm_type_v sty,
1324: okl_strm_elements ste,
1325: okl_streams stm,
1326: okc_statuses_b khs,
1327: okc_statuses_b kls,
1328: okl_k_headers khl,
1329: okc_k_lines_b kle,
1361: pdt.name product_name,
1362: SUM(ste.amount) amount
1363: FROM okl_strm_type_v sty,
1364: okl_strm_elements ste,
1365: okl_streams stm,
1366: okc_statuses_b khs,
1367: okc_statuses_b kls,
1368: okl_products pdt,
1369: fnd_lookup_values fnd,
1411: 'N' cancelled_streams,
1412: 'N' unbilled_streams,
1413: SUM(ele.amount) amount
1414: FROM okl_strm_elements ele,
1415: okl_streams stm,
1416: okl_strm_type_b sty,
1417: okl_k_headers khr,
1418: okc_k_headers_b chrb,
1419: okc_k_lines_b kle,
1447: 'N' unbilled_streams,
1448: SUM(ste.amount) billed_streams
1449: FROM okl_strm_type_v sty,
1450: okl_strm_elements ste,
1451: okl_streams stm,
1452: okc_statuses_b khs,
1453: okc_statuses_b kls,
1454: okl_k_headers khl,
1455: okc_k_lines_b kle,
1483: 'N' unbilled_streams,
1484: SUM(ste.amount)
1485: FROM okl_strm_type_v sty,
1486: okl_strm_elements ste,
1487: okl_streams stm,
1488: okc_statuses_b khs,
1489: okc_statuses_b kls,
1490: okl_k_headers khl,
1491: okc_k_lines_b kle,
1521: 'Y' unbilled_streams,
1522: SUM(ste.amount)
1523: FROM okl_strm_type_v sty,
1524: okl_strm_elements ste,
1525: okl_streams stm,
1526: okc_statuses_b khs,
1527: okc_statuses_b kls,
1528: okl_k_headers khl,
1529: okc_k_lines_b kle,
1944: END LOOP;
1945: END IF;
1946: fnd_file.put_line(fnd_file.log, SQLERRM);
1947: END xml_recon_qry;
1948: END OKL_STREAMS_RECON_PVT;