[Home] [Help]
1: PACKAGE BODY OZF_SALES_TRANSACTIONS_PVT AS
2: /* $Header: ozfvstnb.pls 120.15.12020000.2 2013/02/11 06:29:56 annsrini ship $ */
3:
4: -- Package name : OZF_SALES_TRANSACTIONS_PVT
5: -- Purpose :
1: PACKAGE BODY OZF_SALES_TRANSACTIONS_PVT AS
2: /* $Header: ozfvstnb.pls 120.15.12020000.2 2013/02/11 06:29:56 annsrini ship $ */
3:
4: -- Package name : OZF_SALES_TRANSACTIONS_PVT
5: -- Purpose :
6: -- History :
7: -- 24/NOV/2008 - nirprasa Fixed bug 7030415
8: -- 01/DEC/2008 - nirprasa Fixed bug 6808124
10: -- 13/DEC/2012 - annsrini Fixed bug 14761031
11: -- NOTE :
12: -- End of Comments
13:
14: G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_SALES_TRANSACTIONS_PVT';
15: G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvstnb.pls';
16:
17: OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
18: OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
242: l_common_uom_code VARCHAR2(30);
243: l_common_currency_code VARCHAR2(30);
244:
245: CURSOR transaction_id_csr is
246: select ozf_Sales_Transactions_all_s.nextval
247: from dual;
248: l_sales_transaction_id number;
249:
250: CURSOR sales_transation_csr(p_line_id NUMBER,p_source_code VARCHAR2) IS
249:
250: CURSOR sales_transation_csr(p_line_id NUMBER,p_source_code VARCHAR2) IS
251: SELECT 1 FROM DUAL WHERE EXISTS
252: ( SELECT 1
253: FROM ozf_sales_transactions_all trx
254: WHERE trx.line_id = p_line_id
255: AND source_code = nvl(p_source_code,'OM')); --fix for bug 6808124
256: --Added for bug 7030415
257: CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
455: OPEN transaction_id_csr;
456: FETCH transaction_id_csr INTO l_sales_transaction_id;
457: CLOSE transaction_id_csr;
458:
459: insert into ozf_sales_transactions_all(
460: Sales_Transaction_id,
461: OBJECT_VERSION_NUMBER,
462: LAST_UPDATE_DATE,
463: LAST_UPDATED_BY,
750: ) * NVL(stn.primary_quantity,0)),
751: stn.primary_uom_code,
752: NULL,
753: NULL
754: FROM ozf_sales_transactions_all stn
755: WHERE stn.transaction_date <= p_start_date
756: AND stn.source_code IN ('OM', 'MA')
757: AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
758: FROM ozf_resale_lines_int_all rli
782: ) * NVL(stn.primary_quantity,0)),
783: stn.primary_uom_code,
784: NULL,
785: NULL
786: FROM ozf_sales_transactions_all stn
787: WHERE stn.transaction_date <= p_start_date
788: AND stn.source_code = 'IS'
789: AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
790: FROM ozf_resale_lines_int_all rli
831: ) * NVL(stn.primary_quantity,0),
832: stn.primary_uom_code,
833: NULL,
834: NULL
835: FROM ozf_sales_transactions_all stn
836: WHERE stn.transaction_date > p_start_date
837: AND stn.transaction_date <= p_end_date
838: AND stn.source_code IN ('OM', 'MA')
839: AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
861: ) * NVL(stn.primary_quantity,0),
862: stn.primary_uom_code,
863: NULL,
864: NULL
865: FROM ozf_sales_transactions_all stn
866: WHERE stn.transaction_date > p_start_date
867: AND stn.transaction_date <= p_end_date
868: AND source_code = 'IS'
869: AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
1006: l_msg_count number;
1007:
1008: CURSOR transaction_info_csr (p_id number) is
1009: select primary_quantity, inventory_item_id, sold_from_party_id
1010: from OZF_SALES_TRANSACTIONS_ALL
1011: where Sales_Transaction_id = p_id;
1012: l_primary_quantity number;
1013: l_inventory_item_id number;
1014: l_party_id number;
1451: a.currency_code,
1452: a.transaction_date,
1453: decode(a.transfer_type, 'IN', a.available_primary_quantity, 'OUT', -1 * a.available_primary_quantity),
1454: a.primary_uom_code
1455: FROM ozf_sales_transactions a
1456: WHERE a.available_primary_quantity > 0
1457: AND a.inventory_item_id = p_inventory_item_id
1458: AND a.sold_to_cust_account_id = p_sold_from_cust_account_id
1459: -- AND sold_to_party_site_id = p_sold_from_site_id
1567: END LOOP;
1568: x_purchase_price := l_numerator / l_denominator;
1569:
1570: FORALL i IN 1..l_trans_id_tbl.LAST
1571: UPDATE ozf_sales_transactions_all
1572: SET available_primary_quantity = available_primary_quantity - l_used_quantity_tbl(i)
1573: WHERE sales_transaction_id = l_trans_id_tbl(i);
1574:
1575: IF p_x_purchase_uom_code IS NULL THEN
1641: p_data => x_msg_data
1642: );
1643: END Get_Purchase_Price;
1644:
1645: END OZF_SALES_TRANSACTIONS_PVT;