29: SELECT MIN(ste.stream_element_date)
30: FROM OKL_STRM_ELEMENTS ste,
31: OKL_STREAMS stm,
32: okl_strm_type_v sty,
33: okc_k_headers_b khr,
34: OKL_K_HEADERS khl,
35: okc_k_lines_b kle,
36: okc_statuses_b khs,
37: okc_statuses_b kls
752:
753: cursor chk_update_header_csr ( p_date date, orgId VARCHAr2 ) is -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
754: -- -- bug# 5872306 (ssiruvol)
755: SELECT khr.contract_number contract_number, khr.id khr_id
756: FROM okc_k_headers_b khr, -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
757: okl_k_control nbd -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
758: WHERE ((p_contract_number is not null and KHR.CONTRACT_NUMBER=p_contract_number)
759: or (p_contract_number is null and KHR.CONTRACT_NUMBER=KHR.CONTRACT_NUMBER))
760: AND ((p_cust_acct_id is not null and KHR.CUST_ACCT_ID=p_cust_acct_id)
789: -- rmunjulu R12 fixes - comment out Prepare Recvbles
790: /* UNION
791: --3 levels
792: SELECT khr.contract_number contract_number, khr.id khr_id
793: FROM okc_k_headers_b khr
794: WHERE id IN (
795: SELECT CHR.id
796: FROM okl_txd_ar_ln_dtls_b tld,
797: okl_txl_ar_inv_lns_b til,
796: FROM okl_txd_ar_ln_dtls_b tld,
797: okl_txl_ar_inv_lns_b til,
798: okl_trx_ar_invoices_b tai,
799: okl_strm_type_v sty,
800: okc_k_headers_b CHR
801: WHERE tai.trx_status_code = 'SUBMITTED'
802: AND tai.khr_id = CHR.id
803: AND til.tai_id = tai.id
804: AND tld.til_id_details = til.id
814:
815: UNION
816: -- 2 levels
817: SELECT khr.contract_number contract_number, khr.id khr_id
818: FROM okc_k_headers_b khr
819: WHERE id IN (
820: SELECT CHR.id
821: FROM okl_txl_ar_inv_lns_b til,
822: okl_trx_ar_invoices_b tai,
820: SELECT CHR.id
821: FROM okl_txl_ar_inv_lns_b til,
822: okl_trx_ar_invoices_b tai,
823: okl_strm_type_v sty,
824: okc_k_headers_b CHR
825: WHERE tai.trx_status_code = 'SUBMITTED'
826: AND tai.khr_id = CHR.id
827: AND til.tai_id = tai.id
828: AND til.sty_id = sty.id
843: */
844: -- rmunjulu R12 Fixes modify AR Transfer
845: UNION
846: SELECT khr.contract_number contract_number, khr.id khr_id
847: FROM okc_k_headers_b khr
848: WHERE id in (
849: SELECT TAI.khr_id -- rmunjulu R12 fixes - changed to TAI
850: FROM --okl_ext_sell_invs_b xsi, -- rmunjulu R12 fixes - commented
851: --okl_xtl_sell_invs_b xls, -- rmunjulu R12 fixes - commented
851: --okl_xtl_sell_invs_b xls, -- rmunjulu R12 fixes - commented
852: --okl_txd_ar_ln_dtls_b tld,-- rmunjulu R12 fixes - commented
853: --okl_txl_ar_inv_lns_b til,-- rmunjulu R12 fixes - commented
854: okl_trx_ar_invoices_b tai,
855: okc_k_headers_b chr-- rmunjulu R12 fixes - commented
856: WHERE tai.TRX_STATUS_CODE = 'SUBMITTED' -- rmunjulu R12 fixes - changed to TAI + SUBMITTED
857: --AND XSI.ID = XLS.XSI_ID_DETAILS -- rmunjulu R12 fixes - commented
858: --AND xls.tld_id = tld.id -- rmunjulu R12 fixes - commented
859: --and tld.til_id_details = til.id -- rmunjulu R12 fixes - commented
870: -- rmunjulu R12 Fixes -- below select not needed as will be same as above select
871: /*
872: UNION
873: SELECT khr.contract_number contract_number, khr.id khr_id
874: FROM okc_k_headers_b khr
875: WHERE id in (
876: SELECT chr.id
877: FROM okl_ext_sell_invs_b xsi,
878: okl_xtl_sell_invs_b xls,
877: FROM okl_ext_sell_invs_b xsi,
878: okl_xtl_sell_invs_b xls,
879: okl_txl_ar_inv_lns_b til,
880: okl_trx_ar_invoices_b tai,
881: okc_k_headers_b chr
882: WHERE XSI.TRX_STATUS_CODE = 'WORKING'
883: AND XSI.ID = XLS.XSI_ID_DETAILS
884: AND xls.til_id = til.id
885: and til.tai_id = tai.id
909: */
910: -- modified by zrehman for Bug#6788005 on 01-Feb-2008 start
911: UNION
912: SELECT khr.contract_number contract_number, khr.id khr_id
913: FROM okc_k_headers_b khr,
914: okl_k_control nbd,
915: okc_k_lines_b cle
916: WHERE ((p_contract_number is not null and KHR.CONTRACT_NUMBER=p_contract_number)
917: or (p_contract_number is null and KHR.CONTRACT_NUMBER=KHR.CONTRACT_NUMBER))
953: (p_date_from is null)
954: ) AND
955: STE.STREAM_ELEMENT_DATE <= (NVL(p_date_to, SYSDATE) +
956: NVL( NVL(
957: (SELECT RULE.RULE_INFORMATION3 FROM OKC_RULES_B RULE, OKC_RULE_GROUPS_B RGP, okc_k_headers_b khr
958: WHERE opp.khr_id = khr.id AND RGP.ID = RULE.RGP_ID AND RGP.DNZ_CHR_ID = KHR.ID
959: AND RGP.RGD_CODE = 'LABILL' AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
960: ),
961: (SELECT TERM.PRINTING_LEAD_DAYS FROM HZ_CUSTOMER_PROFILES CP, RA_TERMS TERM, okc_k_headers_b khr
957: (SELECT RULE.RULE_INFORMATION3 FROM OKC_RULES_B RULE, OKC_RULE_GROUPS_B RGP, okc_k_headers_b khr
958: WHERE opp.khr_id = khr.id AND RGP.ID = RULE.RGP_ID AND RGP.DNZ_CHR_ID = KHR.ID
959: AND RGP.RGD_CODE = 'LABILL' AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
960: ),
961: (SELECT TERM.PRINTING_LEAD_DAYS FROM HZ_CUSTOMER_PROFILES CP, RA_TERMS TERM, okc_k_headers_b khr
962: WHERE opp.khr_id = khr.id AND KHR.BILL_TO_SITE_USE_ID = CP.SITE_USE_ID AND CP.STANDARD_TERMS = TERM.TERM_ID)
963: ),0)
964: --Bug# 7701159 - Susbtitute SQL query for PL//*/*SQL call
965: /*NVL((SELECT NVL(RULE.RULE_INFORMATION3,TERM.PRINTING_LEAD_DAYS)
966: FROM OKC_RULES_B RULE,
967: OKC_RULE_GROUPS_B RGP,
968: HZ_CUSTOMER_PROFILES CP,
969: RA_TERMS TERM
970: ,okc_k_headers_b khr
971: WHERE opp.khr_id = khr.id and
972: RGP.ID = RULE.RGP_ID
973: AND RGP.DNZ_CHR_ID = KHR.ID
974: AND RGP.RGD_CODE = 'LABILL'
999: FROM
1000: OKL_TXD_AR_LN_DTLS_B TLD,
1001: OKL_TXL_AR_INV_LNS_B TIL,
1002: OKL_TRX_AR_INVOICES_B TAI,
1003: OKC_K_HEADERS_B CHR,
1004: OKL_STRM_TYPE_V STY,
1005: OKL_PARALLEL_PROCESSES OPP
1006: WHERE tai.trx_status_code = 'SUBMITTED'
1007: AND tai.khr_id = CHR.id
1020: COUNT(*) LINE_COUNT
1021: FROM
1022: OKL_TXL_AR_INV_LNS_B TIL,
1023: OKL_TRX_AR_INVOICES_B TAI,
1024: OKC_K_HEADERS_B CHR,
1025: OKL_STRM_TYPE_V STY,
1026: OKL_PARALLEL_PROCESSES OPP
1027: WHERE tai.trx_status_code = 'SUBMITTED'
1028: AND tai.khr_id = CHR.id
1051: --OKL_XTL_SELL_INVS_B XLS, -- rmunjulu R12 fixes - commented
1052: --OKL_TXD_AR_LN_DTLS_B TLD, -- rmunjulu R12 fixes - commented
1053: --OKL_TXL_AR_INV_LNS_B TIL, -- rmunjulu R12 fixes - commented
1054: OKL_TRX_AR_INVOICES_B TAI,
1055: OKC_K_HEADERS_B KHR,
1056: OKL_PARALLEL_PROCESSES OPP
1057: WHERE
1058: TAI.TRX_STATUS_CODE = 'SUBMITTED' AND -- rmunjulu R12 fixes - changed to TAI and SUBMITTED
1059: --XSI.ID = XLS.XSI_ID_DETAILS AND -- rmunjulu R12 fixes - commented
1076: OKL_EXT_SELL_INVS_V XSI,
1077: OKL_XTL_SELL_INVS_V XLS,
1078: OKL_TXL_AR_INV_LNS_V TIL,
1079: OKL_TRX_AR_INVOICES_V TAI,
1080: OKC_K_HEADERS_B KHR,
1081: OKL_PARALLEL_PROCESSES OPP
1082: WHERE
1083: XSI.TRX_STATUS_CODE = 'WORKING' AND
1084: XSI.ID = XLS.XSI_ID_DETAILS AND
1364: SET volume = volume* (SELECT CEIL((lp_date_to
1365: -- rmunjulu R12 Forward Port bug 5634652 logic to check print lead days
1366: +
1367: NVL( NVL(
1368: (SELECT RULE.RULE_INFORMATION3 FROM OKC_RULES_B RULE, OKC_RULE_GROUPS_B RGP, okc_k_headers_b khr
1369: WHERE opp.khr_id = khr.id AND RGP.ID = RULE.RGP_ID AND RGP.DNZ_CHR_ID = KHR.ID
1370: AND RGP.RGD_CODE = 'LABILL' AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
1371: ),
1372: (SELECT TERM.PRINTING_LEAD_DAYS FROM HZ_CUSTOMER_PROFILES CP, RA_TERMS TERM, okc_k_headers_b khr
1368: (SELECT RULE.RULE_INFORMATION3 FROM OKC_RULES_B RULE, OKC_RULE_GROUPS_B RGP, okc_k_headers_b khr
1369: WHERE opp.khr_id = khr.id AND RGP.ID = RULE.RGP_ID AND RGP.DNZ_CHR_ID = KHR.ID
1370: AND RGP.RGD_CODE = 'LABILL' AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
1371: ),
1372: (SELECT TERM.PRINTING_LEAD_DAYS FROM HZ_CUSTOMER_PROFILES CP, RA_TERMS TERM, okc_k_headers_b khr
1373: WHERE opp.khr_id = khr.id AND KHR.BILL_TO_SITE_USE_ID = CP.SITE_USE_ID AND CP.STANDARD_TERMS = TERM.TERM_ID)
1374: ),0)
1375: --Bug# 7701159 - Susbtitute SQL query for PL/SQL call
1376: /*NVL((SELECT NVL(RULE.RULE_INFORMATION3,TERM.PRINTING_LEAD_DAYS)
1377: FROM OKC_RULES_B RULE,
1378: OKC_RULE_GROUPS_B RGP,
1379: HZ_CUSTOMER_PROFILES CP,
1380: RA_TERMS TERM
1381: ,okc_k_headers_b khr
1382: WHERE opp.khr_id = khr.id and
1383: RGP.ID = RULE.RGP_ID
1384: AND RGP.DNZ_CHR_ID = KHR.ID
1385: AND RGP.RGD_CODE = 'LABILL'
3144: WHERE id = p_cle_id;
3145:
3146: CURSOR derive_top_line_id (p_lsm_id NUMBER) IS
3147: SELECT FA.ID
3148: FROM OKC_K_HEADERS_B CHR,
3149: OKC_K_LINES_B TOP_CLE,
3150: OKC_LINE_STYLES_b TOP_LSE,
3151: OKC_K_LINES_B SUB_CLE,
3152: OKC_LINE_STYLES_b SUB_LSE,
3848: okl_xtl_sell_invs_b xls,
3849: okl_txd_ar_ln_dtls_b tld,
3850: okl_txl_ar_inv_lns_b til,
3851: okl_trx_ar_invoices_b tai,
3852: okc_k_headers_b chr,
3853: okl_invoice_formats_b inf, -- 5138822
3854: okl_parallel_processes pws
3855: WHERE
3856: xsiB.TRX_STATUS_CODE = 'SUBMITTED' AND
3912: okl_xtl_sell_invs_b xls,
3913: okl_txd_ar_ln_dtls_b tld,
3914: okl_txl_ar_inv_lns_b til,
3915: okl_trx_ar_invoices_b tai,
3916: okc_k_headers_b chr,
3917: okl_invoice_formats_b inf -- 5138822
3918: WHERE
3919: xsib.TRX_STATUS_CODE = 'SUBMITTED' AND
3920: xls.xsi_id_details = xsib.id AND
3976: okl_ext_sell_invs_v xsi,
3977: okl_xtl_sell_invs_v xls,
3978: okl_txl_ar_inv_lns_v til,
3979: okl_trx_ar_invoices_v tai,
3980: okc_k_headers_b chr,
3981: okl_invoice_formats_b inf, -- 5138822
3982: okl_parallel_processes pws
3983: WHERE
3984: xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
4109: l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4110:
4111: CURSOR cntrct_csr ( p_id NUMBER ) IS
4112: SELECT contract_number
4113: FROM okc_k_headers_b
4114: WHERE id = p_id;
4115:
4116: CURSOR strm_csr ( p_id NUMBER ) IS
4117: SELECT name
4117: SELECT name
4118: FROM okl_strm_type_v
4119: WHERE id = p_id;
4120:
4121: l_contract_number okc_k_headers_b.contract_number%TYPE;
4122: l_stream_name1 okl_strm_type_v.name%TYPE;
4123:
4124:
4125: l_temp_khr_id NUMBER;