[Home] [Help]
953: ,qlhv.name name
954: ,qlhv.description description
955: ,qlhv.start_date_active start_date_active
956: ,qlhv.end_date_active end_date_active
957: FROM qp_list_headers_vl qlhv
958: ,qp_currency_details qdt
959: WHERE qlhv.currency_header_id = qdt.currency_header_id
960: AND qdt.to_currency_code = l_currency_code
961: AND qlhv.active_flag = 'Y'
959: WHERE qlhv.currency_header_id = qdt.currency_header_id
960: AND qdt.to_currency_code = l_currency_code
961: AND qlhv.active_flag = 'Y'
962: --added for MOAC
963: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
964: --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
965: -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
966: AND qlhv.list_type_code = 'PRL'
967: -- If there is a blanket reference show all standard pricelist and
1004: ,qlhv.name name
1005: ,qlhv.description description
1006: ,qlhv.start_date_active start_date_active
1007: ,qlhv.end_date_active end_date_active
1008: FROM qp_list_headers_vl qlhv
1009: ,oe_agreements oa
1010: ,qp_currency_details qdt
1011: WHERE ( ( oa.price_list_id = qlhv.list_header_id
1012: AND qlhv.list_type_code IN ('PRL', 'AGR') )
1032: )
1033: )
1034: ) -- Blanket Pricing
1035: --added for MOAC
1036: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1037: --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1038: -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1039: AND qlhv.currency_header_id = qdt.currency_header_id
1040: AND qdt.to_currency_code = l_currency_code
1063: ,qlhv.name name
1064: ,qlhv.description description
1065: ,qlhv.start_date_active start_date_active
1066: ,qlhv.end_date_active end_date_active
1067: FROM qp_list_headers_vl qlhv
1068: ,oe_agreements oa
1069: ,qp_currency_details qdt
1070: WHERE ( ( oa.price_list_id = qlhv.list_header_id
1071: AND qlhv.list_type_code IN ('PRL', 'AGR') )
1091: )
1092: )
1093: ) -- Blanket Pricing
1094: --added for MOAC
1095: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1096: --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1097: -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1098: AND qlhv.currency_header_id = qdt.currency_header_id
1099: AND qdt.to_currency_code = l_currency_code
1129: ,qlhv.name name
1130: ,qlhv.description description
1131: ,qlhv.start_date_active start_date_active
1132: ,qlhv.end_date_active end_date_active
1133: FROM qp_list_headers_vl qlhv
1134: WHERE list_type_code = 'PRL'
1135: AND qlhv.active_flag = 'Y'
1136: --added for MOAC
1137: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1133: FROM qp_list_headers_vl qlhv
1134: WHERE list_type_code = 'PRL'
1135: AND qlhv.active_flag = 'Y'
1136: --added for MOAC
1137: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1138: --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1139: -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1140: --AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
1141: -- If there is a blanket reference show all standard pricelist and
1174: ,qlhv.description description
1175: ,qlhv.start_date_active start_date_active
1176: ,qlhv.end_date_active end_date_active
1177: FROM
1178: qp_list_headers_vl qlhv
1179: ,oe_agreements oa
1180: WHERE ( (oa.price_list_id = qlhv.list_header_id AND
1181: qlhv.list_type_code IN ('PRL', 'AGR'))
1182: OR
1201: )
1202: )
1203: ) -- Blanket Pricing
1204: --added for MOAC
1205: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1206: --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1207: -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1208: AND l_temp_date
1209: BETWEEN
1225: ,qlhv.description description
1226: ,qlhv.start_date_active start_date_active
1227: ,qlhv.end_date_active end_date_active
1228: FROM
1229: qp_list_headers_vl qlhv
1230: ,oe_agreements oa
1231: WHERE ( (oa.price_list_id = qlhv.list_header_id AND
1232: qlhv.list_type_code IN ('PRL', 'AGR'))
1233: OR
1251: )
1252: )
1253: ) -- Blanket Pricing
1254: --added for MOAC
1255: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1256: --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1257: -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1258: AND l_temp_date
1259: BETWEEN
1395: description description,
1396: -rounding_factor rounding_factor,
1397: start_date_active start_date_active,
1398: end_date_active end_date_active
1399: FROM qp_list_headers_vl
1400: WHERE list_type_code IN ('PRL' ,'AGR') AND
1401: NVL(active_flag,'N') ='Y'
1402: --added for MOAC
1403: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1399: FROM qp_list_headers_vl
1400: WHERE list_type_code IN ('PRL' ,'AGR') AND
1401: NVL(active_flag,'N') ='Y'
1402: --added for MOAC
1403: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1404: -- (((nvl(global_flag,'Y') = 'Y' or orig_org_id = fnd_profile.Value('ORG_ID'))
1405: -- and qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1406: AND source_system_code IN (SELECT qpss.application_short_name
1407: FROM qp_pte_source_systems qpss
1415: description description,
1416: -rounding_factor rounding_factor,
1417: start_date_active start_date_active,
1418: end_date_active end_date_active
1419: FROM qp_list_headers_vl
1420: WHERE currency_code = p_currency_code
1421: --added for MOAC
1422: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1423: -- (((nvl(global_flag,'Y') = 'Y' or orig_org_id = fnd_profile.Value('ORG_ID'))
1418: end_date_active end_date_active
1419: FROM qp_list_headers_vl
1420: WHERE currency_code = p_currency_code
1421: --added for MOAC
1422: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1423: -- (((nvl(global_flag,'Y') = 'Y' or orig_org_id = fnd_profile.Value('ORG_ID'))
1424: -- and qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
1425: AND TRUNC(l_temp_date) BETWEEN NVL(TRUNC(start_date_active), TRUNC(l_temp_date)) AND
1426: NVL(TRUNC(end_date_active), TRUNC(l_temp_date)) AND
1530: IS
1531: SELECT SUBSTRB(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1532: a.agreement_type, q.name price_list_name, p.party_name customer_name,
1533: t.name payment_term_name, a.start_date_active, a.end_date_active
1534: FROM oe_agreements_lov_v a, qp_list_headers_vl q,
1535: hz_parties p, hz_cust_accounts c, ra_terms_tl t,
1536: -- qp_list_headers_b l,
1537: oe_transaction_types_all ot
1538: WHERE a.sold_to_org_id IN (
1548: TRUNC(NVL(a.start_date_active, ADD_MONTHS(SYSDATE, -10000))) AND
1549: TRUNC(NVL(a.end_date_active, ADD_MONTHS(SYSDATE, +10000))) AND
1550: a.price_list_id = q.list_header_id
1551: --added for MOAC
1552: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1553: -- (((nvl(q.global_flag,'Y') = 'Y' or q.orig_org_id = fnd_profile.Value('ORG_ID')) and
1554: -- qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
1555: AND a.price_list_id = q.list_header_id AND
1556: -- q.language = userenv('LANG') and
1569: IS
1570: select substr(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1571: a.agreement_type, q.name price_list_name, null customer_name,
1572: t.name payment_term_name, a.start_date_active, a.end_date_active
1573: from oe_agreements_lov_v a, qp_list_headers_vl q, ra_terms_tl t,
1574: oe_transaction_types_all ot
1575: where l_temp_date between
1576: trunc(nvl(a.start_date_active, add_months(sysdate, -10000))) and
1577: trunc(nvl(a.end_date_active, add_months(sysdate, +10000))) and
1591: IS
1592: select substr(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1593: a.agreement_type, q.name price_list_name, p.party_name customer_name,
1594: null payment_term_name, a.start_date_active, a.end_date_active
1595: from oe_agreements_lov_v a, qp_list_headers_vl q,
1596: hz_parties p, hz_cust_accounts c --, qp_list_headers_b l
1597: where a.sold_to_org_id in (
1598: select to_number(p_sold_to_org_id) from dual
1599: union
1623: IS
1624: SELECT SUBSTRB(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1625: a.agreement_type, q.name price_list_name, NULL customer_name,
1626: NULL payment_term_name, a.start_date_active, a.end_date_active
1627: FROM oe_agreements_lov_v a, qp_list_headers_vl q
1628: --, qp_list_headers_b l
1629: WHERE l_temp_date BETWEEN
1630: TRUNC(NVL(a.start_date_active, ADD_MONTHS(SYSDATE, -10000))) AND
1631: TRUNC(NVL(a.end_date_active, ADD_MONTHS(SYSDATE, +10000))) AND
1630: TRUNC(NVL(a.start_date_active, ADD_MONTHS(SYSDATE, -10000))) AND
1631: TRUNC(NVL(a.end_date_active, ADD_MONTHS(SYSDATE, +10000))) AND
1632: a.price_list_id = q.list_header_id
1633: --added for MOAC
1634: --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1635: -- (((nvl(q.global_flag,'Y') = 'Y' or q.orig_org_id = fnd_profile.Value('ORG_ID')) and
1636: -- qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
1637: AND a.price_list_id = q.list_header_id
1638: -- q.language = userenv('LANG')