[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
t_hdr.header_id,
t_hdr.start_org_id,
t_hdr.end_org_id,
t_hdr.organization_id,
t_hdr.start_date,
t_hdr.end_date,
t_hdr.asset_item_pricing_option,
t_hdr.expense_item_pricing_option,
t_hdr.new_accounting_flag,
t_hdr.qualifier_code,
t_hdr.qualifier_value_id
FROM
mtl_transaction_flow_headers t_hdr
WHERE
t_hdr.start_org_id = l_start_org_id
and t_hdr.end_org_id = l_end_org_id
and l_txn_date between t_hdr.start_date and nvl(t_hdr.end_date,l_txn_date+1)
and t_hdr.flow_type = l_flow_type
ORDER BY
t_hdr.organization_id,t_hdr.qualifier_code;
SELECT
t_line.line_number,
t_line.from_org_id,
t_line.from_organization_id,
t_line.to_org_id,
t_line.to_organization_id,
--
icp.customer_id,
icp.address_id,
icp.customer_site_id,
icp.cust_trx_type_id,
icp.vendor_id,
icp.vendor_site_id,
icp.freight_code_combination_id,
icp.inventory_accrual_account_id,
icp.expense_accrual_account_id,
icp.intercompany_cogs_account_id
FROM
mtl_transaction_flow_lines t_line,
mtl_intercompany_parameters icp
WHERE
t_line.header_id = l_header_id
and icp.ship_organization_id=t_line.from_org_id
and icp.sell_organization_id=t_line.to_org_id
and icp.flow_type = l_flow_type
ORDER BY t_line.line_number;
SELECT default_cost_group_id
INTO l_transaction_flows_tbl(rcount).From_ORG_COST_GROUP_ID
FROM mtl_parameters mp
WHERE
organization_id = l_txn_flow_lines.from_organization_id;
SELECT default_cost_group_id
INTO l_transaction_flows_tbl(rcount).to_ORG_COST_GROUP_ID
FROM mtl_parameters mp
WHERE
organization_id = l_txn_flow_lines.to_organization_id;
select name
into l_from_ou_name
FROM hr_organization_units
WHERE organization_id = p_start_operating_unit;
select name
into l_to_ou_name
FROM hr_organization_units
WHERE organization_id = p_end_operating_unit;
x_transaction_flows_tbl.DELETE;
x_transaction_flows_tbl.delete;
x_transaction_flows_tbl.delete;
x_transaction_flows_tbl.delete;
SELECT
t_hdr.header_id,
t_hdr.start_org_id,
t_hdr.end_org_id,
t_hdr.organization_id,
t_hdr.start_date,
t_hdr.end_date,
t_hdr.asset_item_pricing_option,
t_hdr.expense_item_pricing_option,
t_hdr.new_accounting_flag,
t_hdr.qualifier_code,
t_hdr.qualifier_value_id,
--
t_line.line_number,
t_line.from_org_id,
t_line.from_organization_id,
t_line.to_org_id,
t_line.to_organization_id,
--
icp.customer_id,
icp.address_id,
icp.customer_site_id,
icp.cust_trx_type_id,
icp.vendor_id,
icp.vendor_site_id,
icp.freight_code_combination_id,
icp.inventory_accrual_account_id,
icp.expense_accrual_account_id,
icp.intercompany_cogs_account_id
FROM
mtl_transaction_flow_headers t_hdr,
mtl_transaction_flow_lines t_line,
mtl_intercompany_parameters icp
WHERE
t_hdr.header_id = l_header_id
AND t_hdr.header_id = t_line.header_id (+)
and icp.ship_organization_id=t_line.from_org_id
and icp.sell_organization_id=t_line.to_org_id
AND icp.flow_type = t_hdr.flow_type
ORDER BY t_line.line_number;
SELECT default_cost_group_id
INTO l_transaction_flows_tbl(rcount).From_ORG_COST_GROUP_ID
FROM mtl_parameters mp
WHERE
organization_id = l_txn_flows.from_organization_id;
SELECT default_cost_group_id
INTO l_transaction_flows_tbl(rcount).to_ORG_COST_GROUP_ID
FROM mtl_parameters mp
WHERE
organization_id = l_txn_flows.to_organization_id;
x_transaction_flows_tbl.delete;
x_transaction_flows_tbl.delete;
x_transaction_flows_tbl.delete;
SELECT
t_hdr.HEADER_ID,
t_hdr.organization_id,
t_hdr.new_accounting_flag,
t_hdr.Qualifier_Code,
t_hdr.Qualifier_Value_Id
FROM
mtl_transaction_flow_headers t_hdr
WHERE
t_hdr.start_org_id = l_start_org_id
and t_hdr.end_org_id = l_end_org_id
and l_txn_date between t_hdr.start_date and nvl(t_hdr.end_date,l_txn_date+1)
and t_hdr.flow_type = l_flow_type
ORDER BY
t_hdr.organization_id, t_hdr.qualifier_code;
select name
into l_from_ou_name
FROM hr_organization_units
WHERE organization_id = p_start_operating_unit;
select name
into l_to_ou_name
FROM hr_organization_units
WHERE organization_id = p_end_operating_unit;
* Procedure: Insert_Row()
* This API is a private API to insert new transaction flow for a start operating unit and end operating unit.
* This API will be called by the Transaction Flow Setup Form on the ON-INSERT trigger of the block.
* Inputs:
*
* 1. Start OU: The start Operating Unit for which the Global Procurement or Drop Ship occurred.
* This is a required parameter.
* 2. End OU: The End Operating Unit for which the Global Procurement of Drop Ship occurred.
* This is a required parameter
* 3. Flow Type: To indicate what is the flow type, either Global Procurement or Drop Ship
* 4. Qualifier Code: The qualifier code, for this release, it will be "1" - Category.
* This is an optional parameter. Default value for this parameter is NULL.
* 5. Qualifier Value ID: The value of the qualifier.
* For this release, it will be the category_id of the item. The default value of this parameter will be NULL.
* 6. Start Date: The date when the Inter-company Transaction Flow become active.
* The default value is SYSDATE. This is required parameter
* 7. End Date: The date when the when Inter-company Transaction Flow become inactive.
* 8. Asset Item Pricing Option: The pricing option for asset item for global procurement flow.
* 9. Expense Item Pricing option: the pricing option for expense item
* 10. new accounting flag : flag to indicate new accounting will be use
* 11. line_number_tbl - list of sequence of the line nodes
* 12. from_ou_tbl - list of from operating unit of the line nodes
* 13. to_ou_tbl - list of to_operating unit of the line nodes
*
* Outputs:
* 1. header_id
* 2. line_number
*
*=======================================================================================================*/
PROCEDURE create_transaction_flow
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_header_id OUT NOCOPY NUMBER
, x_line_number_tbl OUT NOCOPY NUMBER_TBL
, p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_validation_level IN NUMBER
, p_start_org_id IN NUMBER
, p_end_org_id IN NUMBER
, p_flow_type IN NUMBER
, p_organization_id IN NUMBER
, p_qualifier_code IN NUMBER
, p_qualifier_value_id IN NUMBER
, p_asset_item_pricing_option IN NUMBER
, p_expense_item_pricing_option IN NUMBER
, p_new_accounting_flag IN VARCHAR2
, p_start_date IN DATE
, p_end_date IN DATE
, P_Attribute_Category IN VARCHAR2
, P_Attribute1 IN VARCHAR2
, P_Attribute2 IN VARCHAR2
, P_Attribute3 IN VARCHAR2
, P_Attribute4 IN VARCHAR2
, P_Attribute5 IN VARCHAR2
, P_Attribute6 IN VARCHAR2
, P_Attribute7 IN VARCHAR2
, P_Attribute8 IN VARCHAR2
, P_Attribute9 IN VARCHAR2
, P_Attribute10 IN VARCHAR2
, P_Attribute11 IN VARCHAR2
, P_Attribute12 IN VARCHAR2
, P_Attribute13 IN VARCHAR2
, P_Attribute14 IN VARCHAR2
, P_Attribute15 IN VARCHAR2
, p_line_number_tbl IN NUMBER_TBL
, p_from_org_id_tbl IN NUMBER_TBL
, p_from_organization_id_tbl IN NUMBER_TBL
, p_to_org_id_tbl IN NUMBER_TBL
, p_to_organization_id_tbl IN NUMBER_TBL
, P_LINE_Attribute_Category_tbl IN VARCHAR2_tbl
, P_LINE_Attribute1_tbl IN VARCHAR2_tbl
, P_LINE_Attribute2_tbl IN VARCHAR2_tbl
, P_LINE_Attribute3_tbl IN VARCHAR2_tbl
, P_LINE_Attribute4_tbl IN VARCHAR2_tbl
, P_LINE_Attribute5_tbl IN VARCHAR2_tbl
, P_LINE_Attribute6_tbl IN VARCHAR2_tbl
, P_LINE_Attribute7_tbl IN VARCHAR2_tbl
, P_LINE_Attribute8_tbl IN VARCHAR2_tbl
, P_LINE_Attribute9_tbl IN VARCHAR2_tbl
, P_LINE_Attribute10_tbl IN VARCHAR2_tbl
, P_LINE_Attribute11_tbl IN VARCHAR2_tbl
, P_LINE_Attribute12_tbl IN VARCHAR2_tbl
, P_LINE_Attribute13_tbl IN VARCHAR2_tbl
, P_LINE_Attribute14_tbl IN VARCHAR2_tbl
, P_LINE_Attribute15_tbl IN VARCHAR2_tbl
, P_Ship_Organization_Id_tbl IN NUMBER_tbl
, P_Sell_Organization_Id_tbl IN NUMBER_tbl
, P_Vendor_Id_tbl IN NUMBER_tbl
, P_Vendor_Site_Id_tbl IN NUMBER_tbl
, P_Customer_Id_tbl IN NUMBER_tbl
, P_Address_Id_tbl IN NUMBER_tbl
, P_Customer_Site_Id_tbl IN NUMBER_tbl
, P_Cust_Trx_Type_Id_tbl IN NUMBER_tbl
, P_IC_Attribute_Category_tbl IN VARCHAR2_tbl
, P_IC_Attribute1_tbl IN VARCHAR2_tbl
, P_IC_Attribute2_tbl IN VARCHAR2_tbl
, P_IC_Attribute3_tbl IN VARCHAR2_tbl
, P_IC_Attribute4_tbl IN VARCHAR2_tbl
, P_IC_Attribute5_tbl IN VARCHAR2_tbl
, P_IC_Attribute6_tbl IN VARCHAR2_tbl
, P_IC_Attribute7_tbl IN VARCHAR2_tbl
, P_IC_Attribute8_tbl IN VARCHAR2_tbl
, P_IC_Attribute9_tbl IN VARCHAR2_tbl
, P_IC_Attribute10_tbl IN VARCHAR2_tbl
, P_IC_Attribute11_tbl IN VARCHAR2_tbl
, P_IC_Attribute12_tbl IN VARCHAR2_tbl
, P_IC_Attribute13_tbl IN VARCHAR2_tbl
, P_IC_Attribute14_tbl IN VARCHAR2_tbl
, P_IC_Attribute15_tbl IN VARCHAR2_tbl
, P_Revalue_Average_Flag_tbl IN VARCHAR2_tbl
, P_Freight_Code_Comb_Id_tbl IN NUMBER_tbl
, p_inv_currency_code_tbl IN NUMBER_tbl
, P_IC_COGS_Acct_Id_tbl IN NUMBER_tbl
, P_Inv_Accrual_Acct_Id_tbl IN NUMBER_tbl
, P_Exp_Accrual_Acct_Id_tbl IN NUMBER_tbl
) IS
l_lines_tab INV_TRANSACTION_FLOW_PVT.trx_flow_lines_tab;
SELECT mtl_transaction_flow_headers_s.NEXTVAL
INTO l_header_id FROM dual;
SELECT 'ic_relation_exists' INTO vDummy
FROM MTL_INTERCOMPANY_PARAMETERS
WHERE ship_organization_id = p_from_org_id_tbl(i)
AND sell_organization_id = p_to_org_id_tbl(i)
AND flow_type = p_flow_type;
print_debug('Before MTL_IC_PARAMETERS_PKG.INSERT_ROW call...');
MTL_IC_PARAMETERS_PKG.INSERT_ROW
(X_Rowid => l_ic_rowid,
X_Ship_Organization_Id => p_from_org_id_tbl(i),
X_Sell_Organization_Id => p_to_org_id_tbl(i),
X_Last_Update_Date => Sysdate,
X_Last_Updated_By => FND_GLOBAL.user_id,
X_Creation_Date => Sysdate,
X_Created_By => FND_GLOBAL.user_id,
X_Last_Update_Login => fnd_global.login_id,
X_Vendor_Id => p_vendor_id_tbl(i),
X_Vendor_Site_Id => p_vendor_site_id_tbl(i),
X_Customer_Id => p_customer_id_tbl(i),
X_Address_Id => p_address_id_tbl(i),
X_Customer_Site_Id => p_customer_site_id_tbl(i),
X_Cust_Trx_Type_Id => p_cust_trx_type_id_tbl(i),
X_Attribute_Category => p_ic_attribute_category_tbl(i),
X_Attribute1 => p_ic_attribute1_tbl(i),
X_Attribute2 => p_ic_attribute2_tbl(i),
X_Attribute3 => p_ic_attribute3_tbl(i),
X_Attribute4 => p_ic_attribute4_tbl(i),
X_Attribute5 => p_ic_attribute5_tbl(i),
X_Attribute6 => p_ic_attribute6_tbl(i),
X_Attribute7 => p_ic_attribute7_tbl(i),
X_Attribute8 => p_ic_attribute8_tbl(i),
X_Attribute9 => p_ic_attribute9_tbl(i),
X_Attribute10 => p_ic_attribute10_tbl(i),
X_Attribute11 => p_ic_attribute11_tbl(i),
X_Attribute12 => p_ic_attribute12_tbl(i),
X_Attribute13 => p_ic_attribute13_tbl(i),
X_Attribute14 => p_ic_attribute14_tbl(i),
X_Attribute15 => p_ic_attribute15_tbl(i),
X_Revalue_Average_Flag => p_revalue_average_flag_tbl(i),
X_Freight_Code_Combination_Id => p_freight_code_comb_id_tbl(i),
X_Inv_Currency_Code => p_inv_currency_code_tbl(i),
X_Flow_Type => p_flow_type,
X_Intercompany_COGS_Account_Id => p_IC_COGS_Acct_Id_tbl(i) ,
X_Inventory_Accrual_Account_Id => p_Inv_Accrual_Acct_Id_tbl(i) ,
X_Expense_Accrual_Account_Id => p_Exp_Accrual_Acct_Id_tbl(i));
print_debug('After MTL_IC_PARAMETERS_PKG.INSERT_ROW call...');
x_line_number_tbl.DELETE;
x_line_number_tbl.DELETE;
x_line_number_tbl.DELETE;
* Procedure: Update_Transaction_Flow()
*
* Description:
* This API is used to update the transaction flow. Once a transaction flow is created, user can only
* update the start date and the end date and desc flex field attributes of headers and lines and the Ic relation
* defined.
*
*
*
*========================================================================================================*/
PROCEDURE update_transaction_flow
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_validation_level IN NUMBER
, p_header_id IN NUMBER
, p_flow_type IN NUMBER
, p_start_date IN DATE
, p_end_date IN DATE
, P_Attribute_Category IN VARCHAR2
, P_Attribute1 IN VARCHAR2
, P_Attribute2 IN VARCHAR2
, P_Attribute3 IN VARCHAR2
, P_Attribute4 IN VARCHAR2
, P_Attribute5 IN VARCHAR2
, P_Attribute6 IN VARCHAR2
, P_Attribute7 IN VARCHAR2
, P_Attribute8 IN VARCHAR2
, P_Attribute9 IN VARCHAR2
, P_Attribute10 IN VARCHAR2
, P_Attribute11 IN VARCHAR2
, P_Attribute12 IN VARCHAR2
, P_Attribute13 IN VARCHAR2
, P_Attribute14 IN VARCHAR2
, P_Attribute15 IN VARCHAR2
, p_line_number_tbl IN NUMBER_TBL
, P_LINE_Attribute_Category_tbl IN VARCHAR2_tbl
, P_LINE_Attribute1_tbl IN VARCHAR2_tbl
, P_LINE_Attribute2_tbl IN VARCHAR2_tbl
, P_LINE_Attribute3_tbl IN VARCHAR2_tbl
, P_LINE_Attribute4_tbl IN VARCHAR2_tbl
, P_LINE_Attribute5_tbl IN VARCHAR2_tbl
, P_LINE_Attribute6_tbl IN VARCHAR2_tbl
, P_LINE_Attribute7_tbl IN VARCHAR2_tbl
, P_LINE_Attribute8_tbl IN VARCHAR2_tbl
, P_LINE_Attribute9_tbl IN VARCHAR2_tbl
, P_LINE_Attribute10_tbl IN VARCHAR2_tbl
, P_LINE_Attribute11_tbl IN VARCHAR2_tbl
, P_LINE_Attribute12_tbl IN VARCHAR2_tbl
, P_LINE_Attribute13_tbl IN VARCHAR2_tbl
, P_LINE_Attribute14_tbl IN VARCHAR2_tbl
, P_LINE_Attribute15_tbl IN VARCHAR2_tbl
, P_Ship_Organization_Id_tbl IN NUMBER_tbl
, P_Sell_Organization_Id_tbl IN NUMBER_tbl
, P_Vendor_Id_tbl IN NUMBER_tbl
, P_Vendor_Site_Id_tbl IN NUMBER_tbl
, P_Customer_Id_tbl IN NUMBER_tbl
, P_Address_Id_tbl IN NUMBER_tbl
, P_Customer_Site_Id_tbl IN NUMBER_tbl
, P_Cust_Trx_Type_Id_tbl IN NUMBER_tbl
, P_IC_Attribute_Category_tbl IN VARCHAR2_tbl
, P_IC_Attribute1_tbl IN VARCHAR2_tbl
, P_IC_Attribute2_tbl IN VARCHAR2_tbl
, P_IC_Attribute3_tbl IN VARCHAR2_tbl
, P_IC_Attribute4_tbl IN VARCHAR2_tbl
, P_IC_Attribute5_tbl IN VARCHAR2_tbl
, P_IC_Attribute6_tbl IN VARCHAR2_tbl
, P_IC_Attribute7_tbl IN VARCHAR2_tbl
, P_IC_Attribute8_tbl IN VARCHAR2_tbl
, P_IC_Attribute9_tbl IN VARCHAR2_tbl
, P_IC_Attribute10_tbl IN VARCHAR2_tbl
, P_IC_Attribute11_tbl IN VARCHAR2_tbl
, P_IC_Attribute12_tbl IN VARCHAR2_tbl
, P_IC_Attribute13_tbl IN VARCHAR2_tbl
, P_IC_Attribute14_tbl IN VARCHAR2_tbl
, P_IC_Attribute15_tbl IN VARCHAR2_tbl
, P_Revalue_Average_Flag_tbl IN VARCHAR2_tbl
, P_Freight_Code_Comb_Id_tbl IN NUMBER_tbl
, p_inv_currency_code_tbl IN NUMBER_tbl
, P_IC_COGS_Acct_Id_tbl IN NUMBER_tbl
, P_Inv_Accrual_Acct_Id_tbl IN NUMBER_tbl
, P_Exp_Accrual_Acct_Id_tbl IN NUMBER_tbl
) IS
l_return_status VARCHAR2(1) := NULL;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRANSACTION_FLOW';
SAVEPOINT UPDATE_TRANSACTION_FLOW;
print_debug('Before inv_transaction_flow_pub.update_transaction_flow_header call...');
inv_transaction_flow_pub.update_transaction_flow_header
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
p_header_id => p_header_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15
);
print_debug('After inv_transaction_flow_pub.update_transaction_flow_header call... Return Status=' || l_return_status);
print_debug('Before inv_transaction_flow_pub.update_transaction_flow_line call...');
inv_transaction_flow_pub.update_transaction_flow_line
( x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
, p_api_version => p_api_version
, p_init_msg_list => G_FALSE
, p_header_id => p_header_id
, p_line_number => p_line_number_tbl(i)
, p_attribute_category => p_line_attribute_category_tbl(i)
, p_attribute1 => p_line_attribute1_tbl(i)
, p_attribute2 => p_line_attribute2_tbl(i)
, p_attribute3 => p_line_attribute3_tbl(i)
, p_attribute4 => p_line_attribute4_tbl(i)
, p_attribute5 => p_line_attribute5_tbl(i)
, p_attribute6 => p_line_attribute6_tbl(i)
, p_attribute7 => p_line_attribute7_tbl(i)
, p_attribute8 => p_line_attribute8_tbl(i)
, p_attribute9 => p_line_attribute9_tbl(i)
, p_attribute10 => p_line_attribute10_tbl(i)
, p_attribute11 => p_line_attribute11_tbl(i)
, p_attribute12 => p_line_attribute12_tbl(i)
, p_attribute13 => p_line_attribute13_tbl(i)
, p_attribute14 => p_line_attribute14_tbl(i)
, p_attribute15 => p_line_attribute15_tbl(i)
);
print_debug('After inv_transaction_flow_pub.update_transaction_flow_line call... Return Status=' || l_return_status);
print_debug('Before inv_transaction_flow_pub.update_ic_relation call...');
inv_transaction_flow_pub.update_ic_relation
(x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
p_Ship_Organization_Id => p_ship_organization_id_tbl(i),
p_Sell_Organization_Id => p_Sell_Organization_Id_tbl(i),
p_Vendor_Id => p_vendor_id_tbl(i),
p_Vendor_Site_Id => p_vendor_site_id_tbl(i),
p_Customer_Id => p_customer_id_tbl(i) ,
p_Address_Id => p_address_id_tbl(i) ,
p_Customer_Site_Id => p_customer_site_id_tbl(i) ,
p_Cust_Trx_Type_Id => p_cust_trx_type_id_tbl(i) ,
p_Attribute_Category => p_ic_attribute_category_tbl(i) ,
p_Attribute1 => p_ic_attribute1_tbl(i) ,
p_Attribute2 => p_ic_attribute2_tbl(i) ,
p_Attribute3 => p_ic_attribute3_tbl(i) ,
p_Attribute4 => p_ic_attribute4_tbl(i) ,
p_Attribute5 => p_ic_attribute5_tbl(i) ,
p_Attribute6 => p_ic_attribute6_tbl(i) ,
p_Attribute7 => p_ic_attribute7_tbl(i) ,
p_Attribute8 => p_ic_attribute8_tbl(i) ,
p_Attribute9 => p_ic_attribute9_tbl(i) ,
p_Attribute10 => p_ic_attribute10_tbl(i) ,
p_Attribute11 => p_ic_attribute11_tbl(i) ,
p_Attribute12 => p_ic_attribute12_tbl(i) ,
p_Attribute13 => p_ic_attribute13_tbl(i) ,
p_Attribute14 => p_ic_attribute14_tbl(i) ,
p_Attribute15 => p_ic_attribute15_tbl(i) ,
p_Revalue_Average_Flag => p_revalue_average_flag_tbl(i) ,
p_Freight_Code_Combination_Id =>p_freight_code_comb_id_tbl(i),
p_inv_currency_code => p_inv_currency_code_tbl(i),
p_Flow_Type =>p_flow_type,
p_Intercompany_COGS_Account_Id =>p_IC_COGS_Acct_Id_tbl(i),
p_Inventory_Accrual_Account_Id => p_Inv_Accrual_Acct_Id_tbl(i),
p_Expense_Accrual_Account_Id => p_Exp_Accrual_Acct_Id_tbl(i)
);
print_debug('After inv_transaction_flow_pub.update_ic_relation call...Return Status=' || l_return_status);
ROLLBACK TO UPDATE_TRANSACTION_FLOW;
ROLLBACK TO UPDATE_TRANSACTION_FLOW;
ROLLBACK TO UPDATE_TRANSACTION_FLOW;
END UPDATE_TRANSACTION_FLOW;
PROCEDURE update_transaction_flow_header
(X_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
, p_header_id IN NUMBER
, p_end_date IN DATE
, p_start_date IN DATE
, P_Attribute_Category IN VARCHAR2
, P_Attribute1 IN VARCHAR2
, P_Attribute2 IN VARCHAR2
, P_Attribute3 IN VARCHAR2
, P_Attribute4 IN VARCHAR2
, P_Attribute5 IN VARCHAR2
, P_Attribute6 IN VARCHAR2
, P_Attribute7 IN VARCHAR2
, P_Attribute8 IN VARCHAR2
, P_Attribute9 IN VARCHAR2
, P_Attribute10 IN VARCHAR2
, P_Attribute11 IN VARCHAR2
, P_Attribute12 IN VARCHAR2
, P_Attribute13 IN VARCHAR2
, P_Attribute14 IN VARCHAR2
, P_Attribute15 IN VARCHAR2)
IS
l_ref_date DATE := Sysdate;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRANSACTION_FLOW_HEADER';
SAVEPOINT UPDATE_TRANSACTION_FLOW_HEADER;
l_lines_tab.DELETE;
inv_transaction_flow_pvt.update_ic_txn_flow_hdr
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_header_id => p_header_id,
p_commit => false,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_ref_date => l_ref_date,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15
);
'inv_transaction_flow_pvt.update_ic_txn_flow_hdr'||
x_return_status,l_api_name);
'inv_transaction_flow_pvt.update_ic_txn_flow_hdr'||
l_msg_data,l_api_name);
'inv_transaction_flow_pvt.update_ic_txn_flow_hdr'||
l_msg_data,l_api_name);
ROLLBACK TO UPDATE_TRANSACTION_FLOW_HEADER;
ROLLBACK TO UPDATE_TRANSACTION_FLOW_HEADER;
ROLLBACK TO UPDATE_TRANSACTION_FLOW_HEADER;
( G_PACKAGE_NAME, 'UPDATE_TRANSACTION_FLOW_HEADER');
END Update_Transaction_Flow_Header;
PROCEDURE update_transaction_flow_line
(x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY VARCHAR2
, p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT g_false
, p_header_id IN NUMBER
, p_line_number IN NUMBER
, p_attribute_category IN VARCHAR2
, p_attribute1 IN VARCHAR2
, p_attribute2 IN VARCHAR2
, p_attribute3 IN VARCHAR2
, p_attribute4 IN VARCHAR2
, p_attribute5 IN VARCHAR2
, p_attribute6 IN VARCHAR2
, p_attribute7 IN VARCHAR2
, p_attribute8 IN VARCHAR2
, p_attribute9 IN VARCHAR2
, p_attribute10 IN VARCHAR2
, p_attribute11 IN VARCHAR2
, p_attribute12 IN VARCHAR2
, p_attribute13 IN VARCHAR2
, p_attribute14 IN VARCHAR2
, p_attribute15 IN VARCHAR2
)IS
l_return_status VARCHAR2(1) := NULL;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Transaction_flow_line';
SAVEPOINT UPDATE_TRANSACTION_FLOW_LINE;
inv_transaction_flow_pvt.update_ic_txn_flow_line
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_commit => FALSE,
p_header_id => p_header_id,
p_line_number => p_line_number,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15
);
'inv_transaction_flow_pvt.update_ic_txn_flow_line'||
x_return_status,l_api_name);
'inv_transaction_flow_pvt.update_ic_txn_flow_line'||
l_msg_data,l_api_name);
'inv_transaction_flow_pvt.update_ic_txn_flow_line'||
l_msg_data,l_api_name);
ROLLBACK TO update_transaction_flow_line;
ROLLBACK TO update_transaction_flow_line;
ROLLBACK TO update_transaction_flow_line;
( G_PACKAGE_NAME, 'UPDATE_TRANSACTION_FLOW');
END Update_Transaction_Flow_line;
PROCEDURE update_ic_relation
(x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT g_false,
p_Ship_Organization_Id IN NUMBER,
p_Sell_Organization_Id IN NUMBER,
p_Vendor_Id IN NUMBER,
p_Vendor_Site_Id IN NUMBER,
p_Customer_Id IN NUMBER,
p_Address_Id IN NUMBER,
p_Customer_Site_Id IN NUMBER,
p_Cust_Trx_Type_Id IN NUMBER,
p_Attribute_Category IN VARCHAR2,
p_Attribute1 IN VARCHAR2,
p_Attribute2 IN VARCHAR2,
p_Attribute3 IN VARCHAR2,
p_Attribute4 IN VARCHAR2,
p_Attribute5 IN VARCHAR2,
p_Attribute6 IN VARCHAR2,
p_Attribute7 IN VARCHAR2,
p_Attribute8 IN VARCHAR2,
p_Attribute9 IN VARCHAR2,
p_Attribute10 IN VARCHAR2,
p_Attribute11 IN VARCHAR2,
p_Attribute12 IN VARCHAR2,
p_Attribute13 IN VARCHAR2,
p_Attribute14 IN VARCHAR2,
p_Attribute15 IN VARCHAR2,
p_Revalue_Average_Flag IN VARCHAR2,
p_Freight_Code_Combination_Id IN NUMBER,
p_inv_currency_code IN NUMBER,
p_Flow_Type IN NUMBER,
p_Intercompany_COGS_Account_Id IN NUMBER,
p_Inventory_Accrual_Account_Id IN NUMBER,
p_Expense_Accrual_Account_Id IN NUMBER
)IS
CURSOR ic_information(l_ship_organization_id NUMBER,
l_sell_organization_id NUMBER,
l_flow_type NUMBER)
IS
SELECT
rowid,
ship_organization_id,
sell_organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_id,
address_id,
customer_site_id,
cust_trx_type_id,
vendor_id,
vendor_site_id,
revalue_average_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
freight_code_combination_id,
inv_currency_code,
flow_type,
intercompany_cogs_account_id,
inventory_accrual_account_id,
expense_accrual_account_id
FROM
mtl_intercompany_parameters
WHERE
ship_organization_id = l_ship_organization_id
AND sell_organization_id = l_sell_organization_id
AND flow_type = l_flow_type;
l_api_name CONSTANT VARCHAR2(30) := 'Update_ic_relation';
SAVEPOINT update_ic_relation;
mtl_ic_parameters_pkg.update_row
(x_rowid=> l_rowid,
x_ship_organization_id=> p_ship_organization_id,
x_sell_organization_id=> p_sell_organization_id,
x_last_update_date=>sysdate,
x_last_updated_by=> fnd_global.user_id,
x_last_update_login=> fnd_global.login_id,
x_vendor_id=> l_vendor_id,
x_vendor_site_id=> l_vendor_site_id,
x_customer_id=> l_customer_id,
x_address_id=> l_address_id,
x_customer_site_id=> l_customer_site_id,
x_cust_trx_type_id=> l_cust_trx_type_id,
x_attribute_category=> p_attribute_category,
x_attribute1=> p_attribute1,
x_attribute2=> p_attribute2,
x_attribute3=> p_attribute3,
x_attribute4=> p_attribute4,
x_attribute5=> p_attribute5,
x_attribute6=> p_attribute6,
x_attribute7=> p_attribute7,
x_attribute8=> p_attribute8,
x_attribute9=> p_attribute9,
x_attribute10=> p_attribute10,
x_attribute11=> p_attribute11,
x_attribute12=> p_attribute12,
x_attribute13=> p_attribute13,
x_attribute14=> p_attribute14,
x_attribute15=> p_attribute15,
x_revalue_average_flag=> p_revalue_average_flag,
x_freight_code_combination_id=> l_freight_code_combination_id,
x_inv_currency_code => p_inv_currency_Code,
x_flow_type=> p_flow_type,
x_intercompany_cogs_account_id=> l_intercompany_cogs_account_id,
x_inventory_accrual_account_id=> l_inventory_accrual_account_id,
x_expense_accrual_account_id=> l_expense_accrual_account_id
);
ROLLBACK TO update_ic_relation;
ROLLBACK TO update_ic_relation;
ROLLBACK TO update_ic_relation;
( G_PACKAGE_NAME, 'UPDATE_TRANSACTION_FLOW');
END Update_IC_RELATION;
SELECT 1 INTO nDummy FROM DUAL
WHERE EXISTS (SELECT 1 FROM MTL_TRANSACTION_FLOW_HEADERS
WHERE START_ORG_ID = p_ship_organization_id
AND END_ORG_ID = p_ship_organization_id
AND (NEW_ACCOUNTING_FLAG = 'Y' OR FLOW_TYPE <> 1));
SELECT 'Y' INTO l_valid FROM dual
WHERE
exists(SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNTS
WHERE CUST_ACCOUNT_ID = p_customer_id);
SELECT 'Y' INTO l_valid FROM dual
WHERE
exists
(select rsu.address_id
from ra_addresses_all ra , ra_site_uses_all rsu
where nvl(rsu.status,'A') = 'A'
and rsu.site_use_code = 'BILL_TO'
and ra.address_id = rsu.address_id and nvl(ra.status,'A') = 'A'
and ra.customer_id = Decode(p_customer_id,g_miss_num,ra.customer_id,p_customer_id)
and ra.org_id = p_ship_organization_id
AND rsu.address_id = p_address_id
AND rsu.site_use_id = Decode(p_customer_site_id,g_miss_num,rsu.site_use_id,p_customer_site_id));
SELECT 'Y' INTO l_valid
FROM DUAL
WHERE EXISTS(
SELECT rsu.cust_acct_site_id
FROM (SELECT loc_id address_id
, acct_site.status
, cust_account_id customer_id
, acct_site.org_id
FROM hz_party_sites party_site, hz_loc_assignments loc_assign, hz_locations loc, hz_cust_acct_sites_all acct_site
WHERE acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND loc.location_id = loc_assign.location_id
AND NVL(acct_site.org_id, -99) = NVL(loc_assign.org_id, -99)) ra
, hz_cust_site_uses_all rsu
WHERE NVL(rsu.status, 'A') = 'A'
AND rsu.site_use_code = 'BILL_TO'
AND ra.address_id = rsu.cust_acct_site_id
AND NVL(ra.status, 'A') = 'A'
AND ra.customer_id = DECODE(p_customer_id, g_miss_num, ra.customer_id, p_customer_id)
AND ra.org_id = p_ship_organization_id
AND rsu.cust_acct_site_id = p_address_id
AND rsu.site_use_id = DECODE(p_customer_site_id, g_miss_num, rsu.site_use_id, p_customer_site_id));
SELECT 'Y' INTO l_valid FROM dual
WHERE
exists
(select rsu.site_use_id
from ra_addresses_all ra , ra_site_uses_all rsu
where nvl(rsu.status,'A') = 'A'
and rsu.site_use_code = 'BILL_TO'
and ra.address_id = rsu.address_id and nvl(ra.status,'A') = 'A'
and ra.customer_id = Decode(p_customer_id,g_miss_num,ra.customer_id,p_customer_id)
and ra.org_id = p_ship_organization_id
AND rsu.address_id = Decode(p_address_id,g_miss_num,rsu.address_id,p_address_id)
AND rsu.site_use_id = p_customer_site_id);
SELECT 'Y' INTO l_valid
FROM DUAL
WHERE EXISTS(
SELECT rsu.site_use_id
FROM (SELECT loc_id address_id
, acct_site.status
, cust_account_id customer_id
, acct_site.org_id
FROM hz_party_sites party_site, hz_loc_assignments loc_assign, hz_locations loc, hz_cust_acct_sites_all acct_site
WHERE acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND loc.location_id = loc_assign.location_id
AND NVL(acct_site.org_id, -99) = NVL(loc_assign.org_id, -99)) ra
, hz_cust_site_uses_all rsu
WHERE NVL(rsu.status, 'A') = 'A'
AND rsu.site_use_code = 'BILL_TO'
AND ra.address_id = rsu.cust_acct_site_id
AND NVL(ra.status, 'A') = 'A'
AND ra.customer_id = DECODE(p_customer_id, g_miss_num, ra.customer_id, p_customer_id)
AND ra.org_id = p_ship_organization_id
AND rsu.cust_acct_site_id = DECODE(p_address_id, g_miss_num, rsu.cust_acct_site_id, p_address_id)
AND rsu.site_use_id = p_customer_site_id);
SELECT 'Y' INTO l_valid FROM dual
WHERE
exists
(select cust_trx_type_id
from ra_cust_trx_types_all
where
sysdate between nvl(start_date, sysdate-1)
and nvl(end_date, sysdate+1)
and org_id = p_ship_organization_id
AND cust_trx_type_id = p_cust_trx_type_id);
SELECT 'Y' INTO l_valid FROM dual
WHERE
exists
(select pov.vendor_id from
po_vendors pov,
FND_LOOKUPS FL
WHERE
NVL(POV.HOLD_FLAG,'N') = FL.LOOKUP_CODE
AND FL.LOOKUP_TYPE = 'YES_NO'
AND POV.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(POV.START_DATE_ACTIVE, SYSDATE-1)
AND NVL(POV.END_DATE_ACTIVE+1, SYSDATE+1)
and pov.vendor_id = p_vendor_id);
SELECT 'Y' INTO l_valid FROM dual
WHERE
exists
(select vendor_site_id
from po_vendor_sites_all
where pay_site_flag = 'Y'
and vendor_id = p_vendor_id
and org_id = p_sell_organization_id
AND vendor_site_id = p_vendor_site_id);
/* commented the selection of COA using LE - OU link which is obsoleted in R12
and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
Bug No - 4336479
BEGIN
SELECT
gsob.chart_of_accounts_id
into
l_ship_chart_of_accounts_id
from
gl_sets_of_books gsob,
hr_organization_information hoi,
hr_organization_information hoi1
where
hoi1.organization_id = p_ship_organization_id
and hoi1.org_information_context = 'Operating Unit Information'
and hoi.organization_id = to_number(hoi1.org_information2)
and hoi.org_information_context = 'Legal Entity Accounting'
and gsob.set_of_books_id = to_number(hoi.org_information1);
/* commented the selection of COA using LE - OU link which is obsoleted in R12
and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
Bug No - 4336479
BEGIN
select
gsob.chart_of_accounts_id
into
l_chart_of_accounts_id
from
gl_sets_of_books gsob,
hr_organization_information hoi,
hr_organization_information hoi1
where hoi1.organization_id = p_sell_organization_id
and hoi1.org_information_context = 'Operating Unit Information'
and hoi.organization_id = to_number(hoi1.org_information2)
and hoi.org_information_context = 'Legal Entity Accounting'
and gsob.set_of_books_id = to_number(hoi.org_information1);
SELECT header_id, line_id
FROM
oe_drop_ship_sources
WHERE
po_header_id = l_poh_id
AND po_line_id = l_pol_id
AND line_location_id = l_poll_id
ORDER BY header_id,line_id;
SELECT rt.po_header_id, rt.po_line_id, rt.po_line_location_id,
rt.transaction_type, rt.transaction_date, rsl.item_id
INTO
l_po_header_id, l_po_line_id, l_po_line_location_id,
l_transaction_type, l_transaction_date, l_inventory_item_id
FROM
rcv_transactions rt,
rcv_shipment_lines rsl
WHERE
transaction_id = p_rcv_transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id;
SELECT org_id, ship_from_org_id
INTO l_selling_ou, l_ship_from_org_id
FROM oe_order_lines_all
WHERE
header_id = l_header_id AND
line_id = l_line_id;
SELECT org_information3
INTO l_ship_from_ou
FROM HR_ORGANIZATION_INFORMATION HOI
WHERE HOI.ORGANIZATION_ID= l_ship_from_org_id
AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information';
l_qualifier_code_tbl.DELETE;
l_qualifier_value_tbl.DELETE;
SELECT category_id INTO l_qualifier_value_tbl(1)
FROM mtl_item_categories
WHERE
inventory_item_id = l_inventory_item_id
AND organization_id = l_ship_from_org_id
AND category_set_id = 1;
l_qualifier_value_tbl.DELETE;
l_qualifier_value_tbl.DELETE;
SELECT doc_type, line_id, whse_code
INTO l_doc_type, l_line_id, l_whse_code
FROM ic_tran_pnd
WHERE trans_id = p_reference_id;
SELECT WHS.mtl_organization_id, oeh.ordered_date
INTO l_organization_id, l_transaction_date
FROM ic_whse_mst WHS
, oe_order_lines_all OEL
, oe_order_headers_all OEH
WHERE OEL.line_id = l_line_id
AND oel.header_id = oeh.header_id
AND WHS.whse_code = l_whse_code;
SELECT WHS.mtl_organization_id, oeh.ordered_date
INTO l_organization_id, l_transaction_date
FROM ic_whse_mst WHS
, oe_order_lines_all OEL
, oe_order_headers_all OEH
, rcv_transactions RCT
, po_requisition_headers_all poh
, po_requisition_lines_all pol
WHERE poh.requisition_header_id = pol.requisition_header_id
AND pol.requisition_line_id = oel.orig_sys_document_Ref
AND oel.order_source_id = 10
AND oel.header_id = oeh.header_id
AND RCT.transaction_id = l_line_id
AND RCT.requisition_line_id = pol.requisition_line_id
AND WHS.whse_code = l_whse_code;
select mmt.organization_id, transaction_date
into l_organization_id, l_transaction_date
From mtl_material_transactions mmt
where mmt.transaction_id= p_reference_id;
select oel.ship_from_org_id, oeh.ordered_date
into l_organization_id, l_transaction_date
FROM oe_order_lines_all oel, oe_order_headers_all oeh
where oel.line_id = p_reference_id
AND oel.header_id = oeh.header_id;
select rcv.organization_id, transaction_date
into l_organization_id, l_transaction_date
FROM rcv_transactions rcv
WHERE rcv.transaction_id = p_reference_id;
/* commented the selection of COA using LE - OU link which is obsoleted in R12
and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
Bug No - 4336479
BEGIN
l_progress := 1;
SELECT to_number(LEI.org_information1)
into l_set_of_book_id
FROM HR_ORGANIZATION_INFORMATION LEI, HR_ORGANIZATION_UNITS OU,
HR_ORGANIZATION_INFORMATION OUI
WHERE OU.organization_id = p_org_id
AND LEI.org_information_context = 'Legal Entity Accounting'
AND to_char(LEI.organization_id) = OUI.org_information2
AND OUI.org_information_context = 'Operating Unit Information'
AND OUI.organization_id = OU.organization_id;
select currency_code
into l_functional_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_set_of_book_id;
select l.ato_line_id,l.header_id
into l_ato_line_id,l_order_header_id
from mtl_material_transactions mmt
, oe_order_lines_all l
WHERE MMT.transaction_id = p_transaction_id
AND l.line_id = mmt.trx_source_line_id;
SELECT line_id
INTO l_order_line_id
from oe_order_lines_all
where header_id=l_order_header_id
and ato_line_id=l_ato_line_id
and inventory_item_id=p_inventory_item_id;
SELECT trx_source_line_id
INTO l_order_line_id
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
select SPLL.operand, substr(SPL.currency_code, 1, 15)
INTO l_transfer_price, l_invoice_currency_code
FROM qp_list_headers_b spl, qp_list_lines SPLL, qp_pricing_attributes qpa
WHERE SPL.list_header_id = p_price_list_id
AND SPLL.list_header_id = SPL.list_header_id
AND SPLL.list_line_id = qpa.list_line_id
AND qpa.product_attribute_context = 'ITEM'
AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
AND qpa.product_attr_value = to_Char(p_inventory_item_id)
AND qpa.product_uom_code = p_transaction_uom
AND sysdate between nvl(SPLL.start_date_active, (sysdate-1)) AND
nvl(SPLL.end_date_active+0.99999, (sysdate+1))
AND qpa.qualification_ind = 4
AND qpa.excluder_flag = 'N'
AND qpa.pricing_phase_id=1
AND rownum = 1;
SELECT SPLL.operand, substr(SPL.currency_code, 1, 15), msi.primary_uom_code
INTO l_transfer_price, l_invoice_currency_code, l_primary_uom
FROM QP_LIST_HEADERS_B SPL, QP_LIST_LINES SPLL,
QP_PRICING_ATTRIBUTES QPA, MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.organization_id = l_organization_id
AND MSI.inventory_item_id = p_inventory_item_id
AND SPL.list_header_id = p_price_list_id
AND SPLL.list_header_id = SPL.list_header_id
AND QPA.list_header_id = SPL.list_header_id
AND SPLL.list_line_id = QPA.list_line_id
AND QPA.product_attribute_context = 'ITEM'
AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
AND QPA.product_attr_value = to_char(MSI.inventory_item_id)
AND QPA.product_uom_code = MSI.primary_uom_code
AND sysdate between nvl(SPLL.start_date_active, (sysdate-1))
AND nvl(SPLL.end_date_active + 0.99999, (sysdate+1))
AND qpa.qualification_ind = 4
AND qpa.excluder_flag = 'N'
AND qpa.pricing_phase_id=1
AND rownum = 1;
SELECT concatenated_segments, primary_uom_code
INTO l_item_description, l_primary_uom
FROM mtl_system_items_kfv
WHERE organization_id = l_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT name
into l_price_list_name
FROM QP_LIST_HEADERS
WHERE list_header_id = p_price_list_id;
select 1
INTO l_count
From mtl_material_transactions
WHERE transaction_id = p_transaction_id;
print_debug('about to delete the qp temp table', 'Get_Transfer_price_for_item');
select count(*)
into l_count
From qp_preq_lines_tmp;
Delete from qp_preq_lines_tmp_t;
select nvl(inv_currency_code, 1)
into l_inv_currency_code
From mtl_intercompany_parameters
where ship_organization_id = p_from_org_id
and sell_organization_id = p_to_org_id
and flow_type = l_flow_type;
SELECT name
INTO l_from_ou_name
FROM hr_operating_units
WHERE organization_id = p_from_org_id;
SELECT name
INTO l_to_ou_name
FROM hr_operating_units
WHERE organization_id = p_to_org_id;
SELECT description
into l_item_description
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = p_inventory_item_id;
select count(organization_id)
into l_exists
FROM HR_ORGANIZATION_INFORMATION HOI
WHERE HOI.ORG_INFORMATION3 = to_char(p_from_org_id)
AND HOI.ORGANIZATION_ID= l_organization_id
AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information';
select 1
into l_exists
FROM mtl_intercompany_parameters
where sell_organization_id = p_to_org_id
AND ship_organization_id = p_from_org_id
and flow_type = l_flow_type;
select name
INTO l_from_ou_name
FROM hr_operating_units
where organization_id = p_from_org_id;
SELECT NAME
INTO l_to_ou_name
From HR_OPERATING_UNITS
Where organization_id = p_to_org_id;
select nvl(base_item_id, 0)
into l_base_item
from mtl_system_items_b
where inventory_item_id = p_inventory_item_id
and organization_id = l_organization_id;
/* SELECT nvl(RSU.price_list_id, nvl(RC.price_list_id, -1)), RSU.location, RC.Customer_number, RC.Customer_name
INTO l_price_List_Id, l_location, l_customer_number, l_customer_name
FROM mtl_intercompany_parameters MIP
, ra_site_uses_all RSU
, ra_customers RC
WHERE MIP.sell_organization_id = p_to_org_id
AND MIP.ship_organization_id = p_from_org_id
AND MIP.flow_type = l_flow_type
AND RSU.site_use_id = MIP.customer_site_id
AND RSU.org_id = MIP.ship_organization_id
AND RC.customer_id = MIP.customer_id;
SELECT NVL(rsu.price_list_id, NVL(rc.price_list_id, -1))
, rsu.LOCATION
, rc.customer_number
, rc.customer_name
INTO l_price_List_Id, l_location, l_customer_number, l_customer_name
FROM mtl_intercompany_parameters mip
, hz_cust_site_uses_all rsu
, (SELECT cust_account_id customer_id
, party.party_name customer_name
, party.party_number customer_number
, price_list_id
FROM hz_parties party, hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id) rc
WHERE mip.sell_organization_id = p_to_org_id
AND mip.ship_organization_id = p_from_org_id
AND mip.flow_type = l_flow_type
AND rsu.site_use_id = mip.customer_site_id
AND rsu.org_id = mip.ship_organization_id
AND rc.customer_id = mip.customer_id;
SELECT name
into l_from_ou_name
FROM hr_operating_units
WHERE organization_id = p_from_org_id;
SELECT transaction_source_type_id into l_trx_src_type_id
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;