[Home] [Help]
1541: l_na_deduction_rule c_na_deduction_rule%ROWTYPE;
1542:
1543: -- BUG 11889816 start
1544: -- Removed org_id condition for PRM offers, changed Index Hint,
1545: -- replaced table OZF_NA_PRODUCTS_TEMP by OZF_NA_UNIQUE_PRODUCTS_TEMP
1546: -- kdass - bug 14777031 - split the query into 2 similar to c_order_line
1547: CURSOR c_order_line_prm ( p_start_date DATE
1548: , p_end_date DATE
1549: ) IS
1546: -- kdass - bug 14777031 - split the query into 2 similar to c_order_line
1547: CURSOR c_order_line_prm ( p_start_date DATE
1548: , p_end_date DATE
1549: ) IS
1550: SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1551: ol.header_id,
1552: ol.line_id,
1553: ol.invoice_to_org_id,
1554: ol.ship_to_org_id,
1567: ol.shipping_quantity_uom, -- Catch Weight ER
1568: ol.shipping_quantity2, -- Catch Weight ER
1569: ol.shipping_quantity_uom2, -- Catch Weight ER
1570: ol.fulfillment_base -- Catch Weight ER
1571: FROM ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) na,
1572: oe_order_lines_all ol,
1573: oe_order_headers_all oh
1574: WHERE ol.inventory_item_id = na.inventory_item_id
1575: --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1578: AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
1579: --AND ( NVL(OL.ACTUAL_SHIPMENT_DATE, OL.FULFILLMENT_DATE) BETWEEN p_start_date AND p_end_date)
1580: AND OL.HEADER_ID = OH.HEADER_ID
1581: UNION ALL
1582: SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1583: ol.header_id,
1584: ol.line_id,
1585: ol.invoice_to_org_id,
1586: ol.ship_to_org_id,
1599: ol.shipping_quantity_uom, -- Catch Weight ER
1600: ol.shipping_quantity2, -- Catch Weight ER
1601: ol.shipping_quantity_uom2, -- Catch Weight ER
1602: ol.fulfillment_base -- Catch Weight ER
1603: FROM ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) na,
1604: oe_order_lines_all ol,
1605: oe_order_headers_all oh
1606: WHERE ol.inventory_item_id = na.inventory_item_id
1607: --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1612: AND OL.HEADER_ID = OH.HEADER_ID;
1613:
1614: --Bug 8263942 - Modified c_order_line cursor:
1615: --Split the query into 2 based on ACTUAL_SHIPMENT_DATE, changed Index Hint,
1616: --replaced table OZF_NA_PRODUCTS_TEMP by OZF_NA_UNIQUE_PRODUCTS_TEMP
1617: CURSOR c_order_line (p_start_date DATE,
1618: p_end_date DATE,
1619: p_offer_org_id NUMBER) IS
1620: SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1616: --replaced table OZF_NA_PRODUCTS_TEMP by OZF_NA_UNIQUE_PRODUCTS_TEMP
1617: CURSOR c_order_line (p_start_date DATE,
1618: p_end_date DATE,
1619: p_offer_org_id NUMBER) IS
1620: SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1621: OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
1622: OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
1623: OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
1624: OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
1628: OL.SHIPPING_QUANTITY2, -- Catch Weight ER
1629: OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
1630: OL.FULFILLMENT_BASE -- Catch Weight ER
1631: FROM
1632: ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1633: OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
1634: WHERE
1635: OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1636: --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1639: AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
1640: AND OL.ORG_ID = p_offer_org_id
1641: AND OL.HEADER_ID = OH.HEADER_ID
1642: UNION ALL
1643: SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1644: OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
1645: OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
1646: OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
1647: OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
1651: OL.SHIPPING_QUANTITY2, -- Catch Weight ER
1652: OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
1653: OL.FULFILLMENT_BASE -- Catch Weight ER
1654: FROM
1655: ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1656: OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
1657: WHERE
1658: OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1659: --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1666: -- nepanda : bug # 14277687
1667: -- this cursor is for global offers where org_id is null for the offer.
1668: CURSOR c_order_line_global (p_start_date DATE,
1669: p_end_date DATE) IS
1670: SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1671: OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
1672: OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
1673: OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
1674: OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
1678: OL.SHIPPING_QUANTITY2, -- Catch Weight ER
1679: OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
1680: OL.FULFILLMENT_BASE -- Catch Weight ER
1681: FROM
1682: ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1683: OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
1684: WHERE
1685: OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1686: --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1688: AND OL.ACTUAL_SHIPMENT_DATE is not null
1689: AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
1690: AND OL.HEADER_ID = OH.HEADER_ID
1691: UNION ALL
1692: SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1693: OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
1694: OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
1695: OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
1696: OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
1700: OL.SHIPPING_QUANTITY2, -- Catch Weight ER
1701: OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
1702: OL.FULFILLMENT_BASE -- Catch Weight ER
1703: FROM
1704: ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1705: OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
1706: WHERE
1707: OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1708: --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1773: AND b.trx_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
1774: AND b.org_id = p_org_id;
1775:
1776:
1777: -- Bug 11889816 - changed Index Hint, replaced table OZF_NA_PRODUCTS_TEMP by OZF_NA_UNIQUE_PRODUCTS_TEMP
1778: -- CURSOR c_return_line (p_order_type_id NUMBER,
1779: -- p_start_date DATE,
1780: -- p_end_date DATE) IS
1781: -- SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1777: -- Bug 11889816 - changed Index Hint, replaced table OZF_NA_PRODUCTS_TEMP by OZF_NA_UNIQUE_PRODUCTS_TEMP
1778: -- CURSOR c_return_line (p_order_type_id NUMBER,
1779: -- p_start_date DATE,
1780: -- p_end_date DATE) IS
1781: -- SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1782: -- ol.header_id,
1783: -- ol.line_id,
1784: -- ol.actual_shipment_date,
1785: -- ol.fulfillment_date,
1795: -- ol.unit_selling_price,
1796: -- ol.org_id,
1797: -- NVL(ol.actual_arrival_date,ol.fulfillment_date) conv_date,
1798: -- oh.transactional_curr_code
1799: -- FROM ( SELECT /*+ no_merge */ DISTINCT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1800: -- oe_order_lines_all ol,
1801: -- oe_order_headers_all oh
1802: -- WHERE ol.inventory_item_id = na.inventory_item_id
1803: -- AND ol.open_flag = 'N'
1813: CURSOR c_return_line (p_order_type_id NUMBER,
1814: p_start_date DATE,
1815: p_end_date DATE) IS
1816: SELECT
1817: /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1818: OL.HEADER_ID,
1819: OL.LINE_ID,
1820: OL.INVOICE_TO_ORG_ID,
1821: OL.SHIP_TO_ORG_ID,
1830: OL.ORG_ID,
1831: OL.FULFILLMENT_DATE CONV_DATE,
1832: OH.TRANSACTIONAL_CURR_CODE
1833: FROM
1834: ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP) NA,
1835: OE_ORDER_LINES_ALL OL,
1836: OE_ORDER_HEADERS_ALL OH
1837: WHERE OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1838: AND OL.OPEN_FLAG = 'N'
1845:
1846: UNION ALL
1847:
1848: SELECT
1849: /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1850: OL.HEADER_ID,
1851: OL.LINE_ID,
1852: OL.INVOICE_TO_ORG_ID,
1853: OL.SHIP_TO_ORG_ID,
1862: OL.ORG_ID,
1863: OL.FULFILLMENT_DATE CONV_DATE,
1864: OH.TRANSACTIONAL_CURR_CODE
1865: FROM
1866: ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP) NA,
1867: OE_ORDER_LINES_ALL OL,
1868: OE_ORDER_HEADERS_ALL OH
1869: WHERE OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1870: AND OL.OPEN_FLAG = 'N'
2255:
2256: --AMITAMKU bug 14692296 - Moved truncate before SAVEPOINT net_accrual_offer to avoid AUTO-COMMIT, in case of failure of a net accrual offer
2257: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_customers_temp';
2258: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_products_temp';
2259: -- Bug 11889816 - Added TRUNCATE for OZF_NA_UNIQUE_PRODUCTS_TEMP
2260: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.OZF_NA_UNIQUE_PRODUCTS_TEMP';
2261:
2262:
2263: --AMITAMKU bug 14692296 - added BEGIN/EXCEPTION/END block for each net accrual offer so that program can continue for next offer if one offer fails
2256: --AMITAMKU bug 14692296 - Moved truncate before SAVEPOINT net_accrual_offer to avoid AUTO-COMMIT, in case of failure of a net accrual offer
2257: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_customers_temp';
2258: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_products_temp';
2259: -- Bug 11889816 - Added TRUNCATE for OZF_NA_UNIQUE_PRODUCTS_TEMP
2260: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.OZF_NA_UNIQUE_PRODUCTS_TEMP';
2261:
2262:
2263: --AMITAMKU bug 14692296 - added BEGIN/EXCEPTION/END block for each net accrual offer so that program can continue for next offer if one offer fails
2264: BEGIN
2408: RAISE Fnd_Api.g_exc_unexpected_error;
2409: END IF;
2410:
2411: --Bug 11889816 : start
2412: -- Insert distinct products to OZF_NA_UNIQUE_PRODUCTS_TEMP table to be used in Order cursors
2413: INSERT INTO OZF_NA_UNIQUE_PRODUCTS_TEMP(inventory_item_id)
2414: SELECT DISTINCT inventory_item_id FROM ozf_na_products_temp;
2415:
2416: SELECT count(*) INTO l_tot_products
2409: END IF;
2410:
2411: --Bug 11889816 : start
2412: -- Insert distinct products to OZF_NA_UNIQUE_PRODUCTS_TEMP table to be used in Order cursors
2413: INSERT INTO OZF_NA_UNIQUE_PRODUCTS_TEMP(inventory_item_id)
2414: SELECT DISTINCT inventory_item_id FROM ozf_na_products_temp;
2415:
2416: SELECT count(*) INTO l_tot_products
2417: FROM OZF_NA_UNIQUE_PRODUCTS_TEMP;
2413: INSERT INTO OZF_NA_UNIQUE_PRODUCTS_TEMP(inventory_item_id)
2414: SELECT DISTINCT inventory_item_id FROM ozf_na_products_temp;
2415:
2416: SELECT count(*) INTO l_tot_products
2417: FROM OZF_NA_UNIQUE_PRODUCTS_TEMP;
2418:
2419: ozf_utility_pvt.write_conc_log('Total Number of Unique Products: '||l_tot_products);
2420:
2421: SELECT count(*) INTO l_tot_customers
4663:
4664: --AMITAMKU bug 14692296 - moved truncate out of the loop to end of the program
4665: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_customers_temp';
4666: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_products_temp';
4667: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.OZF_NA_UNIQUE_PRODUCTS_TEMP';
4668:
4669: ozf_utility_pvt.write_conc_log('-- Done -- ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
4670:
4671: Fnd_Msg_Pub.Count_AND_Get(p_count => l_msg_count,