[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
mod_cr_percentage
, rounding_factor
FROM
jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
SELECT
mod_cr_percentage
, rounding_factor
FROM
jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
SELECT
original_tax_amount --eric added for bug 6957519/6958938/6968839
, modified_tax_amount
, tax_id
FROM
jai_retro_tax_changes
WHERE line_change_id = pn_line_change_id;
SELECT
original_tax_amount
, modified_tax_amount
, tax_id
FROM
jai_retro_tax_changes
WHERE line_change_id = pn_line_change_id
AND tax_type IN ( JAI_CONSTANTS.tax_type_excise
, JAI_CONSTANTS.tax_type_exc_additional
, JAI_CONSTANTS.tax_type_exc_other
, JAI_CONSTANTS.tax_type_exc_edu_cess
, JAI_CONSTANTS.tax_type_sh_exc_edu_cess
)
AND recoverable_flag ='Y';
SELECT
original_tax_amount
, modified_tax_amount
, tax_id
FROM
jai_retro_tax_changes jrtc
WHERE EXISTS( SELECT
'X'
FROM
JAI_RGM_DEFINITIONS jr
, JAI_RGM_REGISTRATIONS jrr
WHERE jr.regime_id = jrr.regime_id
AND jr.regime_code = jai_constants.vat_regime
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrtc.tax_type = jrr.attribute_code
)
AND recoverable_flag ='Y'
AND line_change_id = pn_line_change_id;
FOR tax_rec IN (SELECT
jrtc.tax_type
, (jrtc.modified_tax_amount - jrtc.original_tax_amount) tax_amount
, nvl(jrtc.recoverable_flag, 'N') modvat_flag
, nvl(jrtc.currency_code, jai_rcv_trx_processing_pkg.gv_func_curr) currency
, nvl(decode(pv_breakup_type, 'RG23D', 100, jtc.mod_cr_percentage), 0) mod_cr_percentage
, nvl(jtc.rounding_factor, 0) rnd
FROM
jai_retro_tax_changes jrtc
, jai_cmn_taxes_all jtc
, jai_retro_line_changes jrlc
WHERE jrlc.doc_line_id = pn_shipment_line_id
AND jrtc.line_change_id = jrlc.line_change_id
AND jrlc.doc_type = 'RECEIPT'
AND jtc.tax_id = jrtc.tax_id
AND jrlc.line_change_id = pn_line_change_id
)
LOOP
IF tax_rec.currency <> jai_rcv_trx_processing_pkg.gv_func_curr
THEN
ln_curr_conv := NVL(pn_curr_conv_rate, 1);
SELECT
*
FROM
jai_rcv_transactions
WHERE transaction_id = pn_transaction_id;
SELECT
*
FROM
jai_rcv_lines
WHERE transaction_id = pn_transaction_id;
SELECT
destination_type_code
, shipment_line_id
FROM rcv_transactions
WHERE transaction_id = pn_transaction_id;
SELECT
primary_cost_method
, expense_account
, purchase_price_var_account
, organization_code
FROM
mtl_parameters
WHERE
organization_id = pn_organization_id;
SELECT
retroprice_adj_account_id
, receiving_account_id
FROM
rcv_parameters
WHERE
organization_id = pn_organization_id;
SELECT gd.period_name
INTO lv_period_name
FROM
gl_ledgers gle
, gl_periods gd
WHERE gle.ledger_id = func_curr_det_rec.ledger_id
AND gd.period_set_name = gle.period_set_name
-- AND SYSDATE BETWEEN gd.start_date AND gd.end_date bug #6788048
--eric changed on Feb 1, 2008 for bug #6788048 begin
---------------------------------------------
AND SYSDATE >=TRUNC(gd.start_date)
AND SYSDATE < TRUNC(gd.end_date+1)
---------------------------------------------
--eric changed on Feb 1, 2008 for bug #6788048 end
AND gd.adjustment_period_flag = 'N';
SELECT receipt_num
INTO lv_receipt_num
FROM jai_rcv_lines
WHERE shipment_line_id = ln_shipment_line_id;
jai_rcv_journal_pkg.insert_row
( p_organization_id => pn_organization_id
, p_organization_code => func_curr_det_rec.organization_code
, p_receipt_num => lv_receipt_num
, p_transaction_id => pn_transaction_id
, p_transaction_date => SYSDATE
, p_shipment_line_id => ln_shipment_line_id
, p_acct_type => 'REGULAR'
, p_acct_nature => 'Average Costing'
, p_source_name => 'Inventory India'
, p_category_name => 'MTL'
, p_code_combination_id => ln_retroprice_adj_account_id
, p_entered_dr => pn_amount
, p_entered_cr => NULL
, p_transaction_type => 'DELIVER'
, p_period_name => lv_period_name
, p_currency_code => 'INR'
, p_currency_conversion_type => NULL
, p_currency_conversion_date => NULL
, p_currency_conversion_rate => NULL
, p_simulate_flag => 'N'
, p_process_status => lv_process_status
, p_process_message => lv_process_message
, p_reference_name => 'RETRO CENVAT CLAIMS ' || pn_version_number
, p_reference_id => 1
);
jai_rcv_journal_pkg.insert_row
( p_organization_id => pn_organization_id
, p_organization_code => func_curr_det_rec.organization_code
, p_receipt_num => lv_receipt_num
, p_transaction_id => pn_transaction_id
, p_transaction_date => SYSDATE
, p_shipment_line_id => ln_shipment_line_id
, p_acct_type => 'REGULAR'
, p_acct_nature => 'Average Costing'
, p_source_name => 'Inventory India'
, p_category_name => 'MTL'
, p_code_combination_id => ln_receiving_account_id
, p_entered_dr => NULL
, p_entered_cr => pn_amount
, p_transaction_type => 'DELIVER'
, p_period_name => lv_period_name
, p_currency_code => 'INR'
, p_currency_conversion_type => NULL
, p_currency_conversion_date => NULL
, p_currency_conversion_rate => NULL
, p_simulate_flag => 'N'
, p_process_status => lv_process_status
, p_process_message => lv_process_message
, p_reference_name => 'RETRO CENVAT CLAIMS ' || pn_version_number
, p_reference_id => 2
);
SELECT
transaction_id
, organization_id
FROM
Rcv_Transactions
WHERE shipment_line_id = pn_shipment_line_id
AND transaction_type = 'DELIVER';
SELECT
NVL(currency_conversion_rate,1)
, organization_id
INTO
ln_curr_conv_rate
, ln_organization_id
FROM
Rcv_Transactions
WHERE shipment_line_id = pn_shipment_line_id
AND transaction_type = 'RECEIVE';
SELECT
SUM((modified_tax_amount - original_tax_amount) * DECODE(currency_code,'INR',1, ln_curr_conv_rate)) tax_diff_tot
INTO
ln_tax_diff_tot
FROM
Jai_Retro_Tax_Changes
WHERE line_change_id = pn_line_change_id ;/*rchandan. removed sub query and replaced with pn_line_change_id*/
/* eric deleted for a bug on Jan 22,2008
IF lv_currency <> 'INR'
THEN
ln_tax_diff_tot := ln_tax_diff_tot * ln_curr_conv_rate;
SELECT
receiving_account_id
INTO
ln_recv_acct_id
FROM
Rcv_Parameters
WHERE organization_id = ln_organization_id;
SELECT
Ap_Accrual_Account
INTO
ln_ap_accrual_acc
FROM
Mtl_Parameters
WHERE organization_id = ln_organization_id;
UPDATE
Jai_Retro_Line_Changes
SET
Excise_Action = 'UNCLAIM'
WHERE line_change_id =pn_line_change_id;
UPDATE
Jai_Retro_Line_Changes
SET
Vat_Action = 'UNCLAIM'
WHERE line_change_id = pn_line_change_id;
SELECT
*
FROM
rcv_transactions
WHERE
transaction_id = pn_transaction_id;
SELECT
*
FROM
jai_rcv_rgm_lines
WHERE shipment_line_id = pn_shipment_line_id ;
SELECT
COUNT(*)
FROM
jai_rcv_rgm_claims
WHERE rcv_rgm_line_id = pn_rcv_rgm_line_id
AND tax_id = NVL(pn_tax_id,tax_id); --added by eric for bug#6968733 on Apr 15,2008
SELECT
COUNT(*)
FROM
jai_rcv_rgm_claims
WHERE rcv_rgm_line_id = pn_rcv_rgm_line_id
AND tax_id = NVL(pn_tax_id,tax_id)
AND claimed_amount IS NOT NULL;
SELECT
*
FROM
jai_retro_tax_changes jrtc
WHERE EXISTS ( SELECT
'X'
FROM
JAI_RGM_DEFINITIONS jr
, JAI_RGM_REGISTRATIONS jrr
WHERE jr.regime_id = jrr.regime_id
AND jr.regime_code = jai_constants.vat_regime
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrtc.tax_type =jrr.attribute_code
)
AND jrtc.recoverable_flag ='Y'
AND jrtc.line_change_id = pn_line_change_id;
SELECT
SUM( a.installment_amount - a.claimed_amount ) claim_amount
, tax_type
, MIN(claim_schedule_id) claim_schedule_id
FROM
jai_rcv_rgm_claims A
WHERE rcv_rgm_line_id = pn_rcv_rgm_line_id
AND claimed_amount IS NOT NULL
GROUP BY a.tax_type;
SELECT
jrrl.organization_id
, jrrl.location_id
, jrl.receipt_num
, jrd.regime_id
FROM
jai_rcv_rgm_lines jrrl
, jai_rgm_definitions jrd
, jai_rcv_lines jrl
WHERE jrrl.rcv_rgm_line_id = pn_rcv_rgm_line_id
AND jrrl.shipment_line_id = jrl.shipment_line_id
AND jrrl.regime_code = jrd.regime_code;
SELECT receipt_num
INTO lv_receipt_number
FROM jai_rcv_lines
WHERE shipment_line_id = pn_shipment_line_id;
UPDATE jai_rcv_rgm_claims
SET installment_amount = installment_amount + ln_instl_diff
WHERE rcv_rgm_line_id = jai_rcv_rgm_lines_rec.rcv_rgm_line_id
AND tax_id = jai_retor_tax_changes_rec.tax_id;
UPDATE jai_rcv_rgm_lines
SET recoverable_amount = recoverable_amount + ln_tot_instl_amt
--WHERE rcv_rgm_line_id = jai_rcv_rgm_lines.rcv_rgm_line_id;
UPDATE jai_rcv_rgm_lines
SET recovered_amount = recovered_amount + ln_tot_clm_instl_amt
--WHERE rcv_rgm_line_id = jai_rcv_rgm_lines.rcv_rgm_line_id;
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry
(
pn_repository_id => ln_repository_id, -- OUT parameter
pn_regime_id => ln_regime_id, -- fetched above
pv_tax_type => claim_schedule_rec.tax_type, -- current tax type in the LOOP
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => ln_organization_id, -- fetched above
pn_location_id => ln_location_id, -- fetched above
pv_source => jai_constants.source_rcv,
pv_source_trx_type => 'RETROACTIVE VAT CLAIM:'||to_char(pn_version_number),
pv_source_table_name => 'RCV_TRANSACTIONS',
pn_source_id => pn_transaction_id,
pd_transaction_date => trunc(sysdate),
pv_account_name => jai_constants.recovery,
pn_charge_account_id => ln_code_combination_id,
pn_balancing_account_id => ln_interim_recovery_account,
pn_credit_amount => claim_schedule_rec.claim_amount, -- current claim amount in the LOOP
pn_debit_amount => claim_schedule_rec.claim_amount,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => claim_schedule_rec.claim_schedule_id,-- Current claim_schedule_id in LOOP
pn_batch_id => NULL,
pn_inv_organization_id => ln_organization_id, -- fetched above
pv_invoice_no => pv_supp_vat_inv_no,
pd_invoice_date => pd_supp_vat_inv_date,
pv_called_from => 'JAI_RETRO_PRC_PKG.DO_VAT_CLAIM',
pv_process_flag => lv_process_status,
pv_process_message => lv_process_message,
pv_attribute_context => NULL,
pv_attribute1 => NULL,
pv_attribute2 => NULL,
pv_attribute3 => NULL,
pv_attribute4 => NULL,
pv_attribute5 => NULL
);
UPDATE jai_rcv_rgm_claims
SET claimed_amount = installment_amount
WHERE rcv_rgm_line_id = jai_rcv_rgm_lines_rec.rcv_rgm_line_id
AND tax_type = claim_schedule_rec.tax_type -- Current tax type in the loop
AND claimed_amount IS NOT NULL;
UPDATE
jai_retro_line_changes
SET
vat_action = 'CLAIM'
WHERE line_change_id = pn_line_change_id;
SELECT
*
FROM
jai_rcv_transactions
WHERE
transaction_id = pn_transaction_id;
SELECT
cenvat_claimed_ptg
FROM
jai_rcv_cenvat_claims
WHERE transaction_id = pn_transaction_id;
UPDATE
jai_retro_line_changes
SET
excise_action ='UNCLAIM'
WHERE
line_change_id = pn_line_change_id;
UPDATE
JAI_CMN_RG_23AC_II_TRXS
SET
excise_invoice_no = pv_supp_exc_inv_no
, excise_invoice_date = pd_supp_exc_inv_date
WHERE register_id = xv_register_id;
UPDATE
jai_retro_line_changes
SET
excise_action = 'CLAIM'
WHERE line_change_id = pn_line_change_id;
PROCEDURE Process_Retroactive_Update
( errbuf OUT NOCOPY VARCHAR2
, retcode OUT NOCOPY VARCHAR2
, pn_vendor_id IN NUMBER
, pn_vendor_site_id IN NUMBER DEFAULT NULL
, pn_po_header_id IN NUMBER DEFAULT NULL
, pv_from_eff_date IN VARCHAR2 DEFAULT NULL
, pv_cenvat_action IN VARCHAR2 DEFAULT NULL
, pv_supp_exc_inv_no IN VARCHAR2 DEFAULT NULL
, pv_supp_exc_inv_date IN VARCHAR2 DEFAULT NULL
, pv_vat_action IN VARCHAR2 DEFAULT NULL
, pv_supp_vat_inv_no IN VARCHAR2 DEFAULT NULL
, pv_supp_vat_inv_date IN VARCHAR2 DEFAULT NULL
, pv_process_downward IN VARCHAR2 DEFAULT NULL
)
IS
jai_rcv_transactions_rec jai_rcv_transactions%ROWTYPE;
SELECT
*
FROM
rcv_transactions rt
WHERE rt.transaction_type = 'RECEIVE'
AND rt.po_line_location_id =pn_line_location_id
AND creation_date >= NVL(ld_from_eff_date,creation_date)-- eric changed according to review comment #36
AND NOT EXISTS ( SELECT
'X'
FROM
jai_retro_line_changes jrlc
WHERE jrlc.doc_line_id = rt.shipment_line_id
AND jrlc.source_line_change_id = pn_line_change_id
AND jrlc.doc_type = 'RECEIPT'
);
SELECT
jrlt.*,
jcta.adhoc_flag
FROM
jai_rcv_line_taxes jrlt
, jai_cmn_taxes_all jcta
WHERE jrlt.transaction_id = pn_transaction_id
AND jrlt.tax_id = jcta.tax_id;
SELECT
line_change_id
, doc_type
, doc_header_id
, doc_line_id
, line_location_id
, from_header_id
, from_line_id
, doc_version_number
, source_line_change_id
, price_change_date
, inventory_item_id
, organization_id
, original_unit_price
, modified_unit_price
, receipt_processed_flag
, excise_action
, vat_action
, excise_invoice_no
, excise_invoice_date
, vat_invoice_no
, vat_invoice_date
, retro_request_id
, doc_number
, vendor_id
, vendor_site_id
FROM
jai_retro_line_changes jrlc
WHERE vendor_id = NVL(pn_vendor_id,vendor_id)
AND vendor_site_id = NVL(pn_vendor_site_id,vendor_site_id)
AND ( (doc_type = 'RELEASE' AND doc_header_id = NVL(pn_po_header_id ,doc_header_id))
OR (doc_type = 'STANDARD PO' AND from_header_id = NVL(pn_po_header_id ,from_header_id))
)
AND NVL(receipt_processed_flag,'N') <>jai_constants.yes
--AND creation_date <= NVL(ld_from_eff_date,creation_date) , eric remomved according to review comment #36
AND (original_unit_price < modified_unit_price OR pv_process_downward = jai_constants.yes )
AND doc_version_number =( SELECT MAX(doc_version_number)
FROM jai_retro_line_changes a
WHERE a.line_location_id = jrlc.line_location_id
) ;
lv_procedure_name VARCHAR2(40):='Process_Retroactive_Update';
FND_FILE.PUT_LINE(fnd_file.log, ' Please Claim it before running the concurrent JAI Retroactive price Update');
SELECT
jai_retro_line_changes_s.nextval
INTO
ln_retro_line_changes_id
FROM
DUAL;
SELECT
NVL(MAX(doc_version_number),0) + 1 /*rchandan. Moved +1 outside NVL*/
INTO
ln_retro_line_changes_version
FROM
jai_retro_line_changes
WHERE doc_header_id = jai_rcv_lines_rec.shipment_header_id
AND doc_line_id = jai_rcv_lines_rec.shipment_line_id
AND doc_type = 'RECEIPT';
INSERT INTO jai_retro_line_changes
( line_change_id
, doc_type
, doc_header_id
, doc_line_id
, line_location_id
, doc_version_number
, source_line_change_id -- added for indicating receipt processed or not
, price_change_date
, inventory_item_id
, organization_id
, original_unit_price
, modified_unit_price
, receipt_processed_flag
, excise_action
, excise_invoice_no
, excise_invoice_date
, vat_action
, vat_invoice_no
, vat_invoice_date
, retro_request_id
, doc_number
, vendor_id
, vendor_site_id
, creation_date
, created_by
, last_update_date
, last_update_login
, last_updated_by
, object_version_number
)
VALUES
( ln_retro_line_changes_id --=> jai_retro_line_changes_s.nextval
, 'RECEIPT' --=> 'RECEIPT'
, jai_rcv_lines_rec.shipment_header_id --=> shipment_header_id from jai_rcv_lines
, jai_rcv_lines_rec.shipment_line_id --=> shipment_Line_Id from jai_rcv_lines
, NULL --=> NULL
, ln_retro_line_changes_version --=> Increment previous version number of this receipt,issue??
, jai_retro_line_change_rec.line_change_id --=> source_line_change_id ,The line_change_id of latest shipment
, SYSDATE --=> Sysdate
, jai_rcv_lines_rec.inventory_item_id --=> inventory_item_id from jai_rcv_lines
, jai_rcv_lines_rec.organization_id --=> Organization_id from jai_rcv_lines
, jai_retro_line_change_rec.original_unit_price --=> original_unit_price from jai_retro_line_changes of the current Release
, jai_retro_line_change_rec.modified_unit_price --=> Modified_unit_price from jai_retro_line_changes of the current Release
, 'Y' --=> receipt_processed_flag :'Y'
, pv_cenvat_action --=> pv_cenvat_action
, pv_supp_exc_inv_no --=> pv_supp_exc_inv_no
, ld_supp_exc_inv_date --=> pd_supp_exc_inv_date
, pv_vat_action --=> pv_vat_action
, pv_supp_vat_inv_no --=> pv_supp_vat_inv_no
, ld_supp_vat_inv_date --=> pd_supp_vat_inv_date
, fnd_global.conc_request_id --=> fnd_global.conc_request_id
, jai_rcv_lines_rec.receipt_num --=> receipt_number from jai_rcv_lines
, rcv_transactions_rec.vendor_id --=> vendor_id from rcv_transactions
, rcv_transactions_rec.vendor_site_id --=> vendor_site_id from rcv_transactions
, SYSDATE --=> sysdate
, FND_GLOBAL.USER_ID --=> fnd_global.user_id
, SYSDATE --=> sysdate
, FND_GLOBAL.LOGIN_ID --=> fnd_global.login_id
, FND_GLOBAL.USER_ID --=> fnd_global.user_id
, NULL --=> NULL
);
FND_FILE.PUT_LINE(fnd_file.log, ' Table jai_retro_line_changes inserted ');
INSERT INTO jai_retro_tax_changes
( tax_change_id
, line_change_id
, tax_line_no
, tax_id
, tax_name
, tax_type
, currency_code
, original_tax_amount
, modified_tax_amount
, Recoverable_flag
, adhoc_flag
, third_party_flag
, creation_date
, created_by
, last_update_date
, last_update_login
, last_updated_by
, object_version_number
)
VALUES
( jai_retro_tax_changes_s.nextval --=> jai_retro_tax_changes_s.nextval
, ln_retro_line_changes_id --=> from jai_retro_line_changes
, jai_rcv_line_taxes_rec.tax_line_no --=> from jai_rcv_line_taxes
, jai_rcv_line_taxes_rec.tax_id --=> from jai_rcv_line_taxes
, jai_rcv_line_taxes_rec.tax_name --=> from jai_rcv_line_taxes
, jai_rcv_line_taxes_rec.tax_type --=> from jai_rcv_line_taxes
, jai_rcv_line_taxes_rec.currency --=> from jai_rcv_line_taxes
, jai_rcv_line_taxes_rec.tax_amount --=> tax_amount from jai_rcv_line_taxes
, NULL --=> NULL ,modified_tax_amount
, jai_rcv_line_taxes_rec.modvat_flag --=> modvat_flag from jai_rcv_line_taxes
, jai_rcv_line_taxes_rec.adhoc_flag --=> adhoc_flag from jai_cmn_taxes_all. Join using tax_id
, jai_rcv_line_taxes_rec.third_party_flag --=> from jai_rcv_line_taxes
, SYSDATE --=> sysdate
, fnd_global.user_id --=> fnd_global.user_id
, SYSDATE --=> sysdate
, fnd_global.login_id --=> fnd_global.login_id
, fnd_global.user_id --=> fnd_global.user_id
, NULL --=> NULL
) ;
FND_FILE.PUT_LINE(fnd_file.log, ' Table jai_retro_tax_changes inserted ');
UPDATE
jai_retro_tax_changes jrtc
SET
modified_tax_amount = ( SELECT tax_amount
FROM jai_rcv_line_taxes jrlt
WHERE jrlt.shipment_header_id = jai_rcv_lines_rec.shipment_header_id
AND jrlt.shipment_line_id = jai_rcv_lines_rec.shipment_line_id
AND jrlt.tax_id = jrtc.tax_id
)
WHERE line_change_id = ln_retro_line_changes_id
AND tax_line_no = jai_rcv_line_taxes_rec.tax_line_no
AND tax_id = jai_rcv_line_taxes_rec.tax_id ;
Fnd_File.Put_Line(FND_FILE.LOG,' New tax has been updated to the table jai_retro_tax_changes ');
UPDATE
jai_retro_line_changes
SET
receipt_processed_flag = 'Y'
WHERE
line_change_id = jai_retro_line_change_rec.line_change_id;
END Process_Retroactive_Update;
PROCEDURE Insert_Price_Changes
( pr_old IN lc_rec%TYPE
, pr_new IN lc_rec%TYPE
, pv_process_flag OUT NOCOPY VARCHAR2
, pv_process_message OUT NOCOPY VARCHAR2
)
IS
CURSOR cur_loc_line_taxes
IS
SELECT
jpt.tax_line_no
, jpt.tax_id
, jcta.tax_name
, jcta.tax_type
, jpt.currency
, jpt.tax_amount
, jcta.adhoc_flag
, jpt.modvat_flag
, jpt.vendor_id
FROM
jai_po_taxes jpt
, jai_cmn_taxes_all jcta
WHERE jpt.tax_id = jcta.tax_id
AND jpt.line_location_id = pr_old.line_location_id;
SELECT
JAI_RETRO_LINE_CHANGES_S.NEXTVAL
INTO
ln_retro_line_changes_id
FROM dual;
SELECT
NVL(max(doc_version_number), 0) + 1 /*added max and replaced -1 with 0*/
INTO
ln_revision_num
FROM
JAI_RETRO_LINE_CHANGES jrlc
WHERE jrlc.doc_header_id = pr_old.po_header_id
AND jrlc.doc_line_id = pr_old.po_line_id
AND jrlc.line_location_id = pr_old.line_location_id /*added by rchandan*/
AND jrlc.doc_type IN ('RELEASE', 'STANDARD PO');
SELECT
item_id
INTO
ln_item_id
FROM
po_lines_all
WHERE po_line_id = pr_old.po_line_id;
SELECT
segment1
, vendor_id
, vendor_site_id
INTO
lv_doc_number
, ln_vendor_id
, ln_vendor_site_id
FROM
po_headers_all
WHERE po_header_id = pr_old.from_header_id;
SELECT
segment1
, vendor_id
, vendor_site_id
INTO
lv_doc_number
, ln_vendor_id
, ln_vendor_site_id
FROM
po_headers_all
WHERE po_header_id = pr_old.po_header_id;
INSERT INTO JAI_RETRO_LINE_CHANGES
( LINE_CHANGE_ID
, DOC_TYPE
, DOC_HEADER_ID
, DOC_LINE_ID
, LINE_LOCATION_ID
, FROM_HEADER_ID
, FROM_LINE_ID
, DOC_VERSION_NUMBER
, PRICE_CHANGE_DATE
, INVENTORY_ITEM_ID
, ORGANIZATION_ID
, ORIGINAL_UNIT_PRICE
, MODIFIED_UNIT_PRICE
, RECEIPT_PROCESSED_FLAG
, EXCISE_ACTION
, VAT_ACTION
, EXCISE_INVOICE_NO
, EXCISE_INVOICE_DATE
, VAT_INVOICE_NO
, VAT_INVOICE_DATE
, RETRO_REQUEST_ID
, DOC_NUMBER
, VENDOR_ID
, VENDOR_SITE_ID
, CREATION_DATE
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LAST_UPDATED_BY
, CREATED_BY
, OBJECT_VERSION_NUMBER
)
VALUES
( ln_retro_line_changes_id
, 'STANDARD PO'
, pr_old.po_header_id
, pr_old.po_line_id
, pr_old.line_location_id
, pr_old.from_header_id
, pr_old.from_line_id
, ln_revision_num
, pr_new.retroactive_date
, ln_item_id
, pr_old.ship_to_organization_id
, pr_old.price_override
, pr_new.price_override
, 'N'
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, lv_doc_number
, ln_vendor_id
, ln_vendor_site_id
, SYSDATE
, SYSDATE
, fnd_global.login_id
, fnd_global.user_id
, fnd_global.user_id
, NULL
);
INSERT INTO JAI_RETRO_LINE_CHANGES
( LINE_CHANGE_ID
, DOC_TYPE
, DOC_HEADER_ID
, DOC_LINE_ID
, LINE_LOCATION_ID
, FROM_HEADER_ID
, FROM_LINE_ID
, DOC_VERSION_NUMBER
, PRICE_CHANGE_DATE
, INVENTORY_ITEM_ID
, ORGANIZATION_ID
, ORIGINAL_UNIT_PRICE
, MODIFIED_UNIT_PRICE
, RECEIPT_PROCESSED_FLAG
, EXCISE_ACTION
, VAT_ACTION
, EXCISE_INVOICE_NO
, EXCISE_INVOICE_DATE
, VAT_INVOICE_NO
, VAT_INVOICE_DATE
, RETRO_REQUEST_ID
, DOC_NUMBER
, VENDOR_ID
, VENDOR_SITE_ID
, CREATION_DATE
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LAST_UPDATED_BY
, CREATED_BY
, OBJECT_VERSION_NUMBER
)
VALUES
( ln_retro_line_changes_id
, 'RELEASE'
, pr_old.po_header_id
, pr_old.po_line_id
, pr_old.line_location_id
, pr_old.po_header_id
, pr_old.po_line_id
, ln_revision_num
, pr_new.retroactive_date
, ln_item_id
, pr_old.ship_to_organization_id
, pr_old.price_override
, pr_new.price_override
, 'N'
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, lv_doc_number
, ln_vendor_id
, ln_vendor_site_id
, SYSDATE
, SYSDATE
, fnd_global.login_id
, fnd_global.user_id
, fnd_global.user_id
, NULL
);
pv_process_message := 'When inserting line change history.'||SQLERRM;
SELECT
JAI_RETRO_TAX_CHANGES_S.NEXTVAL
INTO
ln_retro_tax_changes_id
FROM dual;
INSERT INTO JAI_RETRO_TAX_CHANGES
( TAX_CHANGE_ID
, LINE_CHANGE_ID
, TAX_LINE_NO
, TAX_ID
, TAX_NAME
, TAX_TYPE
, CURRENCY_CODE
, ORIGINAL_TAX_AMOUNT
, MODIFIED_TAX_AMOUNT
, RECOVERABLE_FLAG
, ADHOC_FLAG
, THIRD_PARTY_FLAG
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LAST_UPDATED_BY
, OBJECT_VERSION_NUMBER
)
VALUES
( ln_retro_tax_changes_id
, ln_retro_line_changes_id
, tax_rec.tax_line_no
, tax_rec.tax_id
, tax_rec.tax_name
, tax_rec.tax_type
, tax_rec.currency
, tax_rec.tax_amount
, -99999
, tax_rec.modvat_flag
, tax_rec.adhoc_flag
, lv_third_party_flag
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
, fnd_global.user_id
, NULL
);
pv_process_message := 'When inserting tax line change history.'||SQLERRM;
END Insert_Price_Changes;
PROCEDURE Update_Price_Changes
( pn_tax_amt IN NUMBER
, pn_line_no IN NUMBER
, pn_line_loc_id IN NUMBER
, pv_process_flag OUT NOCOPY VARCHAR2
, pv_process_message OUT NOCOPY VARCHAR2
)
IS
BEGIN
pv_process_flag := 'SS';
UPDATE
JAI_RETRO_TAX_CHANGES
SET
modified_tax_amount = pn_tax_amt
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, last_update_login = fnd_global.login_id
WHERE tax_line_no = pn_line_no
AND line_change_id = (SELECT
line_change_id
FROM
JAI_RETRO_LINE_CHANGES jrpc
WHERE jrpc.Line_location_id = pn_line_loc_id
AND jrpc.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
AND jrpc.Doc_version_number = (SELECT
MAX(Doc_version_number)
FROM
JAI_RETRO_LINE_CHANGES jrpc1
WHERE jrpc1.Line_location_id = pn_line_loc_id
AND jrpc1.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
)
);
END Update_Price_Changes;