[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select primary_uom_code
from mtl_system_items
where inventory_item_id = p_id;
select ozf_Sales_Transactions_all_s.nextval
from dual;
SELECT 1 FROM DUAL WHERE EXISTS
( SELECT 1
FROM ozf_sales_transactions_all trx
WHERE trx.line_id = p_line_id
AND source_code = nvl(p_source_code,'OM')); --fix for bug 6808124
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
insert into ozf_sales_transactions_all(
Sales_Transaction_id,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
REQUEST_ID,
CREATED_BY,
CREATED_FROM,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_ID,
SOLD_FROM_CUST_ACCOUNT_ID,
SOLD_FROM_PARTY_ID,
SOLD_FROM_PARTY_SITE_ID,
SOLD_TO_CUST_ACCOUNT_ID,
SOLD_TO_PARTY_ID,
SOLD_TO_PARTY_SITE_ID,
BILL_TO_SITE_USE_ID,
SHIP_TO_SITE_USE_ID,
TRANSACTION_DATE,
TRANSFER_TYPE,
QUANTITY,
uom_code,
AMOUNT,
CURRENCY_CODE,
INVENTORY_ITEM_ID,
PRIMARY_QUANTITY,
PRIMARY_uom_code,
AVAILABLE_PRIMARY_QUANTITY,
COMMON_QUANTITY,
COMMON_uom_code,
COMMON_CURRENCY_CODE,
COMMON_AMOUNT,
HEADER_ID,
LINE_ID,
REASON_CODE,
SOURCE_CODE,
ERROR_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4 ,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14 ,
ATTRIBUTE15,
org_id
) values (
l_sales_transaction_id,
1.0,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
FND_GLOBAL.CONC_REQUEST_ID,
NVL(FND_GLOBAL.user_id,-1),
NULL,
NVL(FND_GLOBAL.conc_login_id,-1),
FND_GLOBAL.PROG_APPL_ID,
sysdate,
FND_GLOBAL.CONC_PROGRAM_ID,
l_transaction_rec.SOLD_FROM_CUST_ACCOUNT_ID,
l_transaction_rec.SOLD_FROM_PARTY_ID,
l_transaction_rec.SOLD_FROM_PARTY_SITE_ID,
l_transaction_rec.SOLD_TO_CUST_ACCOUNT_ID,
l_transaction_rec.SOLD_TO_PARTY_ID,
l_transaction_rec.SOLD_TO_PARTY_SITE_ID,
l_transaction_rec.BILL_TO_SITE_USE_ID,
l_transaction_rec.SHIP_TO_SITE_USE_ID,
TRUNC(l_transaction_rec.TRANSACTION_DATE),
l_transaction_rec.TRANSFER_TYPE,
l_transaction_rec.QUANTITY,
l_transaction_rec.uom_code,
l_transaction_rec.AMOUNT,
l_transaction_rec.CURRENCY_CODE,
l_transaction_rec.INVENTORY_ITEM_ID,
l_transaction_rec.PRIMARY_QUANTITY,
l_transaction_rec.PRIMARY_uom_code,
l_transaction_rec.PRIMARY_QUANTITY,
l_transaction_rec.COMMON_QUANTITY,
l_transaction_rec.COMMON_uom_code,
l_transaction_rec.COMMON_CURRENCY_CODE,
l_transaction_rec.COMMON_AMOUNT,
l_transaction_rec.HEADER_ID,
l_transaction_rec.LINE_ID,
l_transaction_rec.REASON_CODE,
l_transaction_rec.SOURCE_CODE,
l_transaction_rec.ERROR_FLAG,
l_transaction_rec.ATTRIBUTE_CATEGORY,
l_transaction_rec.ATTRIBUTE1,
l_transaction_rec.ATTRIBUTE2,
l_transaction_rec.ATTRIBUTE3,
l_transaction_rec.ATTRIBUTE4 ,
l_transaction_rec.ATTRIBUTE5,
l_transaction_rec.ATTRIBUTE6,
l_transaction_rec.ATTRIBUTE7,
l_transaction_rec.ATTRIBUTE8,
l_transaction_rec.ATTRIBUTE9,
l_transaction_rec.ATTRIBUTE10,
l_transaction_rec.ATTRIBUTE11,
l_transaction_rec.ATTRIBUTE12,
l_transaction_rec.ATTRIBUTE13,
l_transaction_rec.ATTRIBUTE14 ,
l_transaction_rec.ATTRIBUTE15,
l_transaction_rec.org_id
);
ozf_utility_PVT.debug_message('insert done' || l_sales_transaction_id);
SELECT *
FROM ozf_inventory_tmp_t;
INSERT INTO ozf_inventory_tmp_t(
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login,
party_id,
cust_account_id,
inventory_item_id,
transaction_date,
primary_quantity,
primary_uom_code,
source_code,
transfer_type
)
SELECT
sysdate,
1,
sysdate,
-1,
-1,
stn.sold_to_party_id, --NULL,
NULL, --stn.sold_to_cust_account_id,
stn.inventory_item_id,
p_start_date,
SUM(DECODE(stn.transfer_type, 'IN', 1
, 'OUT', -1
, 0
) * NVL(stn.primary_quantity,0)),
stn.primary_uom_code,
NULL,
NULL
FROM ozf_sales_transactions_all stn
WHERE stn.transaction_date <= p_start_date
AND stn.source_code IN ('OM', 'MA')
AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
FROM ozf_resale_lines_int_all rli
, hz_cust_accounts hca
WHERE rli.resale_batch_id = p_resale_batch_id
--AND rli.sold_from_cust_account_id = stn.sold_to_cust_account_id
AND rli.sold_from_cust_account_id = hca.cust_account_id
AND hca.party_id = stn.sold_to_party_id
)
GROUP BY stn.sold_to_party_id --stn.sold_to_cust_account_id
, stn.inventory_item_id
, stn.primary_uom_code
UNION ALL
SELECT
sysdate,
1,
sysdate,
-1,
-1,
stn.sold_from_party_id, --NULL,
NULL, --stn.sold_from_cust_account_id,
stn.inventory_item_id,
p_start_date,
SUM(DECODE(stn.transfer_type, 'IN', 1
, 'OUT', -1
, 0
) * NVL(stn.primary_quantity,0)),
stn.primary_uom_code,
NULL,
NULL
FROM ozf_sales_transactions_all stn
WHERE stn.transaction_date <= p_start_date
AND stn.source_code = 'IS'
AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
FROM ozf_resale_lines_int_all rli
, hz_cust_accounts hca
WHERE rli.resale_batch_id = p_resale_batch_id
--AND rli.sold_from_cust_account_id = stn.sold_from_cust_account_id
--AND rli.sold_from_cust_account_id = stn.sold_to_cust_account_id
AND rli.sold_from_cust_account_id = hca.cust_account_id
AND hca.party_id = stn.sold_from_party_id
)
GROUP BY stn.sold_from_party_id --stn.sold_from_cust_account_id
, stn.inventory_item_id
, stn.primary_uom_code;
INSERT INTO ozf_inventory_tmp_t(
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login,
party_id,
cust_account_id,
inventory_item_id,
transaction_date,
primary_quantity,
primary_uom_code,
source_code,
transfer_type
)
SELECT
sysdate,
1,
sysdate,
-1,
-1,
stn.sold_to_party_id, --NULL,
NULL, --stn.sold_to_cust_account_id,
stn.inventory_item_id,
stn.transaction_date,
DECODE(stn.transfer_type, 'IN', 1
, 'OUT', -1
, 0
) * NVL(stn.primary_quantity,0),
stn.primary_uom_code,
NULL,
NULL
FROM ozf_sales_transactions_all stn
WHERE stn.transaction_date > p_start_date
AND stn.transaction_date <= p_end_date
AND stn.source_code IN ('OM', 'MA')
AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
FROM ozf_resale_lines_int_all rli
, hz_cust_accounts hca
WHERE rli.resale_batch_id = p_resale_batch_id
--AND rli.sold_from_cust_account_id = stn.sold_to_cust_account_id
AND rli.sold_from_cust_account_id = hca.cust_account_id
AND hca.party_id = stn.sold_to_party_id
)
UNION ALL
SELECT
sysdate,
1,
sysdate,
-1,
-1,
stn.sold_from_party_id, --NULL,
NULL, --stn.sold_from_cust_account_id,
stn.inventory_item_id,
stn.transaction_date,
DECODE(stn.transfer_type, 'IN', 1
, 'OUT', -1
, 0
) * NVL(stn.primary_quantity,0),
stn.primary_uom_code,
NULL,
NULL
FROM ozf_sales_transactions_all stn
WHERE stn.transaction_date > p_start_date
AND stn.transaction_date <= p_end_date
AND source_code = 'IS'
AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
FROM ozf_resale_lines_int_all rli
, hz_cust_accounts hca
WHERE rli.resale_batch_id = p_resale_batch_id
--AND rli.sold_from_cust_account_id = stn.sold_from_cust_account_id
AND rli.sold_from_cust_account_id = hca.cust_account_id
AND hca.party_id = stn.sold_to_party_id
);
insert into ozf_inventory_tmp_t(
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN,
party_id ,
inventory_item_id,
primary_uom_code ,
primary_quantity)
select sysdate,
1,
p_start_date,
-1,
-1,
a.party_id,
a.inventory_item_id,
a.primary_uom,
sum(a.primary_quantity)
from ozf_inventory_summary_mv a, (SELECT time_id
FROM OZF_TIME_RPT_STRUCT
WHERE report_date= trunc(p_start_date)
AND BITAND(record_type_id,1143)=record_type_id
) b
where a.time_id = b.time_id
and a.party_id = p_party_id
group by sysdate,
1,
p_start_date,
-1,
-1,
a.party_id,
a.inventory_item_id,
a.primary_uom;
SELECT SUM(primary_quantity)
INTO l_total_primary_quantity
FROM ozf_inventory_tmp_t;
PROCEDURE update_Inventory_tmp (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_sales_transaction_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
l_api_name CONSTANT VARCHAR2(30) := 'update_inventory_tmp';
select primary_quantity, inventory_item_id, sold_from_party_id
from OZF_SALES_TRANSACTIONS_ALL
where Sales_Transaction_id = p_id;
update ozf_inventory_tmp_t
set primary_quantity = primary_quantity - l_primary_quantity
where party_id = l_party_id
and inventory_item_id = l_inventory_item_id;
END update_inventory_tmp;
SELECT SUM(primary_quantity)
, primary_uom_code
FROM ozf_inventory_tmp_t
--WHERE cust_account_id = cv_cust_account_id
WHERE party_id = cv_party_id
AND inventory_item_id = cv_inventory_item_id
AND transaction_date <= cv_transaction_date
GROUP BY primary_uom_code;
SELECT primary_uom_code, primary_quantity
FROM ozf_inventory_tmp_t
WHERE inventory_item_id = p_inventory_item_id;
SELECT primary_uom_code
FROM ozf_inventory_tmp_t
--WHERE cust_account_id = cv_cust_account_id
WHERE party_id = cv_party_id
AND inventory_item_id = cv_inventory_item_id
AND rownum = 1;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = cv_cust_account_id;
SELECT *
FROM ozf_inventory_tmp_t;
INSERT INTO ozf_inventory_tmp_t(
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login,
party_id,
cust_account_id,
inventory_item_id,
transaction_date,
primary_quantity,
primary_uom_code,
source_code,
transfer_type
) VALUES (
sysdate,
p_line_int_rec.created_by ,
sysdate,
p_line_int_rec.last_updated_by ,
p_line_int_rec.last_update_login,
l_sold_from_party_id,
NULL, --p_line_int_rec.sold_from_cust_account_id,
p_line_int_rec.inventory_item_id,
p_line_int_rec.date_ordered,
l_converted_quantity,
l_primary_uom_code,
'IS',
l_transfer_type
);
SELECT a.sales_transaction_id,
a.amount / a.primary_quantity,
a.currency_code,
a.transaction_date,
decode(a.transfer_type, 'IN', a.available_primary_quantity, 'OUT', -1 * a.available_primary_quantity),
a.primary_uom_code
FROM ozf_sales_transactions a
WHERE a.available_primary_quantity > 0
AND a.inventory_item_id = p_inventory_item_id
AND a.sold_to_cust_account_id = p_sold_from_cust_account_id
-- AND sold_to_party_site_id = p_sold_from_site_id
AND a.source_code = 'OM'
AND a.transaction_date< p_order_date
ORDER BY a.transaction_date DESC;
UPDATE ozf_sales_transactions_all
SET available_primary_quantity = available_primary_quantity - l_used_quantity_tbl(i)
WHERE sales_transaction_id = l_trans_id_tbl(i);