[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE mtl_consumption_transactions
SET net_qty = (net_qty - ABS(p_transaction_quantity)),
secondary_net_qty = (secondary_net_qty - ABS(p_secondary_transaction_qty)), /* INVCONV */
consumption_processed_flag = Decode(Nvl(net_qty,0) - abs(p_transaction_quantity), 0, -- Bug 7361382 Changes Start
null, consumption_processed_flag) -- Bug 7361382 Changes End
WHERE transaction_id = p_transaction_source_id;
INSERT INTO mtl_consumption_transactions
( transaction_id
, consumption_processed_flag
, net_qty
, tax_code_id
, tax_rate
, tax_recovery_rate
, recoverable_tax
, non_recoverable_tax
, rate
, rate_type
, charge_account_id
, variance_account_id
, parent_transaction_id
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
/* Bug 4969420 - Start */
, blanket_price
/* Bug 4969420 - End */
/* Bug 11900144. Addition of po_line_id */
, po_line_id
, secondary_net_qty /* INVCONV */
)
VALUES
( p_mtl_transaction_id
, 'N'
, l_net_qty
, NVL(p_tax_code_id,-1)
, p_tax_rate
, p_tax_recovery_rate
, p_recoverable_tax
, p_non_recoverable_tax
, p_rate
, p_rate_type
, p_charge_account_id
, p_variance_account_id
, l_parent_transaction
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.login_id
/* Bug 4969420 - Start */
, p_unit_price
/* Bug 4969420 - End */
/* Bug 11900144. Addition of po_line_id */
, p_po_line_id
, l_secondary_net_qty /* INVCONV */
);
MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row
( x_rowid => l_rowid
, x_transaction_id => p_rct_transaction_id
, x_organization_id => p_organization_id
, x_cost_element_id => 1 --material cost
, x_level_type => 1 --current level
, 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_inventory_item_id => p_inventory_item_id
, x_transaction_cost => p_po_price
, x_new_average_cost => NULL
, x_percentage_change => NULL
, x_value_change => NULL
);
SELECT
DECODE(NVL(fc.minimum_accountable_unit,0), 0,
round(p_amount*p_quantity,fc.precision)* x_conversion_rate/p_quantity,
round(p_amount* p_quantity/fc.minimum_accountable_unit) *
fc.minimum_accountable_unit*x_conversion_rate/p_quantity)
INTO
x_converted_amount
FROM
fnd_currencies fc
WHERE fc.currency_code = p_from_currency;
SELECT
SUM(NVL( rec_nrec_tax_amt,0))
FROM
zx_rec_nrec_dist_gt
WHERE application_id = 201
AND entity_code = l_entity_code
AND trx_id = p_header_id
AND event_class_code = l_event_class_code
AND NVL(recoverable_flag,'N') = 'N' ;
SELECT
SUM(NVL( rec_nrec_tax_amt,0))
FROM
zx_rec_nrec_dist_gt
WHERE application_id = 201
AND entity_code = l_entity_code
AND trx_id = p_header_id
AND event_class_code = l_event_class_code
AND NVL(recoverable_flag,'N') = 'Y' ;
SELECT
rec_nrec_rate
, tax_rate
FROM
zx_rec_nrec_dist_gt
WHERE application_id = 201
AND entity_code = l_entity_code
AND trx_id = p_header_id
AND event_class_code = l_event_class_code
AND NVL(recoverable_flag,'N') = 'N' ;
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name = 'PO';
SELECT vendor_id
, vendor_site_id
INTO l_vendor_id
, l_vendor_site_id
FROM po_headers_all
WHERE po_header_id = p_header_id;
SELECT pov.party_id
, povs.party_site_id
INTO l_vendor_party_id
, l_vendor_site_party_id
FROM
po_vendors pov
, po_vendor_sites_all povs
WHERE pov.vendor_id = povs.vendor_id
AND povs.vendor_site_id = l_vendor_site_id
AND povs.vendor_id = l_vendor_id;
/* bug 5081702 Start - Insert OU of vendor site id */
SELECT hzps.location_id
INTO
l_ship_from_location_id
FROM
hz_party_sites hzps
WHERE
hzps.party_site_id = l_vendor_site_party_id;
SELECT set_of_books_id
INTO l_set_of_books_id
FROM hr_operating_units
WHERE organization_id = l_vendor_org_id ;
( '*** Start inserting into GT tables '
, 9
);
SELECT
default_rate_type
INTO
l_rate_type
FROM
po_system_parameters_all
WHERE NVL(org_id,-99) = NVL(p_org_id,-99);
/* delete from ZX_TRX_HEADERS_GT if records already exist to
to prevent duplicate records from being entered */
/*bug#7120486 delete statement is moved at end after calling ebtax API */
/* 5084307 - end */
/*5488006 - Start */
/* delete from ZX_TRANSACTION_LINES_GT and ZX_ITM_DISTRIBUTIONS_GT
before inserting records to prevent duplicate insertion of records -
this caused errors in tax calculation */
/*bug#7120486 delete statement is moved at end after calling ebtax API */
/*5488006 - End */
INSERT INTO ZX_TRX_HEADERS_GT
( internal_organization_id
, application_id
, entity_code
, event_class_code
, tax_event_type_code
, event_type_code
, trx_id
, trx_date
, trx_currency_code
, currency_conversion_date
, currency_conversion_rate
, currency_conversion_type
, PRECISION
, legal_entity_id
, quote_flag
, ledger_id
, rounding_ship_from_party_id
/*
, rounding_ship_to_party_id
, ship_third_pty_acct_id
, ship_third_pty_acct_site_id
, bill_third_pty_acct_id
, bill_third_pty_acct_site_id
*/
, provnl_tax_determination_date
, document_sub_type
, trx_number
, icx_session_id
)
SELECT
/* bug 5081702 Start*/
--poh.org_id
NVL(l_vendor_org_id, poh.org_id)
, l_application_id
, l_entity_code
, l_event_class_code
, 'PURCHASE TRANSACTION'
, l_event_type_code
, poh.po_header_id
, p_transaction_date
, poh.currency_code
--, poh.rate_date
, NVL(p_transaction_date, poh.rate_date)
, poh.rate
--, poh.rate_type
, NVL(l_rate_type, poh.rate_type)
, fc.PRECISION
--, ood.legal_entity
, l_legal_entity_id
, 'Y'
--, ood.set_of_books_id
, l_set_of_books_id
, l_vendor_party_id
/*
, poh.vendor_id
, poh.vendor_id
, poh.vendor_site_id
, poh.vendor_id
, poh.vendor_site_id
*/
, p_transaction_date
, poh.type_lookup_code
, poh.segment1
, FND_GLOBAL.session_id
FROM
po_headers_all poh
, fnd_currencies fc
WHERE poh.currency_code = fc.currency_code
AND poh.po_header_id = p_header_id;
INSERT INTO ZX_TRANSACTION_LINES_GT
( application_id
, entity_code
, event_class_code
, trx_level_type
, line_level_action
, line_amt
, trx_line_gl_date
, line_amt_includes_tax_flag
, trx_line_quantity
, uom_code
, ship_to_party_id -- 14532062
, ship_from_party_id
, ship_from_party_site_id
, unit_price
, trx_line_type
, trx_line_date
, product_id
, ship_to_location_id
, trx_id
, trx_line_id
, line_class
, product_org_id
-- , bill_to_party_site_id
, BILL_TO_LOCATION_ID
, ship_from_location_id
, BILL_FROM_LOCATION_ID -- bug 14570035
)
SELECT
l_application_id
, l_entity_code
, l_event_class_code
, 'SHIPMENT'
, 'CREATE'
, p_po_price
, p_transaction_date
, 'N'
, 1
, p_uom_code
, p_ship_to_organization_id -- 14532062
, l_vendor_party_id
, l_vendor_site_party_id
, p_po_price
, 'ITEM'
, p_transaction_date
, p_item_id
, l_ship_to_location_id
, po_header_id
, p_transaction_id
, 'INVOICE'
, p_ship_to_organization_id
-- , l_vendor_site_id
, bill_to_location_id
, l_ship_from_location_id
,(SELECT pvs.location_id from po_vendor_sites_all pvs WHERE pvs.vendor_site_id=ph.vendor_site_id) -- bug 14570035
FROM po_headers_all ph
WHERE ph.po_header_id = p_header_id;
INSERT INTO ZX_ITM_DISTRIBUTIONS_GT
( application_id
, entity_code
, event_class_code
-- , event_type_code
, trx_id
, trx_level_type
, dist_level_action
, trx_line_dist_date
, trx_line_dist_amt
, trx_line_dist_qty
, trx_line_quantity
, trx_line_id
, trx_line_dist_id
)
VALUES
( l_application_id
, l_entity_code
, l_event_class_code
-- , l_event_type_code
, p_header_id
, 'SHIPMENT'
, 'CREATE'
, p_transaction_date
, p_po_price
, 1
, 1
, p_transaction_id
, p_transaction_id
);
( 'Inserted into Tax Global temp tables','INV_THIRD_PARTY_STOCK_PVT'
, 9
);
DELETE FROM ZX_TRX_HEADERS_GT
WHERE APPLICATION_ID = l_application_id
AND ENTITY_CODE = l_entity_code
AND EVENT_CLASS_CODE = l_event_class_code
AND TRX_ID = p_header_id;
DELETE FROM ZX_TRANSACTION_LINES_GT
WHERE APPLICATION_ID = l_application_id
AND ENTITY_CODE = l_entity_code
AND EVENT_CLASS_CODE = l_event_class_code
AND TRX_ID = p_header_id;
DELETE FROM ZX_ITM_DISTRIBUTIONS_GT
WHERE APPLICATION_ID = l_application_id
AND ENTITY_CODE = l_entity_code
AND EVENT_CLASS_CODE = l_event_class_code
AND TRX_ID = p_header_id;
DELETE FROM zx_rec_nrec_dist_gt
WHERE application_id = 201
AND entity_code = l_entity_code
AND trx_id = p_header_id
AND event_class_code = l_event_class_code ;
SELECT /*+ leading(mct) use_nl(mct mmt) index(mmt MTL_MATERIAL_TRANSACTIONS_U1) */
mmt.inventory_item_id
, mmt.organization_id
/* Bug 5395579 - Start */
/* mct.blanket_price is used for getting the consumed amt.
Using mmt.transaction_cost will multiply the consumed amt by the function
UOM if the pur UOM and function UOM are different*/
--, mmt.transaction_cost
, mct.blanket_price
, SUM(mct.net_qty)
FROM
mtl_material_transactions mmt
, mtl_consumption_transactions mct
WHERE mmt.transaction_id = mct.transaction_id
AND mct.consumption_processed_flag IN ('N','E')
AND mmt.transaction_source_type_id = 1
AND mmt.transaction_action_id = 6
AND mmt.transaction_source_id = p_header_id
GROUP BY mmt.inventory_item_id,mmt.organization_id,mct.blanket_price;--mmt.transaction_cost;
SELECT
pol.unit_meas_lookup_code
FROM
po_lines_all pol
WHERE pol.po_header_id = p_header_id
AND pol.item_id = l_item_id
AND ROWNUM = 1;
SELECT
NVL(blanket_total_amount,0)
INTO
l_total_amt
FROM
po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT
transaction_cost
, transaction_source_id
INTO
l_po_price
, l_header_id
FROM
MTL_MATERIAL_TRANSACTIONS
WHERE transaction_id = p_transaction_source_id;
SELECT
price_break_lookup_code
, po_line_id
, po_header_id
, tax_code_id
, unit_meas_lookup_code
/* Bug 5076263 - category_id to be passed to get_break_price API - Start*/
, category_id
, line_type_id
, vendor_product_num
/* Bug 5076263 - category_id to be passed to get_break_price API - End*/
INTO
l_price_break_code
, l_document_line_id
, l_header_id
, l_tax_code_id
, l_purchasing_uom
/* Bug 5076263 - category_id, line_type_id to be passed to get_break_price API - Start*/
, l_category_id
, l_line_type_id
, l_supplier_item_num
/* Bug 5076263 - category_id, line_type_id to be passed to get_break_price API - End*/
FROM
po_lines_all
WHERE po_line_id = l_document_line_id;
SELECT
currency_code
, vendor_id
INTO
l_currency_code
, l_vendor_id
FROM
po_headers_all
WHERE po_header_id = l_header_id;
SELECT
fsp.set_of_books_id
, glb.currency_code
, glc.PRECISION
INTO
l_sob_id
, l_func_currency
, l_precision
FROM
financials_system_params_all fsp
, gl_sets_of_books glb
, gl_currencies glc
WHERE fsp.set_of_books_id = glb.set_of_books_id
AND glb.currency_code = glc.currency_code
AND NVL(fsp.org_id,-99) = NVL(l_org_id,-99);
SELECT
currency_code
, rate_type
, rate
, rate_date
INTO
l_currency_code
, l_conv_type
, l_user_rate
, l_rate_date
FROM
po_headers_all
WHERE po_header_id = l_header_id;
SELECT
default_rate_type
INTO
l_conv_type
FROM
po_system_parameters_all
WHERE NVL(org_id,-99) = NVL(l_org_id,-99);
SELECT
distribution_account_id
INTO
l_accrual_account_id
FROM
mtl_material_transactions
WHERE transaction_id = p_transaction_source_id;
SELECT
gl.chart_of_accounts_id
INTO
l_coa_id
FROM
hr_organization_information hoi
, hr_all_organization_units hou
, gl_sets_of_books gl
WHERE hoi.organization_id = hou.organization_id
AND hoi.org_information1 = TO_CHAR(gl.set_of_books_id)
AND hoi.org_information_context='Accounting Information'
AND hoi.organization_id = p_organization_id;
SELECT
pov.vendor_name
, povs.vendor_site_code
INTO
l_vendor_name
, l_vendor_site
FROM
po_vendors pov
, po_vendor_sites_all povs
WHERE pov.vendor_id = povs.vendor_id
AND pov.vendor_id = p_vendor_id
AND povs.vendor_site_id = l_vendor_site_id;
SELECT
vendor_id
INTO
l_vendor_id
FROM
po_vendor_sites_all
WHERE vendor_site_id = l_vendor_site_id;
INV_LOG_UTIL.trace('Inserted to MCT>>','INV_THIRD_PARTY_STOCK_PVT',9);