[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
aila.line_number
FROM
AP_INVOICE_LINES_ALL aila
, Ap_Invoice_Distributions_All aida
WHERE aila.INVOICE_ID = aida.invoice_id (+) --rollback to original logic
AND aila.line_number = aida.invoice_line_number (+) --rollback to original logic
AND aila.invoice_id = pn_invoice_id
--AND aila.line_number = pn_line_number -- Added by Jia Li for inclusive tax on 2008/01/25
--,commented out for the bug of deleting not working on Jan 28,2008
AND aila.line_type_lookup_code = GV_CONSTANT_ITEM -- ;
SELECT
aila.invoice_id
FROM
AP_INVOICES_ALL aila
WHERE aila.invoice_id = pn_invoice_id
AND aila.INVOICE_NUM LIKE 'ITP-CM/%'
AND aila.description LIKE 'Credit Memo for inclusive 3rd party taxes for receipt%'
AND aila.SOURCE = 'INDIA TAX INVOICE'
AND aila.invoice_type_lookup_code ='CREDIT';
PROCEDURE Insert_Tax_Distribution_Lines
( pn_invoice_id IN NUMBER
, pn_invoice_line_number IN NUMBER
, pn_item_allocation_number IN NUMBER
, pn_tax_allocation_number IN NUMBER
)
IS
ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
lv_proc_name VARCHAR2 (100) := 'Insert_Tax_Distribution_Lines';
INSERT INTO AP_INVOICE_DISTRIBUTIONS_ALL
( accounting_date
, accrual_posted_flag
, assets_addition_flag
, assets_tracking_flag
, cash_posted_flag
, distribution_line_number
, dist_code_combination_id
, invoice_id
, last_updated_by
, last_update_date
, line_type_lookup_code
, period_name
, set_of_books_id
, accts_pay_code_combination_id
, amount
, base_amount
, base_invoice_price_variance
, batch_id
, created_by
, creation_date
, description
, exchange_rate_variance
, final_match_flag
, income_tax_region
, invoice_price_variance
, last_update_login
, match_status_flag
, posted_flag
, po_distribution_id
, program_application_id
, program_id
, program_update_date
, quantity_invoiced
, rate_var_code_combination_id
, request_id
, reversal_flag
, type_1099
, unit_price
, amount_encumbered
, base_amount_encumbered
, encumbered_flag
, exchange_date
, exchange_rate
, exchange_rate_type
, price_adjustment_flag
, price_var_code_combination_id
, quantity_unencumbered
, stat_amount
, amount_to_post
, attribute1
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute_category
, base_amount_to_post
, cash_je_batch_id
, expenditure_item_date
, expenditure_organization_id
, expenditure_type
, je_batch_id
, parent_invoice_id
, pa_addition_flag
, pa_quantity
, posted_amount
, posted_base_amount
, prepay_amount_remaining
, project_accounting_context
, project_id
, task_id
, ussgl_transaction_code
, ussgl_trx_code_context
, earliest_settlement_date
, req_distribution_id
, quantity_variance
, base_quantity_variance
, packet_id
, awt_flag
, awt_group_id
, awt_tax_rate_id
, awt_gross_amount
, awt_invoice_id
, awt_origin_group_id
, reference_1
, reference_2
, org_id
, other_invoice_id
, awt_invoice_payment_id
, 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
, line_group_number
, receipt_verified_flag
, receipt_required_flag
, receipt_missing_flag
, justification
, expense_group
, start_expense_date
, end_expense_date
, receipt_currency_code
, receipt_conversion_rate
, receipt_currency_amount
, daily_amount
, web_parameter_id
, adjustment_reason
, award_id
, mrc_accrual_posted_flag
, mrc_cash_posted_flag
, mrc_dist_code_combination_id
, mrc_amount
, mrc_base_amount
, mrc_base_inv_price_variance
, mrc_exchange_rate_variance
, mrc_posted_flag
, mrc_program_application_id
, mrc_program_id
, mrc_program_update_date
, mrc_rate_var_ccid
, mrc_request_id
, mrc_exchange_date
, mrc_exchange_rate
, mrc_exchange_rate_type
, mrc_amount_to_post
, mrc_base_amount_to_post
, mrc_cash_je_batch_id
, mrc_je_batch_id
, mrc_posted_amount
, mrc_posted_base_amount
, mrc_receipt_conversion_rate
, credit_card_trx_id
, dist_match_type
, rcv_transaction_id
, invoice_distribution_id
, parent_reversal_id
, tax_recoverable_flag
, pa_cc_ar_invoice_id
, pa_cc_ar_invoice_line_num
, pa_cc_processed_code
, merchant_document_number
, merchant_name
, merchant_reference
, merchant_tax_reg_number
, merchant_taxpayer_id
, country_of_supply
, matched_uom_lookup_code
, gms_burdenable_raw_cost
, accounting_event_id
, prepay_distribution_id
, upgrade_posted_amt
, upgrade_base_posted_amt
, inventory_transfer_status
, company_prepaid_invoice_id
, cc_reversal_flag
, awt_withheld_amt
, invoice_includes_prepay_flag
, price_correct_inv_id
, price_correct_qty
, pa_cmt_xface_flag
, cancellation_flag
, invoice_line_number
, corrected_invoice_dist_id
, rounding_amt
, charge_applicable_to_dist_id
, corrected_quantity
, related_id
, asset_book_type_code
, asset_category_id
, distribution_class
, final_payment_rounding
, final_application_rounding
, amount_at_prepay_xrate
, cash_basis_final_app_rounding
, amount_at_prepay_pay_xrate
, intended_use
, detail_tax_dist_id
, rec_nrec_rate
, recovery_rate_id
, recovery_rate_name
, recovery_type_code
, recovery_rate_code
, withholding_tax_code_id
, tax_already_distributed_flag
, summary_tax_line_id
, taxable_amount
, taxable_base_amount
, extra_po_erv
, prepay_tax_diff_amount
, tax_code_id
, vat_code
, amount_includes_tax_flag
, tax_calculated_flag
, tax_recovery_rate
, tax_recovery_override_flag
, tax_code_override_flag
, total_dist_amount
, total_dist_base_amount
, prepay_tax_parent_id
, cancelled_flag
, old_distribution_id
, old_dist_line_number
, amount_variance
, base_amount_variance
, historical_flag
, rcv_charge_addition_flag
, awt_related_id
, related_retainage_dist_id
, retained_amount_remaining
, bc_event_id
, retained_invoice_dist_id
, final_release_rounding
, fully_paid_acctd_flag
, root_distribution_id
, xinv_parent_reversal_id
, recurring_payment_id
, release_inv_dist_derived_from
)
SELECT
accounting_date
, accrual_posted_flag
, assets_addition_flag
, assets_tracking_flag
, cash_posted_flag
, i --distribution_line_number
, dist_code_combination_id
, invoice_id
, last_updated_by
, last_update_date
, line_type_lookup_code
, period_name
, set_of_books_id
, accts_pay_code_combination_id
, amount
, base_amount
, base_invoice_price_variance
, batch_id
, created_by
, creation_date
, description
, exchange_rate_variance
, final_match_flag
, income_tax_region
, invoice_price_variance
, last_update_login
, match_status_flag
, posted_flag
, po_distribution_id
, program_application_id
, program_id
, program_update_date
, quantity_invoiced
, rate_var_code_combination_id
, request_id
, reversal_flag
, type_1099
, unit_price
, amount_encumbered
, base_amount_encumbered
, encumbered_flag
, exchange_date
, exchange_rate
, exchange_rate_type
, price_adjustment_flag
, price_var_code_combination_id
, quantity_unencumbered
, stat_amount
, amount_to_post
, attribute1
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute_category
, base_amount_to_post
, cash_je_batch_id
, expenditure_item_date
, expenditure_organization_id
, expenditure_type
, je_batch_id
, parent_invoice_id
, pa_addition_flag
, pa_quantity
, posted_amount
, posted_base_amount
, prepay_amount_remaining
, project_accounting_context
, project_id
, task_id
, ussgl_transaction_code
, ussgl_trx_code_context
, earliest_settlement_date
, req_distribution_id
, quantity_variance
, base_quantity_variance
, packet_id
, awt_flag
, awt_group_id
, awt_tax_rate_id
, awt_gross_amount
, awt_invoice_id
, awt_origin_group_id
, reference_1
, reference_2
, org_id
, other_invoice_id
, awt_invoice_payment_id
, 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
, line_group_number
, receipt_verified_flag
, receipt_required_flag
, receipt_missing_flag
, justification
, expense_group
, start_expense_date
, end_expense_date
, receipt_currency_code
, receipt_conversion_rate
, receipt_currency_amount
, daily_amount
, web_parameter_id
, adjustment_reason
, award_id
, mrc_accrual_posted_flag
, mrc_cash_posted_flag
, mrc_dist_code_combination_id
, mrc_amount
, mrc_base_amount
, mrc_base_inv_price_variance
, mrc_exchange_rate_variance
, mrc_posted_flag
, mrc_program_application_id
, mrc_program_id
, mrc_program_update_date
, mrc_rate_var_ccid
, mrc_request_id
, mrc_exchange_date
, mrc_exchange_rate
, mrc_exchange_rate_type
, mrc_amount_to_post
, mrc_base_amount_to_post
, mrc_cash_je_batch_id
, mrc_je_batch_id
, mrc_posted_amount
, mrc_posted_base_amount
, mrc_receipt_conversion_rate
, credit_card_trx_id
, dist_match_type
, rcv_transaction_id
, ap_invoice_distributions_s.NEXTVAL --invoice_distribution_id
, parent_reversal_id
, tax_recoverable_flag
, pa_cc_ar_invoice_id
, pa_cc_ar_invoice_line_num
, pa_cc_processed_code
, merchant_document_number
, merchant_name
, merchant_reference
, merchant_tax_reg_number
, merchant_taxpayer_id
, country_of_supply
, matched_uom_lookup_code
, gms_burdenable_raw_cost
, accounting_event_id
, prepay_distribution_id
, upgrade_posted_amt
, upgrade_base_posted_amt
, inventory_transfer_status
, company_prepaid_invoice_id
, cc_reversal_flag
, awt_withheld_amt
, invoice_includes_prepay_flag
, price_correct_inv_id
, price_correct_qty
, pa_cmt_xface_flag
, cancellation_flag
, invoice_line_number
, corrected_invoice_dist_id
, rounding_amt
, charge_applicable_to_dist_id
, corrected_quantity
, related_id
, asset_book_type_code
, asset_category_id
, distribution_class
, final_payment_rounding
, final_application_rounding
, amount_at_prepay_xrate
, cash_basis_final_app_rounding
, amount_at_prepay_pay_xrate
, intended_use
, detail_tax_dist_id
, rec_nrec_rate
, recovery_rate_id
, recovery_rate_name
, recovery_type_code
, recovery_rate_code
, withholding_tax_code_id
, tax_already_distributed_flag
, summary_tax_line_id
, taxable_amount
, taxable_base_amount
, extra_po_erv
, prepay_tax_diff_amount
, tax_code_id
, vat_code
, amount_includes_tax_flag
, tax_calculated_flag
, tax_recovery_rate
, tax_recovery_override_flag
, tax_code_override_flag
, total_dist_amount
, total_dist_base_amount
, prepay_tax_parent_id
, cancelled_flag
, old_distribution_id
, old_dist_line_number
, amount_variance
, base_amount_variance
, historical_flag
, rcv_charge_addition_flag
, awt_related_id
, related_retainage_dist_id
, retained_amount_remaining
, bc_event_id
, retained_invoice_dist_id
, final_release_rounding
, fully_paid_acctd_flag
, root_distribution_id
, xinv_parent_reversal_id
, recurring_payment_id
, release_inv_dist_derived_from
FROM
ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_invoice_line_number
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
AND distribution_line_number = 1;
END Insert_Tax_Distribution_Lines;
PROCEDURE Delete_Tax_Distribution_Lines
( pn_invoice_id IN NUMBER
, pn_invoice_line_number IN NUMBER
, pn_item_allocation_number IN NUMBER
)
IS
ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
lv_proc_name VARCHAR2 (100) := 'Delete_Tax_Distribution_Lines';
DELETE
FROM
ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_invoice_line_number
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
AND distribution_line_number > pn_item_allocation_number;
END Delete_Tax_Distribution_Lines;
SELECT
tax_name
, tax_account_id
, mod_cr_percentage
, adhoc_flag
, NVL (tax_rate, -1) tax_rate
, tax_type
, NVL(rounding_factor,0) rounding_factor
FROM
jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
SELECT
line_number
, amount
FROM
ap_invoice_lines_all
WHERE invoice_id = ln_invoice_id
AND line_number = NVL(ln_invoice_item_line_number,line_number)
AND line_type_lookup_code = GV_CONSTANT_ITEM;
SELECT
amount
, dist_code_combination_id
, assets_tracking_flag
, assets_addition_flag
, project_id
, task_id
, expenditure_type
, pa_addition_flag
, ASSET_BOOK_TYPE_CODE
, ASSET_CATEGORY_ID
FROM
ap_invoice_distributions_all
WHERE invoice_id = ln_invoice_id
AND invoice_line_number = pn_invoice_item_ln_number;
SELECT
jail.invoice_line_number invoice_line_number
, jail.line_amount line_amount
, jcdt.tax_id tax_id
, NVL(jcdt.modvat_flag,'N') modvat_flag
, aila.base_amount base_amount
FROM
jai_ap_invoice_lines jail
, jai_cmn_document_taxes jcdt
, ap_invoice_lines_all aila
WHERE jcdt.source_doc_id = jail.invoice_id
AND jcdt.source_doc_line_id = jail.invoice_line_number
AND aila.invoice_id = jail.invoice_id
AND aila.line_number = jail.invoice_line_number
AND jail.parent_invoice_line_number = pn_invoice_item_ln_number
AND jail.line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
AND jail.invoice_id = ln_invoice_id
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice --Added by eric on Jan 29,2008
ORDER BY jail.invoice_line_number;
SELECT
COUNT(1)
FROM
ap_invoice_distributions_all
WHERE invoice_id = ln_invoice_id
AND invoice_line_number = pn_invoice_line_number;
SELECT
SUM( amount )
, SUM( base_amount )
FROM
ap_invoice_distributions_all
WHERE invoice_id = ln_invoice_id
AND invoice_line_number = pn_invoice_line_number
AND distribution_line_number < pn_dist_line_number;
-- ,insert tax lines.
IF (ln_item_allocation_number >ln_tax_allocation_number)
THEN
insert_tax_distribution_lines
( pn_invoice_id => ln_invoice_id
, pn_invoice_line_number => tax_line_rec.invoice_line_number
, pn_item_allocation_number => ln_item_allocation_number
, pn_tax_allocation_number => ln_tax_allocation_number
);
-- ,delete tax lines.
delete_tax_distribution_lines
( pn_invoice_id => ln_invoice_id
, pn_invoice_line_number => tax_line_rec.invoice_line_number
, pn_item_allocation_number => ln_item_allocation_number
);
UPDATE
ap_invoice_distributions_all
SET
amount =tax_line_rec.line_amount * ln_allocation_factor
/*
ROUND( tax_line_rec.line_amount *
ln_allocation_factor,tax_rec.rounding_factor
)
*/
, base_amount =tax_line_rec.base_amount *ln_allocation_factor
/*
ROUND( tax_line_rec.base_amount *
ln_allocation_factor,tax_rec.rounding_factor
)
*/
, assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
, assets_addition_flag = item_dist_line_rec.assets_addition_flag
, project_id = item_dist_line_rec.project_id
, task_id = item_dist_line_rec.task_id
, expenditure_type = item_dist_line_rec.expenditure_type
, pa_addition_flag = item_dist_line_rec.PA_ADDITION_FLAG
, dist_code_combination_id =
item_dist_line_rec.dist_code_combination_id
, ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
, ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = tax_line_rec.invoice_line_number
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
AND distribution_line_number = ln_loop_counter;
UPDATE
ap_invoice_distributions_all
SET
amount =
tax_line_rec.line_amount - ln_dist_total_amount
, base_amount =
tax_line_rec.base_amount - ln_dist_total_base_amount
, assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
, assets_addition_flag = item_dist_line_rec.assets_addition_flag
, project_id = item_dist_line_rec.project_id
, task_id = item_dist_line_rec.task_id
, expenditure_type = item_dist_line_rec.expenditure_type
, pa_addition_flag = item_dist_line_rec.pa_addition_flag
, dist_code_combination_id =
item_dist_line_rec.dist_code_combination_id
, ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
, ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = tax_line_rec.invoice_line_number
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
AND distribution_line_number = ln_loop_counter;
UPDATE
ap_invoice_distributions_all
SET
amount = tax_line_rec.line_amount
, base_amount = tax_line_rec.base_amount
, assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
, assets_addition_flag = item_dist_line_rec.assets_addition_flag
, project_id = item_dist_line_rec.project_id
, task_id = item_dist_line_rec.task_id
, expenditure_type = item_dist_line_rec.expenditure_type
, pa_addition_flag = item_dist_line_rec.pa_addition_flag
, dist_code_combination_id =
item_dist_line_rec.dist_code_combination_id
, ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
, ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = tax_line_rec.invoice_line_number
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
AND distribution_line_number = ln_loop_counter;
SELECT
dist_code_combination_id
FROM
ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_item_line_number
AND distribution_line_number =1;
SELECT
regime_id
FROM
jai_rgm_definitions
WHERE regime_code = pv_regime_code;
SELECT
attribute_code tax_type
FROM
jai_rgm_registrations
WHERE regime_id = pn_regime_id
AND registration_type =jai_constants.regn_type_tax_types --tax type
AND attribute_code = pv_tax_type_code;
SELECT
TO_NUMBER (accnts.attribute_value)
FROM
jai_rgm_registrations tax_types
, jai_rgm_registrations accnts
WHERE tax_types.regime_id = pn_regime_id
AND tax_types.registration_type = jai_constants.regn_type_tax_types
AND tax_types.attribute_code = pn_tax_type
AND accnts.regime_id = tax_types.regime_id
AND accnts.registration_type = jai_constants.regn_type_accounts
AND accnts.parent_registration_id = tax_types.registration_id
AND accnts.attribute_code = jai_constants.recovery_interim;
UPDATE ap_invoice_lines_all
SET invoice_id = pn_invoice_id
WHERE invoice_id = pn_invoice_id;
SELECT
NVL(MAX (line_number), 0)
INTO
ln_max_line_number
FROM
ap_invoice_lines_all
WHERE invoice_id = pn_invoice_id;
UPDATE
jai_cmn_document_taxes
SET
source_doc_id = pn_invoice_id
WHERE source_doc_id = pn_invoice_id
AND source_doc_type = jai_constants.g_ap_standalone_invoice;
SELECT
NVL(MAX(source_doc_line_id), 0)
INTO
ln_max_line_number
FROM
jai_cmn_document_taxes
WHERE source_doc_id = pn_invoice_id
AND source_doc_type = jai_constants.g_ap_standalone_invoice;
UPDATE
jai_cmn_document_taxes
SET
source_doc_parent_line_no = pn_parent_invoice_line_number
WHERE source_doc_id = pn_invoice_id
AND source_doc_parent_line_no = pn_parent_invoice_line_number
AND source_doc_type = jai_constants.g_ap_standalone_invoice;
SELECT
NVL(MAX(tax_line_no),0)
INTO
ln_max_tax_line_num
FROM
jai_cmn_document_taxes
WHERE source_doc_id = pn_invoice_id
AND source_doc_parent_line_no = pn_parent_invoice_line_number
AND source_doc_type = jai_constants.g_ap_standalone_invoice;
SELECT
account_type
FROM
gl_code_combinations
WHERE code_combination_id = pn_code_combination_id;
SELECT
tax_category_id, service_type_code
FROM
jai_cmn_vendor_sites
WHERE NVL (vendor_site_id, 0) = pn_vendor_site_id
AND vendor_id = pn_vendor_id;
SELECT
vendor_id
, vendor_site_id
, invoice_currency_code
, exchange_rate
, batch_id
FROM
ap_invoices_all
WHERE invoice_id = pn_invoice_id;
PROCEDURE Delete_Tax_Lines
( pn_invoice_id NUMBER
, pn_line_number NUMBER
, pv_modified_only_flag VARCHAR2 DEFAULT 'N'
)
IS
ln_invoice_id NUMBER := pn_invoice_id;
lv_proc_name VARCHAR2 (100) := 'Delete_Tax_Lines';
DELETE
FROM
jai_cmn_document_taxes jcdt
WHERE jcdt.source_doc_id = ln_invoice_id
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
--added by eric for inclusive tax
--------------------------------------------------------------------
AND jcdt.source_doc_parent_line_no=
NVL( ln_invoice_line_number, jcdt.source_doc_parent_line_no);
( SELECT
'X'
FROM
jai_ap_invoice_lines jail
WHERE jail.invoice_line_number = jcdt.source_doc_line_id
AND jail.invoice_id = ln_invoice_id
AND NVL(jail.parent_invoice_line_number,-1) =
NVL(NVL( ln_invoice_line_number
, jail.parent_invoice_line_number),-1)
);
|| '.Delete from jai_cmn_document_taxes'
, SQL%ROWCOUNT||' ROWS DELETED '
);
DELETE
FROM
ap_invoice_lines_all aila
WHERE aila.invoice_id = ln_invoice_id
AND EXISTS
(
SELECT
'X'
FROM
jai_ap_invoice_lines jail
WHERE jail.invoice_id = ln_invoice_id
AND jail.invoice_line_number = aila.line_number
AND jail.parent_invoice_line_number =
NVL ( ln_invoice_line_number
, parent_invoice_line_number
)
)
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
|| '.Delete from ap_invoice_lines_all'
, SQL%ROWCOUNT||' ROWS DELETED '
);
DELETE
FROM
ap_invoice_distributions_all aida
WHERE aida.invoice_id = ln_invoice_id
AND EXISTS
(
SELECT
'X'
FROM
jai_ap_invoice_lines jail
WHERE jail.invoice_id = ln_invoice_id
AND jail.invoice_line_number = aida.invoice_line_number
AND jail.parent_invoice_line_number =
NVL (ln_invoice_line_number, parent_invoice_line_number)
)
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
|| '.Delete from ap_invoice_distributions_all'
, SQL%ROWCOUNT||' ROWS DELETED '
);
DELETE
FROM
jai_ap_invoice_lines
WHERE invoice_id = ln_invoice_id
AND parent_invoice_line_number =
NVL(ln_invoice_line_number,parent_invoice_line_number)
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
|| '.Delete from jai_ap_invoice_lines'
, SQL%ROWCOUNT||' ROWS DELETED '
);
END Delete_Tax_Lines;
PROCEDURE Delete_Useless_Lines (pn_invoice_id IN NUMBER)
IS
ln_invoice_id NUMBER := pn_invoice_id;
lv_proc_name VARCHAR2 (100) := 'Delete_Useless_Lines';
DELETE
FROM
jai_cmn_document_taxes jcdt
WHERE jcdt.source_doc_id = ln_invoice_id
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
--modified by eric for inclusive taxes
----------------------------------------------------------------
AND NOT EXISTS
(
SELECT
'X'
FROM
ap_invoice_lines_all aila
WHERE aila.invoice_id = ln_invoice_id
AND aila.line_number = jcdt.source_doc_parent_line_no
);
SELECT
'X'
FROM
jai_ap_invoice_lines jail
WHERE jail.invoice_id = ln_invoice_id
AND jail.invoice_line_number = jcdt.source_doc_line_id
AND NOT EXISTS
(
SELECT
'X'
FROM
ap_invoice_lines_all aila
WHERE aila.invoice_id = ln_invoice_id
AND aila.line_number =jail.parent_invoice_line_number
)
);
DELETE
FROM
ap_invoice_distributions_all aida
WHERE aida.invoice_id = ln_invoice_id
AND EXISTS
(
SELECT
'X'
FROM
jai_ap_invoice_lines jail
WHERE invoice_id = ln_invoice_id
AND jail.invoice_line_number = aida.invoice_line_number
AND NOT EXISTS
(
SELECT
'X'
FROM
ap_invoice_lines_all aila
WHERE aila.invoice_id = ln_invoice_id
AND aila.line_number =jail.parent_invoice_line_number
)
)
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
DELETE
FROM
ap_invoice_lines_all aila
WHERE aila.invoice_id = ln_invoice_id
AND EXISTS
(
SELECT
'X'
FROM
jai_ap_invoice_lines jail
WHERE jail.invoice_id = ln_invoice_id
AND jail.invoice_line_number = aila.line_number
AND NOT EXISTS
(
SELECT
'X'
FROM
ap_invoice_lines_all aila
WHERE aila.invoice_id =ln_invoice_id
AND aila.line_number = jail.parent_invoice_line_number
)
)
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
DELETE
FROM
jai_ap_invoice_lines jail
WHERE jail.invoice_id = ln_invoice_id
AND NOT EXISTS
(
SELECT
line_number
FROM
ap_invoice_lines_all aila
WHERE aila.invoice_id = ln_invoice_id
AND aila.line_number = jail.invoice_line_number
)
AND line_type_lookup_code = GV_CONSTANT_ITEM;
DELETE
FROM
jai_ap_invoice_lines jail
WHERE
jail.invoice_id = ln_invoice_id
AND NOT EXISTS
(
SELECT
'X'
FROM
ap_invoice_lines_all aila
WHERE aila.invoice_id =ln_invoice_id
AND aila.line_number = jail.parent_invoice_line_number
)
AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
END Delete_Useless_Lines;
PROCEDURE Update_Jai_Line_Amount
( pn_invoice_id IN NUMBER
, pn_line_number IN NUMBER
, pn_line_amount IN NUMBER
)
IS
ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
lv_proc_name VARCHAR2 (100) := 'Update_Jai_Line_Amount';
UPDATE
jai_ap_invoice_lines
SET
line_amount = pn_line_amount
where invoice_id = pn_invoice_id
AND invoice_line_number = pn_line_number ;
|| '.DML (UPDATE jai_ap_invoice_lines)'
,SQL%ROWCOUNT || ' ROWS UPDATED.'
);
END Update_Jai_Line_Amount;
PROCEDURE Update_Jai_Item_Info
( pn_invoice_id IN NUMBER
, pn_vndr_site_id IN NUMBER
, pn_currency_code IN VARCHAR2
, pn_tax_category_id IN NUMBER
)
IS
ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
lv_proc_name VARCHAR2 (100) := 'Update_Jai_Item_Info';
UPDATE
jai_ap_invoice_lines
SET
supplier_site_id = pn_vndr_site_id
, currency_code = pn_currency_code
, tax_category_id = pn_tax_category_id
WHERE invoice_id = pn_invoice_id
AND line_type_lookup_code = GV_CONSTANT_ITEM ;
|| '.DML (UPDATE jai_ap_invoice_lines)'
,SQL%ROWCOUNT || ' ROWS UPDATED.'
);
END Update_Jai_Item_Info;
SELECT
supplier_site_id
, currency_code
, tax_category_id
, line_amount
FROM
jai_ap_invoice_lines
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = NVL (pn_line_number, invoice_line_number)
AND line_type_lookup_code = GV_CONSTANT_ITEM;
SELECT
tax_category_id
FROM
jai_ap_invoice_lines
WHERE invoice_id = pn_invoice_id
AND parent_invoice_line_number = pn_line_number;
SELECT vendor_id
FROM ap_invoices_all
WHERE invoice_id = pn_invoice_id;
select tax_category_id from jai_cmn_vendor_sites where vendor_id =p_supplier_id
and vendor_site_id = p_supplier_site_id;
SELECT
apia.line_number line_number
, apia.amount line_amount
FROM
ap_invoice_lines_all apia
, jai_ap_invoice_lines jail
WHERE apia.invoice_id = jail.invoice_id
AND apia.line_number = jail.invoice_line_number
AND apia.invoice_id = ln_std_invoice_id
AND apia.amount <> jail.line_amount
AND apia.line_type_lookup_code = jail.line_type_lookup_code
AND apia.line_type_lookup_code = GV_CONSTANT_ITEM
UNION ALL
SELECT
apia.line_number line_number
, apia.amount line_amount
FROM
ap_invoice_lines_all apia
, jai_ap_invoice_lines jail
WHERE apia.invoice_id = jail.invoice_id (+)
AND apia.line_number = jail.invoice_line_number(+)
AND apia.invoice_id = ln_std_invoice_id
AND apia.line_type_lookup_code = GV_CONSTANT_ITEM
AND jail.invoice_id IS NULL
AND jail.invoice_line_number IS NULL;
SELECT
COUNT(*)
INTO
ln_tax_line_no
FROM
jai_cmn_document_taxes jcdt
WHERE jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_parent_line_no = pn_line_number
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice;
Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
, pn_line_number => ln_std_line_number
);
Update_Jai_Item_Info
( pn_invoice_id => ln_std_invoice_id
, pn_vndr_site_id => ln_std_vendor_site_id
, pn_currency_code => lv_std_currency_code
, pn_tax_category_id => ln_std_tax_category_id ---ln_tax_category_id
);
Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
, pn_line_number => ln_std_line_number
);
/* vendor updated and GV_LINES_CREATEED is yes , nothing has to be deleted
l_chk_del_flag = 'Y' is to stop deletion of tax lines due to second call of Post form
commit trigger in APXINWKB */
IF pn_old_tax_category_id is null
THEN
l_chk_del_flag := 'Y' ;
Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
, pn_line_number => ln_std_line_number
);
Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
, pn_line_number => ln_std_line_number
);
Delete_Tax_Lines
( pn_invoice_id => ln_std_invoice_id
, pn_line_number => diff_inv_lines_rec.line_number
);
Update_Jai_Line_Amount
( pn_invoice_id => ln_std_invoice_id
, pn_line_number => diff_inv_lines_rec.line_number
, pn_line_amount => diff_inv_lines_rec.line_amount
);
Delete_Useless_Lines ( pn_invoice_id => ln_std_invoice_id );
UPDATE
AP_INVOICES_ALL
SET
invoice_amount = (SELECT SUM(amount) FROM AP_INVOICE_LINES_ALL WHERE invoice_id =pn_invoice_id ),
base_amount = (SELECT SUM(base_amount) FROM AP_INVOICE_LINES_ALL WHERE invoice_id =pn_invoice_id )
WHERE invoice_id =pn_invoice_id;
, p_last_update_date => SYSDATE
, p_last_updated_by => ln_user_id
, p_last_update_login => ln_login_id
, p_operation_flag => NULL
--, p_vat_assessable_value => 0
, p_vat_assessable_value => xn_tax_amount --modified by eric ,replace 0 with line amount
, p_source_trx_type => jai_constants.G_AP_STANDALONE_INVOICE
, p_source_table_name => GV_JAI_AP_INVOICE_LINES --'JAI_AP_INVOICE_LINES'
, p_action => jai_constants.default_taxes
);
SELECT NVL(inclusive_tax_flag,'N')
FROM
jai_cmn_taxes_all
WHERE
tax_id = pn_tax_id;
SELECT
COUNT(tax_id)
INTO
ln_count
FROM
jai_cmn_document_taxes
WHERE source_doc_id = pn_source_doc_id
AND source_DOC_parent_line_no = pn_source_parent_line_no
AND tax_id = pn_tax_id
AND source_doc_type = jai_constants.g_ap_standalone_invoice;
SELECT
invoice_id
, line_number
, line_type_lookup_code
, description
, org_id
, assets_tracking_flag
, match_type
, accounting_date
, period_name
, deferred_acctg_flag
, def_acctg_start_date
, def_acctg_end_date
, def_acctg_number_of_periods
, def_acctg_period_type
, set_of_books_id
, amount
, wfapproval_status
, creation_date
, created_by
, last_updated_by
, last_update_date
, last_update_login
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
FROM
ap_invoice_lines_all
WHERE invoice_id = ln_invoice_id
AND line_type_lookup_code = GV_CONSTANT_ITEM
AND match_type = GV_NOT_MATCH_TYPE
AND line_number = NVL (ln_line_number, line_number);
SELECT
jcdt.doc_tax_id
, jcdt.tax_line_no
, jcdt.tax_id
, jcdt.tax_type
, jcdt.currency_code
, jcdt.tax_rate
, jcdt.qty_rate
, jcdt.uom
, jcdt.tax_amt
, jcdt.func_tax_amt
, jcdt.modvat_flag
, jcdt.tax_category_id
, jcdt.source_doc_type
, jcdt.source_doc_id
, jcdt.source_doc_line_id
, jcdt.source_table_name
, jcdt.tax_modified_by
, jcdt.adhoc_flag
, jcdt.precedence_1
, jcdt.precedence_2
, jcdt.precedence_3
, jcdt.precedence_4
, jcdt.precedence_5
, jcdt.precedence_6
, jcdt.precedence_7
, jcdt.precedence_8
, jcdt.precedence_9
, jcdt.precedence_10
, jcdt.creation_date
, jcdt.created_by
, jcdt.last_update_date
, jcdt.last_updated_by
, jcdt.last_update_login
, jcdt.object_version_number
, jcdt.vendor_id
, jcdt.source_doc_parent_line_no
, jcta.inclusive_tax_flag inc_tax_flag --Added by Eric for Inclusive Tax
FROM
jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
WHERE jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_parent_line_no = pn_parent_line_number
AND jcdt.tax_id = jcta.tax_id --Added by Eric for Inclusive Tax
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
ORDER BY jcdt.doc_tax_id;
SELECT
jcdt.doc_tax_id
, jcdt.tax_line_no
, jcdt.tax_id
, jcdt.tax_type
, jcdt.currency_code
, jcdt.tax_rate
, jcdt.qty_rate
, jcdt.uom
, jcdt.tax_amt
, jcdt.func_tax_amt
, jcdt.modvat_flag
, jcdt.tax_category_id
, jcdt.source_doc_type
, jcdt.source_doc_id
, jcdt.source_doc_line_id
, jcdt.source_table_name
, jcdt.tax_modified_by
, jcdt.adhoc_flag
, jcdt.precedence_1
, jcdt.precedence_2
, jcdt.precedence_3
, jcdt.precedence_4
, jcdt.precedence_5
, jcdt.precedence_6
, jcdt.precedence_7
, jcdt.precedence_8
, jcdt.precedence_9
, jcdt.precedence_10
, jcdt.creation_date
, jcdt.created_by
, jcdt.last_update_date
, jcdt.last_updated_by
, jcdt.last_update_login
, jcdt.object_version_number
, jcdt.vendor_id
, jcdt.source_doc_parent_line_no
FROM
jai_cmn_document_taxes jcdt
WHERE jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_line_id = pn_line_number
AND jcdt.source_doc_parent_line_no = pn_line_number
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
ORDER BY jcdt.tax_line_no FOR UPDATE;
SELECT
tax_name
, tax_account_id
, mod_cr_percentage
, adhoc_flag
, NVL (tax_rate, -1) tax_rate
, tax_type
, NVL(rounding_factor,0) rounding_factor
FROM
jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
SELECT
accounting_date
, accrual_posted_flag
, assets_addition_flag
, assets_tracking_flag
, cash_posted_flag
, distribution_line_number
, dist_code_combination_id
, invoice_id
, last_updated_by
, last_update_date
, line_type_lookup_code
, period_name
, set_of_books_id
, amount
, base_amount
, batch_id
, created_by
, creation_date
, description
, exchange_rate
, exchange_rate_variance
, last_update_login
, match_status_flag
, posted_flag
, rate_var_code_combination_id
, reversal_flag
, program_application_id
, program_id
, program_update_date
, accts_pay_code_combination_id
, invoice_distribution_id
, quantity_invoiced
, po_distribution_id
, rcv_transaction_id
, price_var_code_combination_id
, invoice_price_variance
, base_invoice_price_variance
, matched_uom_lookup_code
, invoice_line_number
, org_id
, charge_applicable_to_dist_id
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
, distribution_class
, ASSET_BOOK_TYPE_CODE
, ASSET_CATEGORY_ID
FROM
ap_invoice_distributions_all
WHERE invoice_id = ln_invoice_id
AND invoice_line_number = pn_line_number
AND distribution_line_number = 1;
SELECT
invoice_id
, line_number
, line_type_lookup_code
, description
, org_id
, assets_tracking_flag
, match_type
, accounting_date
, period_name
, deferred_acctg_flag
, def_acctg_start_date
, def_acctg_end_date
, def_acctg_number_of_periods
, def_acctg_period_type
, set_of_books_id
, amount
, wfapproval_status
, creation_date
, created_by
, last_updated_by
, last_update_date
, last_update_login
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
FROM
ap_invoice_lines_all a
WHERE invoice_id = ln_invoice_id
AND line_number = pn_inovoice_line_num
AND NOT EXISTS
(
SELECT
'X'
FROM
jai_ap_invoice_lines b
WHERE a.invoice_id = b.invoice_id
AND a.line_number = b.invoice_line_number
);
lv_insert_jai_inv_sql VARCHAR2(32000);
lv_insert_jai_tax_sql VARCHAR2(32000);
lv_insert_ap_inv_ln_sql VARCHAR2(32000);
lv_insert_ap_inv_dist_ln_sql VARCHAR2(32000);
lv_insert_jai_inv_sql:=
'INSERT INTO jai_ap_invoice_lines
( jai_ap_invoice_lines_id
, organization_id
, location_id
, invoice_id
, invoice_line_number
, supplier_site_id
, parent_invoice_line_number
, tax_category_id
, service_type_code
, match_type
, currency_code
, line_amount
, line_type_lookup_code
, created_by
, creation_date
, last_update_date
, last_update_login
, last_updated_by
)
VALUES
( :1
, :2
, :3
, :4
, :5
, :6
, :7
, :8
, :9
, :10
, :11
, :12
, :13
, :14
, :15
, :16
, :17
, :18
)';
lv_insert_jai_tax_sql :=
'INSERT INTO jai_cmn_document_taxes
( doc_tax_id
, tax_line_no
, tax_id
, tax_type
, currency_code
, tax_rate
, qty_rate
, uom
, tax_amt
, func_tax_amt
, modvat_flag
, tax_category_id
, source_doc_type
, source_doc_id
, source_doc_line_id
, source_table_name
, tax_modified_by
, adhoc_flag
, precedence_1
, precedence_2
, precedence_3
, precedence_4
, precedence_5
, precedence_6
, precedence_7
, precedence_8
, precedence_9
, precedence_10
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, object_version_number
, vendor_id
, source_doc_parent_line_no
)
VALUES
( :1
, :2
, :3
, :4
, :5
, :6
, :7
, :8
, :9
, :10
, :11
, :12
, :13
, :14
, :15
, :16
, :17
, :18
, :19
, :20
, :21
, :22
, :23
, :24
, :25
, :26
, :27
, :28
, :29
, :30
, :31
, :32
, :33
, :34
, :35
, :36
)';
lv_insert_ap_inv_ln_sql :=
'INSERT INTO ap_invoice_lines_all
( invoice_id
, line_number
, line_type_lookup_code
, description
, org_id
, assets_tracking_flag
, match_type
, accounting_date
, period_name
, deferred_acctg_flag
, def_acctg_start_date
, def_acctg_end_date
, def_acctg_number_of_periods
, def_acctg_period_type
, set_of_books_id
, amount
, wfapproval_status
, creation_date
, created_by
, last_updated_by
, last_update_date
, last_update_login
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
)
VALUES
( :1
, :2
, :3
, :4
, :5
, :6
, :7
, :8
, :9
, :10
, :11
, :12
, :13
, :14
, :15
, :16
, :17
, :18
, :19
, :20
, :21
, :22
, :23
, :24
, :25
, :26
, :27
)';
lv_insert_ap_inv_dist_ln_sql :=
'INSERT INTO ap_invoice_distributions_all
( accounting_date
, accrual_posted_flag
, assets_addition_flag
, assets_tracking_flag
, cash_posted_flag
, distribution_line_number
, dist_code_combination_id
, invoice_id
, last_updated_by
, last_update_date
, line_type_lookup_code
, period_name
, set_of_books_id
, amount
--, base_amount deleted by eric on 2008-Jan-08, as po_matched case not populate the column
, batch_id
, created_by
, creation_date
, description
, exchange_rate_variance
, last_update_login
, match_status_flag
, posted_flag
, rate_var_code_combination_id
, reversal_flag
, program_application_id
, program_id
, program_update_date
, accts_pay_code_combination_id
, invoice_distribution_id
, quantity_invoiced
, po_distribution_id
, rcv_transaction_id
, price_var_code_combination_id
, invoice_price_variance
, base_invoice_price_variance
, matched_uom_lookup_code
, invoice_line_number
, org_id
, charge_applicable_to_dist_id
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
, DISTRIBUTION_CLASS
, TAX_RECOVERABLE_FLAG
)
VALUES
( :1
, :2
, :3
, :4
, :5
, :6
, :7
, :8
, :9
, :10
, :11
, :12
, :13
, :14
, :15
, :16
, :17
, :18
, :19
, :20
, :21
, :22
, :23
, :24
, :25
, :26
, :27
, :28
, :29
, :30
, :31
, :32
, :33
, :34
, :35
, :36
, :37
, :38
, :39
, :40
, :41
, :42
, :43
, :44
, :45
, :46
, :47
, :48
)';
SELECT
jai_ap_invoice_lines_s.NEXTVAL
INTO
ln_jai_inv_line_id
FROM DUAL;
EXECUTE IMMEDIATE lv_insert_jai_inv_sql
USING ln_jai_inv_line_id
, pn_organization_id
, pn_location_id
, item_line_rec.invoice_id
, item_line_rec.line_number
, ln_vendor_site_id
, ''
, ln_tax_category_id
, lv_service_type_code
, item_line_rec.match_type
, lv_currency_code
, item_line_rec.amount
, item_line_rec.line_type_lookup_code
, item_line_rec.created_by
, item_line_rec.creation_date
, item_line_rec.last_update_date
, item_line_rec.last_update_login
, item_line_rec.last_updated_by ;
UPDATE
jai_cmn_document_taxes
SET
--modified by eric for inclusive tax
----------------------------------------------------------------
source_doc_line_id = ln_source_doc_line_id --ln_max_inv_line_num
----------------------------------------------------------------
WHERE CURRENT OF jai_default_doc_taxes_cur ;
UPDATE
jai_cmn_document_taxes
SET
--modified by eric for inclusive tax
----------------------------------------------------------------
source_doc_line_id = ln_source_doc_line_id --ln_max_inv_line_num
----------------------------------------------------------------
, tax_amt = ln_recur_tax_amt
, func_tax_amt = ln_recur_func_tax_amt
, modvat_flag = 'Y'
, creation_date = ld_sys_date --Eric added on 18-Feb-2008,for bug#6824857
, last_update_date = ld_sys_date --sysdate,Eric changed on 18-Feb-2008,for bug#6824857
, created_by = ln_user_id --Eric added on 18-Feb-2008,for bug#6824857
, last_updated_by = ln_user_id
, last_update_login = ln_login_id
WHERE CURRENT OF jai_default_doc_taxes_cur ;
SELECT
jai_cmn_document_taxes_s.nextval
INTO
ln_doc_tax_id
FROM DUAL;
EXECUTE IMMEDIATE lv_insert_jai_tax_sql
USING ln_doc_tax_id
, ln_max_tax_line_num
, jai_default_doc_taxes_rec.tax_id
, jai_default_doc_taxes_rec.tax_type
, jai_default_doc_taxes_rec.currency_code
, jai_default_doc_taxes_rec.tax_rate
, jai_default_doc_taxes_rec.qty_rate
, jai_default_doc_taxes_rec.uom
, ln_nrecur_tax_amt --TAX_AMT
, ln_nrecur_func_tax_amt --FUNC_TAX_AMT
, 'N' --MODVAT_FLAG
, jai_default_doc_taxes_rec.tax_category_id
, jai_default_doc_taxes_rec.source_doc_type
, jai_default_doc_taxes_rec.source_doc_id
--modified by eric for inclusive tax
-----------------------------------------------
,ln_source_doc_line_id --, ln_max_inv_line_num
-----------------------------------------------
, jai_default_doc_taxes_rec.source_table_name
, jai_default_doc_taxes_rec.tax_modified_by
, jai_default_doc_taxes_rec.adhoc_flag
, jai_default_doc_taxes_rec.precedence_1
, jai_default_doc_taxes_rec.precedence_2
, jai_default_doc_taxes_rec.precedence_3
, jai_default_doc_taxes_rec.precedence_4
, jai_default_doc_taxes_rec.precedence_5
, jai_default_doc_taxes_rec.precedence_6
, jai_default_doc_taxes_rec.precedence_7
, jai_default_doc_taxes_rec.precedence_8
, jai_default_doc_taxes_rec.precedence_9
, jai_default_doc_taxes_rec.precedence_10
, ld_sys_date --SYSDATE,Eric changed on 18-Feb-2008,for bug#6824857 --creation_date
, ln_user_id --created_by
, ld_sys_date --SYSDATE,Eric changed on 18-Feb-2008,for bug#6824857 --last_update_date
, ln_user_id --last_updated_by
, ln_login_id --last_update_login
, jai_default_doc_taxes_rec.object_version_number
, jai_default_doc_taxes_rec.vendor_id
, ap_invoice_lines_rec.line_number;
Delete_Tax_Lines ( pn_invoice_id => ln_invoice_id
, pn_line_number => ln_line_number
, pv_modified_only_flag => 'Y'
);
SELECT
jai_ap_invoice_lines_s.NEXTVAL
INTO
ln_jai_inv_line_id
FROM DUAL;
EXECUTE IMMEDIATE lv_insert_jai_inv_sql
USING ln_jai_inv_line_id
, pn_organization_id
, pn_location_id
, jai_doc_taxes_rec.source_doc_id
, jai_doc_taxes_rec.source_doc_line_id
, ln_vendor_site_id
, jai_doc_taxes_rec.source_doc_parent_line_no
, jai_doc_taxes_rec.tax_category_id
, '' --service_type ,used by item line only
, ap_invoice_lines_rec.match_type
, lv_currency_code
, jai_doc_taxes_rec.tax_amt
, GV_CONSTANT_MISCELLANEOUS
, ln_user_id
, SYSDATE
, SYSDATE
, ln_login_id
, ln_user_id ;
, 'Table jai_ap_invoice_lines inserted '
);
EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
USING jai_doc_taxes_rec.source_doc_id
, jai_doc_taxes_rec.source_doc_line_id
, GV_CONSTANT_MISCELLANEOUS
, tax_rec.tax_name
, ap_invoice_lines_rec.org_id
, ln_asset_track_flag
, ap_invoice_lines_rec.match_type
, ap_invoice_lines_rec.accounting_date
, ap_invoice_lines_rec.period_name
, ap_invoice_lines_rec.deferred_acctg_flag
, ap_invoice_lines_rec.def_acctg_start_date
, ap_invoice_lines_rec.def_acctg_end_date
, ap_invoice_lines_rec.def_acctg_number_of_periods
, ap_invoice_lines_rec.def_acctg_period_type
, ap_invoice_lines_rec.set_of_books_id
, jai_doc_taxes_rec.tax_amt
, ap_invoice_lines_rec.wfapproval_status
, SYSDATE
, ln_user_id
, ln_user_id
, SYSDATE
, ln_login_id
, ln_project_id
, ln_task_id
, lv_expenditure_type
, ld_exp_item_date
, ln_exp_org_id ;
, 'Table ap_invoice_lines_all inserted '
);
SELECT
ap_invoice_distributions_s.NEXTVAL
INTO
ln_inv_dist_id
FROM DUAL;
EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
USING ap_invoice_lines_rec.accounting_date
, 'N'
, ln_dist_asst_add_flag
, ln_dist_asst_trck_flag
, 'N'
, 1 --distribution_line_number
, ln_dist_acct_ccid
, ln_invoice_id
, ln_user_id
, SYSDATE
, GV_CONSTANT_MISCELLANEOUS
, ap_invoice_lines_rec.period_name
, ap_invoice_lines_rec.set_of_books_id
, jai_doc_taxes_rec.tax_amt
-- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
, ln_batch_id --invoice header level
, ln_user_id
, SYSDATE
, tax_rec.tax_name
, ''
, ln_login_id
, ap_invoice_dist_rec.match_status_flag
, 'N' -- posted_flag
, ''
, ap_invoice_dist_rec.reversal_flag
, ap_invoice_dist_rec.program_application_id
, ap_invoice_dist_rec.program_id
, ap_invoice_dist_rec.program_update_date
, ap_invoice_dist_rec.accts_pay_code_combination_id
, ln_inv_dist_id
, -1
, ''
, ''
, ap_invoice_dist_rec.rcv_transaction_id
, ap_invoice_dist_rec.invoice_price_variance
, ap_invoice_dist_rec.base_invoice_price_variance
, ap_invoice_dist_rec.matched_uom_lookup_code
, jai_doc_taxes_rec.source_doc_line_id
, ap_invoice_lines_rec.org_id
, ln_chargeble_acct_ccid
, ln_dist_project_id
, ln_dist_task_id
, ln_dist_exp_type
, ld_dist_exp_item_date
, ln_dist_exp_org_id
, ln_dist_pa_context
, ln_dist_pa_addition_flag
, ap_invoice_dist_rec.distribution_class
, lv_tax_recoverable_flag;
, 'Table ap_distribution_lines_all inserted '
);
SELECT
jai_ap_invoice_lines_s.NEXTVAL
INTO
ln_jai_inv_line_id
FROM DUAL;
EXECUTE IMMEDIATE lv_insert_jai_inv_sql
USING ln_jai_inv_line_id
, pn_organization_id
, pn_location_id
, jai_doc_taxes_rec.source_doc_id -- invoice_id
, ln_max_pro_line_num -- invoice_line_num
, ln_vendor_site_id
, jai_doc_taxes_rec.source_doc_parent_line_no
, jai_doc_taxes_rec.tax_category_id
, '' --service_type ,used by item line only
, ap_invoice_lines_rec.match_type
, lv_currency_code
, -jai_doc_taxes_rec.tax_amt -- negative tax amount
, GV_CONSTANT_MISCELLANEOUS
, ln_user_id
, SYSDATE
, SYSDATE
, ln_login_id
, ln_user_id ;
EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
USING jai_doc_taxes_rec.source_doc_id -- invoice_id
, ln_max_pro_line_num -- line_number
, GV_CONSTANT_MISCELLANEOUS
, tax_rec.tax_name
, ap_invoice_lines_rec.org_id
, ap_invoice_lines_rec.assets_tracking_flag
, ap_invoice_lines_rec.match_type
, ap_invoice_lines_rec.accounting_date
, ap_invoice_lines_rec.period_name
, ap_invoice_lines_rec.deferred_acctg_flag
, ap_invoice_lines_rec.def_acctg_start_date
, ap_invoice_lines_rec.def_acctg_end_date
, ap_invoice_lines_rec.def_acctg_number_of_periods
, ap_invoice_lines_rec.def_acctg_period_type
, ap_invoice_lines_rec.set_of_books_id
, -jai_doc_taxes_rec.tax_amt -- negative tax amount
, ap_invoice_lines_rec.wfapproval_status
, SYSDATE
, ln_user_id
, ln_user_id
, SYSDATE
, ln_login_id
, ap_invoice_lines_rec.project_id
, ap_invoice_lines_rec.task_id
, ap_invoice_lines_rec.expenditure_type
, ap_invoice_lines_rec.expenditure_item_date
, ap_invoice_lines_rec.expenditure_organization_id ;
SELECT
ap_invoice_distributions_s.NEXTVAL
INTO
ln_inv_dist_id
FROM DUAL;
EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
USING ap_invoice_lines_rec.accounting_date
, 'N'
, ap_invoice_dist_rec.assets_addition_flag
, ap_invoice_dist_rec.assets_tracking_flag
, 'N'
, 1 --distribution_line_number
, ln_dist_acct_ccid
, ln_invoice_id
, ln_user_id
, SYSDATE
, GV_CONSTANT_MISCELLANEOUS
, ap_invoice_lines_rec.period_name
, ap_invoice_lines_rec.set_of_books_id
, -jai_doc_taxes_rec.tax_amt -- negative tax amount
-- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
, ln_batch_id -- invoice header level
, ln_user_id
, SYSDATE
, tax_rec.tax_name
, ''
, ln_login_id
, ap_invoice_dist_rec.match_status_flag
, 'N' -- posted_flag
, ''
, ap_invoice_dist_rec.reversal_flag
, ap_invoice_dist_rec.program_application_id
, ap_invoice_dist_rec.program_id
, ap_invoice_dist_rec.program_update_date
, ap_invoice_dist_rec.accts_pay_code_combination_id
, ln_inv_dist_id
, -1
, ''
, ''
, ap_invoice_dist_rec.price_var_code_combination_id
, ap_invoice_dist_rec.invoice_price_variance
, ap_invoice_dist_rec.base_invoice_price_variance
, ap_invoice_dist_rec.matched_uom_lookup_code
, ln_max_pro_line_num -- invoice_line_number
, ap_invoice_lines_rec.org_id
, ln_chargeble_acct_ccid
, ap_invoice_dist_rec.project_id
, ap_invoice_dist_rec.task_id
, ap_invoice_dist_rec.expenditure_type
, ap_invoice_dist_rec.expenditure_item_date
, ap_invoice_dist_rec.expenditure_organization_id
, ap_invoice_dist_rec.project_accounting_context
, ap_invoice_dist_rec.pa_addition_flag
, ap_invoice_dist_rec.distribution_class
, 'Y';
SELECT
jai_ap_invoice_lines_s.NEXTVAL
INTO
ln_jai_inv_line_id
FROM DUAL;
EXECUTE IMMEDIATE lv_insert_jai_inv_sql
USING ln_jai_inv_line_id
, pn_organization_id
, pn_location_id
, jai_doc_taxes_rec.source_doc_id -- invoice_id
, ln_max_pro_line_num -- line_number
, ln_vendor_site_id
, jai_doc_taxes_rec.source_doc_parent_line_no
, jai_doc_taxes_rec.tax_category_id
, '' --service_type ,used by item line only
, ap_invoice_lines_rec.match_type
, lv_currency_code
, jai_doc_taxes_rec.tax_amt -- positive tax amount
, GV_CONSTANT_MISCELLANEOUS
, ln_user_id
, SYSDATE
, SYSDATE
, ln_login_id
, ln_user_id ;
EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
USING jai_doc_taxes_rec.source_doc_id -- invoice_id
, ln_max_pro_line_num -- line_number
, GV_CONSTANT_MISCELLANEOUS
, tax_rec.tax_name
, ap_invoice_lines_rec.org_id
, 'N'
, ap_invoice_lines_rec.match_type
, ap_invoice_lines_rec.accounting_date
, ap_invoice_lines_rec.period_name
, ap_invoice_lines_rec.deferred_acctg_flag
, ap_invoice_lines_rec.def_acctg_start_date
, ap_invoice_lines_rec.def_acctg_end_date
, ap_invoice_lines_rec.def_acctg_number_of_periods
, ap_invoice_lines_rec.def_acctg_period_type
, ap_invoice_lines_rec.set_of_books_id
, jai_doc_taxes_rec.tax_amt -- positive tax amount
, ap_invoice_lines_rec.wfapproval_status
, SYSDATE
, ln_user_id
, ln_user_id
, SYSDATE
, ln_login_id
, ''
, ''
, ''
, ''
, '' ;
SELECT
ap_invoice_distributions_s.NEXTVAL
INTO
ln_inv_dist_id
FROM DUAL;
EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
USING ap_invoice_lines_rec.accounting_date
, 'N'
, 'U' -- assets_addition_flag
, 'N' -- assets_tracking_flag
, 'N' -- cash_posted_flag
, 1 -- distribution_line_number
, ln_dist_acct_ccid
, ln_invoice_id
, ln_user_id
, SYSDATE
, GV_CONSTANT_MISCELLANEOUS
, ap_invoice_lines_rec.period_name
, ap_invoice_lines_rec.set_of_books_id
, jai_doc_taxes_rec.tax_amt -- positive tax amount
-- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
, ln_batch_id -- invoice header level
, ln_user_id
, SYSDATE
, tax_rec.tax_name
, ''
, ln_login_id
, ap_invoice_dist_rec.match_status_flag
, 'N' -- posted_flag
, ''
, ap_invoice_dist_rec.reversal_flag
, ap_invoice_dist_rec.program_application_id
, ap_invoice_dist_rec.program_id
, ap_invoice_dist_rec.program_update_date
, ap_invoice_dist_rec.accts_pay_code_combination_id
, ln_inv_dist_id
, -1
, ''
, ''
, ap_invoice_dist_rec.price_var_code_combination_id
, ap_invoice_dist_rec.invoice_price_variance
, ap_invoice_dist_rec.base_invoice_price_variance
, ap_invoice_dist_rec.matched_uom_lookup_code
, ln_max_pro_line_num -- invoice_line_number
, ap_invoice_lines_rec.org_id
, ln_chargeble_acct_ccid
, '' -- project_id
, '' -- task_id
, '' -- expenditure_type
, '' -- expenditure_item_date
, '' -- expenditure_organization_id
, '' -- project_accounting_context
, 'E' -- pa_addition_flag
, ap_invoice_dist_rec.distribution_class
, 'Y';