[Home] [Help]
559: FROM qp_event_phases
560: WHERE pricing_phase_id > 1
561: AND ROWNUM < 7)
562: AND EXISTS (SELECT /*+ ORDERED */ 'x' -- [julin/4261562] added active_flag and PTE/SS filters
563: FROM qp_list_header_phases lhb, qp_list_headers_b qph, qp_price_req_sources_v qprs, qp_list_lines a
564: WHERE lhb.pricing_phase_id = b.pricing_phase_id
565: AND qph.list_header_id = lhb.list_header_id
566: AND qph.active_flag = 'Y'
567: AND qprs.request_type_code = p_request_type_code
803: FROM qp_event_phases
804: WHERE pricing_phase_id > 1
805: AND ROWNUM < 7)
806: AND EXISTS (SELECT /*+ ORDERED */ 'x' -- [julin/4261562] added active_flag and PTE/SS filters
807: FROM qp_list_header_phases lhb, qp_list_headers_b qph, qp_price_req_sources_v qprs, qp_list_lines a
808: WHERE lhb.pricing_phase_id = b.pricing_phase_id
809: AND qph.list_header_id = lhb.list_header_id
810: AND qph.active_flag = 'Y'
811: AND qprs.request_type_code = p_request_type_code
1024: IS
1025:
1026: SELECT 'X'
1027: FROM qp_currency_details a
1028: ,qp_list_headers_b b
1029: WHERE a.currency_header_id = b.currency_header_id
1030: AND a.to_currency_code = l_currency_code
1031: AND b.list_header_id = l_price_list_id
1032: AND TRUNC(l_temp_date) >= TRUNC(NVL(a.start_date_active, l_temp_date))
1038: CURSOR c_validate_pl_curr_no_multi
1039: IS
1040:
1041: SELECT 'X'
1042: FROM qp_list_headers_b
1043: WHERE currency_code = l_currency_code
1044: AND list_header_id = l_price_list_id
1045: AND TRUNC(l_temp_date) >= TRUNC(NVL(start_date_active, l_temp_date))
1046: AND TRUNC(l_temp_date) <= TRUNC(NVL(end_date_active, l_temp_date));
1155: ,a.name currency_name
1156: ,a.PRECISION currency_precision
1157: FROM fnd_currencies_vl a
1158: ,qp_currency_details b
1159: ,qp_list_headers_b c
1160: WHERE c.list_header_id = l_price_list_id
1161: AND b.currency_header_id = c.currency_header_id
1162: AND a.currency_code = b.to_currency_code
1163: AND c.list_type_code IN ('PRL', 'AGR')
1862: a.agreement_type, q.name price_list_name, p.party_name customer_name,
1863: t.name payment_term_name, a.start_date_active, a.end_date_active
1864: FROM oe_agreements_lov_v a, qp_list_headers_vl q,
1865: hz_parties p, hz_cust_accounts c, ra_terms_tl t,
1866: -- qp_list_headers_b l,
1867: oe_transaction_types_all ot
1868: WHERE a.sold_to_org_id IN (
1869: SELECT TO_NUMBER(p_sold_to_org_id) FROM dual
1870: UNION
1922: select substr(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1923: a.agreement_type, q.name price_list_name, p.party_name customer_name,
1924: null payment_term_name, a.start_date_active, a.end_date_active
1925: from oe_agreements_lov_v a, qp_list_headers_vl q,
1926: hz_parties p, hz_cust_accounts c --, qp_list_headers_b l
1927: where a.sold_to_org_id in (
1928: select to_number(p_sold_to_org_id) from dual
1929: union
1930: select -1 from dual
1954: SELECT SUBSTRB(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1955: a.agreement_type, q.name price_list_name, NULL customer_name,
1956: NULL payment_term_name, a.start_date_active, a.end_date_active
1957: FROM oe_agreements_lov_v a, qp_list_headers_vl q
1958: --, qp_list_headers_b l
1959: WHERE l_temp_date BETWEEN
1960: TRUNC(NVL(a.start_date_active, ADD_MONTHS(SYSDATE, -10000))) AND
1961: TRUNC(NVL(a.end_date_active, ADD_MONTHS(SYSDATE, +10000))) AND
1962: a.price_list_id = q.list_header_id
2314:
2315: END IF;
2316: SELECT qcdt.selling_rounding_factor
2317: INTO l_rounding_factor
2318: FROM qp_list_headers_b qb, qp_currency_details qcdt
2319: WHERE qb.list_header_id = p_price_list_id
2320: AND qcdt.currency_header_id = qb.currency_header_id
2321: AND qcdt.to_currency_code = p_currency_code
2322: AND l_pricing_effective_date BETWEEN
2349:
2350: END IF;
2351: SELECT qb.rounding_factor
2352: INTO l_rounding_factor
2353: FROM qp_list_headers_b qb
2354: WHERE qb.list_header_id = p_price_list_id;
2355: /* for bug 2350218
2356: and qb.currency_code = p_currency_code
2357: and l_pricing_effective_date BETWEEN
2805: ,x_sql_string OUT NOCOPY VARCHAR2
2806: )
2807: IS
2808: l_no_multi_curr_sql VARCHAR2(2000) := 'SELECT distinct fnd.currency_code,fnd.name
2809: FROM fnd_currencies_vl fnd, qp_list_headers_b qlh
2810: WHERE fnd.currency_code = qlh.currency_code AND qlh.list_type_code in (''PRL'', ''AGR'')
2811: AND fnd.currency_flag = ''Y'' AND fnd.enabled_flag = ''Y''';
2812:
2813: l_multi_curr_sql VARCHAR2(2000) := 'SELECT distinct fnd.currency_code,fnd.name
2810: WHERE fnd.currency_code = qlh.currency_code AND qlh.list_type_code in (''PRL'', ''AGR'')
2811: AND fnd.currency_flag = ''Y'' AND fnd.enabled_flag = ''Y''';
2812:
2813: l_multi_curr_sql VARCHAR2(2000) := 'SELECT distinct fnd.currency_code,fnd.name
2814: FROM fnd_currencies_vl fnd, qp_currency_details qcd, qp_list_headers_b qlh
2815: WHERE qcd.currency_header_id = qlh.currency_header_id AND fnd.currency_code = qcd.to_currency_code
2816: AND qlh.list_type_code in (''PRL'', ''AGR'') and fnd.currency_flag = ''Y''
2817: AND fnd.enabled_flag = ''Y''';
2818: BEGIN
2962: l_MODIFIER_LIST_rec Qp_Modifiers_Pub.Modifier_List_Rec_Type;
2963: l_MODIFIER_LIST_tbl Qp_Modifiers_Pub.Modifier_List_Tbl_Type;
2964:
2965: CURSOR blanket_price_lists( p_blkt_header_id NUMBER) IS
2966: SELECT list_header_id FROM qp_list_headers_b
2967: WHERE list_source_code = 'BSO'
2968: AND orig_system_header_ref = p_blkt_header_id
2969: AND list_type_code = 'PRL';
2970:
2968: AND orig_system_header_ref = p_blkt_header_id
2969: AND list_type_code = 'PRL';
2970:
2971: CURSOR blanket_modifier_lists( p_blkt_header_id NUMBER) IS
2972: SELECT list_header_id FROM qp_list_headers_b
2973: WHERE list_source_code = 'BSO'
2974: AND orig_system_header_ref = p_blkt_header_id
2975: AND list_type_code NOT IN ('PRL','AGR');
2976:
3073: SELECT 'X'
3074: FROM
3075: QP_PRICING_ATTRIBUTES A,
3076: QP_LIST_LINES L,
3077: QP_LIST_HEADERS_B QLH
3078: WHERE
3079: A.LIST_HEADER_ID = P_Price_List_Id AND
3080: A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
3081: A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
3134: SELECT 'X'
3135: FROM
3136: QP_PRICING_ATTRIBUTES A,
3137: QP_LIST_LINES L,
3138: QP_LIST_HEADERS_B QLH
3139: WHERE
3140: A.LIST_HEADER_ID = P_Price_List_Id AND
3141: A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
3142: A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
3191: SELECT FL.price_modifier_list_id
3192: FROM
3193: QP_PRICING_ATTRIBUTES A,
3194: QP_LIST_LINES L,
3195: QP_LIST_HEADERS_B QLH,
3196: QP_PRICE_FORMULA_LINES FL
3197: WHERE
3198: A.LIST_HEADER_ID = P_Price_List_Id AND
3199: A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
3215: SELECT DISTINCT FL.pricing_attribute_context, FL.pricing_attribute
3216: FROM
3217: QP_PRICING_ATTRIBUTES A,
3218: QP_LIST_LINES L,
3219: QP_LIST_HEADERS_B QLH,
3220: QP_PRICE_FORMULA_LINES FL
3221: WHERE
3222: A.LIST_HEADER_ID = P_Price_List_Id AND
3223: A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND