[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT D.Invoice_Distribution_Id
,D.line_type_lookup_code
,D.dist_code_combination_id
,D.distribution_line_number
,D.related_id
,D.reversal_flag
,DECODE(l_lcm_enabled,'Y',l_inv_variance_account_id,DECODE(PD.destination_type_code,
'EXPENSE', DECODE(PD.accrue_on_receipt_flag,
'Y', PD.code_combination_id,
D.dist_code_combination_id),
PD.variance_account_id)) -- l_po_variance_ccid
,PD.destination_type_code -- l_po_destination_type
,NVL(PD.accrue_on_receipt_flag,'N') -- l_accrue_on_receipt_flag
,D.matched_uom_lookup_code -- rtxn_uom
,PL.unit_meas_lookup_code -- po_uom
,nvl(PLL.match_option, 'P') -- match_option
,RSL.item_id -- rtxn_item_id
,nvl(D.quantity_invoiced, 0) -- qty_invoiced
,D.corrected_invoice_dist_id -- corrected_invoice_dist_id
,decode(I.invoice_currency_code,
p_base_currency_code,1,
nvl(PD.rate,1)) -- po_rate
,nvl(I.exchange_rate, 1) -- inv_rate
,nvl(PLL.price_override,0) -- po_price
,PLL.matching_basis -- matching basis./*Amount Based Matching*/
FROM ap_invoice_distributions D,
ap_invoices I,
po_distributions PD,
po_line_locations PLL,
po_lines PL,
rcv_transactions RTXN,
rcv_shipment_lines RSL,
ap_invoice_lines_all ail
WHERE I.invoice_id = p_invoice_id
AND I.invoice_id = D.invoice_id
AND D.invoice_line_number = p_inv_line_number
AND D.invoice_id= ail.invoice_id
AND D.invoice_line_number=ail.line_number
AND D.po_distribution_id = PD.po_distribution_id
AND PL.po_line_id = PD.po_line_id
AND PLL.line_location_id = PD.line_location_id
AND NVL(D.match_status_flag,'N') IN ('N', 'S', 'A')
AND NVL(D.posted_flag, 'N') IN ('N', 'P')
AND NVL(D.encumbered_flag, 'N') not in ('Y','R') --bug6921447
--Retropricing: The ERV/IPV calculation is only done for
--RetroItem with match_type 'PO_PRICE_ADJUSTMENT'
--Exec_Matched_Variance_Checks is not called for lines with
--match_type 'ADJUSTMENT_CORRECTION'
--Modified below condition for bug#14360581
AND (D.line_type_lookup_code IN ('ITEM', 'ACCRUAL',
'RETROEXPENSE', 'RETROACCRUAL')
OR (D.line_type_lookup_code ='IPV'
AND D.corrected_invoice_dist_id IS NOT NULL))
--Bug#10416960
AND NVL(D.dist_match_type,'NOT_MATCHED') <> 'ADJUSTMENT_CORRECTION'
AND ( (NVL(D.dist_match_type,'NOT_MATCHED') = 'PO_PRICE_ADJUSTMENT'
AND ail.line_source = 'PO PRICE ADJUSTMENT')
OR NVL(D.dist_match_type,'NOT_MATCHED') <> 'PO_PRICE_ADJUSTMENT' )
--End 10416960
AND D.rcv_transaction_id = RTXN.transaction_id (+)
AND RTXN.shipment_line_id = RSL.shipment_line_id (+)
ORDER BY D.po_distribution_id, D.distribution_line_number;
SELECT D.Invoice_Distribution_Id
,D.line_type_lookup_code
,D.dist_code_combination_id
,D.distribution_line_number
,D.related_id
,D.reversal_flag
,DECODE(l_lcm_enabled,'Y',l_inv_variance_account_id,DECODE(PD.destination_type_code,
'EXPENSE', DECODE(PD.accrue_on_receipt_flag,
'Y', PD.code_combination_id,
D.dist_code_combination_id),
PD.variance_account_id)) -- l_po_variance_ccid
,PD.destination_type_code -- l_po_destination_type
,NVL(PD.accrue_on_receipt_flag,'N') -- l_accrue_on_receipt_flag
,D.matched_uom_lookup_code -- rtxn_uom
,PL.unit_meas_lookup_code -- po_uom
,nvl(PLL.match_option, 'P') -- match_option
,RSL.item_id -- rtxn_item_id
,nvl(D.quantity_invoiced, 0) -- qty_invoiced
,D.corrected_invoice_dist_id -- corrected_invoice_dist_id
,decode(I.invoice_currency_code,
p_base_currency_code,1,
nvl(retain_I.exchange_rate,1)) --bug 9242891 retained_inv_rate
,nvl(I.exchange_rate, 1) -- inv_rate
,nvl(PLL.price_override,0) -- po_price
,PLL.matching_basis -- matching basis./*Amount Based Matching*/
FROM ap_invoice_distributions D, --retainage release inv dists
ap_invoice_distributions retain_D, --bug 9242891 retainage invoice dist
ap_invoices I, --retainage release invoice
ap_invoices retain_I, --bug 9242891 retainage invoice
po_distributions PD,
po_line_locations PLL,
po_lines PL,
rcv_transactions RTXN,
rcv_shipment_lines RSL
WHERE I.invoice_id = p_invoice_id
AND I.invoice_type_lookup_code = 'RETAINAGE RELEASE' --bug 9242891
AND I.invoice_id = D.invoice_id
AND D.retained_invoice_dist_id = retain_D.Invoice_Distribution_Id --bug 9242891
AND retain_I.invoice_id = retain_D.invoice_id --bug 9242891
AND D.invoice_line_number = p_inv_line_number
AND D.po_distribution_id = PD.po_distribution_id
AND PL.po_line_id = PD.po_line_id
AND PLL.line_location_id = PD.line_location_id
AND NVL(D.match_status_flag,'N') IN ('N', 'S', 'A')
AND NVL(D.posted_flag, 'N') IN ('N', 'P')
AND NVL(D.encumbered_flag, 'N') not in ('Y','R') --bug6921447
--Retropricing: The ERV/IPV calculation is only done for
--RetroItem with match_type 'PO_PRICE_ADJUSTMENT'
--Exec_Matched_Variance_Checks is not called for lines with
--match_type 'ADJUSTMENT_CORRECTION'
AND D.line_type_lookup_code = 'RETAINAGE' --bug 9242891
AND D.retained_invoice_dist_id is not NULL --bug 9242891
AND D.rcv_transaction_id = RTXN.transaction_id (+)
AND RTXN.shipment_line_id = RSL.shipment_line_id (+)
ORDER BY D.po_distribution_id, D.distribution_line_number;
SELECT D.Invoice_Distribution_Id,
NVL(D.amount, 0),
NVL(D.base_amount, D.amount)
FROM ap_invoice_distributions D
WHERE D.related_id = x_invoice_distribution_id
AND D.line_type_lookup_code = x_variance_type;
SELECT ail.rcv_transaction_id
INTO l_rcv_transaction_id
FROM ap_invoice_lines ail
WHERE ail.invoice_id = p_invoice_id
AND ail.line_number = p_inv_line_number;
SELECT 'Y'
INTO l_lcm_enabled
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_ID = l_rcv_transaction_id
AND LCM_SHIPMENT_LINE_ID IS NOT NULL;
SELECT COUNT(*)
INTO l_cnt
FROM AP_INVOICES_all ai
WHERE ai.invoice_id = p_invoice_id
AND ai.invoice_type_lookup_code = 'RETAINAGE RELEASE';
l_debug_info := 'Non reversal dist line - Insert IPV line';
INSERT INTO ap_invoice_distributions (
invoice_id,
invoice_line_number,
distribution_class,
invoice_distribution_id,
dist_code_combination_id,
last_update_date,
last_updated_by,
accounting_date,
period_name,
set_of_books_id,
amount,
description,
type_1099,
posted_flag,
batch_id,
quantity_invoiced,
unit_price,
match_status_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
po_distribution_id,
base_amount,
encumbered_flag,
accrual_posted_flag,
cash_posted_flag,
last_update_login,
creation_date,
created_by,
attribute11,
attribute12,
attribute13,
attribute14,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute15,
final_match_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
project_id,
task_id,
award_id,
pa_addition_flag, --4591003
quantity_variance,
base_quantity_variance,
packet_id,
reference_1,
reference_2,
program_application_id,
program_id,
program_update_date,
request_id,
rcv_transaction_id,
dist_match_type,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
org_id,
related_id,
asset_book_type_code,
asset_category_id,
accounting_event_id,
cancellation_flag ,
--Freight and Special Charges
rcv_charge_addition_flag,
awt_group_id, -- bug6843734
pay_awt_group_id, -- bug8222382
inventory_transfer_status, /*Bug#11067286 */
income_tax_region --bug 12908372
)
(SELECT invoice_id,
invoice_line_number,
distribution_class,
ap_invoice_distributions_s.NEXTVAL, -- distribution_id
l_Po_variance_ccid, -- dist_code_combination_id
SYSDATE, -- last_update_date
p_system_user, -- last_updated_by
accounting_date, -- accounting_date
period_name, -- period_name
Set_Of_Books_Id, -- set_of_book_id
l_ipv, -- Amount
Description, -- description
Type_1099, -- type_1099
'N', -- posted_flag
batch_id,
NULL, -- quantity_invoiced
NULL, -- unit_price,
'N', -- match_status_flag
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
'U', -- assets_addition_flag
assets_tracking_flag,
l_max_dist_line_number, --distribution_line_number,
'IPV', --line_type_lookup_code,
po_distribution_id,
l_bipv, --base_amount,
'N', -- encumbered_flag
'N', -- accrual_posted_flag
'N', -- cash_posted_flag
fnd_global.login_id, -- last_update_login
SYSDATE, --Creation_Date,
FND_GLOBAL.user_id, --Created_By,
attribute11,
attribute12,
attribute13,
attribute14,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute15,
final_match_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
project_id,
task_id,
award_id,
decode(project_id,NULL,'E','N'), --Modified for bug#9504423 pa_addition_flag
-- pa_addition_flag, --4591003
NULL, -- quantity_variance,
NULL, -- base_quantity_variance,
NULL, -- packet_id
reference_1,
reference_2,
FND_GLOBAL.prog_appl_id, -- program_application_id
FND_GLOBAL.conc_program_id, -- program_id
SYSDATE, -- program_update_date
FND_GLOBAL.conc_request_id, --request_id
rcv_transaction_id,
dist_match_type,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
org_id,
l_related_id, --related_id,
asset_book_type_code,
asset_category_id,
NULL, -- accounting_event_id
cancellation_flag ,
'N', --rcv_charge_addition_flag
awt_group_id, -- bug6843734
pay_awt_group_id, -- bug8222382
'N', --Bug#11067286
income_tax_region --bug 12908372
FROM ap_invoice_distributions
WHERE invoice_distribution_id = l_invoice_distribution_id );
l_debug_info := 'Non reversal line - UPDATE exist ipv line';
UPDATE ap_invoice_distributions
SET base_amount = l_bipv,
amount = l_ipv, --Introduced for bug# 9252266
last_updated_by = p_system_user,
last_update_login = fnd_global.login_id
WHERE invoice_distribution_id = l_ipv_distribution_id;
l_debug_info := 'Non reversal dist line - Insert ERV line';
INSERT INTO ap_invoice_distributions (
invoice_id,
invoice_line_number,
distribution_class,
invoice_distribution_id,
dist_code_combination_id,
last_update_date,
last_updated_by,
accounting_date,
period_name,
set_of_books_id,
amount,
description,
type_1099,
posted_flag,
batch_id,
quantity_invoiced,
unit_price,
match_status_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
po_distribution_id,
base_amount,
encumbered_flag,
accrual_posted_flag,
cash_posted_flag,
last_update_login,
creation_date,
created_by,
attribute11,
attribute12,
attribute13,
attribute14,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute15,
final_match_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
project_id,
task_id,
award_id,
pa_addition_flag,
quantity_variance,
base_quantity_variance,
packet_id,
reference_1,
reference_2,
program_application_id,
program_id,
program_update_date,
request_id,
rcv_transaction_id,
dist_match_type,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
org_id,
related_id,
asset_book_type_code,
asset_category_id,
accounting_event_id,
cancellation_flag,
--Freight and Special Charges
rcv_charge_addition_flag,
awt_group_id, -- bug6843734
pay_awt_group_id, -- bug8222382
inventory_transfer_status, /*Bug#11067286 */
income_tax_region --bug 12908372
)
(SELECT Invoice_Id, -- invoice_id
Invoice_Line_Number, -- invoice_line_number
distribution_class,
ap_invoice_distributions_s.NEXTVAL, -- distribution_id
l_erv_ccid, -- dist_code_combination_id
SYSDATE, -- last_update_date
p_system_user, -- last_updated_by
accounting_date, -- accounting_date
period_name, -- period_name
Set_Of_Books_Id, -- set_of_book_id
0, --amount
description, -- description
type_1099, -- type_1099
'N', -- posted_flag
batch_id, -- batch_id
NULL, -- quantity_invoiced,
NULL, -- unit_price,
'N', -- match_status_flag
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
'U', -- assets_addition_flag
assets_tracking_flag,
l_max_dist_line_number, --distribution_line_number,
'ERV', -- line_type_lookup_code,
po_distribution_id,
l_erv, -- base_amount,
'N', -- encumbered_flag
'N', -- accrual_posted_flag
'N', -- cash_posted_flag
fnd_global.login_id, --last_update_login,
SYSDATE, --creation_date,
p_system_user, --created_by,
attribute11,
attribute12,
attribute13,
attribute14,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute15,
final_match_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
project_id,
task_id,
award_id,
decode(project_id,NULL,'E','N'), --Modified for bug#9504423 pa_addition_flag
-- pa_addition_flag,
NULL, --quantity_variance,
NULL, --base_quantity_variance,
NULL, -- packet_id
reference_1,
reference_2,
FND_GLOBAL.prog_appl_id, -- program_application_id
FND_GLOBAL.conc_program_id, -- program_id
SYSDATE, -- program_update_date
FND_GLOBAL.conc_request_id, --request_id
rcv_transaction_id,
dist_match_type,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
org_id,
l_related_id, --related_id
asset_book_type_code,
asset_category_id,
NULL, -- accounting_event_id
cancellation_flag ,
'N', -- rcv_charge_addition_flag
awt_group_id, -- bug6843734
pay_awt_group_id, -- bug8222382
'N', /*Bug#11067286 */
income_tax_region --bug 12908372
FROM ap_invoice_distributions
WHERE invoice_distribution_id = l_invoice_distribution_id );
UPDATE ap_invoice_distributions
SET base_amount = l_erv,
last_updated_by = p_system_user,
last_update_login = fnd_global.login_id
WHERE invoice_distribution_id = l_erv_distribution_id;
DELETE ap_invoice_distributions
WHERE invoice_distribution_id = l_erv_distribution_id;
| Step 4.1.e - Update the Parent line when variance exists |
| if variance exists, related id of parent is always |
| populated otherwise clear it |
+-----------------------------------------------------------------*/
IF (l_extra_po_erv = 0) THEN
l_extra_po_erv := NULL;
UPDATE ap_invoice_distributions AID
SET amount = l_amount, -- modified entered amt
base_amount = l_base_amount, -- modified base amt
related_id = l_invoice_distribution_id, -- Bug 12660674 (revert 9318619)
extra_po_erv = l_extra_po_erv,
last_updated_by = p_system_user,
last_update_login = fnd_global.login_id
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_inv_line_number
AND distribution_line_number = l_distribution_line_number;
UPDATE ap_invoice_distributions AID
SET amount = l_amount,
base_amount = l_base_amount,
related_id = NULL,
extra_po_erv = l_extra_po_erv,
last_updated_by = p_system_user,
last_update_login = fnd_global.login_id
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_inv_line_number
AND distribution_line_number = l_distribution_line_number;
| Variance for each different po distribtutions. Update the
| corresponding distribution with line number and distribution
| line number combined.
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*==========================================================================*/
PROCEDURE Exec_Qty_Variance_Check(
p_invoice_id IN NUMBER,
p_base_currency_code IN VARCHAR2,
p_inv_currency_code IN VARCHAR2,
p_system_user IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
CURSOR Distribution_Cur IS
SELECT D.Invoice_Distribution_Id
,D.po_distribution_id
,D.invoice_line_number
,D.distribution_line_number
,NVL(PD.accrue_on_receipt_flag,'N') -- l_accrue_on_receipt_flag
,nvl(PD.quantity_ordered,0)
- nvl(PD.quantity_cancelled,0) -- l_po_qty
,nvl(PLL.price_override, 0) -- l_po_price
,RSL.item_id -- l_rtxn_item_id
,PL.unit_meas_lookup_code -- l_po_uom
,PLL.match_option -- l_match_option
FROM ap_invoice_distributions D,
po_distributions_ap_v PD,
rcv_transactions RTXN,
rcv_shipment_lines RSL,
po_lines PL,
po_line_locations PLL
WHERE D.invoice_id = p_invoice_id
AND D.po_distribution_id = PD.po_distribution_id
AND NVL(D.match_status_flag, 'N') IN ('N', 'S', 'A')
AND NVL(D.posted_flag, 'N') IN ('N', 'P')
AND NVL(D.encumbered_flag, 'N') not in ('Y','R') --bug6921447
AND D.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND PD.line_location_id = PLL.line_location_id
AND PL.po_header_id = PD.po_header_id
AND PLL.matching_basis = 'QUANTITY'
AND PL.po_line_id = PD.po_line_id
AND D.rcv_transaction_id = RTXN.transaction_id(+)
AND RTXN.shipment_line_id = RSL.shipment_line_id(+)
--9564576 Added the below condition
AND exists
(select 1 from ap_invoice_distributions_all aid1
where NVL(aid1.match_status_flag , 'N') <> 'A'
and aid1.invoice_id=D.invoice_id /*Bug 15968233*/
and aid1.po_distribution_id = PD.po_distribution_id
and aid1.line_type_lookup_code IN ('ITEM' , 'ACCRUAL')
)
ORDER BY D.po_distribution_id, D.invoice_line_number, D.distribution_line_number;
l_update_line_num NUMBER;
l_update_dist_num NUMBER;
l_update_line_num,
l_update_dist_num,
l_curr_calling_sequence);
| Quantity variance amount is set for line that we want to update |
| only |
+-----------------------------------------------------------------*/
IF (g_debug_mode = 'Y') THEN
l_debug_info := 'Set inv dist qv if right dist_line_num to be updated ';
IF (l_distribution_line_number = l_update_dist_num AND
l_invoice_line_number = l_update_line_num ) THEN
l_qv := l_po_dist_qv;
l_debug_info := 'Exec_Qty_Variance_Checks-update line with dist_line_num'
|| '=' || to_char(l_distribution_line_number)
|| 'line_number' || to_char(l_invoice_line_number);
UPDATE ap_invoice_distributions
SET quantity_variance = decode(nvl(quantity_variance,0)+l_qv,0,
NULL,nvl(quantity_variance,0)+l_qv),
base_quantity_variance = decode(nvl(base_quantity_variance,0)
+l_bqv, 0, NULL,
nvl(base_quantity_variance,0)
+l_bqv),
last_updated_by = p_system_user,
last_update_login = fnd_global.login_id
WHERE invoice_id = p_invoice_id
AND invoice_line_number = l_invoice_line_number
AND distribution_line_number = l_distribution_line_number
RETURNING invoice_distribution_id, quantity_variance, amount, base_quantity_variance, base_amount
INTO l_inv_dist_id_upd, l_qv_upd, l_amount_upd, l_base_qv_upd, l_base_amount_upd;
UPDATE ap_invoice_distributions_all aid
SET quantity_variance = ap_utilities_pkg.ap_round_currency
(aid.amount * l_qv_ratio, p_inv_currency_code)
,base_quantity_variance = ap_utilities_pkg.ap_round_currency
(aid.base_amount * l_base_qv_ratio, p_base_currency_code)
WHERE invoice_id = p_invoice_id
AND charge_applicable_to_dist_id = l_inv_dist_id_upd
AND line_type_lookup_code IN ('NONREC_TAX', 'TRV', 'TIPV');
l_debug_info := 'Exec_Qty_Variance_Checks-finish update the distribution'
|| 'for each distribution line';
| Variance for each different po distribtutions. Update the
| corresponding distribution with line number and distribution
| line number combined.
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
| August, 2004 bghose Created
|
*==========================================================================*/
PROCEDURE Exec_Amt_Variance_Check(
p_invoice_id IN NUMBER,
p_base_currency_code IN VARCHAR2,
p_inv_currency_code IN VARCHAR2,
p_system_user IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
CURSOR Distribution_Cur IS
SELECT D.Invoice_Distribution_Id
,D.po_distribution_id
,D.invoice_line_number
,D.distribution_line_number
,NVL(PD.accrue_on_receipt_flag,'N') -- l_accrue_on_receipt_flag
,nvl(PD.amount_ordered,0)
- nvl(PD.amount_cancelled,0) -- l_po_amt
,PLL.match_option -- l_match_option
FROM ap_invoice_distributions D,
po_distributions_ap_v PD,
rcv_transactions RTXN,
rcv_shipment_lines RSL,
po_lines PL,
po_line_locations PLL
WHERE D.invoice_id = p_invoice_id
AND D.po_distribution_id = PD.po_distribution_id
AND NVL(D.match_status_flag, 'N') IN ('N', 'S', 'A')
AND NVL(D.posted_flag, 'N') IN ('N', 'P')
AND NVL(D.encumbered_flag, 'N') not in ('Y','R') --bug6921447
AND D.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND PD.line_location_id = PLL.line_location_id
AND PL.po_header_id = PD.po_header_id
AND PL.po_line_id = PD.po_line_id
AND PLL.matching_basis = 'AMOUNT'
AND D.rcv_transaction_id = RTXN.transaction_id(+)
AND RTXN.shipment_line_id = RSL.shipment_line_id(+)
ORDER BY D.po_distribution_id, D.invoice_line_number, D.distribution_line_number;
l_update_line_num NUMBER;
l_update_dist_num NUMBER;
l_update_line_num,
l_update_dist_num,
l_curr_calling_sequence);
| Amount variance amount is set for line that we want to update |
| only |
+-----------------------------------------------------------------*/
IF (g_debug_mode = 'Y') THEN
l_debug_info := 'Set inv dist av if right dist_line_num to be updated ';
IF (l_distribution_line_number = l_update_dist_num AND
l_invoice_line_number = l_update_line_num ) THEN
l_av := l_po_dist_av;
l_debug_info := 'Exec_Amt_Variance_Checks-update line with dist_line_num'
|| '=' || to_char(l_distribution_line_number)
|| 'line_number' || to_char(l_invoice_line_number);
UPDATE ap_invoice_distributions
SET amount_variance = decode(nvl(amount_variance,0)+l_av,0,
NULL,nvl(amount_variance,0)+l_av),
base_amount_variance = decode(nvl(base_amount_variance,0)
+l_bav, 0, NULL,
nvl(base_amount_variance,0)
+l_bav),
last_updated_by = p_system_user,
last_update_login = fnd_global.login_id
WHERE invoice_id = p_invoice_id
AND invoice_line_number = l_invoice_line_number
AND distribution_line_number = l_distribution_line_number
RETURNING invoice_distribution_id, amount_variance, amount, base_amount_variance, base_amount
INTO l_inv_dist_id_upd, l_av_upd, l_amount_upd, l_base_av_upd, l_base_amount_upd;
UPDATE ap_invoice_distributions_all aid
SET amount_variance = ap_utilities_pkg.ap_round_currency
(aid.amount * l_av_ratio, p_inv_currency_code)
,base_amount_variance = ap_utilities_pkg.ap_round_currency
(aid.base_amount * l_base_av_ratio, p_base_currency_code)
WHERE invoice_id = p_invoice_id
AND charge_applicable_to_dist_id = l_inv_dist_id_upd
AND line_type_lookup_code IN ('NONREC_TAX', 'TRV', 'TIPV');
l_debug_info := 'Exec_Amt_Variance_Checks-finish update the distribution'
|| 'for each distribution line';
SELECT PLL.line_location_id,
PLL.po_line_id,
SUM(L.amount),
NVL(AP_INVOICE_LINES_UTILITY_PKG.get_approval_status(p_invoice_id,L.line_number),'N'),
--bug 5182413
SUM(nvl(L.quantity_invoiced,0)),
PLL.price_override, -- BUG 4123171
ROUND((nvl(PLL.quantity,0) - nvl(PLL.quantity_cancelled,0)), 15),
ROUND(nvl(PLL.quantity_received, 0), 15),
ROUND(nvl(PLL.quantity_accepted, 0), 15),
nvl(PLL.amount,0) - nvl(PLL.amount_cancelled,0), --Amount Based Matching
nvl(PLL.amount_received, 0), --Amount Based Matching
nvl(PLL.amount_cancelled,0), --Contract Payments
NVL(PLL.cancel_flag, 'N'),
NVL(PLL.receipt_required_flag, 'N'),
NVL(PLL.inspection_required_flag, 'N'),
I.invoice_currency_code,
PH.currency_code,
PLL.approved_flag,
PLL.closed_code,
decode(PLL.final_match_flag, 'Y', 'D', nvl(L.final_match_flag, 'N')), --Bug 3489536
nvl(L.final_match_flag, 'N'),--Bug 5759169
decode(PH.type_lookup_code, 'STANDARD', 'PO', 'RELEASE'),
decode(L.po_release_id, null, PH.type_lookup_code, PR.release_type),
nvl(PLL.accrue_on_receipt_flag, 'N'),
DECODE(L.po_release_id, null, L.po_header_id, L.po_release_id),
PH.segment1,
nvl(PLL.match_option,'P'),
L.rcv_transaction_id,
L.unit_meas_lookup_code,
RSL.item_id,
decode(PLL.unit_meas_lookup_code,null,PL.unit_meas_lookup_code,PLL.unit_meas_lookup_code), -- BUG 4184044
L.discarded_flag,
L.cancelled_flag,
PLL.matching_basis, -- Amount Based Matching
--bugfix:4709926 added the NVL condition
nvl(PLL.payment_type,'DUMMY'),-- Contract Payments: Tolerances Redesign
I.invoice_type_lookup_code, --Contract Payments: Tolerances Redesign
I.org_id -- Bug 5500101
FROM po_lines PL,
rcv_transactions RTXN,
rcv_shipment_lines RSL,
ap_invoice_lines L,
ap_invoices I,
po_line_locations PLL,
po_headers PH,
po_releases PR
WHERE I.invoice_id = L.invoice_id
AND L.po_line_location_id = PLL.line_location_id
AND L.match_type in ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
'ITEM_TO_PO', 'ITEM_TO_RECEIPT',
'ITEM_TO_SERVICE_PO', 'ITEM_TO_SERVICE_RECEIPT', -- ABM
'AMOUNT_CORRECTION', -- Amount Based Matching
'PO_PRICE_ADJUSTMENT') --Retropricing
AND L.po_release_id = PR.po_release_id(+)
AND PLL.po_line_id = PL.po_line_id
AND PH.po_header_id = PL.po_header_id
AND L.rcv_transaction_id = RTXN.transaction_id(+)
AND RTXN.shipment_line_id = RSL.shipment_line_id(+)
AND (I.payment_status_flag IN ('N', 'P')
OR EXISTS (SELECT 'Holds have to be released'
FROM ap_holds H
WHERE H.invoice_id = I.invoice_id
AND H.release_lookup_code is null
AND H.hold_lookup_code in
('QTY ORD', 'QTY REC',
'AMT ORD', 'AMT REC',
'QUALITY', 'PRICE',
'CURRENCY DIFFERENCE',
'REC EXCEPTION', 'PO NOT APPROVED',
'MAX QTY REC', 'MAX QTY ORD',
'MAX AMT REC', 'MAX AMT ORD',
'FINAL MATCHING',
'MAX SHIP AMOUNT',
'MAX RATE AMOUNT',
'MAX TOTAL AMOUNT'))
OR EXISTS (SELECT 'Unapproved matched dist'
FROM ap_invoice_distributions AID2
WHERE AID2.invoice_id = I.invoice_id
AND AID2.invoice_line_number = L.line_number
AND nvl(AID2.match_status_flag, 'X') <> 'A'))
AND I.invoice_id = p_invoice_id
GROUP BY PLL.line_location_id, L.rcv_transaction_id,
nvl(PLL.match_option,'P'),PLL.po_line_id,
I.invoice_currency_code,
ROUND((nvl(PLL.quantity,0) - nvl(PLL.quantity_cancelled,0)), 15),
PLL.quantity_received,
PLL.price_override, PLL.quantity_billed, PLL.quantity_accepted,
nvl(PLL.amount,0) - nvl(PLL.amount_cancelled,0),
PLL.amount_received,
PLL.amount_cancelled,
PLL.amount_billed,
PLL.cancel_flag, PLL.receipt_required_flag,
PLL.inspection_required_flag,
PH.currency_code,
PLL.approved_flag, PLL.closed_code,
decode(PLL.final_match_flag, 'Y', 'D', nvl(L.final_match_flag, 'N')), --Bug 3489536
nvl(L.final_match_flag, 'N'),--Bug 5759169
PLL.accrue_on_receipt_flag,
decode(PH.type_lookup_code, 'STANDARD', 'PO', 'RELEASE'),
DECODE(L.po_release_id, null, L.po_header_id, L.po_release_id),
decode(L.po_release_id, null, PH.type_lookup_code, PR.release_type),
PH.segment1, L.unit_meas_lookup_code,RSL.item_id,
decode(PLL.unit_meas_lookup_code,null,PL.unit_meas_lookup_code,PLL.unit_meas_lookup_code), -- BUG 4184044
L.discarded_flag,L.cancelled_flag,
PLL.matching_basis,PLL.payment_type,I.invoice_type_lookup_code,
I.org_id,
NVL(AP_INVOICE_LINES_UTILITY_PKG.get_approval_status(p_invoice_id,L.line_number),'N');-- Bug 5182413
l_action := 'UPDATE_CLOSE_STATE';
SELECT 1 INTO l_final_match_count
FROM ap_invoice_lines_all
WHERE po_line_location_id = l_line_location_id
AND nvl(final_match_flag,'N') in ('D','Y')
AND nvl(discarded_flag,'N')<>'Y'
AND nvl(cancelled_flag,'N')<>'Y'
AND rownum=1;
SELECT PLL.closed_code
INTO p_po_closed_code
FROM po_line_locations PLL
WHERE line_location_id = p_line_location_id;
SELECT 'Y'
INTO l_holds_exist
FROM sys.dual
WHERE EXISTS (SELECT DISTINCT 'Invoice has unreleased holds'
FROM ap_holds AH
WHERE AH.invoice_id = p_invoice_id
AND AH.hold_lookup_code NOT IN (p_hold_code, p_hold_code2)
AND AH.release_lookup_code IS NULL);
SELECT 'Y'
INTO l_holds_exist
FROM sys.dual
WHERE EXISTS (SELECT DISTINCT 'Invoice has unreleased holds'
FROM ap_holds AH
WHERE AH.invoice_id = p_invoice_id
AND AH.hold_lookup_code IN (p_hold_code, p_hold_code2)
AND AH.release_lookup_code IS NULL);
SELECT ROUND(SUM(nvl(PD.quantity_delivered, 0)), 5)
INTO p_qty_delivered
FROM po_distributions_ap_v PD
WHERE PD.line_location_id = p_line_location_id;
| PROCEDURE UPDATE_FINAL_MATCH_FLAG
|
| DESCRIPTION:
| Procedure to update the final_match_flag to a given value for
| a invoice_distribution
|
*==========================================================================*/
--BugFix 3489536.Added the parameter p_invoice_id to the function call
PROCEDURE Update_Final_Match_Flag(
p_line_location_id IN NUMBER,
p_final_match_flag IN VARCHAR2,
p_calling_sequence IN VARCHAR2,
p_invoice_id IN NUMBER) IS
l_debug_loc VARCHAR2(30) := 'Update_Final_Match_Flag';
UPDATE ap_invoice_distributions AID
SET final_match_flag = p_final_match_flag
WHERE AID.invoice_id = p_invoice_id -- Bug 3489536
AND AID.po_distribution_id IN
(SELECT PD.po_distribution_id
FROM po_distributions_ap_v PD
WHERE line_location_id = p_line_location_id);
UPDATE ap_invoice_lines AIL
SET final_match_flag = p_final_match_flag
WHERE AIL.po_line_location_id = p_line_location_id
AND AIL.invoice_id=p_invoice_id;--bug5759169
END Update_Final_Match_Flag;
| Update final_match_flag to 'D' for
| ALL invoice distributions matched to
| this PO shipment
| ELSE (closed_code <> 'FINALLY CLOSED')
| Raise Exception
| END IF
| END IF
| ELSE (quantity_delivered < quantity_received)
| Invoice should be on 'CANT TRY PO CLOSE' hold
| Exit Loop;
| 1. Select each 'CANT CLOSE PO' hold associated with |
| distributions where final_match_flag <> 'Y' |
| 2. SELECT each match where final_match_flag = 'Y' and the sum |
| of the distribution amount is 0 |
| (final match has been reversed ) and the invoice is on |
| 'CANT TRY PO CLOSE' hold - To release |
+-----------------------------------------------------------------*/
CURSOR Final_Match_Release_Cur IS
SELECT PD.line_location_id,
'CANT CLOSE PO'
FROM ap_invoice_distributions AID,
ap_holds AH,
po_distributions_ap_v PD,
po_line_locations PLL --Bug 3489536
WHERE AH.invoice_id = p_invoice_id
AND AH.hold_lookup_code = 'CANT CLOSE PO'
AND AH.release_lookup_code IS NULL
AND AH.invoice_id = AID.invoice_id
AND AID.po_distribution_id = PD.po_distribution_id
AND PLL.line_location_id = PD.line_location_id --Bug 3489536
AND decode(PLL.final_match_flag, 'Y', 'D', NVL(AID.final_match_flag, 'N')) <> 'Y' --Bug 3489536
-- AND NVL(AID.final_match_flag, 'N') <> 'Y'--3489536
GROUP BY PD.line_location_id
UNION
SELECT PD.line_location_id,
'CANT TRY PO CLOSE'
FROM ap_invoice_distributions AID,
ap_holds AH,
po_distributions_ap_v PD,
po_line_locations PLL --Bug 3489536
WHERE AH.invoice_id = p_invoice_id
AND AH.hold_lookup_code = 'CANT TRY PO CLOSE'
AND AH.release_lookup_code IS NULL
AND AH.invoice_id = AID.invoice_id
AND AID.po_distribution_id = PD.po_distribution_id
AND AID.final_match_flag = 'Y'
AND PLL.line_location_id = PD.line_location_id --Bug 3489536
AND decode(PLL.final_match_flag, 'Y', 'D', NVL(AID.final_match_flag, 'N')) = 'Y' --Bug 3489536
GROUP BY PD.line_location_id
HAVING SUM(AID.amount) = 0;
SELECT PLL.line_location_id,
PLL.po_line_id,
ROUND(NVL(PLL.quantity_received, 0), 5),
DECODE(PH.type_lookup_code, 'STANDARD', 'PO', 'RELEASE'),
DECODE(PD.po_release_id, NULL, PH.type_lookup_code,
PR.release_type),
NVL(PLL.accrue_on_receipt_flag, 'N'),
DECODE(PD.po_release_id, NULL, PD.po_header_id,
PD.po_release_id),
PH.segment1,
MAX(aid.accounting_date) Accounting_date
FROM po_distributions_ap_v PD,
ap_invoice_distributions AID,
po_line_locations PLL,
po_headers PH,
po_releases PR
WHERE AID.invoice_id = p_invoice_id
AND AID.final_match_flag = 'Y'
AND AID.po_distribution_id = PD.po_distribution_id
AND PD.line_location_id = PLL.line_location_id
AND PD.po_release_id = PR.po_release_id(+)
AND PLL.po_header_id = PH.po_header_id
AND decode(PLL.final_match_flag, 'Y', 'D', NVL(AID.final_match_flag, 'N')) = 'Y' --Bug 3489536
-- Bug 5441016. made the last condition to be = , was <> before
GROUP BY PLL.line_location_id,
PLL.po_line_id,
ROUND(NVL(PLL.quantity_received, 0), 5),
DECODE(PH.type_lookup_code, 'STANDARD', 'PO', 'RELEASE'),
DECODE(PD.po_release_id, NULL, PH.type_lookup_code,
PR.release_type),
NVL(PLL.accrue_on_receipt_flag, 'N'),
DECODE(PD.po_release_id, NULL, PD.po_header_id,
PD.po_release_id),
PH.segment1
HAVING SUM(AID.amount) <> 0;
l_debug_info := 'Update Inv Dist/Line Final_Match_Flag to D';
Update_Final_Match_Flag(l_line_location_id, 'D',
l_curr_calling_sequence, p_invoice_id);
SELECT count(*)
INTO l_rec_exception_count
FROM rcv_transactions rt,
ap_invoice_lines ail
WHERE rt.receipt_exception_flag = 'Y'
AND rt.transaction_type = 'RECEIVE'
AND rt.po_line_location_id = ail.po_line_location_id
AND ail.po_line_location_id = p_line_location_id
AND ail.invoice_id = p_invoice_id ;
SELECT 'Y'
INTO l_rec_exception_exists
FROM rcv_transactions rtxn
WHERE rtxn.transaction_id = p_rcv_transaction_id
AND rtxn.receipt_exception_flag = 'Y';
SELECT distinct corrected_inv_id
FROM ap_invoice_lines AIL
WHERE AIL.invoice_id = p_invoice_id
AND ( ( AIL.po_line_location_id is not null and
AIL.po_line_location_id = p_line_location_id )
OR( AIL.rcv_transaction_id is not null and
AIL.rcv_transaction_id = p_rcv_transaction_id) )
AND AIL.corrected_inv_id is not null
AND AIL.corrected_inv_id <> p_invoice_id;
SELECT count(*)
INTO l_correction_count
FROM ap_invoice_lines AIL
WHERE AIL.invoice_id = p_invoice_id
AND po_line_location_id = p_line_location_id
AND corrected_inv_id is not null
AND corrected_inv_id <> p_invoice_id;
SELECT sum( decode( nvl(AIL.unit_price,0) * nvl(AIL.quantity_invoiced,0),
0, nvl(AIL.amount, 0),
nvl(AIL.unit_price,0) * nvl(AIL.quantity_invoiced,0) ) ),
sum( decode( AIL.match_type, 'PRICE_CORRECTION', 0,
nvl(AIL.quantity_invoiced,0)) )
INTO l_sum_pc_inv_amount,
l_sum_qty_invoiced
FROM ap_invoice_lines AIL
WHERE AIL.po_line_location_id = p_line_location_id
AND ( AIL.corrected_inv_id = p_invoice_id
OR (AIL.invoice_id = p_invoice_id and
AIL.corrected_inv_id is null) )
and nvl(AIL.discarded_flag,'N') = 'N' --for the bug 6882864
and AIL.line_type_lookup_code = 'ITEM'; --for the bug 9686240
SELECT sum (decode(nvl(AIL.unit_price,0) * nvl(AIL.quantity_invoiced,0),
0, NVL(AIL.amount, 0),
nvl(AIL.unit_price,0) * nvl(AIL.quantity_invoiced,0)
)
),
sum (decode( AIL.match_type, 'PRICE_CORRECTION', 0,
(nvl(AIL.quantity_invoiced,0) * l_qty_ratio)
)
)
INTO l_sum_pc_inv_amount,
l_sum_qty_invoiced
FROM ap_invoice_lines AIL
WHERE AIL.po_line_location_id = p_line_location_id
AND ( AIL.corrected_inv_id = p_invoice_id
OR (AIL.invoice_id = p_invoice_id and
AIL.corrected_inv_id is null) )
AND nvl(AIL.discarded_flag,'N') = 'N' --for the bug 6908761
and AIL.line_type_lookup_code = 'ITEM'; --for the bug 9686240
SELECT ROUND(DECODE(l_inv_qty_billed,0,
0, NVL(DECODE(p_invoice_type_lookup_code,'PREPAYMENT',
PLL.quantity_financed,PLL.quantity_billed)
,0)
)
,5)
INTO p_qty_billed
FROM po_line_locations PLL
WHERE PLL.line_location_id = p_line_location_id;
SELECT ROUND(DECODE(l_inv_qty_billed,0,
0, (nvl(pll.quantity_financed,0) + nvl(pll.quantity_billed,0) - nvl(pll.quantity_recouped,0))
)
,15)
INTO p_qty_billed
FROM po_line_locations PLL
WHERE PLL.line_location_id = p_line_location_id;
SELECT nvl(trunc(sum(quantity_invoiced),5),0) --7021414
INTO p_qty_billed
FROM ap_invoice_lines L
WHERE L.po_line_location_id = p_line_location_id
AND ( (L.invoice_id = p_invoice_id and
L.match_type = 'ITEM_TO_PO' ) -- query condition 1
or (L.corrected_inv_id = p_invoice_id and
L.match_type = 'QTY_CORRECTION') -- query condition 2
or (L.invoice_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L2
WHERE L2.invoice_id = p_invoice_id
AND L2.po_line_location_id = p_line_location_id
AND L2.match_type = 'QTY_CORRECTION') and
L.match_type = 'ITEM_TO_PO' ) -- query condition 3
or ( L.corrected_inv_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L3
WHERE L3.invoice_id = p_invoice_id
AND L3.po_line_location_id = p_line_location_id
AND L3.match_type = 'QTY_CORRECTION') and
L.match_type = 'QTY_CORRECTION' ) ) -- query condition 4
AND nvl(L.discarded_flag,'N')='N'; --bug 7021414
SELECT nvl(trunc(sum(quantity_invoiced),5),0)
INTO p_qty_billed
FROM ap_invoice_lines L
WHERE L.po_line_location_id = p_line_location_id
AND ( (L.invoice_id = p_invoice_id and
L.match_type = 'ITEM_TO_RECEIPT' ) -- query condition 1
or (L.corrected_inv_id = p_invoice_id and
L.match_type = 'QTY_CORRECTION') -- query condition 2
or (L.invoice_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L2
WHERE L2.invoice_id = p_invoice_id
AND L2.po_line_location_id = p_line_location_id
AND L2.match_type = 'QTY_CORRECTION') and
L.match_type = 'ITEM_TO_RECEIPT' ) -- query condition 3
or (L.corrected_inv_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L3
WHERE L3.invoice_id = p_invoice_id
AND L3.po_line_location_id = p_line_location_id
AND L3.match_type = 'QTY_CORRECTION') and
L.match_type = 'QTY_CORRECTION' ) ) -- query condition 4
AND nvl(L.discarded_flag,'N')='N'; --bug 7021414
SELECT ROUND(DECODE(l_inv_amt_billed,0,
0, NVL(DECODE(p_invoice_type_lookup_code,'PREPAYMENT',
PLL.amount_financed,PLL.amount_billed)
,0)
)
,5)
INTO p_amt_billed
FROM po_line_locations PLL
WHERE PLL.line_location_id = p_line_location_id;
SELECT ROUND(DECODE(l_inv_amt_billed,0,
0, (nvl(PLL.amount_financed,0) + nvl(PLL.amount_billed,0) - nvl(PLL.amount_recouped,0))
)
,5)
INTO p_amt_billed
FROM po_line_locations PLL
WHERE PLL.line_location_id = p_line_location_id;
SELECT trunc(sum(amount),5)
INTO p_amt_billed
FROM ap_invoice_lines L
WHERE L.po_line_location_id = p_line_location_id
AND ( (L.invoice_id = p_invoice_id and
L.match_type = 'ITEM_TO_PO' ) -- query condition 1
or (L.corrected_inv_id = p_invoice_id and
L.match_type = 'AMOUNT_CORRECTION') -- query condition 2
or (L.invoice_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L2
WHERE L2.invoice_id = p_invoice_id
AND L2.po_line_location_id = p_line_location_id
AND L2.match_type = 'AMOUNT_CORRECTION') and
L.match_type = 'ITEM_TO_PO' ) -- query condition 3
or ( L.corrected_inv_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L3
WHERE L3.invoice_id = p_invoice_id
AND L3.po_line_location_id = p_line_location_id
AND L3.match_type = 'AMOUNT_CORRECTION') and
L.match_type = 'AMOUNT_CORRECTION' ) ); -- query condition 4
SELECT trunc(sum(amount),5)
INTO p_amt_billed
FROM ap_invoice_lines L
WHERE L.po_line_location_id = p_line_location_id
AND ( (L.invoice_id = p_invoice_id and
L.match_type = 'ITEM_TO_RECEIPT' ) -- query condition 1
or (L.corrected_inv_id = p_invoice_id and
L.match_type = 'AMOUNT_CORRECTION') -- query condition 2
or (L.invoice_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L2
WHERE L2.invoice_id = p_invoice_id
AND L2.po_line_location_id = p_line_location_id
AND L2.match_type = 'AMOUNT_CORRECTION') and
L.match_type = 'ITEM_TO_RECEIPT' ) -- query condition 3
or (L.corrected_inv_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L3
WHERE L3.invoice_id = p_invoice_id
AND L3.po_line_location_id = p_line_location_id
AND L3.match_type = 'AMOUNT_CORRECTION') and
L.match_type = 'AMOUNT_CORRECTION' ) ); -- query condition 4
SELECT DECODE(FC.minimum_accountable_unit, NULL,
ROUND(((NVL(PLL.quantity, 0) -
NVL(PLL.quantity_cancelled, 0)) * p_po_price),
FC.precision),
ROUND(((NVL(PLL.quantity, 0) -
NVL(PLL.quantity_cancelled, 0))* p_po_price)
/ FC.minimum_accountable_unit)
* FC.minimum_accountable_unit)
INTO l_po_total
FROM fnd_currencies FC,
po_line_locations PLL,
po_headers PH
WHERE PLL.line_location_id = p_line_location_id
AND PH.po_header_id = PLL.po_header_id
AND FC.currency_code = PH.currency_code;
SELECT DECODE(FC.minimum_accountable_unit, null,
ROUND((NVL(PLL.amount, 0) -
NVL(PLL.amount_cancelled, 0)),
FC.precision),
ROUND((NVL(PLL.amount, 0) -
NVL(PLL.amount_cancelled, 0))
/ FC.minimum_accountable_unit)
* FC.minimum_accountable_unit)
INTO l_po_total
FROM fnd_currencies FC, po_line_locations PLL, po_headers PH
WHERE PLL.line_location_id = p_line_location_id
AND PH.po_header_id = PLL.po_header_id
AND FC.currency_code = PH.currency_code;
SELECT SUM(decode(PD.distribution_type,'PREPAYMENT',
nvl(PD.amount_financed,0),
nvl(PD.amount_billed,0)
)
)
INTO p_ship_trx_amt_var
FROM po_distributions_ap_v PD
WHERE PD.line_location_id = p_line_location_id;
SELECT nvl(sum(nvl(AIDF.amount,0)),0)
INTO l_freight_total
FROM ap_invoice_distributions AIDF,
ap_invoice_distributions AIDI,
po_distributions_all POD
WHERE AIDF.charge_applicable_to_dist_id = AIDI.invoice_distribution_id
AND AIDF.line_type_lookup_code = 'FREIGHT'
AND AIDI.line_type_lookup_code = 'ITEM'
AND AIDI.po_distribution_id = POD.po_distribution_id
AND POD.line_location_id = p_line_location_id;
SELECT sum( NVL(L.amount, 0) )
INTO p_ship_trx_amt
FROM ap_invoice_lines L
WHERE L.po_line_location_id = p_line_location_id
AND ( (L.invoice_id = p_invoice_id and
L.match_type IN ('ITEM_TO_PO', -- query condition 1
'ITEM_TO_SERVICE_PO')) -- Amount Based Matching
or (L.corrected_inv_id = p_invoice_id ) -- query condition 2
or (L.invoice_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L2
WHERE L2.po_line_location_id = p_line_location_id
AND L2.invoice_id = p_invoice_id
AND L2.corrected_inv_id is not null ) and
L.match_type IN ('ITEM_TO_PO', -- query condition 3
'ITEM_TO_SERVICE_PO')) -- Amount Based Matching
or (L.corrected_inv_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L3
WHERE L3.po_line_location_id = p_line_location_id
AND L3.invoice_id = p_invoice_id
AND L3.corrected_inv_id is not null ) ) );
SELECT sum(NVL(L.amount, 0))
INTO p_ship_trx_amt
FROM ap_invoice_lines L
WHERE L.po_line_location_id = p_line_location_id
AND ( (L.invoice_id = p_invoice_id and
L.match_type IN ('ITEM_TO_RECEIPT', -- query condition 1
'ITEM_TO_SERVICE_RECEIPT')) -- Amount Based Matching
or (L.corrected_inv_id = p_invoice_id ) -- query condition 2
or (L.invoice_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L2
WHERE L2.po_line_location_id = p_line_location_id
AND L2.invoice_id = p_invoice_id
AND L2.corrected_inv_id is not null ) and
L.match_type IN ('ITEM_TO_RECEIPT', -- query condition 3
'ITEM_TO_SERVICE_RECEIPT')) -- Amount Based Matching
or (L.corrected_inv_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L3
WHERE L3.po_line_location_id = p_line_location_id
AND L3.invoice_id = p_invoice_id
AND L3.corrected_inv_id is not null ) ) );
SELECT SUM( NVL(D.base_amount, 0))
INTO p_rate_amt_var
FROM ap_invoice_distributions D, po_distributions_ap_v PD
WHERE D.po_distribution_id = PD.po_distribution_id
AND PD.line_location_id = p_line_location_id
AND D.invoice_id = p_invoice_id
AND D.line_type_lookup_code = 'ERV';
SELECT SUM(NVL(D.base_amount, 0))
INTO p_rate_amt_var
FROM ap_invoice_distributions D
WHERE D.rcv_transaction_id = p_rcv_transaction_id
AND D.invoice_id = p_invoice_id
AND D.line_type_lookup_code = 'ERV';
SELECT SUM((NVL(PD.quantity_ordered, 0) -
NVL(PD.quantity_cancelled, 0)) * p_po_price
* DECODE(p_inv_curr_code, p_base_curr_code,1, PD.rate))
INTO l_po_total
FROM po_distributions_ap_v PD
WHERE PD.line_location_id = p_line_location_id;
SELECT SUM(DECODE(p_inv_curr_code, p_base_curr_code, nvl(D.amount,0),
nvl(D.base_amount,D.amount/*,(D.amount * DECODE(I.exchange_rate, null,
PD.rate, I.exchange_rate))*/))) /*commented in bug: 14726598 as additional fix*/
INTO p_ship_base_amt_var
FROM ap_invoice_distributions D
, po_distributions_ap_v PD
, ap_invoices I
, ap_invoice_lines L --Bug6824860
WHERE D.po_distribution_id = PD.po_distribution_id
AND PD.line_location_id = p_line_location_id
AND D.invoice_id = I.invoice_id
AND L.invoice_id = I.invoice_id --Bug6824860
AND L.line_number = D.invoice_line_number --Bug6824860
AND L.match_type not in ('NOT MATCHED','NOT_MATCHED'); --Bug6824860
SELECT SUM((NVL(RT.quantity, 0)) * p_po_price* DECODE(p_inv_curr_code, p_base_curr_code,1,RT.currency_conversion_rate))
INTO rcv_total
FROM rcv_transactions RT
WHERE RT.transaction_id=p_rcv_transaction_id;
SELECT SUM(DECODE(p_inv_curr_code, p_base_curr_code, nvl(D.amount,0),nvl(D.base_amount,D.amount)))
INTO p_ship_base_amt_var
FROM ap_invoice_distributions D
, ap_invoices I
, ap_invoice_lines L
,rcv_transactions RT
WHERE D.rcv_transaction_id = RT.transaction_id
AND RT.transaction_id=p_rcv_transaction_id
AND D.invoice_id = I.invoice_id
AND L.invoice_id = I.invoice_id --Bug6824860
AND L.line_number = D.invoice_line_number --Bug6824860
AND L.match_type not in ('NOT MATCHED','NOT_MATCHED');
SELECT SUM((NVL(PD.amount_ordered, 0) -
NVL(PD.amount_cancelled, 0))
* DECODE(p_inv_curr_code, p_base_curr_code,1,
PD.rate))
INTO l_po_total
FROM po_distributions_ap_v PD
WHERE PD.line_location_id = p_line_location_id;
SELECT SUM(DECODE(p_inv_curr_code, p_base_curr_code, nvl(D.amount,0),
nvl(D.base_amount,D.amount/*,(D.amount * DECODE(I.exchange_rate, null,
PD.rate, I.exchange_rate))*/))) /*commented in bug: 14726598 as additional fix*/
INTO p_ship_base_amt_var
FROM ap_invoice_distributions D
, po_distributions_ap_v PD
, ap_invoices I
, ap_invoice_lines L --Bug6824860
WHERE D.po_distribution_id = PD.po_distribution_id
AND PD.line_location_id = p_line_location_id
AND D.invoice_id = I.invoice_id
AND L.invoice_id = I.invoice_id --Bug6824860
AND L.line_number = D.invoice_line_number --Bug6824860
AND L.match_type not in ('NOT MATCHED','NOT_MATCHED'); --Bug6824860
SELECT SUM((NVL(RT.amount, 0)) * DECODE(p_inv_curr_code, p_base_curr_code,1,RT.currency_conversion_rate))
INTO rcv_total
FROM rcv_transactions RT
WHERE RT.transaction_id=p_rcv_transaction_id;
SELECT SUM(DECODE(p_inv_curr_code, p_base_curr_code, nvl(D.amount,0),nvl(D.base_amount,D.amount)))
INTO p_ship_base_amt_var
FROM ap_invoice_distributions D
, ap_invoices I
, ap_invoice_lines L
,rcv_transactions RT
WHERE D.rcv_transaction_id = RT.transaction_id
AND RT.transaction_id=p_rcv_transaction_id
AND D.invoice_id = I.invoice_id
AND L.invoice_id = I.invoice_id --Bug6824860
AND L.line_number = D.invoice_line_number --Bug6824860
AND L.match_type not in ('NOT MATCHED','NOT_MATCHED');
SELECT SUM( DECODE( p_inv_curr_code
,p_base_curr_code
,nvl(L.amount,0)
,nvl(L.base_amount, (L.amount * AI.exchange_rate )) )
)
INTO p_ship_base_amt
FROM ap_invoice_lines L,
ap_invoices AI
WHERE AI.invoice_id = L.invoice_id
AND L.po_line_location_id = p_line_location_id
AND ( (L.invoice_id = p_invoice_id and
L.match_type IN ('ITEM_TO_PO', -- query condition 1
'ITEM_TO_SERVICE_PO')) -- Amount Based Matching
or (L.corrected_inv_id = p_invoice_id ) -- query condition 2
or (L.invoice_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L2
WHERE L2.po_line_location_id = p_line_location_id
AND L2.invoice_id = p_invoice_id
AND L2.corrected_inv_id is not null ) and
L.match_type IN ('ITEM_TO_PO', -- query condition 3
'ITEM_TO_SERVICE_PO')) -- Amount Based Matching
or (L.corrected_inv_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L3
WHERE L3.po_line_location_id = p_line_location_id
AND L3.invoice_id = p_invoice_id
AND L3.corrected_inv_id is not null ) ) );
SELECT SUM( DECODE( p_inv_curr_code
,p_base_curr_code
,nvl(L.amount,0)
,nvl(L.base_amount, (L.amount * AI.exchange_rate )) )
)
INTO p_ship_base_amt
FROM ap_invoice_lines L,
ap_invoices AI
WHERE AI.invoice_id = L.invoice_id
AND L.rcv_transaction_id = p_rcv_transaction_id /*Added for Bug 14726598 as additional fix*/
AND ( (L.invoice_id = p_invoice_id and
L.match_type IN ('ITEM_TO_RECEIPT', -- query condition 1
'ITEM_TO_SERVICE_RECEIPT')) -- Amount Based Matching
or (L.corrected_inv_id = p_invoice_id ) -- query condition 2
or (L.invoice_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L2
WHERE L2.po_line_location_id = p_line_location_id
AND L2.invoice_id = p_invoice_id
AND L2.corrected_inv_id is not null ) and
L.match_type IN ('ITEM_TO_RECEIPT', -- query condition 3
'ITEM_TO_SERVICE_RECEIPT')) -- Amount Based Matching
or (L.corrected_inv_id IN
( SELECT corrected_inv_id
FROM ap_invoice_lines L3
WHERE L3.po_line_location_id = p_line_location_id
AND L3.invoice_id = p_invoice_id
AND L3.corrected_inv_id is not null ) ) );
SELECT 'Price Difference' -- bug8704810
INTO l_check
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND po_line_location_id = p_line_location_id
AND unit_price <> p_po_unit_price;
FOR i IN (SELECT DISTINCT po_header_id
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND po_header_id is NOT NULL)
LOOP
Print_Debug(l_debug_loc, 'pay when paid check for po_heade_id : '||i.po_header_id );
FOR i IN (SELECT DISTINCT po_header_id
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND po_header_id is NOT NULL)
LOOP
Print_Debug(l_debug_loc, 'po deliverable check for po_heade_id : '||i.po_header_id );
select distinct po_line_location_id
from ap_invoice_lines
where invoice_id = p_invoice_id
and po_line_location_id is not null --Bug16406697
and line_type_lookup_code not in ('TAX','PREPAY');
For rec_part_funds_check in (select distinct aid.po_distribution_id
from ap_invoice_distributions aid,
ap_invoice_lines ail
where ail.po_line_location_id =l_line_location_id
and ail.invoice_id = P_INVOICE_ID
and ail.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.po_distribution_id is not null --Bug16406697
and aid.line_type_lookup_code not in ('REC_TAX',
'NONREC_TAX','TERV','TIPV','TRV','PREPAY'))
LOOP
IF(l_hold_required = 'N')THEN
--End bug#9868268
Print_Debug(l_debug_loc, 'exec_partial_funds_check - po_distribution_id: '||
rec_part_funds_check.po_distribution_id);