[Home] [Help]
1775: WHERE org.set_of_books_id = gs.set_of_books_id
1776: AND org.org_id = bh.org_id
1777: AND bh.batch_id = p_batch_id;
1778:
1779: FND_DSQL.init;
1780:
1781: FND_DSQL.add_text('SELECT ');
1782: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1783: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1777: AND bh.batch_id = p_batch_id;
1778:
1779: FND_DSQL.init;
1780:
1781: FND_DSQL.add_text('SELECT ');
1782: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1783: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1784: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1778:
1779: FND_DSQL.init;
1780:
1781: FND_DSQL.add_text('SELECT ');
1782: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1783: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1784: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786: FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1779: FND_DSQL.init;
1780:
1781: FND_DSQL.add_text('SELECT ');
1782: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1783: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1784: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786: FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1787: FND_DSQL.add_text('HZCA.PARTY_ID, ');
1780:
1781: FND_DSQL.add_text('SELECT ');
1782: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1783: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1784: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786: FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1787: FND_DSQL.add_text('HZCA.PARTY_ID, ');
1788: FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1781: FND_DSQL.add_text('SELECT ');
1782: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1783: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1784: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786: FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1787: FND_DSQL.add_text('HZCA.PARTY_ID, ');
1788: FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1789: FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1782: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1783: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1784: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786: FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1787: FND_DSQL.add_text('HZCA.PARTY_ID, ');
1788: FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1789: FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1790:
1783: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1784: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786: FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1787: FND_DSQL.add_text('HZCA.PARTY_ID, ');
1788: FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1789: FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1790:
1791: FND_DSQL.add_text('OEL.END_CUSTOMER_ID, ');
1784: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786: FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1787: FND_DSQL.add_text('HZCA.PARTY_ID, ');
1788: FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1789: FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1790:
1791: FND_DSQL.add_text('OEL.END_CUSTOMER_ID, ');
1792: FND_DSQL.add_text('OEL.END_CUSTOMER_CONTACT_ID, ');
1785: FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786: FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1787: FND_DSQL.add_text('HZCA.PARTY_ID, ');
1788: FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1789: FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1790:
1791: FND_DSQL.add_text('OEL.END_CUSTOMER_ID, ');
1792: FND_DSQL.add_text('OEL.END_CUSTOMER_CONTACT_ID, ');
1793:
1787: FND_DSQL.add_text('HZCA.PARTY_ID, ');
1788: FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1789: FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1790:
1791: FND_DSQL.add_text('OEL.END_CUSTOMER_ID, ');
1792: FND_DSQL.add_text('OEL.END_CUSTOMER_CONTACT_ID, ');
1793:
1794:
1795: FND_DSQL.add_text('OEL.HEADER_ID, ');
1788: FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1789: FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1790:
1791: FND_DSQL.add_text('OEL.END_CUSTOMER_ID, ');
1792: FND_DSQL.add_text('OEL.END_CUSTOMER_CONTACT_ID, ');
1793:
1794:
1795: FND_DSQL.add_text('OEL.HEADER_ID, ');
1796: FND_DSQL.add_text('OEL.LINE_ID, ');
1791: FND_DSQL.add_text('OEL.END_CUSTOMER_ID, ');
1792: FND_DSQL.add_text('OEL.END_CUSTOMER_CONTACT_ID, ');
1793:
1794:
1795: FND_DSQL.add_text('OEL.HEADER_ID, ');
1796: FND_DSQL.add_text('OEL.LINE_ID, ');
1797:
1798:
1799: FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA
1792: FND_DSQL.add_text('OEL.END_CUSTOMER_CONTACT_ID, ');
1793:
1794:
1795: FND_DSQL.add_text('OEL.HEADER_ID, ');
1796: FND_DSQL.add_text('OEL.LINE_ID, ');
1797:
1798:
1799: FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA
1800: WHERE CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
1795: FND_DSQL.add_text('OEL.HEADER_ID, ');
1796: FND_DSQL.add_text('OEL.LINE_ID, ');
1797:
1798:
1799: FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA
1800: WHERE CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
1801: AND CTLA.LINE_TYPE = ''LINE''
1802: AND CTLA.INTERFACE_LINE_CONTEXT = ''ORDER ENTRY''
1803: AND ROWNUM = 1) TRX_NUMBER,');
1801: AND CTLA.LINE_TYPE = ''LINE''
1802: AND CTLA.INTERFACE_LINE_CONTEXT = ''ORDER ENTRY''
1803: AND ROWNUM = 1) TRX_NUMBER,');
1804:
1805: FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_LINE_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA
1806: WHERE CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
1807: AND CTLA.LINE_TYPE = ''LINE''
1808: AND CTLA.INTERFACE_LINE_CONTEXT = ''ORDER ENTRY''
1809: AND ROWNUM = 1) LINE_NUMBER, ');
1808: AND CTLA.INTERFACE_LINE_CONTEXT = ''ORDER ENTRY''
1809: AND ROWNUM = 1) LINE_NUMBER, ');
1810:
1811: --Compute Resale Price
1812: FND_DSQL.add_text('OEH.TRANSACTIONAL_CURR_CODE, ');
1813: FND_DSQL.add_text('OEL.UNIT_SELLING_PRICE, ');
1814:
1815: --Compute cost price
1816: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), ');
1809: AND ROWNUM = 1) LINE_NUMBER, ');
1810:
1811: --Compute Resale Price
1812: FND_DSQL.add_text('OEH.TRANSACTIONAL_CURR_CODE, ');
1813: FND_DSQL.add_text('OEL.UNIT_SELLING_PRICE, ');
1814:
1815: --Compute cost price
1816: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), ');
1817:
1812: FND_DSQL.add_text('OEH.TRANSACTIONAL_CURR_CODE, ');
1813: FND_DSQL.add_text('OEL.UNIT_SELLING_PRICE, ');
1814:
1815: --Compute cost price
1816: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), ');
1817:
1818: -- Call CONV_AMT_TO_OFFER_CURR_AMOUNT where
1819: -- To Currency Offer CURRENCY_CODE
1820: -- From Currency FU.DISCOUNT_AMOUNT_CURRENCY_CODE
1842: ''AMT'', null,
1843: ''NEWPRICE'', '|| l_func_call_string ||' + FU.PLAN_CURR_AMOUNT_REMAINING/NVL(oel.invoiced_quantity, NVL('|| l_shipped_qty_string || ' , 1)) ),FU.PLAN_CURRENCY_CODE) )');
1844: END IF;
1845:
1846: FND_DSQL.add_text(l_cost_price_string || 'COST_PRICE' );
1847:
1848: FND_DSQL.add_text(', DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), ');
1849:
1850: l_agmt_price_string:= ('decode (FU.DISCOUNT_TYPE,
1844: END IF;
1845:
1846: FND_DSQL.add_text(l_cost_price_string || 'COST_PRICE' );
1847:
1848: FND_DSQL.add_text(', DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), ');
1849:
1850: l_agmt_price_string:= ('decode (FU.DISCOUNT_TYPE,
1851: ''%'', ('|| l_cost_price_string ||' * (1 - FU.DISCOUNT_AMOUNT/100)),
1852: ''AMT'', null,
1854: l_func_call_string || ') ');
1855:
1856: l_agmt_price_string:= 'OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC('||l_agmt_price_string||', FU.PLAN_CURRENCY_CODE)';
1857:
1858: FND_DSQL.add_text(l_agmt_price_string || 'APPROVED_DISCOUNT_VALUE' );
1859:
1860: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
1861:
1862: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
1856: l_agmt_price_string:= 'OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC('||l_agmt_price_string||', FU.PLAN_CURRENCY_CODE)';
1857:
1858: FND_DSQL.add_text(l_agmt_price_string || 'APPROVED_DISCOUNT_VALUE' );
1859:
1860: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
1861:
1862: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
1863: FND_DSQL.add_bind(''||p_batch_currency||'' );
1864: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1858: FND_DSQL.add_text(l_agmt_price_string || 'APPROVED_DISCOUNT_VALUE' );
1859:
1860: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
1861:
1862: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
1863: FND_DSQL.add_bind(''||p_batch_currency||'' );
1864: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1865: FND_DSQL.add_bind(''||l_func_currency||'' );
1866: FND_DSQL.add_text('=');
1859:
1860: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
1861:
1862: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
1863: FND_DSQL.add_bind(''||p_batch_currency||'' );
1864: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1865: FND_DSQL.add_bind(''||l_func_currency||'' );
1866: FND_DSQL.add_text('=');
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1860: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
1861:
1862: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
1863: FND_DSQL.add_bind(''||p_batch_currency||'' );
1864: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1865: FND_DSQL.add_bind(''||l_func_currency||'' );
1866: FND_DSQL.add_text('=');
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1861:
1862: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
1863: FND_DSQL.add_bind(''||p_batch_currency||'' );
1864: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1865: FND_DSQL.add_bind(''||l_func_currency||'' );
1866: FND_DSQL.add_text('=');
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1862: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
1863: FND_DSQL.add_bind(''||p_batch_currency||'' );
1864: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1865: FND_DSQL.add_bind(''||l_func_currency||'' );
1866: FND_DSQL.add_text('=');
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1870: FND_DSQL.add_text(',');
1863: FND_DSQL.add_bind(''||p_batch_currency||'' );
1864: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1865: FND_DSQL.add_bind(''||l_func_currency||'' );
1866: FND_DSQL.add_text('=');
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1870: FND_DSQL.add_text(',');
1871: FND_DSQL.add_bind(''||p_batch_currency||'' );
1864: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1865: FND_DSQL.add_bind(''||l_func_currency||'' );
1866: FND_DSQL.add_text('=');
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1870: FND_DSQL.add_text(',');
1871: FND_DSQL.add_bind(''||p_batch_currency||'' );
1872: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1865: FND_DSQL.add_bind(''||l_func_currency||'' );
1866: FND_DSQL.add_text('=');
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1870: FND_DSQL.add_text(',');
1871: FND_DSQL.add_bind(''||p_batch_currency||'' );
1872: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1873: FND_DSQL.add_bind(''||l_date||'' );
1866: FND_DSQL.add_text('=');
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1870: FND_DSQL.add_text(',');
1871: FND_DSQL.add_bind(''||p_batch_currency||'' );
1872: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1873: FND_DSQL.add_bind(''||l_date||'' );
1874: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
1867: FND_DSQL.add_bind(''||p_batch_currency||'' );
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1870: FND_DSQL.add_text(',');
1871: FND_DSQL.add_bind(''||p_batch_currency||'' );
1872: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1873: FND_DSQL.add_bind(''||l_date||'' );
1874: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
1875:
1868: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1870: FND_DSQL.add_text(',');
1871: FND_DSQL.add_bind(''||p_batch_currency||'' );
1872: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1873: FND_DSQL.add_bind(''||l_date||'' );
1874: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
1875:
1876: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
1869: FND_DSQL.add_bind(''||l_func_currency||'' );
1870: FND_DSQL.add_text(',');
1871: FND_DSQL.add_bind(''||p_batch_currency||'' );
1872: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1873: FND_DSQL.add_bind(''||l_date||'' );
1874: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
1875:
1876: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
1877:
1870: FND_DSQL.add_text(',');
1871: FND_DSQL.add_bind(''||p_batch_currency||'' );
1872: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1873: FND_DSQL.add_bind(''||l_date||'' );
1874: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
1875:
1876: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
1877:
1878: FND_DSQL.add_text(' OEL.ORDER_QUANTITY_UOM, ');
1872: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1873: FND_DSQL.add_bind(''||l_date||'' );
1874: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
1875:
1876: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
1877:
1878: FND_DSQL.add_text(' OEL.ORDER_QUANTITY_UOM, ');
1879:
1880: IF l_ozf_gl_entries = 'SHIPPED' THEN
1874: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
1875:
1876: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
1877:
1878: FND_DSQL.add_text(' OEL.ORDER_QUANTITY_UOM, ');
1879:
1880: IF l_ozf_gl_entries = 'SHIPPED' THEN
1881: --If accrual is negative for shipped profile, set the quantity as negative. This is required for RMA orders
1882: FND_DSQL.add_text(l_shipped_qty_string || ' , ');
1878: FND_DSQL.add_text(' OEL.ORDER_QUANTITY_UOM, ');
1879:
1880: IF l_ozf_gl_entries = 'SHIPPED' THEN
1881: --If accrual is negative for shipped profile, set the quantity as negative. This is required for RMA orders
1882: FND_DSQL.add_text(l_shipped_qty_string || ' , ');
1883: END IF;
1884:
1885: IF l_ozf_gl_entries = 'INVOICED' THEN
1886: FND_DSQL.add_text('NVL(oel.invoiced_quantity, NVL('|| l_shipped_qty_string || ' ,1)), ');
1882: FND_DSQL.add_text(l_shipped_qty_string || ' , ');
1883: END IF;
1884:
1885: IF l_ozf_gl_entries = 'INVOICED' THEN
1886: FND_DSQL.add_text('NVL(oel.invoiced_quantity, NVL('|| l_shipped_qty_string || ' ,1)), ');
1887: END IF;
1888:
1889: FND_DSQL.add_text('OEH.ordered_date, ');
1890:
1885: IF l_ozf_gl_entries = 'INVOICED' THEN
1886: FND_DSQL.add_text('NVL(oel.invoiced_quantity, NVL('|| l_shipped_qty_string || ' ,1)), ');
1887: END IF;
1888:
1889: FND_DSQL.add_text('OEH.ordered_date, ');
1890:
1891: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1892: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1893: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1887: END IF;
1888:
1889: FND_DSQL.add_text('OEH.ordered_date, ');
1890:
1891: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1892: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1893: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1894: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
1895: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1888:
1889: FND_DSQL.add_text('OEH.ordered_date, ');
1890:
1891: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1892: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1893: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1894: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
1895: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1896: FND_DSQL.add_text('OEL.SHIP_FROM_ORG_ID, '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.
1889: FND_DSQL.add_text('OEH.ordered_date, ');
1890:
1891: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1892: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1893: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1894: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
1895: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1896: FND_DSQL.add_text('OEL.SHIP_FROM_ORG_ID, '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.
1897: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
1890:
1891: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1892: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1893: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1894: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
1895: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1896: FND_DSQL.add_text('OEL.SHIP_FROM_ORG_ID, '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.
1897: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
1898:
1891: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1892: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1893: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1894: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
1895: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1896: FND_DSQL.add_text('OEL.SHIP_FROM_ORG_ID, '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.
1897: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
1898:
1899: --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
1892: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1893: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1894: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
1895: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1896: FND_DSQL.add_text('OEL.SHIP_FROM_ORG_ID, '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.
1897: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
1898:
1899: --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
1900: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,
1893: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1894: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
1895: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1896: FND_DSQL.add_text('OEL.SHIP_FROM_ORG_ID, '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.
1897: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
1898:
1899: --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
1900: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,
1901: ''NEWPRICE'', ' || l_agmt_price_string || ',
1896: FND_DSQL.add_text('OEL.SHIP_FROM_ORG_ID, '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.
1897: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
1898:
1899: --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
1900: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,
1901: ''NEWPRICE'', ' || l_agmt_price_string || ',
1902: ''AMT'', '|| l_func_call_string ||',
1903: ''%'', FU.DISCOUNT_AMOUNT ) APPROVED_DISCOUNT_VALUE, ');
1904:
1901: ''NEWPRICE'', ' || l_agmt_price_string || ',
1902: ''AMT'', '|| l_func_call_string ||',
1903: ''%'', FU.DISCOUNT_AMOUNT ) APPROVED_DISCOUNT_VALUE, ');
1904:
1905: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1906: FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
1907:
1908: --BEGIN FROM CLAUSE
1909: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1902: ''AMT'', '|| l_func_call_string ||',
1903: ''%'', FU.DISCOUNT_AMOUNT ) APPROVED_DISCOUNT_VALUE, ');
1904:
1905: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1906: FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
1907:
1908: --BEGIN FROM CLAUSE
1909: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1910: FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1905: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1906: FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
1907:
1908: --BEGIN FROM CLAUSE
1909: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1910: FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1911: FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1912: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913: FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1906: FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
1907:
1908: --BEGIN FROM CLAUSE
1909: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1910: FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1911: FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1912: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913: FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1914: FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1907:
1908: --BEGIN FROM CLAUSE
1909: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1910: FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1911: FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1912: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913: FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1914: FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1915: FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1908: --BEGIN FROM CLAUSE
1909: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1910: FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1911: FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1912: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913: FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1914: FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1915: FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1916: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
1909: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1910: FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1911: FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1912: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913: FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1914: FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1915: FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1916: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
1917:
1910: FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1911: FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1912: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913: FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1914: FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1915: FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1916: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
1917:
1918: --BEGIN WHERE CLAUSE
1911: FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1912: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913: FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1914: FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1915: FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1916: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
1917:
1918: --BEGIN WHERE CLAUSE
1919: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
1912: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913: FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1914: FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1915: FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1916: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
1917:
1918: --BEGIN WHERE CLAUSE
1919: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
1920: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1915: FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1916: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
1917:
1918: --BEGIN WHERE CLAUSE
1919: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
1920: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1921: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
1922: FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1916: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
1917:
1918: --BEGIN WHERE CLAUSE
1919: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
1920: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1921: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
1922: FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1924: FND_DSQL.add_bind(p_supplier_site_id);
1917:
1918: --BEGIN WHERE CLAUSE
1919: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
1920: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1921: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
1922: FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1924: FND_DSQL.add_bind(p_supplier_site_id);
1925: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1918: --BEGIN WHERE CLAUSE
1919: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
1920: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1921: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
1922: FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1924: FND_DSQL.add_bind(p_supplier_site_id);
1925: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1926: FND_DSQL.add_bind(l_org_id);
1919: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
1920: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1921: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
1922: FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1924: FND_DSQL.add_bind(p_supplier_site_id);
1925: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1926: FND_DSQL.add_bind(l_org_id);
1927:
1920: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1921: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
1922: FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1924: FND_DSQL.add_bind(p_supplier_site_id);
1925: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1926: FND_DSQL.add_bind(l_org_id);
1927:
1928: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
1921: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
1922: FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1924: FND_DSQL.add_bind(p_supplier_site_id);
1925: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1926: FND_DSQL.add_bind(l_org_id);
1927:
1928: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
1929: FND_DSQL.add_bind(l_org_id);
1922: FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1924: FND_DSQL.add_bind(p_supplier_site_id);
1925: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1926: FND_DSQL.add_bind(l_org_id);
1927:
1928: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
1929: FND_DSQL.add_bind(l_org_id);
1930:
1924: FND_DSQL.add_bind(p_supplier_site_id);
1925: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1926: FND_DSQL.add_bind(l_org_id);
1927:
1928: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
1929: FND_DSQL.add_bind(l_org_id);
1930:
1931: FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1932: FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1925: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1926: FND_DSQL.add_bind(l_org_id);
1927:
1928: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
1929: FND_DSQL.add_bind(l_org_id);
1930:
1931: FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1932: FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1933: FND_DSQL.add_text('HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID AND ');
1927:
1928: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
1929: FND_DSQL.add_bind(l_org_id);
1930:
1931: FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1932: FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1933: FND_DSQL.add_text('HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID AND ');
1934: FND_DSQL.add_text('FU.ORDER_LINE_ID = OEL.LINE_ID AND ');
1935: FND_DSQL.add_text('FU.GL_POSTED_FLAG = ''Y'' AND ');
1928: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
1929: FND_DSQL.add_bind(l_org_id);
1930:
1931: FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1932: FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1933: FND_DSQL.add_text('HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID AND ');
1934: FND_DSQL.add_text('FU.ORDER_LINE_ID = OEL.LINE_ID AND ');
1935: FND_DSQL.add_text('FU.GL_POSTED_FLAG = ''Y'' AND ');
1936:
1929: FND_DSQL.add_bind(l_org_id);
1930:
1931: FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1932: FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1933: FND_DSQL.add_text('HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID AND ');
1934: FND_DSQL.add_text('FU.ORDER_LINE_ID = OEL.LINE_ID AND ');
1935: FND_DSQL.add_text('FU.GL_POSTED_FLAG = ''Y'' AND ');
1936:
1937: IF l_ozf_gl_entries = 'SHIPPED' THEN
1930:
1931: FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1932: FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1933: FND_DSQL.add_text('HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID AND ');
1934: FND_DSQL.add_text('FU.ORDER_LINE_ID = OEL.LINE_ID AND ');
1935: FND_DSQL.add_text('FU.GL_POSTED_FLAG = ''Y'' AND ');
1936:
1937: IF l_ozf_gl_entries = 'SHIPPED' THEN
1938: FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND ');
1931: FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1932: FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1933: FND_DSQL.add_text('HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID AND ');
1934: FND_DSQL.add_text('FU.ORDER_LINE_ID = OEL.LINE_ID AND ');
1935: FND_DSQL.add_text('FU.GL_POSTED_FLAG = ''Y'' AND ');
1936:
1937: IF l_ozf_gl_entries = 'SHIPPED' THEN
1938: FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND ');
1939: END IF;
1934: FND_DSQL.add_text('FU.ORDER_LINE_ID = OEL.LINE_ID AND ');
1935: FND_DSQL.add_text('FU.GL_POSTED_FLAG = ''Y'' AND ');
1936:
1937: IF l_ozf_gl_entries = 'SHIPPED' THEN
1938: FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND ');
1939: END IF;
1940:
1941: IF l_ozf_gl_entries = 'INVOICED' THEN
1942: FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND oel.flow_status_code in (''CLOSED'',''INVOICED'') AND ');
1938: FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND ');
1939: END IF;
1940:
1941: IF l_ozf_gl_entries = 'INVOICED' THEN
1942: FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND oel.flow_status_code in (''CLOSED'',''INVOICED'') AND ');
1943: END IF;
1944:
1945: FND_DSQL.add_text(' RH.OFFER_TYPE= ''ACCRUAL'' ');
1946: -- FND_DSQL.add_text(' RL.ORG_ID =' || l_org_id); -- AND '); --this was commented already
1941: IF l_ozf_gl_entries = 'INVOICED' THEN
1942: FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND oel.flow_status_code in (''CLOSED'',''INVOICED'') AND ');
1943: END IF;
1944:
1945: FND_DSQL.add_text(' RH.OFFER_TYPE= ''ACCRUAL'' ');
1946: -- FND_DSQL.add_text(' RL.ORG_ID =' || l_org_id); -- AND '); --this was commented already
1947:
1948: IF g_currency IS NULL THEN
1949: -- currency not set on trade profile
1942: FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND oel.flow_status_code in (''CLOSED'',''INVOICED'') AND ');
1943: END IF;
1944:
1945: FND_DSQL.add_text(' RH.OFFER_TYPE= ''ACCRUAL'' ');
1946: -- FND_DSQL.add_text(' RL.ORG_ID =' || l_org_id); -- AND '); --this was commented already
1947:
1948: IF g_currency IS NULL THEN
1949: -- currency not set on trade profile
1950: FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
1946: -- FND_DSQL.add_text(' RL.ORG_ID =' || l_org_id); -- AND '); --this was commented already
1947:
1948: IF g_currency IS NULL THEN
1949: -- currency not set on trade profile
1950: FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
1951: FND_DSQL.add_bind('' || l_batch_currency || '');
1952: END IF;
1953:
1954: --request ID filter
1947:
1948: IF g_currency IS NULL THEN
1949: -- currency not set on trade profile
1950: FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
1951: FND_DSQL.add_bind('' || l_batch_currency || '');
1952: END IF;
1953:
1954: --request ID filter
1955: IF p_request_id is not null then
1952: END IF;
1953:
1954: --request ID filter
1955: IF p_request_id is not null then
1956: FND_DSQL.add_text(' AND RH.REQUEST_HEADER_ID = ' );
1957: FND_DSQL.add_bind(p_request_id);
1958: END IF;
1959:
1960: --product filter
1953:
1954: --request ID filter
1955: IF p_request_id is not null then
1956: FND_DSQL.add_text(' AND RH.REQUEST_HEADER_ID = ' );
1957: FND_DSQL.add_bind(p_request_id);
1958: END IF;
1959:
1960: --product filter
1961: IF p_product_id is not null then
1958: END IF;
1959:
1960: --product filter
1961: IF p_product_id is not null then
1962: FND_DSQL.add_text(' AND FU.PRODUCT_ID = ' );
1963: FND_DSQL.add_bind(p_product_id);
1964: END IF;
1965:
1966: -- offer filter
1959:
1960: --product filter
1961: IF p_product_id is not null then
1962: FND_DSQL.add_text(' AND FU.PRODUCT_ID = ' );
1963: FND_DSQL.add_bind(p_product_id);
1964: END IF;
1965:
1966: -- offer filter
1967: IF p_fund_id is not null then
1964: END IF;
1965:
1966: -- offer filter
1967: IF p_fund_id is not null then
1968: FND_DSQL.add_text(' AND FU.fund_id = ' );
1969: FND_DSQL.add_bind(p_fund_id);
1970: END IF;
1971:
1972: --start date filter
1965:
1966: -- offer filter
1967: IF p_fund_id is not null then
1968: FND_DSQL.add_text(' AND FU.fund_id = ' );
1969: FND_DSQL.add_bind(p_fund_id);
1970: END IF;
1971:
1972: --start date filter
1973: IF p_start_date is not null then
1970: END IF;
1971:
1972: --start date filter
1973: IF p_start_date is not null then
1974: FND_DSQL.add_text(' AND FU.creation_date >=' );
1975: FND_DSQL.add_bind('' || p_start_date || '');
1976: END IF;
1977:
1978: --end date filter
1971:
1972: --start date filter
1973: IF p_start_date is not null then
1974: FND_DSQL.add_text(' AND FU.creation_date >=' );
1975: FND_DSQL.add_bind('' || p_start_date || '');
1976: END IF;
1977:
1978: --end date filter
1979: IF p_end_date is not null then
1976: END IF;
1977:
1978: --end date filter
1979: IF p_end_date is not null then
1980: FND_DSQL.add_text(' AND TRUNC(FU.creation_date) <=' );
1981: FND_DSQL.add_bind('' || p_end_date || '');
1982: END IF;
1983:
1984: --period filter
1977:
1978: --end date filter
1979: IF p_end_date is not null then
1980: FND_DSQL.add_text(' AND TRUNC(FU.creation_date) <=' );
1981: FND_DSQL.add_bind('' || p_end_date || '');
1982: END IF;
1983:
1984: --period filter
1985: IF p_period is not null then
1988: INTO l_period_start, l_period_end;
1989: CLOSE get_period_limits;
1990:
1991: IF l_period_start is not null then
1992: FND_DSQL.add_text(' AND FU.creation_date >= ');
1993: FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1994: END IF;
1995:
1996: IF l_period_end is not null then
1989: CLOSE get_period_limits;
1990:
1991: IF l_period_start is not null then
1992: FND_DSQL.add_text(' AND FU.creation_date >= ');
1993: FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1994: END IF;
1995:
1996: IF l_period_end is not null then
1997: FND_DSQL.add_text(' AND FU.creation_date <= ');
1993: FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1994: END IF;
1995:
1996: IF l_period_end is not null then
1997: FND_DSQL.add_text(' AND FU.creation_date <= ');
1998: FND_DSQL.add_bind(''||to_date(to_char(l_period_end,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1999: END IF;
2000:
2001: END IF;
1994: END IF;
1995:
1996: IF l_period_end is not null then
1997: FND_DSQL.add_text(' AND FU.creation_date <= ');
1998: FND_DSQL.add_bind(''||to_date(to_char(l_period_end,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1999: END IF;
2000:
2001: END IF;
2002:
2001: END IF;
2002:
2003: -- attribute1 filter
2004: IF p_attribute1 is not null then
2005: FND_DSQL.add_text(' AND FU.ATTRIBUTE1 =' );
2006: FND_DSQL.add_bind('' || p_attribute1 || '');
2007: END IF;
2008:
2009: -- attribute2 filter
2002:
2003: -- attribute1 filter
2004: IF p_attribute1 is not null then
2005: FND_DSQL.add_text(' AND FU.ATTRIBUTE1 =' );
2006: FND_DSQL.add_bind('' || p_attribute1 || '');
2007: END IF;
2008:
2009: -- attribute2 filter
2010: IF p_attribute2 is not null then
2007: END IF;
2008:
2009: -- attribute2 filter
2010: IF p_attribute2 is not null then
2011: FND_DSQL.add_text(' AND FU.ATTRIBUTE2 =' );
2012: FND_DSQL.add_bind('' || p_attribute2 || '');
2013: END IF;
2014:
2015: -- attribute3 filter
2008:
2009: -- attribute2 filter
2010: IF p_attribute2 is not null then
2011: FND_DSQL.add_text(' AND FU.ATTRIBUTE2 =' );
2012: FND_DSQL.add_bind('' || p_attribute2 || '');
2013: END IF;
2014:
2015: -- attribute3 filter
2016: IF p_attribute3 is not null then
2013: END IF;
2014:
2015: -- attribute3 filter
2016: IF p_attribute3 is not null then
2017: FND_DSQL.add_text(' AND FU.ATTRIBUTE3 =' );
2018: FND_DSQL.add_bind('' || p_attribute3 || '');
2019: END IF;
2020:
2021: -- attribute4 filter
2014:
2015: -- attribute3 filter
2016: IF p_attribute3 is not null then
2017: FND_DSQL.add_text(' AND FU.ATTRIBUTE3 =' );
2018: FND_DSQL.add_bind('' || p_attribute3 || '');
2019: END IF;
2020:
2021: -- attribute4 filter
2022: IF p_attribute4 is not null then
2019: END IF;
2020:
2021: -- attribute4 filter
2022: IF p_attribute4 is not null then
2023: FND_DSQL.add_text(' AND FU.ATTRIBUTE4 =' );
2024: FND_DSQL.add_bind('' || p_attribute4 || '');
2025: END IF;
2026:
2027: -- attribute5 filter
2020:
2021: -- attribute4 filter
2022: IF p_attribute4 is not null then
2023: FND_DSQL.add_text(' AND FU.ATTRIBUTE4 =' );
2024: FND_DSQL.add_bind('' || p_attribute4 || '');
2025: END IF;
2026:
2027: -- attribute5 filter
2028: IF p_attribute5 is not null then
2025: END IF;
2026:
2027: -- attribute5 filter
2028: IF p_attribute5 is not null then
2029: FND_DSQL.add_text(' AND FU.ATTRIBUTE5 =' );
2030: FND_DSQL.add_bind('' || p_attribute5 || '');
2031: END IF;
2032:
2033: -- attribute6 filter
2026:
2027: -- attribute5 filter
2028: IF p_attribute5 is not null then
2029: FND_DSQL.add_text(' AND FU.ATTRIBUTE5 =' );
2030: FND_DSQL.add_bind('' || p_attribute5 || '');
2031: END IF;
2032:
2033: -- attribute6 filter
2034: IF p_attribute6 is not null then
2031: END IF;
2032:
2033: -- attribute6 filter
2034: IF p_attribute6 is not null then
2035: FND_DSQL.add_text(' AND FU.ATTRIBUTE6 =' );
2036: FND_DSQL.add_bind('' || p_attribute6 || '');
2037: END IF;
2038:
2039: -- attribute7 filter
2032:
2033: -- attribute6 filter
2034: IF p_attribute6 is not null then
2035: FND_DSQL.add_text(' AND FU.ATTRIBUTE6 =' );
2036: FND_DSQL.add_bind('' || p_attribute6 || '');
2037: END IF;
2038:
2039: -- attribute7 filter
2040: IF p_attribute7 is not null then
2037: END IF;
2038:
2039: -- attribute7 filter
2040: IF p_attribute7 is not null then
2041: FND_DSQL.add_text(' AND FU.ATTRIBUTE7 =' );
2042: FND_DSQL.add_bind('' || p_attribute7 || '');
2043: END IF;
2044:
2045: -- attribute8 filter
2038:
2039: -- attribute7 filter
2040: IF p_attribute7 is not null then
2041: FND_DSQL.add_text(' AND FU.ATTRIBUTE7 =' );
2042: FND_DSQL.add_bind('' || p_attribute7 || '');
2043: END IF;
2044:
2045: -- attribute8 filter
2046: IF p_attribute8 is not null then
2043: END IF;
2044:
2045: -- attribute8 filter
2046: IF p_attribute8 is not null then
2047: FND_DSQL.add_text(' AND FU.ATTRIBUTE8 =' );
2048: FND_DSQL.add_bind('' || p_attribute8 || '');
2049: END IF;
2050:
2051: -- attribute9 filter
2044:
2045: -- attribute8 filter
2046: IF p_attribute8 is not null then
2047: FND_DSQL.add_text(' AND FU.ATTRIBUTE8 =' );
2048: FND_DSQL.add_bind('' || p_attribute8 || '');
2049: END IF;
2050:
2051: -- attribute9 filter
2052: IF p_attribute9 is not null then
2049: END IF;
2050:
2051: -- attribute9 filter
2052: IF p_attribute9 is not null then
2053: FND_DSQL.add_text(' AND FU.ATTRIBUTE9 =' );
2054: FND_DSQL.add_bind('' || p_attribute9 || '');
2055: END IF;
2056:
2057: -- attribute10 filter
2050:
2051: -- attribute9 filter
2052: IF p_attribute9 is not null then
2053: FND_DSQL.add_text(' AND FU.ATTRIBUTE9 =' );
2054: FND_DSQL.add_bind('' || p_attribute9 || '');
2055: END IF;
2056:
2057: -- attribute10 filter
2058: IF p_attribute10 is not null then
2055: END IF;
2056:
2057: -- attribute10 filter
2058: IF p_attribute10 is not null then
2059: FND_DSQL.add_text(' AND FU.ATTRIBUTE10 =' );
2060: FND_DSQL.add_bind('' || p_attribute10 || '');
2061: END IF;
2062:
2063: -- attribute11 filter
2056:
2057: -- attribute10 filter
2058: IF p_attribute10 is not null then
2059: FND_DSQL.add_text(' AND FU.ATTRIBUTE10 =' );
2060: FND_DSQL.add_bind('' || p_attribute10 || '');
2061: END IF;
2062:
2063: -- attribute11 filter
2064: IF p_attribute11 is not null then
2061: END IF;
2062:
2063: -- attribute11 filter
2064: IF p_attribute11 is not null then
2065: FND_DSQL.add_text(' AND FU.ATTRIBUTE11 =' );
2066: FND_DSQL.add_bind('' || p_attribute11 || '');
2067: END IF;
2068:
2069: -- attribute12 filter
2062:
2063: -- attribute11 filter
2064: IF p_attribute11 is not null then
2065: FND_DSQL.add_text(' AND FU.ATTRIBUTE11 =' );
2066: FND_DSQL.add_bind('' || p_attribute11 || '');
2067: END IF;
2068:
2069: -- attribute12 filter
2070: IF p_attribute12 is not null then
2067: END IF;
2068:
2069: -- attribute12 filter
2070: IF p_attribute12 is not null then
2071: FND_DSQL.add_text(' AND FU.ATTRIBUTE12 =' );
2072: FND_DSQL.add_bind('' || p_attribute12 || '');
2073: END IF;
2074:
2075: -- attribute13 filter
2068:
2069: -- attribute12 filter
2070: IF p_attribute12 is not null then
2071: FND_DSQL.add_text(' AND FU.ATTRIBUTE12 =' );
2072: FND_DSQL.add_bind('' || p_attribute12 || '');
2073: END IF;
2074:
2075: -- attribute13 filter
2076: IF p_attribute13 is not null then
2073: END IF;
2074:
2075: -- attribute13 filter
2076: IF p_attribute13 is not null then
2077: FND_DSQL.add_text(' AND FU.ATTRIBUTE13 =' );
2078: FND_DSQL.add_bind('' || p_attribute13 || '');
2079: END IF;
2080:
2081: -- attribute14 filter
2074:
2075: -- attribute13 filter
2076: IF p_attribute13 is not null then
2077: FND_DSQL.add_text(' AND FU.ATTRIBUTE13 =' );
2078: FND_DSQL.add_bind('' || p_attribute13 || '');
2079: END IF;
2080:
2081: -- attribute14 filter
2082: IF p_attribute14 is not null then
2079: END IF;
2080:
2081: -- attribute14 filter
2082: IF p_attribute14 is not null then
2083: FND_DSQL.add_text(' AND FU.ATTRIBUTE14 =' );
2084: FND_DSQL.add_bind('' || p_attribute14 || '');
2085: END IF;
2086:
2087: -- attribute15 filter
2080:
2081: -- attribute14 filter
2082: IF p_attribute14 is not null then
2083: FND_DSQL.add_text(' AND FU.ATTRIBUTE14 =' );
2084: FND_DSQL.add_bind('' || p_attribute14 || '');
2085: END IF;
2086:
2087: -- attribute15 filter
2088: IF p_attribute15 is not null then
2085: END IF;
2086:
2087: -- attribute15 filter
2088: IF p_attribute15 is not null then
2089: FND_DSQL.add_text(' AND FU.ATTRIBUTE15 =' );
2090: FND_DSQL.add_bind('' || p_attribute15 || '');
2091: END IF;
2092:
2093:
2086:
2087: -- attribute15 filter
2088: IF p_attribute15 is not null then
2089: FND_DSQL.add_text(' AND FU.ATTRIBUTE15 =' );
2090: FND_DSQL.add_bind('' || p_attribute15 || '');
2091: END IF;
2092:
2093:
2094: FND_DSQL.add_text(' FOR UPDATE OF FU.PLAN_CURR_AMOUNT_REMAINING NOWAIT' );
2090: FND_DSQL.add_bind('' || p_attribute15 || '');
2091: END IF;
2092:
2093:
2094: FND_DSQL.add_text(' FOR UPDATE OF FU.PLAN_CURR_AMOUNT_REMAINING NOWAIT' );
2095:
2096:
2097: --creating cursor
2098: l_lines_csr := DBMS_SQL.open_cursor;
2095:
2096:
2097: --creating cursor
2098: l_lines_csr := DBMS_SQL.open_cursor;
2099: l_lines_sql := FND_DSQL.get_text(FALSE); -- Get SQL query built above
2100:
2101: IF OZF_DEBUG_LOW_ON THEN
2102: FND_FILE.PUT_LINE(FND_FILE.LOG, '---Start Query Text ---');
2103: FND_FILE.PUT_LINE(FND_FILE.LOG,' l_lines_sql = ' || l_lines_sql);
2119: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_lines_sql = ' || l_lines_sql);
2120: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --End Query Text --');
2121: END IF;
2122:
2123: FND_DSQL.set_cursor(l_lines_csr);
2124: DBMS_SQL.parse(l_lines_csr, l_lines_sql, DBMS_SQL.native);
2125: FND_DSQL.do_binds;
2126:
2127: --define columns
2121: END IF;
2122:
2123: FND_DSQL.set_cursor(l_lines_csr);
2124: DBMS_SQL.parse(l_lines_csr, l_lines_sql, DBMS_SQL.native);
2125: FND_DSQL.do_binds;
2126:
2127: --define columns
2128:
2129: DBMS_SQL.define_column(l_lines_csr, 1, l_batch_line_id);
2644: WHERE org.set_of_books_id = gs.set_of_books_id
2645: AND org.org_id = bh.org_id
2646: AND bh.batch_id = p_batch_id;
2647:
2648: FND_DSQL.init;
2649:
2650: FND_DSQL.add_text('SELECT ');
2651: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
2652: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2646: AND bh.batch_id = p_batch_id;
2647:
2648: FND_DSQL.init;
2649:
2650: FND_DSQL.add_text('SELECT ');
2651: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
2652: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2653: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2647:
2648: FND_DSQL.init;
2649:
2650: FND_DSQL.add_text('SELECT ');
2651: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
2652: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2653: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2648: FND_DSQL.init;
2649:
2650: FND_DSQL.add_text('SELECT ');
2651: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
2652: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2653: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2649:
2650: FND_DSQL.add_text('SELECT ');
2651: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
2652: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2653: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2650: FND_DSQL.add_text('SELECT ');
2651: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
2652: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2653: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2651: FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
2652: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2653: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2652: FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2653: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2653: FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661: FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2654: FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661: FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2662: FND_DSQL.add_text('ORL.resale_line_id, '); -- ORDER_LINE_ID
2655: FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661: FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2662: FND_DSQL.add_text('ORL.resale_line_id, '); -- ORDER_LINE_ID
2663: FND_DSQL.add_text('null TRX_NUMBER,'); --INVOICE_NUMBER
2656: FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661: FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2662: FND_DSQL.add_text('ORL.resale_line_id, '); -- ORDER_LINE_ID
2663: FND_DSQL.add_text('null TRX_NUMBER,'); --INVOICE_NUMBER
2664: FND_DSQL.add_text('NULL LINE_NUMBER, '); --INVOICE_LINE_NUMBER
2657: FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661: FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2662: FND_DSQL.add_text('ORL.resale_line_id, '); -- ORDER_LINE_ID
2663: FND_DSQL.add_text('null TRX_NUMBER,'); --INVOICE_NUMBER
2664: FND_DSQL.add_text('NULL LINE_NUMBER, '); --INVOICE_LINE_NUMBER
2665:
2658: FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661: FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2662: FND_DSQL.add_text('ORL.resale_line_id, '); -- ORDER_LINE_ID
2663: FND_DSQL.add_text('null TRX_NUMBER,'); --INVOICE_NUMBER
2664: FND_DSQL.add_text('NULL LINE_NUMBER, '); --INVOICE_LINE_NUMBER
2665:
2666: --Compute Resale Price
2659: FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661: FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2662: FND_DSQL.add_text('ORL.resale_line_id, '); -- ORDER_LINE_ID
2663: FND_DSQL.add_text('null TRX_NUMBER,'); --INVOICE_NUMBER
2664: FND_DSQL.add_text('NULL LINE_NUMBER, '); --INVOICE_LINE_NUMBER
2665:
2666: --Compute Resale Price
2667: FND_DSQL.add_text('ORL.currency_code, '); --TRANSACTIONAL_CURR_CODE
2660: FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661: FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2662: FND_DSQL.add_text('ORL.resale_line_id, '); -- ORDER_LINE_ID
2663: FND_DSQL.add_text('null TRX_NUMBER,'); --INVOICE_NUMBER
2664: FND_DSQL.add_text('NULL LINE_NUMBER, '); --INVOICE_LINE_NUMBER
2665:
2666: --Compute Resale Price
2667: FND_DSQL.add_text('ORL.currency_code, '); --TRANSACTIONAL_CURR_CODE
2668: /* Resale Price Computation logic
2663: FND_DSQL.add_text('null TRX_NUMBER,'); --INVOICE_NUMBER
2664: FND_DSQL.add_text('NULL LINE_NUMBER, '); --INVOICE_LINE_NUMBER
2665:
2666: --Compute Resale Price
2667: FND_DSQL.add_text('ORL.currency_code, '); --TRANSACTIONAL_CURR_CODE
2668: /* Resale Price Computation logic
2669: For direct resale order, OZF_RESALE_LINES_ALL.SELLING_PRICE
2670: For indirect resale order,if OZF_RESALE_LINES_ALL.purchase_uom_code and OZF_RESALE_LINES_ALL .uom_code are same then ORL.purchase_price
2671: For indirect resale order,if OZF_RESALE_LINES_ALL.purchase_uom_code and OZF_RESALE_LINES_ALL .uom_code are different
2672: then ORL.purchase_price should be converted
2673: Inventory Utility function for UOM conversion:
2674: number inv_um_convert(item_id, precision, from_quantity,from_unit, to_unit,from_name, to_name)
2675: */
2676: FND_DSQL.add_text('CASE WHEN (ORB.DIRECT_ORDER_FLAG = ''Y'') THEN ORL.selling_price
2677: WHEN ORB.DIRECT_ORDER_FLAG <> ''Y'' AND ORL.UOM_CODE = ORL.PURCHASE_UOM_CODE THEN ORL.purchase_price
2678: WHEN (ORB.DIRECT_ORDER_FLAG <> ''Y'' AND ORL.UOM_CODE <> ORL.PURCHASE_UOM_CODE) THEN
2679: (NVL(ORL.purchase_price,0))/NVL(inv_convert.inv_um_convert(ORL.inventory_item_id, null,
2680: orl.quantity,ORL.PURCHASE_UOM_CODE, ORL.UOM_CODE,null,null),1)
2680: orl.quantity,ORL.PURCHASE_UOM_CODE, ORL.UOM_CODE,null,null),1)
2681: END unit_selling_price, '); --UNIT_SELLING_PRICE
2682:
2683: --Compute cost price
2684: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), '); --LIST_PRICE_CURRENCY_CODE
2685:
2686: -- Call CONV_AMT_TO_OFFER_CURR_AMOUNT where
2687: -- To Currency Offer CURRENCY_CODE
2688: -- From Currency FU.DISCOUNT_AMOUNT_CURRENCY_CODE
2695: FU.COST_PRICE_CURRENCY_CODE,
2696: FU.COST_PRICE,
2697: FU.EXCHANGE_RATE_DATE,
2698: FU.EXCHANGE_RATE_TYPE ))';
2699: FND_DSQL.add_text(l_cost_price_offer_string || 'COST_PRICE' );
2700:
2701: l_discount_amount_string := 'OZF_SD_BATCH_PVT.CONV_AMT_TO_OFFER_CURR_AMOUNT(FU.PLAN_CURRENCY_CODE ,
2702: FU.DISCOUNT_AMOUNT_CURRENCY_CODE,
2703: FU.DISCOUNT_AMOUNT,
2704: FU.EXCHANGE_RATE_DATE,
2705: FU.EXCHANGE_RATE_TYPE )' ;
2706: l_qty_string := 'decode(ORL. resale_transfer_type, ''BN'', -1 * ABS(nvl(orl. Quantity,1) ),nvl(orl. Quantity,1))' ;
2707:
2708: FND_DSQL.add_text(', DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), '); --agreement_price_currency_code
2709:
2710: l_agmt_price_string:= ('decode (FU.DISCOUNT_TYPE,
2711: ''%'', ('|| l_cost_price_string ||' * (1 - FU.DISCOUNT_AMOUNT/100)),
2712: ''AMT'', null,
2719: l_discount_amount_string || ') ');
2720:
2721: l_agmt_price_string:= 'OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC('||l_agmt_price_string||', FU.PLAN_CURRENCY_CODE)';
2722:
2723: FND_DSQL.add_text(l_agmt_price_string || 'AGREEMENT_PRICE' );
2724:
2725: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
2726:
2727: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
2721: l_agmt_price_string:= 'OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC('||l_agmt_price_string||', FU.PLAN_CURRENCY_CODE)';
2722:
2723: FND_DSQL.add_text(l_agmt_price_string || 'AGREEMENT_PRICE' );
2724:
2725: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
2726:
2727: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
2728: FND_DSQL.add_bind(''||p_batch_currency||'' );
2729: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2723: FND_DSQL.add_text(l_agmt_price_string || 'AGREEMENT_PRICE' );
2724:
2725: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
2726:
2727: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
2728: FND_DSQL.add_bind(''||p_batch_currency||'' );
2729: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2730: FND_DSQL.add_bind(''||l_func_currency||'' );
2731: FND_DSQL.add_text('=');
2724:
2725: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
2726:
2727: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
2728: FND_DSQL.add_bind(''||p_batch_currency||'' );
2729: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2730: FND_DSQL.add_bind(''||l_func_currency||'' );
2731: FND_DSQL.add_text('=');
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2725: FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
2726:
2727: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
2728: FND_DSQL.add_bind(''||p_batch_currency||'' );
2729: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2730: FND_DSQL.add_bind(''||l_func_currency||'' );
2731: FND_DSQL.add_text('=');
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2726:
2727: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
2728: FND_DSQL.add_bind(''||p_batch_currency||'' );
2729: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2730: FND_DSQL.add_bind(''||l_func_currency||'' );
2731: FND_DSQL.add_text('=');
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2727: FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
2728: FND_DSQL.add_bind(''||p_batch_currency||'' );
2729: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2730: FND_DSQL.add_bind(''||l_func_currency||'' );
2731: FND_DSQL.add_text('=');
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2735: FND_DSQL.add_text(',');
2728: FND_DSQL.add_bind(''||p_batch_currency||'' );
2729: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2730: FND_DSQL.add_bind(''||l_func_currency||'' );
2731: FND_DSQL.add_text('=');
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2735: FND_DSQL.add_text(',');
2736: FND_DSQL.add_bind(''||p_batch_currency||'' );
2729: FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2730: FND_DSQL.add_bind(''||l_func_currency||'' );
2731: FND_DSQL.add_text('=');
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2735: FND_DSQL.add_text(',');
2736: FND_DSQL.add_bind(''||p_batch_currency||'' );
2737: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2730: FND_DSQL.add_bind(''||l_func_currency||'' );
2731: FND_DSQL.add_text('=');
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2735: FND_DSQL.add_text(',');
2736: FND_DSQL.add_bind(''||p_batch_currency||'' );
2737: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2738: FND_DSQL.add_bind(''||l_date||'' );
2731: FND_DSQL.add_text('=');
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2735: FND_DSQL.add_text(',');
2736: FND_DSQL.add_bind(''||p_batch_currency||'' );
2737: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2738: FND_DSQL.add_bind(''||l_date||'' );
2739: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
2732: FND_DSQL.add_bind(''||p_batch_currency||'' );
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2735: FND_DSQL.add_text(',');
2736: FND_DSQL.add_bind(''||p_batch_currency||'' );
2737: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2738: FND_DSQL.add_bind(''||l_date||'' );
2739: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
2740:
2733: FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2735: FND_DSQL.add_text(',');
2736: FND_DSQL.add_bind(''||p_batch_currency||'' );
2737: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2738: FND_DSQL.add_bind(''||l_date||'' );
2739: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
2740:
2741: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
2734: FND_DSQL.add_bind(''||l_func_currency||'' );
2735: FND_DSQL.add_text(',');
2736: FND_DSQL.add_bind(''||p_batch_currency||'' );
2737: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2738: FND_DSQL.add_bind(''||l_date||'' );
2739: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
2740:
2741: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
2742:
2735: FND_DSQL.add_text(',');
2736: FND_DSQL.add_bind(''||p_batch_currency||'' );
2737: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2738: FND_DSQL.add_bind(''||l_date||'' );
2739: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
2740:
2741: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
2742:
2743: FND_DSQL.add_text('ORL.UOM_CODE, ');
2737: FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2738: FND_DSQL.add_bind(''||l_date||'' );
2739: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
2740:
2741: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
2742:
2743: FND_DSQL.add_text('ORL.UOM_CODE, ');
2744:
2745: FND_DSQL.add_text(l_qty_string || ' , ');
2739: FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
2740:
2741: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
2742:
2743: FND_DSQL.add_text('ORL.UOM_CODE, ');
2744:
2745: FND_DSQL.add_text(l_qty_string || ' , ');
2746:
2747: FND_DSQL.add_text(' ORL.DATE_ORDERED, '); --ORL.DATE_ORDERED will pick from resale table on the fly
2741: FND_DSQL.add_text(', FU.PRODUCT_ID, ');
2742:
2743: FND_DSQL.add_text('ORL.UOM_CODE, ');
2744:
2745: FND_DSQL.add_text(l_qty_string || ' , ');
2746:
2747: FND_DSQL.add_text(' ORL.DATE_ORDERED, '); --ORL.DATE_ORDERED will pick from resale table on the fly
2748:
2749:
2743: FND_DSQL.add_text('ORL.UOM_CODE, ');
2744:
2745: FND_DSQL.add_text(l_qty_string || ' , ');
2746:
2747: FND_DSQL.add_text(' ORL.DATE_ORDERED, '); --ORL.DATE_ORDERED will pick from resale table on the fly
2748:
2749:
2750: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
2751: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
2746:
2747: FND_DSQL.add_text(' ORL.DATE_ORDERED, '); --ORL.DATE_ORDERED will pick from resale table on the fly
2748:
2749:
2750: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
2751: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
2752: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
2753: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
2754: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
2747: FND_DSQL.add_text(' ORL.DATE_ORDERED, '); --ORL.DATE_ORDERED will pick from resale table on the fly
2748:
2749:
2750: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
2751: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
2752: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
2753: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
2754: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
2755: FND_DSQL.add_text(l_idsm_inv_org ||' , '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.The profile AMS_ITEM_ORGANIZATION_ID is used for inventory org in IDSM
2748:
2749:
2750: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
2751: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
2752: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
2753: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
2754: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
2755: FND_DSQL.add_text(l_idsm_inv_org ||' , '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.The profile AMS_ITEM_ORGANIZATION_ID is used for inventory org in IDSM
2756: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
2749:
2750: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
2751: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
2752: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
2753: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
2754: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
2755: FND_DSQL.add_text(l_idsm_inv_org ||' , '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.The profile AMS_ITEM_ORGANIZATION_ID is used for inventory org in IDSM
2756: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
2757:
2750: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
2751: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
2752: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
2753: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
2754: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
2755: FND_DSQL.add_text(l_idsm_inv_org ||' , '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.The profile AMS_ITEM_ORGANIZATION_ID is used for inventory org in IDSM
2756: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
2757:
2758: --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
2751: FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
2752: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
2753: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
2754: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
2755: FND_DSQL.add_text(l_idsm_inv_org ||' , '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.The profile AMS_ITEM_ORGANIZATION_ID is used for inventory org in IDSM
2756: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
2757:
2758: --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
2759: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,''NEWPRICE'', ' || l_agmt_price_string || ',
2752: FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
2753: FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
2754: FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
2755: FND_DSQL.add_text(l_idsm_inv_org ||' , '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.The profile AMS_ITEM_ORGANIZATION_ID is used for inventory org in IDSM
2756: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
2757:
2758: --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
2759: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,''NEWPRICE'', ' || l_agmt_price_string || ',
2760: ''AMT'', '|| l_discount_amount_string ||',
2755: FND_DSQL.add_text(l_idsm_inv_org ||' , '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.The profile AMS_ITEM_ORGANIZATION_ID is used for inventory org in IDSM
2756: FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
2757:
2758: --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
2759: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,''NEWPRICE'', ' || l_agmt_price_string || ',
2760: ''AMT'', '|| l_discount_amount_string ||',
2761: ''%'', FU.DISCOUNT_AMOUNT ) APPROVED_DISCOUNT_VALUE, ');
2762:
2763: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, '); --approved discount currency
2759: FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,''NEWPRICE'', ' || l_agmt_price_string || ',
2760: ''AMT'', '|| l_discount_amount_string ||',
2761: ''%'', FU.DISCOUNT_AMOUNT ) APPROVED_DISCOUNT_VALUE, ');
2762:
2763: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, '); --approved discount currency
2764: FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
2765:
2766: --BEGIN FROM CLAUSE
2767: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
2760: ''AMT'', '|| l_discount_amount_string ||',
2761: ''%'', FU.DISCOUNT_AMOUNT ) APPROVED_DISCOUNT_VALUE, ');
2762:
2763: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, '); --approved discount currency
2764: FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
2765:
2766: --BEGIN FROM CLAUSE
2767: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
2768: FND_DSQL.add_text('OZF_RESALE_LINES_ALL ORL, ');
2763: FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, '); --approved discount currency
2764: FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
2765:
2766: --BEGIN FROM CLAUSE
2767: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
2768: FND_DSQL.add_text('OZF_RESALE_LINES_ALL ORL, ');
2769: FND_DSQL.add_text('OZF_RESALE_BATCH_LINE_MAPS_ALL ORBMP, ');
2770: FND_DSQL.add_text('OZF_RESALE_BATCHES_ALL ORB, ');
2771: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
2764: FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
2765:
2766: --BEGIN FROM CLAUSE
2767: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
2768: FND_DSQL.add_text('OZF_RESALE_LINES_ALL ORL, ');
2769: FND_DSQL.add_text('OZF_RESALE_BATCH_LINE_MAPS_ALL ORBMP, ');
2770: FND_DSQL.add_text('OZF_RESALE_BATCHES_ALL ORB, ');
2771: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
2772: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
2765:
2766: --BEGIN FROM CLAUSE
2767: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
2768: FND_DSQL.add_text('OZF_RESALE_LINES_ALL ORL, ');
2769: FND_DSQL.add_text('OZF_RESALE_BATCH_LINE_MAPS_ALL ORBMP, ');
2770: FND_DSQL.add_text('OZF_RESALE_BATCHES_ALL ORB, ');
2771: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
2772: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
2773:
2766: --BEGIN FROM CLAUSE
2767: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
2768: FND_DSQL.add_text('OZF_RESALE_LINES_ALL ORL, ');
2769: FND_DSQL.add_text('OZF_RESALE_BATCH_LINE_MAPS_ALL ORBMP, ');
2770: FND_DSQL.add_text('OZF_RESALE_BATCHES_ALL ORB, ');
2771: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
2772: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
2773:
2774: --BEGIN WHERE CLAUSE
2767: FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
2768: FND_DSQL.add_text('OZF_RESALE_LINES_ALL ORL, ');
2769: FND_DSQL.add_text('OZF_RESALE_BATCH_LINE_MAPS_ALL ORBMP, ');
2770: FND_DSQL.add_text('OZF_RESALE_BATCHES_ALL ORB, ');
2771: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
2772: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
2773:
2774: --BEGIN WHERE CLAUSE
2775: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
2768: FND_DSQL.add_text('OZF_RESALE_LINES_ALL ORL, ');
2769: FND_DSQL.add_text('OZF_RESALE_BATCH_LINE_MAPS_ALL ORBMP, ');
2770: FND_DSQL.add_text('OZF_RESALE_BATCHES_ALL ORB, ');
2771: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
2772: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
2773:
2774: --BEGIN WHERE CLAUSE
2775: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
2776: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
2771: FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
2772: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
2773:
2774: --BEGIN WHERE CLAUSE
2775: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
2776: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
2777: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
2778: FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2772: FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
2773:
2774: --BEGIN WHERE CLAUSE
2775: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
2776: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
2777: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
2778: FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2773:
2774: --BEGIN WHERE CLAUSE
2775: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
2776: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
2777: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
2778: FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2774: --BEGIN WHERE CLAUSE
2775: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
2776: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
2777: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
2778: FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2775: FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
2776: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
2777: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
2778: FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2783: FND_DSQL.add_bind(p_supplier_site_id);
2776: FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
2777: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
2778: FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2783: FND_DSQL.add_bind(p_supplier_site_id);
2784: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2777: FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
2778: FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2783: FND_DSQL.add_bind(p_supplier_site_id);
2784: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2785: FND_DSQL.add_bind(l_org_id);
2778: FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2783: FND_DSQL.add_bind(p_supplier_site_id);
2784: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2785: FND_DSQL.add_bind(l_org_id);
2786:
2779: FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2783: FND_DSQL.add_bind(p_supplier_site_id);
2784: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2785: FND_DSQL.add_bind(l_org_id);
2786:
2787: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
2780: FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2783: FND_DSQL.add_bind(p_supplier_site_id);
2784: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2785: FND_DSQL.add_bind(l_org_id);
2786:
2787: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
2788: FND_DSQL.add_bind(l_org_id);
2781: FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782: FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2783: FND_DSQL.add_bind(p_supplier_site_id);
2784: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2785: FND_DSQL.add_bind(l_org_id);
2786:
2787: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
2788: FND_DSQL.add_bind(l_org_id);
2789: FND_DSQL.add_text(' AND FU.GL_POSTED_FLAG = ''Y'' ');
2783: FND_DSQL.add_bind(p_supplier_site_id);
2784: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2785: FND_DSQL.add_bind(l_org_id);
2786:
2787: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
2788: FND_DSQL.add_bind(l_org_id);
2789: FND_DSQL.add_text(' AND FU.GL_POSTED_FLAG = ''Y'' ');
2790:
2791: IF g_currency IS NULL THEN
2784: FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2785: FND_DSQL.add_bind(l_org_id);
2786:
2787: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
2788: FND_DSQL.add_bind(l_org_id);
2789: FND_DSQL.add_text(' AND FU.GL_POSTED_FLAG = ''Y'' ');
2790:
2791: IF g_currency IS NULL THEN
2792: -- currency not set on trade profile
2785: FND_DSQL.add_bind(l_org_id);
2786:
2787: FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
2788: FND_DSQL.add_bind(l_org_id);
2789: FND_DSQL.add_text(' AND FU.GL_POSTED_FLAG = ''Y'' ');
2790:
2791: IF g_currency IS NULL THEN
2792: -- currency not set on trade profile
2793: FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
2789: FND_DSQL.add_text(' AND FU.GL_POSTED_FLAG = ''Y'' ');
2790:
2791: IF g_currency IS NULL THEN
2792: -- currency not set on trade profile
2793: FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
2794: FND_DSQL.add_bind('' || l_batch_currency || '');
2795: END IF;
2796:
2797: --request ID filter
2790:
2791: IF g_currency IS NULL THEN
2792: -- currency not set on trade profile
2793: FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
2794: FND_DSQL.add_bind('' || l_batch_currency || '');
2795: END IF;
2796:
2797: --request ID filter
2798: IF p_request_id is not null then
2795: END IF;
2796:
2797: --request ID filter
2798: IF p_request_id is not null then
2799: FND_DSQL.add_text(' AND RH.REQUEST_HEADER_ID = ' );
2800: FND_DSQL.add_bind(p_request_id);
2801: END IF;
2802:
2803: --product filter
2796:
2797: --request ID filter
2798: IF p_request_id is not null then
2799: FND_DSQL.add_text(' AND RH.REQUEST_HEADER_ID = ' );
2800: FND_DSQL.add_bind(p_request_id);
2801: END IF;
2802:
2803: --product filter
2804: IF p_product_id is not null then
2801: END IF;
2802:
2803: --product filter
2804: IF p_product_id is not null then
2805: FND_DSQL.add_text(' AND FU.PRODUCT_ID = ' );
2806: FND_DSQL.add_bind(p_product_id);
2807: END IF;
2808:
2809: -- offer filter
2802:
2803: --product filter
2804: IF p_product_id is not null then
2805: FND_DSQL.add_text(' AND FU.PRODUCT_ID = ' );
2806: FND_DSQL.add_bind(p_product_id);
2807: END IF;
2808:
2809: -- offer filter
2810: IF p_fund_id is not null then
2807: END IF;
2808:
2809: -- offer filter
2810: IF p_fund_id is not null then
2811: FND_DSQL.add_text(' AND FU.fund_id = ' );
2812: FND_DSQL.add_bind(p_fund_id);
2813: END IF;
2814:
2815: --start date filter
2808:
2809: -- offer filter
2810: IF p_fund_id is not null then
2811: FND_DSQL.add_text(' AND FU.fund_id = ' );
2812: FND_DSQL.add_bind(p_fund_id);
2813: END IF;
2814:
2815: --start date filter
2816: IF p_start_date is not null then
2813: END IF;
2814:
2815: --start date filter
2816: IF p_start_date is not null then
2817: FND_DSQL.add_text(' AND FU.creation_date >=' );
2818: FND_DSQL.add_bind('' || p_start_date || '');
2819: END IF;
2820:
2821: --end date filter
2814:
2815: --start date filter
2816: IF p_start_date is not null then
2817: FND_DSQL.add_text(' AND FU.creation_date >=' );
2818: FND_DSQL.add_bind('' || p_start_date || '');
2819: END IF;
2820:
2821: --end date filter
2822: IF p_end_date is not null then
2819: END IF;
2820:
2821: --end date filter
2822: IF p_end_date is not null then
2823: FND_DSQL.add_text(' AND TRUNC(FU.creation_date) <=' );
2824: FND_DSQL.add_bind('' || p_end_date || '');
2825: END IF;
2826:
2827: --period filter
2820:
2821: --end date filter
2822: IF p_end_date is not null then
2823: FND_DSQL.add_text(' AND TRUNC(FU.creation_date) <=' );
2824: FND_DSQL.add_bind('' || p_end_date || '');
2825: END IF;
2826:
2827: --period filter
2828: IF p_period is not null then
2831: INTO l_period_start, l_period_end;
2832: CLOSE get_period_limits;
2833:
2834: IF l_period_start is not null then
2835: FND_DSQL.add_text(' AND FU.creation_date >= ');
2836: FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
2837: END IF;
2838:
2839: IF l_period_end is not null then
2832: CLOSE get_period_limits;
2833:
2834: IF l_period_start is not null then
2835: FND_DSQL.add_text(' AND FU.creation_date >= ');
2836: FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
2837: END IF;
2838:
2839: IF l_period_end is not null then
2840: FND_DSQL.add_text(' AND FU.creation_date <= ');
2836: FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
2837: END IF;
2838:
2839: IF l_period_end is not null then
2840: FND_DSQL.add_text(' AND FU.creation_date <= ');
2841: FND_DSQL.add_bind(''||to_date(to_char(l_period_end,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
2842: END IF;
2843:
2844: END IF;
2837: END IF;
2838:
2839: IF l_period_end is not null then
2840: FND_DSQL.add_text(' AND FU.creation_date <= ');
2841: FND_DSQL.add_bind(''||to_date(to_char(l_period_end,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
2842: END IF;
2843:
2844: END IF;
2845:
2844: END IF;
2845:
2846: -- attribute1 filter
2847: IF p_attribute1 is not null then
2848: FND_DSQL.add_text(' AND FU.ATTRIBUTE1 =' );
2849: FND_DSQL.add_bind('' || p_attribute1 || '');
2850: END IF;
2851:
2852: -- attribute2 filter
2845:
2846: -- attribute1 filter
2847: IF p_attribute1 is not null then
2848: FND_DSQL.add_text(' AND FU.ATTRIBUTE1 =' );
2849: FND_DSQL.add_bind('' || p_attribute1 || '');
2850: END IF;
2851:
2852: -- attribute2 filter
2853: IF p_attribute2 is not null then
2850: END IF;
2851:
2852: -- attribute2 filter
2853: IF p_attribute2 is not null then
2854: FND_DSQL.add_text(' AND FU.ATTRIBUTE2 =' );
2855: FND_DSQL.add_bind('' || p_attribute2 || '');
2856: END IF;
2857:
2858: -- attribute3 filter
2851:
2852: -- attribute2 filter
2853: IF p_attribute2 is not null then
2854: FND_DSQL.add_text(' AND FU.ATTRIBUTE2 =' );
2855: FND_DSQL.add_bind('' || p_attribute2 || '');
2856: END IF;
2857:
2858: -- attribute3 filter
2859: IF p_attribute3 is not null then
2856: END IF;
2857:
2858: -- attribute3 filter
2859: IF p_attribute3 is not null then
2860: FND_DSQL.add_text(' AND FU.ATTRIBUTE3 =' );
2861: FND_DSQL.add_bind('' || p_attribute3 || '');
2862: END IF;
2863:
2864: -- attribute4 filter
2857:
2858: -- attribute3 filter
2859: IF p_attribute3 is not null then
2860: FND_DSQL.add_text(' AND FU.ATTRIBUTE3 =' );
2861: FND_DSQL.add_bind('' || p_attribute3 || '');
2862: END IF;
2863:
2864: -- attribute4 filter
2865: IF p_attribute4 is not null then
2862: END IF;
2863:
2864: -- attribute4 filter
2865: IF p_attribute4 is not null then
2866: FND_DSQL.add_text(' AND FU.ATTRIBUTE4 =' );
2867: FND_DSQL.add_bind('' || p_attribute4 || '');
2868: END IF;
2869:
2870: -- attribute5 filter
2863:
2864: -- attribute4 filter
2865: IF p_attribute4 is not null then
2866: FND_DSQL.add_text(' AND FU.ATTRIBUTE4 =' );
2867: FND_DSQL.add_bind('' || p_attribute4 || '');
2868: END IF;
2869:
2870: -- attribute5 filter
2871: IF p_attribute5 is not null then
2868: END IF;
2869:
2870: -- attribute5 filter
2871: IF p_attribute5 is not null then
2872: FND_DSQL.add_text(' AND FU.ATTRIBUTE5 =' );
2873: FND_DSQL.add_bind('' || p_attribute5 || '');
2874: END IF;
2875:
2876: -- attribute6 filter
2869:
2870: -- attribute5 filter
2871: IF p_attribute5 is not null then
2872: FND_DSQL.add_text(' AND FU.ATTRIBUTE5 =' );
2873: FND_DSQL.add_bind('' || p_attribute5 || '');
2874: END IF;
2875:
2876: -- attribute6 filter
2877: IF p_attribute6 is not null then
2874: END IF;
2875:
2876: -- attribute6 filter
2877: IF p_attribute6 is not null then
2878: FND_DSQL.add_text(' AND FU.ATTRIBUTE6 =' );
2879: FND_DSQL.add_bind('' || p_attribute6 || '');
2880: END IF;
2881:
2882: -- attribute7 filter
2875:
2876: -- attribute6 filter
2877: IF p_attribute6 is not null then
2878: FND_DSQL.add_text(' AND FU.ATTRIBUTE6 =' );
2879: FND_DSQL.add_bind('' || p_attribute6 || '');
2880: END IF;
2881:
2882: -- attribute7 filter
2883: IF p_attribute7 is not null then
2880: END IF;
2881:
2882: -- attribute7 filter
2883: IF p_attribute7 is not null then
2884: FND_DSQL.add_text(' AND FU.ATTRIBUTE7 =' );
2885: FND_DSQL.add_bind('' || p_attribute7 || '');
2886: END IF;
2887:
2888: -- attribute8 filter
2881:
2882: -- attribute7 filter
2883: IF p_attribute7 is not null then
2884: FND_DSQL.add_text(' AND FU.ATTRIBUTE7 =' );
2885: FND_DSQL.add_bind('' || p_attribute7 || '');
2886: END IF;
2887:
2888: -- attribute8 filter
2889: IF p_attribute8 is not null then
2886: END IF;
2887:
2888: -- attribute8 filter
2889: IF p_attribute8 is not null then
2890: FND_DSQL.add_text(' AND FU.ATTRIBUTE8 =' );
2891: FND_DSQL.add_bind('' || p_attribute8 || '');
2892: END IF;
2893:
2894: -- attribute9 filter
2887:
2888: -- attribute8 filter
2889: IF p_attribute8 is not null then
2890: FND_DSQL.add_text(' AND FU.ATTRIBUTE8 =' );
2891: FND_DSQL.add_bind('' || p_attribute8 || '');
2892: END IF;
2893:
2894: -- attribute9 filter
2895: IF p_attribute9 is not null then
2892: END IF;
2893:
2894: -- attribute9 filter
2895: IF p_attribute9 is not null then
2896: FND_DSQL.add_text(' AND FU.ATTRIBUTE9 =' );
2897: FND_DSQL.add_bind('' || p_attribute9 || '');
2898: END IF;
2899:
2900: -- attribute10 filter
2893:
2894: -- attribute9 filter
2895: IF p_attribute9 is not null then
2896: FND_DSQL.add_text(' AND FU.ATTRIBUTE9 =' );
2897: FND_DSQL.add_bind('' || p_attribute9 || '');
2898: END IF;
2899:
2900: -- attribute10 filter
2901: IF p_attribute10 is not null then
2898: END IF;
2899:
2900: -- attribute10 filter
2901: IF p_attribute10 is not null then
2902: FND_DSQL.add_text(' AND FU.ATTRIBUTE10 =' );
2903: FND_DSQL.add_bind('' || p_attribute10 || '');
2904: END IF;
2905:
2906: -- attribute11 filter
2899:
2900: -- attribute10 filter
2901: IF p_attribute10 is not null then
2902: FND_DSQL.add_text(' AND FU.ATTRIBUTE10 =' );
2903: FND_DSQL.add_bind('' || p_attribute10 || '');
2904: END IF;
2905:
2906: -- attribute11 filter
2907: IF p_attribute11 is not null then
2904: END IF;
2905:
2906: -- attribute11 filter
2907: IF p_attribute11 is not null then
2908: FND_DSQL.add_text(' AND FU.ATTRIBUTE11 =' );
2909: FND_DSQL.add_bind('' || p_attribute11 || '');
2910: END IF;
2911:
2912: -- attribute12 filter
2905:
2906: -- attribute11 filter
2907: IF p_attribute11 is not null then
2908: FND_DSQL.add_text(' AND FU.ATTRIBUTE11 =' );
2909: FND_DSQL.add_bind('' || p_attribute11 || '');
2910: END IF;
2911:
2912: -- attribute12 filter
2913: IF p_attribute12 is not null then
2910: END IF;
2911:
2912: -- attribute12 filter
2913: IF p_attribute12 is not null then
2914: FND_DSQL.add_text(' AND FU.ATTRIBUTE12 =' );
2915: FND_DSQL.add_bind('' || p_attribute12 || '');
2916: END IF;
2917:
2918: -- attribute13 filter
2911:
2912: -- attribute12 filter
2913: IF p_attribute12 is not null then
2914: FND_DSQL.add_text(' AND FU.ATTRIBUTE12 =' );
2915: FND_DSQL.add_bind('' || p_attribute12 || '');
2916: END IF;
2917:
2918: -- attribute13 filter
2919: IF p_attribute13 is not null then
2916: END IF;
2917:
2918: -- attribute13 filter
2919: IF p_attribute13 is not null then
2920: FND_DSQL.add_text(' AND FU.ATTRIBUTE13 =' );
2921: FND_DSQL.add_bind('' || p_attribute13 || '');
2922: END IF;
2923:
2924: -- attribute14 filter
2917:
2918: -- attribute13 filter
2919: IF p_attribute13 is not null then
2920: FND_DSQL.add_text(' AND FU.ATTRIBUTE13 =' );
2921: FND_DSQL.add_bind('' || p_attribute13 || '');
2922: END IF;
2923:
2924: -- attribute14 filter
2925: IF p_attribute14 is not null then
2922: END IF;
2923:
2924: -- attribute14 filter
2925: IF p_attribute14 is not null then
2926: FND_DSQL.add_text(' AND FU.ATTRIBUTE14 =' );
2927: FND_DSQL.add_bind('' || p_attribute14 || '');
2928: END IF;
2929:
2930: -- attribute15 filter
2923:
2924: -- attribute14 filter
2925: IF p_attribute14 is not null then
2926: FND_DSQL.add_text(' AND FU.ATTRIBUTE14 =' );
2927: FND_DSQL.add_bind('' || p_attribute14 || '');
2928: END IF;
2929:
2930: -- attribute15 filter
2931: IF p_attribute15 is not null then
2928: END IF;
2929:
2930: -- attribute15 filter
2931: IF p_attribute15 is not null then
2932: FND_DSQL.add_text(' AND FU.ATTRIBUTE15 =' );
2933: FND_DSQL.add_bind('' || p_attribute15 || '');
2934: END IF;
2935:
2936:
2929:
2930: -- attribute15 filter
2931: IF p_attribute15 is not null then
2932: FND_DSQL.add_text(' AND FU.ATTRIBUTE15 =' );
2933: FND_DSQL.add_bind('' || p_attribute15 || '');
2934: END IF;
2935:
2936:
2937: FND_DSQL.add_text(' FOR UPDATE OF FU.PLAN_CURR_AMOUNT_REMAINING NOWAIT' );
2933: FND_DSQL.add_bind('' || p_attribute15 || '');
2934: END IF;
2935:
2936:
2937: FND_DSQL.add_text(' FOR UPDATE OF FU.PLAN_CURR_AMOUNT_REMAINING NOWAIT' );
2938:
2939:
2940: --creating cursor
2941: l_lines_csr := DBMS_SQL.open_cursor;
2938:
2939:
2940: --creating cursor
2941: l_lines_csr := DBMS_SQL.open_cursor;
2942: l_lines_sql := FND_DSQL.get_text(FALSE); -- Get SQL query built above
2943:
2944: IF OZF_DEBUG_LOW_ON THEN
2945: FND_FILE.PUT_LINE(FND_FILE.LOG, '---Start Query Text ---');
2946: FND_FILE.PUT_LINE(FND_FILE.LOG,' l_lines_sql = ' || l_lines_sql);
2962: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_lines_sql = ' || l_lines_sql);
2963: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --End Query Text --');
2964: END IF;
2965:
2966: FND_DSQL.set_cursor(l_lines_csr);
2967: DBMS_SQL.parse(l_lines_csr, l_lines_sql, DBMS_SQL.native);
2968: FND_DSQL.do_binds;
2969:
2970: --define columns
2964: END IF;
2965:
2966: FND_DSQL.set_cursor(l_lines_csr);
2967: DBMS_SQL.parse(l_lines_csr, l_lines_sql, DBMS_SQL.native);
2968: FND_DSQL.do_binds;
2969:
2970: --define columns
2971:
2972: DBMS_SQL.define_column(l_lines_csr, 1, l_batch_line_id);