DBA Data[Home] [Help]

APPS.POS_SCO_TOLERANCE_PVT dependencies on PO_LINES_ALL

Line 661: po_lines_all pl

657: -- cursor to check for the COMPLEX WORK (Financing Case)
658: CURSOR c_line_unit_price_cw (p_po_release_id_csr IN NUMBER,p_po_header_id IN NUMBER,p_change_group_id_csr IN NUMBER) IS
659: SELECT pl.unit_price,pcr.new_price
660: FROM po_change_requests pcr,
661: po_lines_all pl
662: WHERE pcr.document_header_id= p_po_header_id
663: AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
664: AND pcr.request_level = 'LINE'
665: AND pcr.new_price IS NOT NULL

Line 956: FROM po_lines_all pl,

952:
953: -- Picks up Old Amount for SPO
954: CURSOR c_old_doc_amt_changes(p_po_header_id_csr IN NUMBER) IS
955: SELECT sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pl.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0)))))
956: FROM po_lines_all pl,
957: po_line_locations_all pll
958: WHERE pl.po_header_id = p_po_header_id_csr
959: AND pll.po_line_id = pl.po_line_id;
960: -- Picks up Old Amount For Releases

Line 963: FROM po_lines_all pl,

959: AND pll.po_line_id = pl.po_line_id;
960: -- Picks up Old Amount For Releases
961: CURSOR c_old_doc_amt_changes_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) IS
962: SELECT sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0)))))
963: FROM po_lines_all pl,
964: po_line_locations_all pll
965: WHERE pll.po_release_id = p_po_release_id_csr
966: AND pll.po_header_id = p_po_header_id_csr
967: AND pll.po_line_id = pl.po_line_id;

Line 980: FROM po_lines_all pl,

976: 'MILESTONE',
977: (pll.amount - NVL(pll.amount_cancelled,0)),
978: 'RATE',
979: (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override))))
980: FROM po_lines_all pl,
981: po_line_locations_all pll
982: WHERE pl.po_header_id = p_po_header_id_csr
983: AND pll.po_line_id = pl.po_line_id;
984:

Line 992: FROM po_lines_all pl

988: SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
989: (pl.quantity*pl.unit_price),
990: 'AMOUNT',
991: (pl.amount)))
992: FROM po_lines_all pl
993: WHERE pl.po_header_id = p_po_header_id_csr;
994:
995: l_po_header_id po_change_requests.document_header_id%TYPE;
996: l_po_release_id po_change_requests.po_release_id%TYPE;

Line 1167: po_lines_all pl,

1163: (nvl(pcr.new_price,pl.unit_price) *
1164: (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1165: FROM po_change_requests pcr, --line amount/price change
1166: po_change_requests pcr1, --shipment quantity change
1167: po_lines_all pl,
1168: po_line_locations_all pll
1169: WHERE pl.po_header_id = p_po_header_id_csr
1170: AND pll.po_line_id = pl.po_line_id
1171: AND pcr1.document_header_id (+) = p_po_header_id_csr

Line 1188: po_lines_all pl,

1184: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
1185: (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
1186: FROM po_change_requests pcr, --line amount/price change
1187: po_change_requests pcr2, --for split shipments
1188: po_lines_all pl,
1189: po_line_locations_all pll
1190: WHERE pl.po_header_id = p_po_header_id_csr
1191: AND pll.po_line_id = pl.po_line_id
1192: AND pcr2.document_header_id = p_po_header_id_csr

Line 1209: po_lines_all pl,

1205: -- old_price included for price breaks
1206: CURSOR c_new_doc_amt_changes_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER ) IS
1207: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)), (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) * (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1208: FROM po_change_requests pcr,
1209: po_lines_all pl,
1210: po_line_locations_all pll
1211: WHERE pll.po_header_id = p_po_header_id_csr
1212: AND pll.po_release_id = p_po_release_id_csr
1213: AND pll.po_line_id = pl.po_line_id

Line 1225: po_lines_all pl,

1221: AND pcr.document_line_location_id(+) = pll.line_location_id
1222: UNION ALL
1223: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(pcr2.new_price,pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
1224: FROM po_change_requests pcr2, -- for splitted shipments
1225: po_lines_all pl,
1226: po_line_locations_all pll
1227: WHERE pll.po_header_id = p_po_header_id_csr
1228: AND pll.po_line_id = pl.po_line_id
1229: AND pcr2.po_release_id(+) = p_po_release_id_csr

Line 1252: po_lines_all pl,

1248: 'RATE',
1249: (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1250: FROM
1251: po_change_requests pcr, --shipment changes
1252: po_lines_all pl,
1253: po_line_locations_all pll
1254: WHERE
1255: pl.po_header_id = p_po_header_id_csr
1256: AND pll.po_line_id = pl.po_line_id

Line 1276: po_lines_all pl,

1272: 'RATE',
1273: (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1274: FROM
1275: po_change_requests pcr, --shipment changes
1276: po_lines_all pl,
1277: po_line_locations_all pll
1278: WHERE
1279: pl.po_header_id = p_po_header_id_csr
1280: AND pll.po_line_id = pl.po_line_id

Line 1294: po_lines_all pl,

1290: CURSOR c_new_doc_amt_chg_cw_financing(p_po_header_id_csr IN NUMBER) IS
1291: SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',(pl.quantity*nvl(pcr.new_price,pl.unit_price)),
1292: 'AMOUNT',nvl(pcr.new_amount,pl.amount))),0)
1293: FROM
1294: po_lines_all pl,
1295: po_change_requests pcr
1296: WHERE
1297: pl.po_header_id = p_po_header_id_csr
1298: AND pcr.document_header_id (+) = p_po_header_id_csr

Line 1393: from po_lines_all pol,

1389: IS
1390: -- Picks up Old Line Amount for SPO
1391: CURSOR c_line_amt_old(p_po_header_id_csr IN NUMBER) IS
1392: select sum(decode(pol.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)), (pol.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0))))),pll.po_line_id
1393: from po_lines_all pol,
1394: po_line_locations_all pll
1395: where pll.po_header_id = p_po_header_id_csr
1396: AND pll.po_line_id = pol.po_line_id
1397: GROUP BY pll.po_line_id;

Line 1401: from po_lines_all pol,

1397: GROUP BY pll.po_line_id;
1398:
1399: CURSOR c_line_amt_old_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) IS
1400: select sum(decode(pol.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)), (pol.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0))))),pll.po_line_id
1401: from po_lines_all pol,
1402: po_line_locations_all pll
1403: where pll.po_release_id =p_po_release_id_csr
1404: AND pll.po_header_id = p_po_header_id_csr
1405: AND pll.po_line_id = pol.po_line_id

Line 1412: FROM po_lines_all pl

1408:
1409: -- Picks up Old Line Amount ( Financing Case)
1410: CURSOR c_line_amt_old_cw_financing(p_po_header_id_csr IN NUMBER) IS
1411: SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',pl.unit_price*pl.quantity,'AMOUNT',pl.amount)),pl.po_line_id
1412: FROM po_lines_all pl
1413: WHERE pl.po_header_id = p_po_header_id_csr
1414: GROUP BY pl.po_line_id;
1415:
1416: -- Picks up Old Line Amount ( Actuals case)

Line 1427: FROM po_lines_all pl,

1423: 'MILESTONE',
1424: (pll.amount - NVL(pll.amount_cancelled,0)),
1425: 'RATE',
1426: (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.po_line_id
1427: FROM po_lines_all pl,
1428: po_line_locations_all pll
1429: WHERE pl.po_header_id = p_po_header_id_csr
1430: AND pll.po_line_id = pl.po_line_id
1431: GROUP BY pll.po_line_id;

Line 1648: po_lines_all pl,

1644: (nvl(pcr.new_price,pl.unit_price) *
1645: (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1646: FROM po_change_requests pcr, --line amount/price change
1647: po_change_requests pcr1, --shipment quantity change
1648: po_lines_all pl,
1649: po_line_locations_all pll
1650: WHERE pl.po_header_id = p_po_header_id_csr
1651: AND pl.po_line_id = p_temp_po_line_id_csr
1652: AND pll.po_line_id = pl.po_line_id

Line 1670: po_lines_all pl,

1666: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
1667: (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
1668: FROM po_change_requests pcr, --line amount/price change
1669: po_change_requests pcr2, --for split shipments
1670: po_lines_all pl,
1671: po_line_locations_all pll
1672: WHERE pl.po_header_id = p_po_header_id_csr
1673: AND pl.po_line_id = p_temp_po_line_id_csr
1674: AND pll.po_line_id = pl.po_line_id

Line 1695: po_lines_all pl,

1691: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
1692: (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
1693: (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1694: FROM po_change_requests pcr,
1695: po_lines_all pl,
1696: po_line_locations_all pll
1697: WHERE pll.po_header_id = p_po_header_id_csr
1698: AND pll.po_line_id = p_temp_po_line_id_csr
1699: AND pll.po_line_id = pl.po_line_id

Line 1712: po_lines_all pl,

1708: AND pcr.document_line_location_id(+) = pll.line_location_id
1709: UNION ALL
1710: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(nvl(pcr2.new_price,pcr2.old_price),pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
1711: FROM po_change_requests pcr2, -- for splitted shipments
1712: po_lines_all pl,
1713: po_line_locations_all pll
1714: WHERE pll.po_header_id = p_po_header_id_csr
1715: AND pll.po_line_id = p_temp_po_line_id_csr
1716: AND pll.po_line_id = pl.po_line_id

Line 1740: po_lines_all pl,

1736: 'RATE',
1737: (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1738: FROM
1739: po_change_requests pcr, --shipment quantity changes
1740: po_lines_all pl,
1741: po_line_locations_all pll
1742: WHERE
1743: pl.po_header_id = p_po_header_id_csr
1744: AND pl.po_line_id = p_temp_po_line_id_csr

Line 1765: po_lines_all pl,

1761: 'RATE',
1762: (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1763: FROM
1764: po_change_requests pcr, --shipment quantity changes
1765: po_lines_all pl,
1766: po_line_locations_all pll
1767: WHERE
1768: pl.po_header_id = p_po_header_id_csr
1769: AND pl.po_line_id = p_temp_po_line_id_csr

Line 1784: po_lines_all pl,

1780: CURSOR c_line_amt_new_cw_financing(p_po_header_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1781: SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',(pl.quantity*nvl(pcr.new_price,pl.unit_price)),
1782: 'AMOUNT',nvl(pcr.new_amount,pl.amount))),0)
1783: FROM
1784: po_lines_all pl,
1785: po_change_requests pcr
1786: WHERE
1787: pl.po_header_id = p_po_header_id_csr
1788: AND pl.po_line_id = p_temp_po_line_id_csr

Line 1889: po_lines_all pol

1885: -- Calculates the old shipment amount
1886: CURSOR c_old_ship_amt(p_po_header_id_csr IN NUMBER) IS
1887: select sum(decode(pol.matching_basis,'AMOUNT',(nvl(pll.amount,0) - nvl(pll.amount_cancelled,0)),(pol.unit_price *(pll.quantity-nvl(pll.quantity_cancelled,0))))),pll.line_location_id
1888: from po_line_locations_all pll,
1889: po_lines_all pol
1890: where pll.po_header_id = p_po_header_id_csr
1891: AND pll.po_line_id = pol.po_line_id
1892: GROUP BY pll.line_location_id;
1893:

Line 1898: FROM po_lines_all pl,

1894: -- Calculate Old Shipment amount for the BPA Release
1895: CURSOR c_old_ship_amt_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) IS
1896: SELECT sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0))))),
1897: pll.line_location_id
1898: FROM po_lines_all pl,
1899: po_line_locations_all pll
1900: WHERE pll.po_release_id = p_po_release_id_csr
1901: AND pll.po_header_id = p_po_header_id_csr
1902: AND pll.po_line_id = pl.po_line_id

Line 1916: FROM po_lines_all pl,

1912: 'MILESTONE',
1913: (pll.amount - NVL(pll.amount_cancelled,0)),
1914: 'RATE',
1915: (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.line_location_id
1916: FROM po_lines_all pl,
1917: po_line_locations_all pll
1918: WHERE pl.po_header_id = p_po_header_id_csr
1919: AND pll.po_line_id = pl.po_line_id
1920: GROUP BY pll.line_location_id;

Line 1931: l_matching_basis po_lines_all.matching_basis%TYPE;

1927: l_po_header_id po_change_requests.document_header_id%TYPE;
1928: l_po_release_id po_change_requests.po_release_id%TYPE;
1929: l_po_line_id po_change_requests.document_line_id%TYPE;
1930: l_change_group_id po_change_requests.change_request_group_id%type;
1931: l_matching_basis po_lines_all.matching_basis%TYPE;
1932: l_old_shipamt po_change_requests.old_amount%TYPE;
1933: l_new_shipamt po_change_requests.new_amount%TYPE;
1934: l_old_ship_amt_rel NUMBER;
1935: l_new_ship_amt_rel NUMBER;

Line 2154: po_lines_all pl,

2150: (nvl(pcr.new_price,pl.unit_price) *
2151: (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
2152: FROM po_change_requests pcr, --line amount/price change
2153: po_change_requests pcr1, --shipment quantity change
2154: po_lines_all pl,
2155: po_line_locations_all pll
2156: WHERE pl.po_header_id = p_po_header_id_csr
2157: AND pll.line_location_id = p_line_location_id_csr
2158: AND pll.po_line_id = pl.po_line_id

Line 2177: po_lines_all pl,

2173: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
2174: (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
2175: FROM po_change_requests pcr, --line amount/price change
2176: po_change_requests pcr2, --for split shipments
2177: po_lines_all pl,
2178: po_line_locations_all pll
2179: WHERE pl.po_header_id = p_po_header_id_csr
2180: AND pll.line_location_id = p_line_location_id_csr
2181: AND pll.po_line_id = pl.po_line_id

Line 2202: po_lines_all pl,

2198: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
2199: (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
2200: (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
2201: FROM po_change_requests pcr,
2202: po_lines_all pl,
2203: po_line_locations_all pll
2204: WHERE pll.po_header_id = p_po_header_id_csr
2205: AND pll.line_location_id = p_line_location_id_csr
2206: AND pll.po_release_id = p_po_release_id_csr

Line 2220: po_lines_all pl,

2216:
2217: CURSOR c_new_ship_amt_rel_split(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER, p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR,p_po_shipment_num IN NUMBER) IS
2218: SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(nvl(pcr2.new_price,pcr2.old_price),pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
2219: FROM po_change_requests pcr2, -- for splitted shipments
2220: po_lines_all pl,
2221: po_line_locations_all pll
2222: WHERE pll.po_header_id = p_po_header_id_csr
2223: AND pll.line_location_id = p_line_location_id_csr
2224: AND pll.po_line_id = pl.po_line_id

Line 2248: po_lines_all pl,

2244: 'RATE',
2245: (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
2246: FROM
2247: po_change_requests pcr, --shipment quantity changes
2248: po_lines_all pl,
2249: po_line_locations_all pll
2250: WHERE
2251: pl.po_header_id = p_po_header_id_csr
2252: AND pll.line_location_id = p_line_location_id_csr

Line 2274: po_lines_all pl,

2270: 'RATE',
2271: (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
2272: FROM
2273: po_change_requests pcr, --shipment quantity changes
2274: po_lines_all pl,
2275: po_line_locations_all pll
2276: WHERE
2277: pl.po_header_id = p_po_header_id_csr
2278: AND pll.line_location_id = p_line_location_id_csr