[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_po_distrubution_id
IS
l_transaction_id NUMBER;
SELECT
mct.transaction_id
, mct.po_distribution_id
, mct.consumption_release_id
, mct.consumption_po_header_id
/* Bug 5373370 - Start */
, mct.interface_distribution_ref
/* Bug 5373370 - End */
FROM
MTL_CONSUMPTION_TRANSACTIONS mct
WHERE
( mct.blanket_price IS NULL OR mct.po_distribution_id IS NULL )
AND mct.net_qty > 0
AND mct.request_id = g_request_id
AND mct.consumption_processed_flag = 'Y';
( '>> Update po_distrubution_id','INV_CONSUMPTION_ADVICE_PROC'
, 9
);
SELECT pod.po_distribution_id
INTO l_po_distribution_id
FROM
MTL_CONSUMPTION_TRANSACTIONS mct,
--MTL_MATERIAL_TRANSACTIONS mmt,
po_line_locations_all poll,
po_distributions_all pod,
po_lines_all pol
WHERE mct.transaction_id = l_transaction_id
AND mct.consumption_processed_flag = 'Y'
AND mct.inventory_item_id = pol.item_id
AND mct.transaction_source_id = pol.po_header_id
AND poll.po_line_id = pol.po_line_id
AND poll.po_header_id = pol.po_header_id
AND poll.po_release_id = mct.consumption_release_id
AND poll.shipment_type = 'BLANKET'
AND poll.line_location_id = pod.line_location_id
AND pod.po_release_id = mct.consumption_release_id
AND pod.po_header_id = poll.po_header_id
/* Bug 5604129 - Start */
--AND NVL(mct.tax_code_id, -1) = NVL(poll.TAX_CODE_ID, -1)
/* Bug 5373370 - Start */
/*AND ( NVL(mct.RECOVERABLE_TAX,0) = NVL(pod.RECOVERABLE_TAX,0)
OR ( NVL(mct.RECOVERABLE_TAX,0) =
NVL(pod.RECOVERABLE_TAX,0) / pod.quantity_ordered ) )
AND ( NVL(mct.NON_RECOVERABLE_TAX,0) = NVL(pod.NONRECOVERABLE_TAX,0)
OR ( NVL(mct.NON_RECOVERABLE_TAX,0) =
NVL(pod.NONRECOVERABLE_TAX,0) / pod.quantity_ordered ) )*/
AND mct.interface_distribution_ref = pod.interface_distribution_ref
/* Buf 5373370 - End*/
--AND NVL(mct.TAX_RECOVERY_RATE,0) = NVL(pod.RECOVERY_RATE,0)
AND mct.CHARGE_ACCOUNT_ID = pod.CODE_COMBINATION_ID
AND mct.VARIANCE_ACCOUNT_ID = pod.VARIANCE_ACCOUNT_ID
AND mct.accrual_account_id = pod.ACCRUAL_ACCOUNT_ID
AND poll.price_override = mct.blanket_price
AND ROWNUM = 1;
SELECT pod.po_distribution_id
INTO l_po_distribution_id
FROM
MTL_CONSUMPTION_TRANSACTIONS mct,
--MTL_MATERIAL_TRANSACTIONS mmt,
po_line_locations_all poll,
po_distributions_all pod,
po_lines_all pol
WHERE mct.transaction_id = l_transaction_id
AND mct.consumption_processed_flag = 'Y'
AND mct.inventory_item_id = pol.item_id
AND pol.from_header_id = mct.transaction_source_id
AND pol.po_header_id = mct.consumption_po_header_id
AND poll.po_line_id = pol.po_line_id
AND poll.po_header_id = pol.po_header_id
AND poll.shipment_type = 'STANDARD'
AND poll.line_location_id = pod.line_location_id
AND pod.po_header_id = mct.consumption_po_header_id
/* Bug 5604129 - Start */
--AND NVL(mct.tax_code_id, -1) = NVL(poll.TAX_CODE_ID, -1)
/* Bug 5373370 - Start */
/*AND ( NVL(mct.RECOVERABLE_TAX,0) = NVL(pod.RECOVERABLE_TAX,0)
OR ( NVL(mct.RECOVERABLE_TAX,0) =
NVL(pod.RECOVERABLE_TAX,0) / pod.quantity_ordered ) )
AND ( NVL(mct.NON_RECOVERABLE_TAX,0) = NVL(pod.NONRECOVERABLE_TAX,0)
OR ( NVL(mct.NON_RECOVERABLE_TAX,0) =
NVL(pod.NONRECOVERABLE_TAX,0) / pod.quantity_ordered ) )*/
AND mct.interface_distribution_ref = pod.interface_distribution_ref
/* Bug 5373370 - End*/
--AND NVL(mct.TAX_RECOVERY_RATE,0) = NVL(pod.RECOVERY_RATE,0)
AND mct.CHARGE_ACCOUNT_ID = pod.CODE_COMBINATION_ID
AND mct.VARIANCE_ACCOUNT_ID = pod.VARIANCE_ACCOUNT_ID
AND mct.accrual_ACCOUNT_ID = pod.ACCRUAL_ACCOUNT_ID
--AND poll.price_override = mct.blanket_price
AND ROWNUM = 1;
UPDATE MTL_CONSUMPTION_TRANSACTIONS
SET po_distribution_id = l_po_distribution_id
WHERE transaction_id = l_transaction_id ;
( '<< Update po_distrubution_id','INV_CONSUMPTION_ADVICE_PROC'
, 9
);
END update_po_distrubution_id ;
PROCEDURE Update_Consumption
( p_consumption_po_header_id IN NUMBER
, p_consumption_release_id IN NUMBER
, p_error_code IN VARCHAR2
, p_batch_id IN NUMBER
, p_transaction_source_id IN NUMBER
, p_consumption_processed_flag IN VARCHAR2
, p_accrual_account_id IN NUMBER
, p_variance_account_id IN NUMBER
, p_charge_account_id IN NUMBER
, p_transaction_date IN DATE
, p_global_rate_type IN VARCHAR2
, p_global_rate IN NUMBER
, p_vendor_site_id IN NUMBER
)
IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
( '>> Update Consumption','INV_CONSUMPTION_ADVICE_PROC'
, 9
);
SELECT segment1
INTO l_blanket_po_number
FROM po_headers_all
WHERE po_header_id = p_consumption_po_header_id;
UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
SET mct.consumption_po_header_id = p_consumption_po_header_id
, mct.consumption_release_id = p_consumption_release_id
, mct.consumption_processed_flag = l_consumption_processed_flag
, mct.error_code = p_error_code
, mct.error_explanation = l_error_explanation -- Bug 12679286
WHERE mct.batch_id = p_batch_id
AND mct.charge_account_id = p_charge_account_id
AND mct.variance_account_id = p_variance_account_id
AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
AND mct.consumption_processed_flag IN ('N', 'E')
AND mct.transaction_source_id = p_transaction_source_id
AND mct.accrual_account_id = p_accrual_account_id
AND mct.owning_organization_id = p_vendor_site_id
AND ((NVL(mct.global_agreement_flag,'N') = 'Y'
AND TRUNC(mct.transaction_date) = TRUNC(p_transaction_date))
OR (NVL(mct.global_agreement_flag,'N') = 'N'));
UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
SET mct.consumption_po_header_id = p_consumption_po_header_id
, mct.consumption_release_id = p_consumption_release_id
, mct.consumption_processed_flag = l_consumption_processed_flag
, mct.error_code = p_error_code
, mct.error_explanation = l_error_explanation
WHERE mct.batch_id = p_batch_id
AND mct.charge_account_id = p_charge_account_id
AND mct.variance_account_id = p_variance_account_id
AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
AND mct.consumption_processed_flag IN ('N', 'E')
AND mct.transaction_source_id = p_transaction_source_id
AND mct.accrual_account_id = p_accrual_account_id
AND mct.owning_organization_id = p_vendor_site_id
AND TRUNC(mct.transaction_date) = TRUNC(p_transaction_date);
(SELECT transaction_id
FROM MTL_MATERIAL_TRANSACTIONS
WHERE transaction_source_id = p_transaction_source_id
AND distribution_account_id = p_accrual_account_id
AND owning_organization_id = p_vendor_site_id
AND TRUNC(transaction_date) = TRUNC(p_transaction_date)
AND inventory_item_id IN
(SELECT inventory_item_id
FROM MTL_CONSUMPTION_TXN_TEMP
WHERE transaction_source_id = p_transaction_source_id));
UPDATE MTL_CONSUMPTION_TRANSACTIONS
SET consumption_po_header_id = p_consumption_po_header_id
, consumption_release_id = p_consumption_release_id
, consumption_processed_flag = l_consumption_processed_flag
, error_code = p_error_code
, error_explanation = l_error_explanation
WHERE parent_transaction_id IN
(SELECT mct.transaction_id
FROM MTL_CONSUMPTION_TRANSACTIONS mct
WHERE mct.batch_id = p_batch_id
AND mct.charge_account_id = p_charge_account_id
AND mct.variance_account_id = p_variance_account_id
AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
AND mct.transaction_source_id = p_transaction_source_id
AND mct.accrual_account_id = p_accrual_account_id
AND mct.owning_organization_id = p_vendor_site_id
)
AND consumption_processed_flag IN ('N', 'E');
( '<< Update Consumption','INV_CONSUMPTION_ADVICE_PROC'
, 9
);
END Update_Consumption;
INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
( mctt.transaction_source_id
, mctt.inventory_item_id
, mctt.organization_id
, mctt.owning_organization_id
/* Bug 4969421 Starts here*/
/* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
, mctt.transaction_cost
/* Bug 4969421 Ends here*/
, mctt.batch_id
, mctt.tax_code_id
, mctt.tax_rate
, mctt.recoverable_tax
, mctt.non_recoverable_tax
, mctt.tax_recovery_rate
, mctt.accrual_account_id
, mctt.charge_account_id
, mctt.variance_account_id
, mctt.rate_type
, mctt.rate
, mctt.transaction_date
, mctt.global_agreement_flag
, mctt.net_qty
/* Bug 11900144. Addition of po_line_id */
, mctt.po_line_id
)
SELECT
mct.transaction_source_id
, mct.inventory_item_id
, mct.organization_id
, mct.owning_organization_id
, mct.blanket_price
/* Bug 4969421 Ends here*/
, p_batch_id
, NVL(mct.tax_code_id,-1)
, NVL(mct.tax_rate,-1)
, NVL(mct.recoverable_tax,0)
, NVL(mct.non_recoverable_tax,0)
, NVL(mct.tax_recovery_rate,0)
, mct.accrual_account_id
, mct.charge_account_id
, mct.variance_account_id
, NVL(mct.rate_type,'##')
, NVL(mct.rate,-1)
, TRUNC(mct.transaction_date)
, mct.global_agreement_flag
, SUM(mct.net_qty)
/* Bug 11900144. Addition of po_line_id */
, mct.po_line_id
FROM
MTL_CONSUMPTION_TRANSACTIONS mct
, po_vendor_sites_all pvsa
WHERE mct.owning_organization_id = pvsa.vendor_site_id
AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
AND mct.owning_organization_id =
NVL(p_vendor_site_id,mct.owning_organization_id)
AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
AND mct.consumption_processed_flag IN ('N', 'E')
AND mct.batch_id = p_batch_id
GROUP BY
mct.transaction_source_id
, mct.inventory_item_id
, mct.organization_id
, mct.owning_organization_id
, mct.blanket_price
/* Bug 11900144. Addition of po_line_id */
, mct.po_line_id
, mct.tax_code_id
, mct.tax_rate
, mct.recoverable_tax
, mct.non_recoverable_tax
, mct.tax_recovery_rate
, mct.accrual_account_id
, mct.charge_account_id
, mct.variance_account_id
, mct.rate_type
, mct.rate
, mct.global_agreement_flag
, TRUNC(mct.transaction_date)
;
select count(*) into l_count
from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
( mctt.transaction_source_id
, mctt.inventory_item_id
, mctt.organization_id
, mctt.owning_organization_id
/* Bug 4969421 Starts here*/
/* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
, mctt.transaction_cost
/* Bug 4969421 Ends here*/
, mctt.batch_id
, mctt.tax_code_id
, mctt.tax_rate
, mctt.recoverable_tax
, mctt.non_recoverable_tax
, mctt.tax_recovery_rate
, mctt.accrual_account_id
, mctt.charge_account_id
, mctt.variance_account_id
, mctt.rate_type
, mctt.rate
, mctt.transaction_date
, mctt.global_agreement_flag
, mctt.net_qty
/* Bug 11900144. Addition of po_line_id */
, mctt.po_line_id
)
SELECT
mct.transaction_source_id
, mct.inventory_item_id
, mct.organization_id
, mct.owning_organization_id
, mct.blanket_price
, p_batch_id
, NVL(mct.tax_code_id,-1)
, NVL(mct.tax_rate,-1)
, NVL(mct.recoverable_tax,0)
, NVL(mct.non_recoverable_tax,0)
, NVL(mct.tax_recovery_rate,0)
, mct.accrual_account_id
, mct.charge_account_id
, mct.variance_account_id
, NVL(mct.rate_type,'##')
, NVL(mct.rate,-1)
/* Start Bug 6388514 Splitting the deode function into two different INSERT into MCT */
--, DECODE(mct.global_agreement_flag, 'Y', TRUNC(mct.transaction_date),'N', TRUNC(MAX(mct.transaction_date)))
, TRUNC(mct.transaction_date)
/* End Bug 6388514 */
, mct.global_agreement_flag
, SUM(mct.net_qty)
/* Bug 11900144. Addition of po_line_id */
, mct.po_line_id
FROM
MTL_CONSUMPTION_TRANSACTIONS mct
, po_vendor_sites_all pvsa
WHERE mct.owning_organization_id = pvsa.vendor_site_id
AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
AND mct.owning_organization_id =
NVL(p_vendor_site_id,mct.owning_organization_id)
AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
AND mct.consumption_processed_flag IN ('N','E')
AND mct.batch_id = p_batch_id
/* Start Bug 6388514 */
AND mct.global_agreement_flag = 'Y'
/* End Bug 6388514 */
GROUP BY
mct.transaction_source_id
, mct.inventory_item_id
, mct.organization_id
, mct.owning_organization_id
, mct.blanket_price
/* Bug 11900144. Addition of po_line_id */
, mct.po_line_id
, mct.tax_code_id
, mct.tax_rate
, mct.recoverable_tax
, mct.non_recoverable_tax
, mct.tax_recovery_rate
, mct.accrual_account_id
, mct.charge_account_id
, mct.variance_account_id
, mct.rate_type
, mct.rate
, mct.global_agreement_flag
, TRUNC(mct.transaction_date)
;
/* Start Bug 6388514 INSERT into MCT for global_agreement_flag = 'N' */
INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
( mctt.transaction_source_id
, mctt.inventory_item_id
, mctt.organization_id
, mctt.owning_organization_id
, mctt.transaction_cost
, mctt.batch_id
, mctt.tax_code_id
, mctt.tax_rate
, mctt.recoverable_tax
, mctt.non_recoverable_tax
, mctt.tax_recovery_rate
, mctt.accrual_account_id
, mctt.charge_account_id
, mctt.variance_account_id
, mctt.rate_type
, mctt.rate
, mctt.global_agreement_flag
, mctt.net_qty
/* Bug 11900144. Addition of po_line_id */
, mctt.po_line_id
)
SELECT
mct.transaction_source_id
, mct.inventory_item_id
, mct.organization_id
, mct.owning_organization_id
, mct.blanket_price
, p_batch_id
, NVL(mct.tax_code_id,-1)
, NVL(mct.tax_rate,-1)
, NVL(mct.recoverable_tax,0)
, NVL(mct.non_recoverable_tax,0)
, NVL(mct.tax_recovery_rate,0)
, mct.accrual_account_id
, mct.charge_account_id
, mct.variance_account_id
, NVL(mct.rate_type,'##')
, NVL(mct.rate,-1)
, mct.global_agreement_flag
, SUM(mct.net_qty)
/* Bug 11900144. Addition of po_line_id */
, mct.po_line_id
FROM
MTL_CONSUMPTION_TRANSACTIONS mct
, po_vendor_sites_all pvsa
WHERE mct.owning_organization_id = pvsa.vendor_site_id
AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
AND mct.owning_organization_id =
NVL(p_vendor_site_id,mct.owning_organization_id)
AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
AND mct.consumption_processed_flag IN ('N','E')
AND mct.batch_id = p_batch_id
AND mct.global_agreement_flag = 'N'
GROUP BY
mct.transaction_source_id
, mct.inventory_item_id
, mct.organization_id
, mct.owning_organization_id
, mct.blanket_price
/* Bug 11900144. Addition of po_line_id */
, mct.po_line_id
, mct.tax_code_id
, mct.tax_rate
, mct.recoverable_tax
, mct.non_recoverable_tax
, mct.tax_recovery_rate
, mct.accrual_account_id
, mct.charge_account_id
, mct.variance_account_id
, mct.rate_type
, mct.rate
, mct.global_agreement_flag
;
/* Start bug 6388514 Update transaction_date in MCTT */
UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
SET mctt.transaction_date =
(SELECT
TRUNC(MAX(mct.transaction_date))
FROM
--MTL_MATERIAL_TRANSACTIONS mmt
MTL_CONSUMPTION_TRANSACTIONS mct
WHERE --mct.transaction_id = mmt.transaction_id AND
mct.transaction_source_id = mctt.transaction_source_id
AND mct.inventory_item_id = mctt.inventory_item_id
AND mct.organization_id = mctt.organization_id
/* Bug 4969420 Starts here*/
/* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
--AND mmt.transaction_cost = mctt.transaction_cost
AND mct.blanket_price = mctt.transaction_cost
/* Bug 4969420 Ends here*/
/* Bug 11900144. Addition of po_line_id clause */
AND mct.po_line_id=mctt.po_line_id
AND NVL(mct.tax_code_id,-1) = NVL(mctt.tax_code_id,-1)
AND NVL(mct.recoverable_tax,0) = NVL(mctt.recoverable_tax,0)
AND NVL(mct.non_recoverable_tax,0) = NVL(mctt.non_recoverable_tax,0)
AND NVL(mct.tax_recovery_rate,0) = NVL(mctt.tax_recovery_rate,0)
AND NVL(mct.rate,-1) = NVL(mctt.rate,-1)
AND mct.accrual_account_id = mctt.accrual_account_id
AND mct.charge_account_id = mctt.charge_account_id
AND mct.variance_account_id = mctt.variance_account_id
AND mct.global_agreement_flag = 'N'
AND mct.consumption_processed_flag IN ('N', 'E'))
WHERE mctt.transaction_date IS NULL;
select count(*) into l_count
from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
SELECT DISTINCT
transaction_source_id
, inventory_item_id
, owning_organization_id
FROM
mtl_consumption_txn_temp
WHERE batch_id=p_batch_id;
SELECT
COUNT(1)
INTO
l_count
FROM
po_headers_all poh
, po_lines_all pol
WHERE poh.po_header_id = pol.po_header_id
AND (TRUNC(NVL(poh.start_date,SYSDATE -1)) <= TRUNC(SYSDATE))
AND (TRUNC(NVL(poh.end_date+NVL(FND_PROFILE.VALUE('PO_REL_CREATE_TOLERANCE'),0),SYSDATE +1)) >= TRUNC(SYSDATE)) -- Bug 8397146
AND (TRUNC(NVL(pol.expiration_date,SYSDATE )) >= TRUNC(SYSDATE))
AND poh.approved_flag = 'Y'
AND NVL(poh.frozen_flag, 'N') = 'N'
AND (NVL(poh.cancel_flag,'N') = 'N'
OR NVL(pol.cancel_flag,'N') = 'N')
AND NVL(pol.closed_code,'OPEN') = 'OPEN'
AND poh.po_header_id = l_header_id
AND pol.item_id = l_item_id;
UPDATE mtl_consumption_txn_temp
SET valid_flag = l_valid_flag
,org_id = l_org_id
WHERE transaction_source_id = l_header_id
AND inventory_item_id = l_item_id
AND owning_organization_id = l_owning_organization_id
AND valid_flag IS NULL;
UPDATE mtl_consumption_txn_temp
SET valid_flag = 'N'
,org_id = l_org_id
WHERE transaction_source_id = l_header_id
AND inventory_item_id = l_item_id
AND owning_organization_id = l_owning_organization_id
AND valid_flag IS NULL;
/* delete all records in MCT_TEMP that have valid flag as 'N' */
/* Also update MCT records with error status and message */
/*Bug 5092489. Query is modified to eliminate use of MMT. */
UPDATE mtl_consumption_transactions SET
consumption_processed_flag = 'E'
, error_code = l_error_msg
WHERE transaction_source_id = l_header_id
AND inventory_item_id = l_item_id
AND owning_organization_id = l_owning_organization_id
AND consumption_processed_flag IN ('N','E')
AND batch_id = p_batch_id;
(SELECT transaction_id FROM mtl_material_transactions
WHERE transaction_source_id = l_header_id
AND inventory_item_id = l_item_id
AND owning_organization_id = l_owning_organization_id)
AND consumption_processed_flag IN ('N','E')
AND batch_id = p_batch_id;
DELETE FROM mtl_consumption_txn_temp
WHERE valid_flag = 'N'
AND org_id = l_org_id
AND transaction_source_id = l_header_id
AND inventory_item_id = l_item_id;
SELECT DISTINCT
transaction_source_id
, valid_flag
, org_id
, accrual_account_id
, charge_account_id
, variance_account_id
, rate_type
/* bug 5210850 - Start */
--, TRUNC(transaction_date)
, DECODE(global_agreement_flag,'Y',TRUNC(transaction_date),
DECODE(l_profile_option,'N', NULL,TRUNC(transaction_date)) )
/* bug 5210850 - End */
, rate
, owning_organization_id
, currency_code
FROM
mtl_consumption_txn_temp
WHERE batch_id = p_batch_id;
SELECT
inventory_item_id
, organization_id
, net_qty
, secondary_net_qty /* INVCONV */
, transaction_cost -- This is the blanket_price from MCT Bug 4969421
, TRUNC(transaction_date)
, NVL(tax_code_id,-1)
, rate
, owning_organization_id
, recoverable_tax
, non_recoverable_tax
, tax_recovery_rate
, asl_id
/* Bug 11900144. Addition of po_line_id */
, po_line_id
FROM
mtl_consumption_txn_temp
WHERE transaction_source_id = l_blanket_id
AND valid_flag = l_valid_flag
AND TRUNC(transaction_date) = NVL(l_transaction_date, TRUNC(transaction_date))
AND rate_type = NVL(l_global_rate_type,'##')
AND rate = NVL(l_global_rate,-1)
AND owning_organization_id = l_vendor_site_id
AND accrual_account_id = l_accrual_account_id
AND charge_account_id = l_charge_account_id
AND variance_account_id = l_variance_account_id;
SELECT m.unit_of_measure
FROM mtl_system_items i,
mtl_units_of_measure m
WHERE i.inventory_item_id = l_item_id
AND i.organization_id = l_organization_id
AND i.secondary_uom_code = m.uom_code;
INV_CONSUMPTION_ADVICE_PROC.update_consumption
(p_consumption_po_header_id => NULL
,p_consumption_release_id => NULL
,p_error_code => l_error_msg
,p_batch_id => p_batch_id
,p_transaction_source_id => l_blanket_id
,p_consumption_processed_flag => 'E'
,p_accrual_account_id => l_accrual_account_id
,p_charge_account_id => l_charge_account_id
,p_variance_account_id => l_variance_account_id
,p_transaction_date => l_global_rate_date
,p_global_rate_type => l_global_rate_type
,p_global_rate => l_global_rate
,p_vendor_site_id => l_vendor_site_id);
SELECT
-- NVL(povs.ship_to_location_id,pov.ship_to_location_id)
-- , NVL(povs.bill_to_location_id,pov.bill_to_location_id)
/* fix for bug 5230913 - Start */
povs.ship_to_location_id
, povs.bill_to_location_id
, pov.vendor_id --Bug 4723164
/* fix for bug 5230913 - End */
INTO
l_vendor_ship_to_location
, l_vendor_bill_to_location
, l_vendor_id --Bug 4723164
FROM
po_vendor_sites_all povs
, po_vendors pov
WHERE povs.vendor_id = pov.vendor_id
AND povs.vendor_site_id = l_vendor_site_id;
/* SELECT
glc.PRECISION
, glc.currency_code
, fsp.ship_to_location_id
, fsp.bill_to_location_id
INTO
l_precision
, l_fin_curr_code
, l_ship_to_location
, l_bill_to_location
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_current_org_id,-99);
SELECT
fsp.bill_to_location_id
INTO
l_bill_to_location
FROM
financials_system_params_all fsp
WHERE NVL(fsp.org_id,-99) = NVL(l_current_org_id,-99);
SELECT
user_defined_po_num_code
INTO
l_po_num_code
FROM
po_system_parameters_all
WHERE NVL(org_id,-99) = NVL(l_current_org_id,-99);
SELECT
segment1
INTO
l_document_number
FROM
po_headers_all
WHERE po_header_id = l_blanket_id;
SELECT
po_headers_interface_s.NEXTVAL
INTO
l_interface_header_id
FROM
DUAL;
SELECT
pay_on_code
INTO
l_pay_on_code
FROM
po_vendor_sites_all
WHERE vendor_site_id = l_vendor_site_id;
INSERT INTO po_headers_interface
( interface_header_id
, interface_source_code
, batch_id
, document_type_code
, document_subtype
, document_num
, vendor_id
, vendor_site_id
, agent_id
, currency_code
, rate_type_code
, rate_date
, rate
, ship_to_location_id
, bill_to_location_id
, terms_id
, fob
, pay_on_code
, freight_terms
, min_release_amount
, creation_date
, created_by
, group_code
, action
, org_id
)
SELECT
l_interface_header_id
, 'CONSUMPTION_ADVICE'
, p_batch_id
, l_document_type_code
, DECODE(l_valid_flag,'Y','RELEASE','STANDARD')
, DECODE(l_valid_flag,'Y',segment1,NULL)
, DECODE(l_valid_flag,'G',l_vendor_id,vendor_id) --Bug 4723164
, DECODE(l_valid_flag,'G',l_vendor_site_id,vendor_site_id) --Bug 4723164
, agent_id
, currency_code
, DECODE(l_valid_flag,'G',l_global_rate_type,rate_type)
, DECODE(l_valid_flag,'G',l_global_rate_date,rate_date)
, DECODE(l_valid_flag,'G',l_global_rate,rate)
--, DECODE(l_valid_flag,'G',NVL(l_ship_to_location,l_location_id),ship_to_location_id)
-- Bug Fix for 3959073
-- Ship To Location to be set at the Header of the Consumption Advice should
-- be taken from the BPA, regardless if the BPA is local or global
, ship_to_location_id
, DECODE(l_valid_flag,'G',NVL(l_bill_to_location,l_location_id),bill_to_location_id)
, terms_id
, fob_lookup_code
, l_pay_on_flag
, freight_terms_lookup_code
, min_release_amount
, SYSDATE
, l_user
, 'DEFAULT'
, 'NEW'
, l_current_org_id
FROM
po_headers_all
WHERE po_header_id = l_blanket_id;
SELECT
unit_meas_lookup_code
--, po_line_id
INTO
l_purchasing_uom
--, l_po_line_id
FROM po_lines_all
WHERE po_header_id = l_blanket_id
AND item_id = l_item_id
AND po_line_id = l_po_line_id --bug 12580131,
AND ROWNUM = 1;
SELECT
DECODE(NVL(fc.minimum_accountable_unit,0), 0,
ROUND((l_func_po_price*l_conv_qty)* (1/ABS(l_rate))/l_conv_qty,
NVL(fc.extended_precision,fc.PRECISION)),
ROUND(l_func_po_price* l_conv_qty/fc.minimum_accountable_unit) *
fc.minimum_accountable_unit*(1/ABS(l_rate))/l_conv_qty)
INTO
l_po_price
FROM
fnd_currencies fc
WHERE fc.currency_code = NVL(l_currency_code,l_fin_curr_code);
SELECT
ship_to_location_id
INTO
l_header_ship_to_location
FROM
po_headers_all
WHERE po_header_id = l_blanket_id;
SELECT PO_LINES_INTERFACE_S.NEXTVAL
INTO l_interface_line_id
FROM DUAL;
separate insert statements based on profile value are not required */
--IF (nvl(fnd_profile.value('PO_AUTO_SOURCE_DOC'),'N') = 'Y') THEN
INSERT INTO po_lines_interface
( interface_header_id
, interface_line_id
, line_num
, line_type_id
, item_id
, item_description
, category_id
, unit_of_measure
, quantity
, vendor_product_num
, unit_price
, ship_to_organization_id
, ship_to_location_id
, need_by_date
, promised_date
, creation_date
, created_by
, tax_code_id
, from_header_id
, from_line_id
, closed_date
, closed_by
, receive_close_tolerance
, closed_code
, closed_reason
, secondary_quantity --/* INVCONV
, secondary_unit_of_measure --/* INVCONV
)
SELECT
l_interface_header_id
, l_interface_line_id
, DECODE(l_valid_flag,'Y',line_num,NULL)
, line_type_id
, l_item_id
, item_description
, category_id
, unit_meas_lookup_code
, l_conv_qty
, vendor_product_num
,l_blanket_price
, l_organization_id
, NVL(l_inv_org_location,l_header_ship_to_location)
, l_date
, SYSDATE
, SYSDATE
, l_user
, DECODE(l_tax_code_id,-1,NULL,l_tax_code_id)
, DECODE(l_valid_flag,'G',l_blanket_id,NULL)
, DECODE(l_valid_flag,'G',po_line_id,NULL)
, SYSDATE
, l_user
, 100
, 'OPEN'
, 'Consumption Advice'
, l_secondary_quantity --/* INVCONV
, l_secondary_uom --/* INVCONV
FROM
po_lines_all pla
WHERE po_header_id = l_blanket_id
AND item_id = l_item_id
AND po_line_id=l_po_line_id;
SELECT
vendor_id
INTO
l_vendor_id
FROM
po_vendor_sites_all
WHERE vendor_site_id = l_owning_organization_id;
SELECT
PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL
INTO
l_dist_interface_header_id
FROM DUAL;
INSERT INTO po_distributions_interface
( interface_header_id
, interface_line_id
, interface_distribution_id
, quantity_ordered
, charge_account_id
, accrual_account_id
, variance_account_id
, deliver_to_location_id
, destination_organization_id
, recoverable_Tax
, nonrecoverable_Tax
, recovery_rate
, creation_date
, created_by
, destination_type_code
, rate
, rate_date
/* Bug fix 5373370 - new column addded to po_dist_interface - Start*/
, INTERFACE_DISTRIBUTION_REF
/* Bug fix 5373370 - End */
)
SELECT
l_interface_header_id
, l_interface_line_id
, l_dist_interface_header_id -- bug 5373370
, l_conv_qty
, l_charge_account_id
, l_accrual_account_id
, l_variance_account_id
--, DECODE(l_valid_flag,'G',NVL(l_ship_to_location,l_location_id),l_location_id)
-- Bug Fix for 3959073
-- Ship To Location to be set at the Distribution Line of the Consumption
-- Advice should be taken from the Inventory Organization. If the location
-- is not defined for the Inventory Organization, then take the Ship To
-- Location from the Header of the current Consumption Advice, i.e.,
-- from the Header of the BPA.
, NVL(l_inv_org_location,l_header_ship_to_location)
, l_organization_id
, (l_recoverable_tax*l_conv_qty)
, (l_nonrecoverable_tax*l_conv_qty)
, l_recovery_rate
, SYSDATE
, l_user
, 'INVENTORY'
, DECODE(l_valid_flag,'G',l_global_rate,DECODE(l_rate,-1,NULL,l_rate))
, DECODE(l_valid_flag,'G',l_global_rate_date
,DECODE(l_rate,-1,NULL,l_date))
/* Bug fix 5373370 - new column addded to po_dist_interface - Start*/
, TO_CHAR(l_dist_interface_header_id)
/* Bug fix 5373370 - End */
FROM DUAL;
/* The distribution_interface_ref that is inserted into
PO_DISTRIBUTIONS_INTERFACE is inserted into MCT. This column
will later be joined to PO_DISTRIBUTIONS_ALL to fetch the
PO_DISTRIBUTION_ID */
/* MMT no longer used in this query */
UPDATE mtl_consumption_transactions
SET interface_distribution_ref = TO_CHAR(l_dist_interface_header_id)
WHERE transaction_id IN
( SELECT mct.transaction_id
FROM MTL_CONSUMPTION_TRANSACTIONS mct
WHERE mct.consumption_processed_flag IN ('N','E')
AND mct.inventory_item_id = l_item_id
AND mct.transaction_source_id = l_blanket_id
AND mct.blanket_price = l_blanket_price
AND mct.organization_id = l_organization_id -- bug 14758693
AND NVL(mct.recoverable_tax,0) = l_recoverable_tax
AND NVL(mct.non_recoverable_tax,0) = l_nonrecoverable_tax
AND mct.charge_account_id = l_charge_account_id
AND mct.variance_account_id = l_variance_account_id
AND mct.ACCRUAL_ACCOUNT_ID = l_accrual_account_id
AND NVL(mct.tax_recovery_rate,0) = l_recovery_rate
AND NVL(mct.tax_code_id,-1) = l_tax_code_id
AND mct.batch_id = p_batch_id
);
( '>> Update ASL ' ,'INV_CONSUMPTION_ADVICE_PROC'
, 9
);
INV_PO_THIRD_PARTY_STOCK_MDTR.update_asl
(p_asl_id => l_asl_id);
( '<< Update ASL ','INV_CONSUMPTION_ADVICE_PROC'
, 9
);
INV_CONSUMPTION_ADVICE_PROC.Update_Consumption
(p_consumption_po_header_id => l_consumption_po_header_id
,p_consumption_release_id => l_consumption_release_id
,p_error_code => l_error_msg
,p_batch_id => p_batch_id
,p_transaction_source_id => l_blanket_id
,p_consumption_processed_flag => l_consumption_processed_flag
,p_accrual_account_id => l_accrual_account_id
,p_charge_account_id => l_charge_account_id
,p_variance_account_id => l_variance_account_id
,p_transaction_date => l_transaction_date
,p_global_rate_type => l_global_rate_type
,p_global_rate => l_global_rate
,p_vendor_site_id => l_vendor_site_id);
UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
SET (mctt.net_qty,mctt.secondary_net_qty) =
(SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
FROM MTL_CONSUMPTION_TRANSACTIONS mct
--, MTL_MATERIAL_TRANSACTIONS mmt
WHERE --mmt.transaction_id = mct.transaction_id AND
mct.transaction_source_id = p_txn_source_tab(i)
AND mct.inventory_item_id = p_inventory_item_tab(i)
AND mct.organization_id = p_organization_tab(i)
AND mct.owning_organization_id = p_own_org_tab(i)
/* Bug 4969420 Starts here*/
/* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
--AND mmt.transaction_cost = p_transaction_cost_tab(i)
AND mct.blanket_price = p_transaction_cost_tab(i)
/* Bug 4969421 Ends here*/
/* Bug 11900144. Addition of po_line_id */
AND mct.po_line_id=mctt.po_line_id
AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
AND mct.accrual_account_id = p_accrual_account_tab(i)
AND mct.charge_account_id = p_charge_account_tab(i)
AND mct.variance_account_id = p_variance_account_tab(i)
AND TRUNC(mct.transaction_date) = TRUNC(p_date_tab(i))
AND NVL(mct.rate,-1) = p_rate_tab(i)
AND NVL(mct.rate_type,'##') = p_rate_type_tab(i)
/* Bug 4969421 - Starts here - new check included for tax recovery rate */
AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
/*Bug 4969421 - Ends here */
AND mct.batch_id = p_batch_id
AND mct.consumption_processed_flag IN ('N', 'E'))
WHERE mctt.transaction_source_id = p_txn_source_tab(i)
AND mctt.inventory_item_id = p_inventory_item_tab(i)
AND mctt.organization_id = p_organization_tab(i)
AND mctt.owning_organization_id = p_own_org_tab(i)
AND mctt.transaction_cost = p_transaction_cost_tab(i)
AND mctt.tax_code_id = p_tax_code_tab(i)
AND mctt.recoverable_tax = p_rec_tax_tab(i)
AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
AND mctt.accrual_account_id = p_accrual_account_tab(i)
AND mctt.charge_account_id = p_charge_account_tab(i)
AND mctt.variance_account_id = p_variance_account_tab(i)
AND mctt.transaction_date = TRUNC(p_date_tab(i))
AND mctt.rate = p_rate_tab(i)
/* Bug 4969421 - Starts here - new check included for tax recovery rate */
AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
/*Bug 4969421 - Ends here */
AND mctt.rate_type = p_rate_type_tab(i);
UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
SET (mctt.net_qty,mctt.secondary_net_qty) =
(SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
FROM MTL_CONSUMPTION_TRANSACTIONS mct
--, MTL_MATERIAL_TRANSACTIONS mmt
WHERE --mmt.transaction_id = mct.transaction_id AND
mct.transaction_source_id = p_txn_source_tab(i)
AND mct.inventory_item_id = p_inventory_item_tab(i)
AND mct.organization_id = p_organization_tab(i)
AND mct.owning_organization_id = p_own_org_tab(i)
/* Bug 4969420 Starts here*/
/* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
--AND mmt.transaction_cost = p_transaction_cost_tab(i)
AND mct.blanket_price = p_transaction_cost_tab(i)
/* Bug 4969421 Ends here*/
/* Bug 11900144. Addition of po_line_id */
AND mct.po_line_id=mctt.po_line_id
AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
AND mct.accrual_account_id = p_accrual_account_tab(i)
AND mct.charge_account_id = p_charge_account_tab(i)
AND mct.variance_account_id = p_variance_account_tab(i)
AND TRUNC(mct.transaction_date) = TRUNC(p_date_tab(i))
AND NVL(mct.rate,-1) = p_rate_tab(i)
AND NVL(mct.rate_type,'##') = p_rate_type_tab(i)
/* Bug 4969421 - Starts here - new check included for tax recovery rate */
AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
/*Bug 4969421 - Ends here */
AND mct.batch_id = p_batch_id
AND mct.consumption_processed_flag IN ('N', 'E'))
WHERE mctt.transaction_source_id = p_txn_source_tab(i)
AND mctt.inventory_item_id = p_inventory_item_tab(i)
AND mctt.organization_id = p_organization_tab(i)
AND mctt.owning_organization_id = p_own_org_tab(i)
AND mctt.transaction_cost = p_transaction_cost_tab(i)
AND mctt.tax_code_id = p_tax_code_tab(i)
AND mctt.recoverable_tax = p_rec_tax_tab(i)
AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
AND mctt.accrual_account_id = p_accrual_account_tab(i)
AND mctt.charge_account_id = p_charge_account_tab(i)
AND mctt.variance_account_id = p_variance_account_tab(i)
AND mctt.transaction_date = TRUNC(p_date_tab(i))
AND mctt.rate = p_rate_tab(i)
AND mctt.rate_type = p_rate_type_tab(i)
/* Bug 4969421 - Starts here - new check included for tax recovery rate */
AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
/*Bug 4969421 - Ends here */
AND mctt.global_agreement_flag = 'Y';
UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
SET mctt.net_qty =
(SELECT SUM(mct.net_qty)
FROM MTL_CONSUMPTION_TRANSACTIONS mct
--, MTL_MATERIAL_TRANSACTIONS mmt
WHERE --mmt.transaction_id = mct.transaction_id AND
mct.transaction_source_id = p_txn_source_tab(i)
AND mct.inventory_item_id = p_inventory_item_tab(i)
AND mct.organization_id = p_organization_tab(i)
AND mct.owning_organization_id = p_own_org_tab(i)
/* Bug 4969420 Starts here*/
/* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
--AND mmt.transaction_cost = p_transaction_cost_tab(i)
AND mct.blanket_price = p_transaction_cost_tab(i)
/* Bug 4969421 Ends here*/
/* Bug 11900144. Addition of po_line_id */
AND mct.po_line_id=mctt.po_line_id
AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
AND mct.accrual_account_id = p_accrual_account_tab(i)
AND mct.charge_account_id = p_charge_account_tab(i)
AND mct.variance_account_id = p_variance_account_tab(i)
/* Bug 4969421 - Starts here - new check included for tax recovery rate */
AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
/*Bug 4969421 - Ends here */
AND mct.batch_id = p_batch_id
AND mct.consumption_processed_flag IN ('N', 'E'))
WHERE mctt.transaction_source_id = p_txn_source_tab(i)
AND mctt.inventory_item_id = p_inventory_item_tab(i)
AND mctt.organization_id = p_organization_tab(i)
AND mctt.owning_organization_id = p_own_org_tab(i)
AND mctt.transaction_cost = p_transaction_cost_tab(i)-- mctt.transaction_cost is the blanket_price from MCT
AND mctt.tax_code_id = p_tax_code_tab(i)
AND mctt.recoverable_tax = p_rec_tax_tab(i)
AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
/* Bug 4969421 - Starts here - new check included for tax recovery rate */
AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
/*Bug 4969421 - Ends here */
AND mctt.accrual_account_id = p_accrual_account_tab(i)
AND mctt.charge_account_id = p_charge_account_tab(i)
AND mctt.variance_account_id = p_variance_account_tab(i)
AND mctt.global_agreement_flag = 'N';
PROCEDURE delete_record
( p_txn_source_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_own_org_id IN NUMBER
, p_price IN NUMBER
, p_tax_code_id IN NUMBER
, p_rec_tax_id IN NUMBER
, p_non_rec_tax_id IN NUMBER
, p_accrual_account_id IN NUMBER
, p_charge_account_id IN NUMBER
, p_variance_account_id IN NUMBER
, p_date IN DATE
, p_rate IN NUMBER
, p_rate_type IN VARCHAR
)
IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
( '>> Delete Record','INV_CONSUMPTION_ADVICE_PROC'
, 9
);
DELETE FROM mtl_consumption_txn_temp mctt
WHERE mctt.transaction_source_id = p_txn_source_id
AND mctt.inventory_item_id = p_inventory_item_id
AND mctt.organization_id = p_organization_id
AND mctt.owning_organization_id = p_own_org_id
AND mctt.transaction_cost = p_price
AND mctt.tax_code_id = p_tax_code_id
AND mctt.recoverable_tax = p_rec_tax_id
AND mctt.non_recoverable_tax = p_non_rec_tax_id
AND mctt.accrual_account_id = p_accrual_account_id
AND mctt.charge_account_id = p_charge_account_id
AND mctt.variance_account_id = p_variance_account_id
AND mctt.transaction_date = TRUNC(p_date)
AND mctt.rate = p_rate
AND mctt.rate_type = p_rate_type;
UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
SET mct.batch_id = NULL
WHERE mct.transaction_source_id = p_txn_source_id
AND mct.inventory_item_id = p_inventory_item_id
AND mct.organization_id = p_organization_id
AND mct.owning_organization_id = p_own_org_id
AND mct.accrual_account_id = p_accrual_account_id
/* Bug 4969420 Starts here*/
/* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
--AND mmt.transaction_cost = p_price
-- AND mct.blanket_price = p_price
/* Bug 4969420 Ends here*/
AND TRUNC(mct.transaction_date) = TRUNC(p_date)
AND NVL(mct.tax_code_id,-1) = p_tax_code_id
AND NVL(mct.recoverable_tax,0) = p_rec_tax_id
AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_id
AND mct.charge_account_id = p_charge_account_id
AND mct.variance_account_id = p_variance_account_id
AND NVL(mct.rate,-1) = p_rate
AND NVL(mct.rate_type,'##') = p_rate_type
AND mct.consumption_processed_flag IN ('N', 'E');
( '<< Delete Record','INV_CONSUMPTION_ADVICE_PROC'
, 9
);
END delete_record;
SELECT mct.transaction_id
, mct.error_code
FROM MTL_CONSUMPTION_TRANSACTIONS mct
WHERE mct.consumption_processed_flag = 'E'
AND mct.batch_id = p_batch_id;
SELECT mmt.transaction_id ,
mmt.TRANSACTION_SOURCE_ID ,
mmt.inventory_item_id ,
mmt.REVISION ,
mmt.OWNING_ORGANIZATION_ID ,
mmt.ORGANIZATION_ID ,
mmt.TRANSACTION_DATE
FROM MTL_CONSUMPTION_TRANSACTIONS mct ,
MTL_MATERIAL_TRANSACTIONS mmt
WHERE mmt.transaction_id = mct.transaction_id
AND consumption_processed_flag IN ( 'N',
'E')
AND PO_LINE_ID IS NULL ;
select po_line_id
into l_document_line_id
from po_lines_All
where po_header_id = l_transaction_source_id(i)
and item_id = l_inventory_item_id(i)
and rownum = 1 ;
/* update the po line id and make it ready to be processed */
UPDATE mtl_consumption_transactions mct
SET batch_id = NULL,
po_line_id =l_document_line_id
WHERE consumption_processed_flag in ('N','E')
AND transaction_id = l_transaction_id(i)
AND po_line_id IS NULL;
INV_LOG_UTIL.trace('Updated the Trx id '||l_transaction_id(i)||' with po_line_id '||l_document_line_id ,'INV_CONSUMPTION_ADVICE_PROC',9);
SELECT mctt.transaction_source_id
, mctt.inventory_item_id
, mctt.organization_id
, mctt.owning_organization_id
, mctt.transaction_cost-- This is the blanket_price from MCT Bug 4969421
, mctt.tax_code_id
, mctt.recoverable_tax
, mctt.non_recoverable_tax
, mctt.accrual_account_id
, mctt.charge_account_id
, mctt.variance_account_id
, mctt.rate
, mctt.rate_type
, mctt.transaction_date
, mctt.tax_recovery_rate -- Bug 4969420
FROM MTL_CONSUMPTION_TXN_TEMP mctt
/* bug 5113064 - Start */
/* filter just for the given batch */
WHERE batch_id =p_batch_id ;
/* BUg 5092489. Following IF block is commented becasue quantity is populated during insert
IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
THEN
INV_CONSUMPTION_ADVICE_PROC.load_summarized_quantity_prf
(p_txn_source_tab => l_txn_source_tab
,p_inventory_item_tab => l_item_tab
,p_organization_tab => l_org_tab
,p_own_org_tab => l_owning_org_tab
,p_transaction_cost_tab => l_price_tab -- blanket_price from MCT Bug 4969421
,p_tax_code_tab => l_tax_code_tab
,p_rec_tax_tab => l_rec_tax_tab
,p_non_rec_tax_tab => l_non_rec_tax_tab
,p_accrual_account_tab => l_accrual_account_tab
,p_charge_account_tab => l_charge_account_tab
,p_variance_account_tab => l_variance_account_tab
,p_rate_tab => l_rate_tab
,p_rate_type_tab => l_rate_type_tab
,p_date_tab => l_date_tab
,p_batch_id => l_batch_id
,p_tax_rec_rate_tab => l_tax_rec_rate_tab); -- Bug 4969421
SELECT pvsa.vendor_id
INTO l_vendor_id
FROM po_vendor_sites_all pvsa
WHERE pvsa.vendor_site_id = l_owning_org_tab(i)
AND ROWNUM = 1;
UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
SET mctt.currency_code = (SELECT poa.currency_code
FROM po_headers_all poa
WHERE poa.po_header_id
= l_txn_source_tab(i))
,mctt.asl_id = l_asl_id
WHERE mctt.transaction_source_id = l_txn_source_tab(i)
AND mctt.organization_id = l_org_tab(i)
AND mctt.inventory_item_id = l_item_tab(i);
( 'Consumption Txn Worker:Inside delete record','INV_CONSUMPTION_ADVICE_PROC'
, 9
);
INV_CONSUMPTION_ADVICE_PROC.delete_record
(p_txn_source_id => l_txn_source_tab(i)
,p_inventory_item_id => l_item_tab(i)
,p_organization_id => l_org_tab(i)
,p_own_org_id => l_owning_org_tab(i)
,p_price => l_price_tab(i)
,p_tax_code_id => l_tax_code_tab(i)
,p_rec_tax_id => l_rec_tax_tab(i)
,p_non_rec_tax_id => l_non_rec_tax_tab(i)
,p_accrual_account_id => l_accrual_account_tab(i)
,p_charge_account_id => l_charge_account_tab(i)
,p_variance_account_id => l_variance_account_tab(i)
,p_rate => l_rate_tab(i)
,p_rate_type => l_rate_type_tab(i)
,p_date => l_date_tab(i));
UPDATE mtl_consumption_transactions
SET batch_id = NULL
WHERE batch_id = p_batch_id
AND consumption_processed_flag IN ('N','E')
AND consumption_po_header_id IS NULL
AND consumption_release_id IS NULL;
SELECT po_headers_interface_s.nextval
INTO l_batch_id
FROM dual;
SELECT COUNT(*)
INTO l_count
FROM fnd_concurrent_requests
WHERE request_id = p_request_id
AND phase_code = 'C';
SELECT mctt.transaction_source_id
, mctt.inventory_item_id
, mctt.organization_id
, mctt.transaction_cost-- blanket_price from MCT Bug 4969421
, mctt.tax_code_id
, mctt.accrual_account_id
, mctt.charge_account_id
, mctt.variance_account_id
, mctt.rate
, mctt.rate_type
, mctt.transaction_date
FROM MTL_CONSUMPTION_TXN_TEMP mctt
ORDER BY mctt.transaction_source_id
, mctt.organization_id;
SELECT
COUNT(*)
INTO
l_group_size
FROM
mtl_consumption_txn_temp mctt
WHERE mctt.transaction_source_id = l_txn_source_tab(l_current_cons_index);
SELECT
COUNT(*)
INTO
l_group_size
FROM
mtl_consumption_txn_temp mctt
WHERE mctt.transaction_source_id =
l_txn_source_tab(l_next_cons_index);
UPDATE
MTL_CONSUMPTION_TRANSACTIONS mct
SET mct.batch_id = l_current_batch_id
/* request id stamped to MCT - bug 5200436 - Start*/
, mct.request_id = g_request_id
WHERE mct.transaction_source_id = l_txn_source_tab(i)
AND mct.batch_id = -1
AND mct.consumption_processed_flag IN ('N','E')
AND mct.inventory_item_id = l_item_tab(i)
AND mct.organization_id = l_org_tab(i)
AND mct.blanket_price = l_price_tab(i)
AND NVL(mct.tax_code_id,-1) = NVL(l_tax_code_tab(i),-1)
AND NVL(mct.accrual_account_id,-1) =
NVL(l_accrual_account_tab(i),-1)
AND NVL(mct.charge_account_id,-1) =
NVL(l_charge_account_tab(i),-1)
AND NVL(mct.variance_account_id,-1) =
NVL(l_variance_account_tab(i),-1)
AND NVL(mct.rate,-1) = NVL(l_rate_tab(i),-1)
AND NVL(mct.rate_type,'##') = NVL(l_rate_type_tab(i), '##');
UPDATE
MTL_CONSUMPTION_TRANSACTIONS mct
SET mct.batch_id = l_current_batch_id
WHERE mct.parent_transaction_id IN
(SELECT
mct_in.transaction_id
FROM
MTL_CONSUMPTION_TRANSACTIONS mct_in
WHERE mct_in.batch_id = l_current_batch_id
--AND mct.consumption_processed_flag <> 'Y');
select 1 into l_count from dual
where exists(select /*+ no_unnest */ 1 from MTL_CONSUMPTION_TRANSACTIONS mct
where consumption_processed_flag IN ('N', 'E')
AND ( mct.batch_id = l_batch_id OR mct.batch_id IS NULL) AND NVL(net_qty,0) > 0
and exists(select /*+ no_unnest */ 1 from MTL_MATERIAL_TRANSACTIONS mmt
where mct.transaction_id = mmt.transaction_id
AND mmt.owning_organization_id = NVL(p_vendor_site_id, mmt.owning_organization_id)
AND mmt.organization_id = NVL(p_organization_id, mmt.organization_id)
AND mmt.inventory_item_id = NVL(p_inventory_item_id, mmt.inventory_item_id)
AND mmt.transaction_type_id = 74
AND mmt.transaction_action_id = 6
AND mmt.transaction_source_type_id = 1
and exists(select /*+ no_unnest */ 1 from po_vendor_sites_all pvsa
where mmt.owning_organization_id = pvsa.vendor_site_id
and pvsa.vendor_id = NVL(p_vendor_id, pvsa.vendor_id)))) ;
UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
SET (mct.batch_id, mct.transaction_source_id, mct.inventory_item_id,
mct.accrual_account_id, mct.organization_id,
mct.owning_organization_id, mct.transaction_date)
= (SELECT
l_batch_id, mmt.transaction_source_id,
mmt.inventory_item_id, mmt.distribution_account_id,
mmt.organization_id, mmt.owning_organization_id,
mmt.transaction_date
FROM mtl_material_transactions mmt,po_vendor_sites_all pvsa
WHERE mct.transaction_id = mmt.transaction_id
AND mmt.owning_organization_id = pvsa.vendor_site_id
AND (p_vendor_id IS NULL OR pvsa.vendor_id = p_vendor_id)
AND (p_vendor_site_id IS NULL OR pvsa.vendor_site_id =
p_vendor_site_id)
AND (p_organization_id IS NULL OR mmt.organization_id = p_organization_id)
AND (p_inventory_item_id IS NULL OR mmt.inventory_item_id = p_inventory_item_id)
)
WHERE mct.consumption_processed_flag IN ('N', 'E')
AND NVL(net_qty,0) > 0
AND ( mct.batch_id = l_batch_id OR mct.batch_id IS NULL)
AND mct.po_line_id IS NOT NULL; --bug 11900144
UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
SET (mct.global_agreement_flag) =
(SELECT NVL(global_agreement_flag,'N') FROM po_headers_all
WHERE po_header_id = mct.transaction_source_id)
WHERE mct.consumption_processed_flag IN ('N', 'E')
AND mct.batch_id = l_batch_id;
( 'Calling update_po_distrubution_id '
, 9
);
update_po_distrubution_id ;