[Home] [Help]
49: lx_disb_rules disb_rules_type;
50:
51: cursor c_disb_rules( p_vendor_id NUMBER,
52: p_vendor_site_id NUMBER,
53: p_stream_type_purpose OKL_STRM_TYPE_B.STREAM_TYPE_PURPOSE%type,
54: p_transaction_date DATE) -- reserved for future use
55: IS
56: select dra.disb_rule_id,
57: dra.rule_name,
975: AND nvl(tap.vendor_id,-1) = nvl(p_vendor_id, nvl(tap.vendor_id,-1) )
976: AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
977: --start:| 24-APR-2007 cklee Disbursement changes for R12B |
978: -- AND nvl(tpl.sty_id,-1) = nvl(p_sty_id, nvl(tpl.sty_id,-1))
979: AND nvl(tpl.sty_id,-1) = NVL((select id from OKL_STRM_TYPE_B where STREAM_TYPE_PURPOSE = nvl(p_stream_type_purpose, 'XXX')),nvl(tpl.sty_id,-1))
980: --end:| 24-APR-2007 cklee Disbursement changes for R12B |
981: AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
982: UNION ALL
983: SELECT
1066: AND nvl(tap.vendor_id,-1) = nvl(p_vendor_id, nvl(tap.vendor_id,-1) )
1067: AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
1068: --start:| 24-APR-2007 cklee Disbursement changes for R12B |
1069: -- AND nvl(tpl.sty_id,-1) = nvl(p_sty_id, nvl(tpl.sty_id,-1))
1070: AND nvl(tpl.sty_id,-1) = NVL((select id from OKL_STRM_TYPE_B where STREAM_TYPE_PURPOSE = nvl(p_stream_type_purpose, 'XXX')), nvl(tpl.sty_id,-1))
1071: --end:| 24-APR-2007 cklee Disbursement changes for R12B |
1072: AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
1073: ORDER BY vendor_id,
1074: ipvs_id,
1092: r_invoice c_invoice%ROWTYPE;
1093:
1094: cursor c_get_sty_id(p_sty_id NUMBER) IS
1095: select id
1096: from OKL_STRM_TYPE_B
1097: where STREAM_TYPE_PURPOSE = p_stream_type_purpose;
1098:
1099: cursor c_disb_rules( vId NUMBER,
1100: vSiteid NUMBER,
1121: where drv.DISB_RULE_ID = dra.disb_rule_id
1122: and drs.disb_rule_id = dra.disb_rule_id
1123: and drv.vendor_id = vId
1124: and drv.vendor_site_id = vSiteid
1125: and drs.stream_type_purpose = nvl((select stream_type_purpose from OKL_STRM_TYPE_B where id = nvl(styId, -1)),nvl(drs.stream_type_purpose,'XXX'))
1126: and TRUNC(sysdate) between TRUNC(NVL(drv.start_date, dra.start_date)) and TRUNC(NVL(NVL(drv.end_date, dra.end_date),TRUNC(sysdate)));
1127:
1128: r_disb_rules c_disb_rules%ROWTYPE;
1129:
1728: ,okl_txl_ap_inv_lns_b tpl
1729: ,okl_trx_ap_invoices_b tap
1730: ,po_vendor_sites_all povs
1731: -- ,okc_k_headers_all_b chr -- cklee 09/13/07
1732: ,OKL_STRM_TYPE_B sty -- cklee 09/13/07
1733: WHERE
1734: NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' ) AND
1735: trunc(tap.date_invoiced) BETWEEN
1736: NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
1799: ,okl_txl_ap_inv_lns_b tpl
1800: ,okl_trx_ap_invoices_b tap
1801: ,po_vendor_sites_all povs
1802: -- ,okc_k_headers_all_b chr -- cklee 09/13/07
1803: ,OKL_STRM_TYPE_B sty -- cklee 09/13/07
1804: WHERE
1805: NVL(tap.trx_status_code, 'APPROVED') in ( 'APPROVED') AND
1806: trunc(tap.date_invoiced) BETWEEN
1807: NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
1899: ,po_vendor_sites_all povs
1900: ,okl_txl_ap_inv_lns_b tpl
1901: ,okl_trx_ap_invoices_b tap
1902: -- ,okc_k_headers_all_b chr -- cklee 09/13/07
1903: ,OKL_STRM_TYPE_B sty -- cklee 09/13/07
1904: WHERE
1905: NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' )
1906: AND trunc(tap.date_invoiced) BETWEEN
1907: NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
1980: ,po_vendor_sites_all povs
1981: ,okl_txl_ap_inv_lns_b tpl
1982: ,okl_trx_ap_invoices_b tap
1983: -- ,okc_k_headers_all_b chr -- cklee 09/13/07
1984: ,OKL_STRM_TYPE_B sty -- cklee 09/13/07
1985: WHERE
1986: NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' )
1987: AND trunc(tap.date_invoiced) BETWEEN
1988: NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))