[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PROCEDURE - insert_ap_inv_interface()
|
| DESCRIPTION
| Private procedure called from Create_Instructions. Program identifies PO
| suppliers that are listed in the PO view within the report parameters.
| It then populates the Payables Open Interface Table with one instruction
| record per supplier. Each header record will include: a source of PPA,
| the supplier ID, userid of the PO user and a unique group_id for the CADIP.
|
| PARAMETERS
| p_group_id - Unique group_id generated in Create_Instructions
| p_org_id - Org Id of the PO User
| p_po_user_id - PO's User Id
| p_vendor_id - Vendor Id
| p_vendor_site_id - Vendor Site Id
| p_po_header_id - Valid PO's Header Id
| p_po_release_id - Valid PO Release Id
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 29-JUL-2003 dgulraja Creation
|
*============================================================================*/
PROCEDURE insert_ap_inv_interface (
p_group_id IN VARCHAR2,
p_org_id IN NUMBER,
p_po_user_id IN NUMBER,
p_vendor_id IN NUMBER,
p_vendor_site_id IN NUMBER,
p_po_header_id IN NUMBER,
p_po_release_id IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_vendor_id_list id_list_type;
l_api_name CONSTANT VARCHAR2(200) := 'INSERT_AP_INV_INTERFACE';
SELECT DISTINCT pd.vendor_id,
pv.segment1, -- supplier number
pv.vendor_name
FROM po_ap_retroactive_dist_v pd,
po_vendors pv
WHERE mo_global.check_access(pd.org_id) = 'Y'
AND pd.vendor_id = pv.vendor_id
AND pd.invoice_adjustment_flag = 'R'
AND pd.org_id = p_org_id
AND pd.vendor_id = DECODE(p_vendor_id, NULL,
pd.vendor_id, p_vendor_id)
AND pd.po_header_id = DECODE(p_po_header_id, NULL,
pd.po_header_id, p_po_header_id)
-- Commented out until bug 4484058 is resolved.
AND pd.vendor_site_id = DECODE(p_vendor_site_id, NULL,
pd.vendor_site_id, p_vendor_site_id)
AND NVL(pd.po_release_id, 1) = DECODE(p_po_release_id, NULL,
NVL(pd.po_release_id,1),
p_po_release_id);*/
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.INSERT_AP_INV_INTERFACE(+)');
sql_stmt := 'SELECT DISTINCT pd.vendor_id,
pv.segment1, -- supplier number
pv.vendor_name
FROM po_ap_retroactive_dist_v pd,
po_vendors pv
WHERE mo_global.check_access(pd.org_id) = ''Y''
AND pd.vendor_id = pv.vendor_id
AND pd.invoice_adjustment_flag = ''R'' ' ;
debug_info := 'Step 4b.Insert into ap_invoices_interface';
INSERT INTO ap_invoices_interface
(org_id,
invoice_id,
source,
vendor_id,
vendor_num,
vendor_name,
group_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (p_org_id,
AP_INVOICES_INTERFACE_S.nextval,
'PPA',
l_vendor_id_list(i),
l_vendor_num_list(i),
l_vendor_name_list(i),
p_group_id,
p_po_user_id,
SYSDATE, --creation_date
FND_GLOBAL.user_id, --last_updated_by
SYSDATE, --last_update_date
FND_GLOBAL.conc_login_id, --last_update_login
FND_GLOBAL.conc_request_id --request_id
);
l_vendor_id_list.DELETE;
l_vendor_num_list.DELETE;
l_vendor_name_list.DELETE;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.INSERT_AP_INV_INTERFACE(-)');
END insert_ap_inv_interface;
| PROCEDURE - insert_ap_inv_lines_interface()
|
| DESCRIPTION
| Private procedure called from Create_Instructions. Program identifies
| for header record, a unique line record for each retropriced PO shipment.
|
| PARAMETERS
| p_group_id - Unique group_id generated in Create_Instructions
| p_org_id - Org Id of the PO User
| p_po_user_id - PO's User Id
| p_vendor_id - Vendor Id
| p_vendor_site_id - Vendor Site Id
| p_po_header_id - Valid PO's Header Id
| p_po_release_id - Valid PO Release Id
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 29-JUL-2003 dgulraja Creation
|
*============================================================================*/
PROCEDURE insert_ap_inv_lines_interface (
p_group_id IN VARCHAR2,
p_org_id IN NUMBER,
p_po_user_id IN NUMBER,
p_vendor_id IN NUMBER,
p_vendor_site_id IN NUMBER,
p_po_header_id IN NUMBER,
p_po_release_id IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_po_line_loc_id_list id_list_type;
SELECT DISTINCT pd.line_location_id,
pll.shipment_num,
pd.po_header_id,
ph.segment1,
pd.po_line_id,
pl.line_num,
pd.po_release_id,
pr.release_num,
pd.price_override,
aii.invoice_id
FROM po_ap_retroactive_dist_v pd,
po_headers_all ph,
po_releases_all pr,
po_lines_all pl,
po_line_locations_all pll,
ap_invoices_interface aii
WHERE mo_global.check_access(pd.org_id) = 'Y'
AND pd.po_header_id = ph.po_header_id
AND pd.po_release_id = pr.po_release_id(+)
AND pd.po_line_id = pl.po_line_id
AND pd.line_location_id = pll.line_location_id
AND pd.invoice_adjustment_flag = 'R'
AND pd.org_id = aii.org_id
AND aii.vendor_id = pd.vendor_id
AND aii.source = 'PPA'
AND aii.group_id = p_group_id
AND aii.org_id = p_org_id
AND pd.vendor_id = DECODE(p_vendor_id, NULL,
pd.vendor_id, p_vendor_id)
AND pd.po_header_id = DECODE(p_po_header_id, NULL,
pd.po_header_id, p_po_header_id)
AND pd.vendor_site_id = DECODE(p_vendor_site_id, NULL,
pd.vendor_site_id, p_vendor_site_id)
AND NVL(pd.po_release_id, 1) = DECODE(p_po_release_id, NULL,
NVL(pd.po_release_id,1),
p_po_release_id);
l_api_name CONSTANT VARCHAR2(200) := 'insert_ap_inv_lines_interface';
current_calling_sequence := 'insert_ap_inv_lines_interface<-'||P_calling_sequence;
'AP_RETRO_PRICING_PKG.INSERT_AP_INV_LINES_INTERFACE(+)');
debug_info := 'Step 5b.Insert into ap_invoice_lines_interface';
INSERT INTO ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
po_header_id,
po_number,
po_line_id,
po_line_number,
-- po_release_id,
-- release_num,
po_line_location_id,
po_shipment_num,
unit_price,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (l_invoice_id_list(i),
AP_INVOICE_LINES_INTERFACE_S.nextval,
l_po_header_id_list(i),
l_po_number_list(i),
l_po_line_id_list(i),
l_po_line_number_list(i),
--l_po_release_id_list(i),
--l_release_num_list(i),
l_po_line_loc_id_list(i),
l_po_shipment_num_list(i),
l_unit_price_list(i),
p_po_user_id,
SYSDATE, --creation_date
FND_GLOBAL.user_id, --last_updated_by
SYSDATE, --last_update_date
FND_GLOBAL.conc_login_id --last_update_login
);
l_po_line_loc_id_list.DELETE;
l_po_header_id_list.DELETE;
l_po_line_id_list.DELETE;
l_po_release_id_list.DELETE;
l_invoice_id_list.DELETE;
l_unit_price_list.DELETE;
l_po_number_list.DELETE;
l_po_line_number_list.DELETE;
l_release_num_list.DELETE;
l_po_shipment_num_list.DELETE;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.INSERT_AP_INV_LINES_INTERFACE(-)');
END insert_ap_inv_lines_interface;
SELECT DISTINCT pd.org_id
FROM po_ap_retroactive_dist_v pd,
po_vendors pv
WHERE mo_global.check_access(pd.org_id) = 'Y'
AND pd.vendor_id = pv.vendor_id
AND pd.invoice_adjustment_flag = 'R'
AND pd.vendor_id = DECODE(p_vendor_id, NULL,
pd.vendor_id, p_vendor_id)
AND pd.po_header_id = DECODE(p_po_header_id, NULL,
pd.po_header_id, p_po_header_id)
AND pd.vendor_site_id = DECODE(p_vendor_site_id, NULL,
pd.vendor_site_id, p_vendor_site_id)
AND NVL(pd.po_release_id, 1) = DECODE(p_po_release_id, NULL,
NVL(pd.po_release_id,1),
p_po_release_id);
SELECT ap_batches_s.nextval
INTO l_batch_id
FROM sys.dual;
/*select decode(nvl(fpov1.profile_option_value,'N'),
'N', lc.displayed_field)
INTO l_batch_name
FROM fnd_profile_option_values fpov1,
fnd_profile_options fpo1,
ap_lookup_codes lc
WHERE fpov1.profile_option_id = fpo1.profile_option_id
AND fpo1.profile_option_name ='AP_USE_INV_BATCH_CONTROLS'
AND fpov1.level_id = 10004
AND lc.lookup_type ='NLS REPORT PARAMETER'
and lc.lookup_code = 'NA'
AND rownum = 1;*/
select lc.displayed_field
into l_batch_name
from ap_lookup_codes lc
where lc.lookup_type = 'NLS REPORT PARAMETER'
and lc.lookup_code = 'NA';
SELECT NVL(ALLOW_PAID_INVOICE_ADJUST, 'N')
INTO l_allow_paid_invoice_adjust
FROM ap_system_parameters_all
WHERE org_id = l_org_id_list(i);
AP_RETRO_PRICING_PKG.insert_ap_inv_interface (
l_group_id,
l_org_id,
p_po_user_id,
p_vendor_id,
p_vendor_site_id,
p_po_header_id,
p_po_release_id,
current_calling_sequence);
AP_RETRO_PRICING_PKG.insert_ap_inv_lines_interface(
l_group_id,
l_org_id,
p_po_user_id,
p_vendor_id, -- IN
p_vendor_site_id, -- IN
p_po_header_id, -- IN
p_po_release_id, -- IN
current_calling_sequence);
debug_info := 'Step 3. Update the PO View';
UPDATE PO_AP_RETROACTIVE_DIST_V
SET invoice_adjustment_flag = 'S'
WHERE line_location_id = l_po_line_loc_id_list(i);
SELECT accounting_date,
accrual_posted_flag,
amount,
asset_book_type_code,
asset_category_id,
assets_addition_flag,
assets_tracking_flag,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
award_id,
awt_flag,
awt_group_id,
awt_tax_rate_id,
base_amount,
batch_id,
cancellation_flag,
cash_posted_flag,
corrected_invoice_dist_id,
corrected_quantity,
country_of_supply,
created_by,
description,
dist_code_combination_id,
dist_match_type,
distribution_class,
distribution_line_number,
encumbered_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
final_match_flag,
global_attribute_category,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
income_tax_region,
inventory_transfer_status,
invoice_distribution_id,
invoice_id,
invoice_line_number,
line_type_lookup_code,
match_status_flag,
matched_uom_lookup_code,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
org_id,
pa_addition_flag,
pa_quantity,
period_name,
po_distribution_id,
posted_flag,
project_id,
quantity_invoiced,
rcv_transaction_id,
related_id,
reversal_flag,
rounding_amt,
set_of_books_id,
task_id,
type_1099,
unit_price,
p_instruction_id, --instruction_id,
NULL, --charge_applicable_to_dist_id
INTENDED_USE,
WITHHOLDING_TAX_CODE_ID,
PROJECT_ACCOUNTING_CONTEXT,
REQ_DISTRIBUTION_ID,
REFERENCE_1,
REFERENCE_2,
NULL, -- line_group_number
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
PA_CC_PROCESSED_CODE,
pay_awt_group_id --bug6817107
FROM ap_invoice_distributions_all
WHERE invoice_id = p_existing_ppa_lines_rec.invoice_id
AND invoice_line_number = p_existing_ppa_lines_rec.line_number
AND NVL(cancellation_flag, 'N' ) <> 'Y'
AND NVL(reversal_flag, 'N' ) <> 'Y';
debug_info := 'Reverse_Existing_Ppa_Dists Step 3. Insert PPA Reversal '
||' Dists in the Global Temp Table';
INSERT INTO ap_ppa_invoice_dists_gt values l_ppa_invoice_dists_list(i);
l_existing_ppa_dist_list.DELETE;
l_ppa_invoice_dists_list.DELETE;
SELECT invoice_id,
line_number,
line_type_lookup_code,
requester_id,
description,
line_source,
org_id,
inventory_item_id,
item_description,
serial_number,
manufacturer,
model_number,
generate_dists,
match_type,
default_dist_ccid,
prorate_across_all_items,
accounting_date,
period_name,
deferred_acctg_flag,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_invoiced,
unit_meas_lookup_code,
unit_price,
discarded_flag,
cancelled_flag,
income_tax_region,
type_1099,
corrected_inv_id,
corrected_line_number,
po_header_id,
po_line_id,
po_release_id,
po_line_location_id,
po_distribution_id,
rcv_transaction_id,
final_match_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
award_id,
awt_group_id,
pay_awt_group_id,--bug6817107
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,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
creation_date,
created_by,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
primary_intended_use,
ship_to_location_id,
product_type,
product_category,
product_fisc_classification,
user_defined_fisc_class,
trx_business_category,
summary_tax_line_id,
tax_regime_code,
tax,
tax_jurisdiction_code,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
wfapproval_status,
pa_quantity,
p_instruction_id, --instruction_id
'PPA', --adj_type
cost_factor_id, --cost_factor_id
TAX_CLASSIFICATION_CODE,
SOURCE_APPLICATION_ID ,
SOURCE_EVENT_CLASS_CODE ,
SOURCE_ENTITY_CODE ,
SOURCE_TRX_ID ,
SOURCE_LINE_ID ,
SOURCE_TRX_LEVEL_TYPE ,
PA_CC_AR_INVOICE_ID ,
PA_CC_AR_INVOICE_LINE_NUM ,
PA_CC_PROCESSED_CODE ,
REFERENCE_1 ,
REFERENCE_2 ,
DEF_ACCTG_START_DATE ,
DEF_ACCTG_END_DATE ,
DEF_ACCTG_NUMBER_OF_PERIODS ,
DEF_ACCTG_PERIOD_TYPE ,
REFERENCE_KEY5 ,
PURCHASING_CATEGORY_ID ,
LINE_GROUP_NUMBER ,
WARRANTY_NUMBER ,
REFERENCE_KEY3 ,
REFERENCE_KEY4 ,
APPLICATION_ID ,
PRODUCT_TABLE ,
REFERENCE_KEY1 ,
REFERENCE_KEY2 ,
RCV_SHIPMENT_LINE_ID
FROM ap_invoice_lines_all
WHERE invoice_id = p_existing_ppa_inv_id
AND line_source = 'PO PRICE ADJUSTMENT'
AND match_type = 'PO_PRICE_ADJUSTMENT'
AND line_type_lookup_code = 'RETROITEM'
AND discarded_flag <> 'Y'
AND cancelled_flag <> 'Y';
debug_info := 'Reverse_Existing_Ppa Step 3. Insert temp PPA Reversal '
||'Line';
'insert_rejections<- '||current_calling_sequence);
l_existing_ppa_lines_list.DELETE;
debug_info := 'Create_Zero_Amt_Adj_Line Step 2. Insert the Adj Line in '
||'the Global Temp Table';
SELECT accounting_date,
accrual_posted_flag,
amount,
asset_book_type_code,
asset_category_id,
assets_addition_flag,
assets_tracking_flag,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
award_id,
awt_flag,
awt_group_id,
awt_tax_rate_id,
base_amount,
batch_id,
cancellation_flag,
cash_posted_flag,
corrected_invoice_dist_id,
corrected_quantity,
country_of_supply,
created_by,
description,
dist_code_combination_id,
dist_match_type,
distribution_class,
distribution_line_number,
encumbered_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
final_match_flag,
global_attribute_category,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
income_tax_region,
inventory_transfer_status,
invoice_distribution_id,
invoice_id,
invoice_line_number,
line_type_lookup_code,
match_status_flag,
matched_uom_lookup_code,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
org_id,
pa_addition_flag,
pa_quantity,
period_name,
po_distribution_id,
posted_flag,
project_id,
quantity_invoiced,
rcv_transaction_id,
related_id,
reversal_flag,
rounding_amt,
set_of_books_id,
task_id,
type_1099,
unit_price,
instruction_id, --instruction_id
NULL, --charge_applicable_dist_id
INTENDED_USE,
WITHHOLDING_TAX_CODE_ID,
PROJECT_ACCOUNTING_CONTEXT,
REQ_DISTRIBUTION_ID,
REFERENCE_1,
REFERENCE_2,
NULL, -- line_group_number
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
PA_CC_PROCESSED_CODE,
pay_awt_group_id --bug6817107
FROM ap_ppa_invoice_dists_gt
WHERE invoice_id = p_adj_lines_rec.invoice_id
AND invoice_line_number = p_adj_lines_rec.line_number
AND line_type_lookup_code IN ('RETROEXPENSE', 'RETROACCRUAL', 'ERV')
ORDER BY invoice_distribution_id;
debug_info := 'Create_Adjustment_Corrections Step 2. Insert the Adj Line in the'
||' Global Temp Table';
'insert_rejections<- '||current_calling_sequence);
debug_info := 'Create_Adjustment_Corrections Step 5. Insert the Adj Dists in the'
||' Global Temp Table';
INSERT INTO ap_ppa_invoice_dists_gt values l_ppa_invoice_dists_list(i);
debug_info := 'Create_Adjustment_Corrections Step 6. Update Related Id';
UPDATE ap_ppa_invoice_dists_gt d1
SET related_id = invoice_distribution_id
WHERE invoice_id = l_ppa_lines_rec.invoice_id
AND invoice_line_number = l_ppa_lines_rec.line_number
AND related_id = ( SELECT related_id
FROM ap_ppa_invoice_dists_gt d2
WHERE d1.related_id = d2.related_id
AND line_type_lookup_code = 'ERV');
l_ppa_invoice_dists_list.DELETE;
l_adj_dists_list.DELETE;
SELECT aid1.accounting_date,
aid1.accrual_posted_flag,
aid1.amount,
aid1.asset_book_type_code,
aid1.asset_category_id,
aid1.assets_addition_flag,
aid1.assets_tracking_flag,
aid1.attribute_category,
aid1.attribute1,
aid1.attribute10,
aid1.attribute11,
aid1.attribute12,
aid1.attribute13,
aid1.attribute14,
aid1.attribute15,
aid1.attribute2,
aid1.attribute3,
aid1.attribute4,
aid1.attribute5,
aid1.attribute6,
aid1.attribute7,
aid1.attribute8,
aid1.attribute9,
aid1.award_id,
aid1.awt_flag,
aid1.awt_group_id,
aid1.awt_tax_rate_id,
aid1.base_amount,
aid1.batch_id,
aid1.cancellation_flag,
aid1.cash_posted_flag,
aid1.corrected_invoice_dist_id,
aid1.corrected_quantity,
aid1.country_of_supply,
aid1.created_by,
aid1.description,
aid1.dist_code_combination_id,
aid1.dist_match_type,
aid1.distribution_class,
aid1.distribution_line_number,
aid1.encumbered_flag,
aid1.expenditure_item_date,
aid1.expenditure_organization_id,
aid1.expenditure_type,
aid1.final_match_flag,
aid1.global_attribute_category,
aid1.global_attribute1,
aid1.global_attribute10,
aid1.global_attribute11,
aid1.global_attribute12,
aid1.global_attribute13,
aid1.global_attribute14,
aid1.global_attribute15,
aid1.global_attribute16,
aid1.global_attribute17,
aid1.global_attribute18,
aid1.global_attribute19,
aid1.global_attribute2,
aid1.global_attribute20,
aid1.global_attribute3,
aid1.global_attribute4,
aid1.global_attribute5,
aid1.global_attribute6,
aid1.global_attribute7,
aid1.global_attribute8,
aid1.global_attribute9,
aid1.income_tax_region,
aid1.inventory_transfer_status,
aid1.invoice_distribution_id,
aid1.invoice_id,
aid1.invoice_line_number,
aid1.line_type_lookup_code,
aid1.match_status_flag,
aid1.matched_uom_lookup_code,
aid1.merchant_document_number,
aid1.merchant_name,
aid1.merchant_reference,
aid1.merchant_tax_reg_number,
aid1.merchant_taxpayer_id,
aid1.org_id,
aid1.pa_addition_flag,
aid1.pa_quantity,
aid1.period_name,
aid1.po_distribution_id,
aid1.posted_flag,
aid1.project_id,
aid1.quantity_invoiced,
aid1.rcv_transaction_id,
aid1.related_id,
aid1.reversal_flag,
aid1.rounding_amt,
aid1.set_of_books_id,
aid1.task_id,
aid1.type_1099,
aid1.unit_price,
p_instruction_id, --instruction_id
NULL, --charge_applicable_to_dist_id
aid1.INTENDED_USE,
aid1.WITHHOLDING_TAX_CODE_ID,
aid1.PROJECT_ACCOUNTING_CONTEXT,
aid1.REQ_DISTRIBUTION_ID,
aid1.REFERENCE_1,
aid1.REFERENCE_2,
NULL, -- line_group_number
aid1.PA_CC_AR_INVOICE_ID,
aid1.PA_CC_AR_INVOICE_LINE_NUM,
aid1.PA_CC_PROCESSED_CODE,
aid1.pay_awt_group_id --bug6817107
FROM ap_invoice_distributions_all aid1,
(SELECT rownum r FROM ap_invoice_distributions_all WHERE ROWNUM <= c_rows) aid2
WHERE aid1.invoice_id = p_lines_rec.invoice_id
AND aid1.invoice_line_number = p_lines_rec.line_number
AND aid2.r <= c_rows
AND aid1.line_type_lookup_code = 'IPV'
AND NVL(aid1.cancellation_flag, 'N' ) <> 'Y'
AND NVL( aid1.reversal_flag, 'N' ) <> 'Y'
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all aid3
WHERE aid3.corrected_invoice_dist_id = aid1.invoice_distribution_id
AND aid3.line_type_lookup_code IN ('RETROACCRUAL', 'RETROEXPENSE')
);
SELECT NVL(exchange_rate, 1)
INTO l_original_exchange_rate
FROM ap_invoices_all
WHERE invoice_id = p_lines_rec.invoice_id;
debug_info := 'Reverse_Redistribute_IPV Step 6. Insert the Adj Dists in '
||'the Global Temp Table ';
INSERT INTO ap_ppa_invoice_dists_gt values l_adj_dists_list(i);
l_ipv_dists_list.DELETE;
l_adj_dists_list.DELETE;
SELECT aid1.accounting_date,
aid1.accrual_posted_flag,
aid1.amount,
aid1.asset_book_type_code,
aid1.asset_category_id,
aid1.assets_addition_flag,
aid1.assets_tracking_flag,
aid1.attribute_category,
aid1.attribute1,
aid1.attribute10,
aid1.attribute11,
aid1.attribute12,
aid1.attribute13,
aid1.attribute14,
aid1.attribute15,
aid1.attribute2,
aid1.attribute3,
aid1.attribute4,
aid1.attribute5,
aid1.attribute6,
aid1.attribute7,
aid1.attribute8,
aid1.attribute9,
aid1.award_id,
aid1.awt_flag,
aid1.awt_group_id,
aid1.awt_tax_rate_id,
aid1.base_amount,
aid1.batch_id,
aid1.cancellation_flag,
aid1.cash_posted_flag,
aid1.corrected_invoice_dist_id,
aid1.corrected_quantity,
aid1.country_of_supply,
aid1.created_by,
aid1.description,
aid1.dist_code_combination_id,
aid1.dist_match_type,
aid1.distribution_class,
aid1.distribution_line_number,
aid1.encumbered_flag,
aid1.expenditure_item_date,
aid1.expenditure_organization_id,
aid1.expenditure_type,
aid1.final_match_flag,
aid1.global_attribute_category,
aid1.global_attribute1,
aid1.global_attribute10,
aid1.global_attribute11,
aid1.global_attribute12,
aid1.global_attribute13,
aid1.global_attribute14,
aid1.global_attribute15,
aid1.global_attribute16,
aid1.global_attribute17,
aid1.global_attribute18,
aid1.global_attribute19,
aid1.global_attribute2,
aid1.global_attribute20,
aid1.global_attribute3,
aid1.global_attribute4,
aid1.global_attribute5,
aid1.global_attribute6,
aid1.global_attribute7,
aid1.global_attribute8,
aid1.global_attribute9,
aid1.income_tax_region,
aid1.inventory_transfer_status,
aid1.invoice_distribution_id,
aid1.invoice_id,
aid1.invoice_line_number,
aid1.line_type_lookup_code,
aid1.match_status_flag,
aid1.matched_uom_lookup_code,
aid1.merchant_document_number,
aid1.merchant_name,
aid1.merchant_reference,
aid1.merchant_tax_reg_number,
aid1.merchant_taxpayer_id,
aid1.org_id,
aid1.pa_addition_flag,
aid1.pa_quantity,
aid1.period_name,
aid1.po_distribution_id,
aid1.posted_flag,
aid1.project_id,
aid1.quantity_invoiced,
aid1.rcv_transaction_id,
aid1.related_id,
aid1.reversal_flag,
aid1.rounding_amt,
aid1.set_of_books_id,
aid1.task_id,
aid1.type_1099,
aid1.unit_price,
p_instruction_id, --instruction_id
aid1.charge_applicable_to_dist_id,
aid1.INTENDED_USE,
aid1.WITHHOLDING_TAX_CODE_ID,
aid1.PROJECT_ACCOUNTING_CONTEXT,
aid1.REQ_DISTRIBUTION_ID,
aid1.REFERENCE_1,
aid1.REFERENCE_2,
NULL, -- line_group_number
aid1.PA_CC_AR_INVOICE_ID,
aid1.PA_CC_AR_INVOICE_LINE_NUM,
aid1.PA_CC_PROCESSED_CODE,
aid1.pay_awt_group_id --bugu6817107
FROM ap_invoice_distributions_all aid1,
(SELECT rownum r FROM ap_invoice_distributions_all WHERE ROWNUM <= c_rows) aid2
WHERE aid1.invoice_id = p_tax_lines_rec.invoice_id
AND aid1.invoice_line_number = p_tax_lines_rec.line_number
AND aid2.r <= c_rows
AND aid1.line_type_lookup_code = 'TIPV'
AND NVL(aid1.cancellation_flag, 'N' ) <> 'Y'
AND NVL( aid1.reversal_flag, 'N' ) <> 'Y'
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all aid3
WHERE aid3.corrected_invoice_dist_id = aid1.invoice_distribution_id
AND aid3.line_type_lookup_code IN ('RETROTAX'))
AND aid1.charge_applicable_to_dist_id IN
(SELECT invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_lines_rec.invoice_id
--Bug5485084 replaced p_tax_lines_rec with p_lines_rec
AND invoice_line_number = p_lines_rec.line_number);
debug_info := 'Reverse_Redistribute_TIPV Step 4. Insert the '
||'Adjustments in the Global Temp Table';
INSERT INTO ap_ppa_invoice_dists_gt values l_tipv_adj_dists_list(i);
l_tipv_adj_dists_list.DELETE;
l_tipv_dists_list.DELETE;
SELECT exchange_rate
INTO l_original_exchange_rate
FROM ap_invoices_all
WHERE invoice_id = p_lines_rec.invoice_id;
SELECT accounting_date,
accrual_posted_flag,
amount,
asset_book_type_code,
asset_category_id,
assets_addition_flag,
assets_tracking_flag,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
award_id,
awt_flag,
awt_group_id,
awt_tax_rate_id,
base_amount,
batch_id,
cancellation_flag,
cash_posted_flag,
corrected_invoice_dist_id,
corrected_quantity,
country_of_supply,
created_by,
description,
dist_code_combination_id,
dist_match_type,
distribution_class,
distribution_line_number,
encumbered_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
final_match_flag,
global_attribute_category,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
income_tax_region,
inventory_transfer_status,
invoice_distribution_id,
invoice_id,
invoice_line_number,
line_type_lookup_code,
match_status_flag,
matched_uom_lookup_code,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
org_id,
pa_addition_flag,
pa_quantity,
period_name,
po_distribution_id,
posted_flag,
project_id,
quantity_invoiced,
rcv_transaction_id,
NULL, --related_id,
reversal_flag,
rounding_amt,
set_of_books_id,
task_id,
type_1099,
unit_price,
p_instruction_id, --instruction_id
NULL, --charge_applicable_dist_id
INTENDED_USE,
WITHHOLDING_TAX_CODE_ID,
PROJECT_ACCOUNTING_CONTEXT,
REQ_DISTRIBUTION_ID,
REFERENCE_1,
REFERENCE_2,
NULL, -- line_group_number
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
PA_CC_PROCESSED_CODE,
pay_awt_group_id --bug6817107
FROM ap_invoice_distributions_all
WHERE invoice_id = p_lines_rec.invoice_id
AND invoice_line_number = p_lines_rec.line_number
AND line_type_lookup_code IN ('ITEM', 'ACCRUAL');
debug_info := 'Insert the PPA Line in the Global Temp Table';
'insert_rejections<- '||current_calling_sequence);
/*select max(distribution_line_number) + 1
into l_ppa_invoice_dists_list(i).distribution_line_number
from ap_ppa_invoice_dists_gt
where invoice_id = l_ppa_lines_rec.invoice_id
and invoice_line_number = l_ppa_lines_rec.line_number; */
debug_info := 'Create_Po_Price_Adjustments Step 4. Insert the PPA Dists in'
||' the Global Temp Table';
INSERT INTO ap_ppa_invoice_dists_gt values l_ppa_invoice_dists_list(i);
l_ppa_invoice_dists_list.DELETE;
l_item_dists_list.DELETE;
debug_info := 'Process Retroprice Adjustments Step 1. Insert Temp PPA Invoice l_base_match_lines_rec.invoice_id,l_prev_invoice_id '||l_base_match_lines_rec.invoice_id||','||l_prev_invoice_id;
l_pc_lines_list.DELETE;
l_qc_lines_list.DELETE;
l_tax_lines_list.DELETE;
| FUNCTION - Insert_Zero_Amt_Adjustments()
|
| DESCRIPTION
| This function creates Zero Amount RetroItem and RetoTax lines on all
| the original invoices that need retro adjustment for a vendor. Furthermore
| this function reverses and redistributes all outstanding IPV and TIPV
| distributions
|
|
| PARAMETERS
| p_base_currency_code
| p_base_match_lines_list
| p_instruction_rec
| p_instruction_lines_rec
| p_batch_id
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 29-JUL-2003 dgulraja Creation
| Bug 5353893 -- Added NVL to the WHO Columns so that in cases
| when it is null we will use the "Standalone Batch
| Process" as the possible user.
|
*============================================================================*/
FUNCTION Insert_Zero_Amt_Adjustments(
p_instruction_id IN NUMBER,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN IS
current_calling_sequence VARCHAR2(1000);
'AP_RETRO_PRICING_PKG.Insert_Zero_Amt_Adjustments<-'
||P_calling_sequence;
debug_info := 'Insert_Zero_Amt_Adjustments Step 1. Insert into '
||'AP_INVOICE_LINES_ALL';
INSERT INTO AP_INVOICE_LINES_ALL(
invoice_id,
line_number,
line_type_lookup_code,
requester_id,
description,
line_source,
org_id,
inventory_item_id,
item_description,
serial_number,
manufacturer,
model_number,
generate_dists,
match_type,
default_dist_ccid,
prorate_across_all_items,
accounting_date,
period_name,
deferred_acctg_flag,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_invoiced,
unit_meas_lookup_code,
unit_price,
discarded_flag,
cancelled_flag,
income_tax_region,
type_1099,
corrected_inv_id,
corrected_line_number,
po_header_id,
po_line_id,
po_release_id,
po_line_location_id,
po_distribution_id,
rcv_transaction_id,
final_match_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
award_id,
awt_group_id,
pay_awt_group_id,--bug6817107
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,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
creation_date,
created_by,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
primary_intended_use,
ship_to_location_id,
product_type,
product_category,
product_fisc_classification,
user_defined_fisc_class,
trx_business_category,
summary_tax_line_id,
tax_regime_code,
tax,
tax_jurisdiction_code,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
wfapproval_status,
pa_quantity,
last_updated_by,
last_update_date)
SELECT invoice_id,
line_number,
line_type_lookup_code,
requester_id,
description,
line_source,
org_id,
inventory_item_id,
item_description,
serial_number,
manufacturer,
model_number,
generate_dists,
match_type,
default_dist_ccid,
prorate_across_all_items,
accounting_date,
period_name,
deferred_acctg_flag,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_invoiced,
unit_meas_lookup_code,
unit_price,
discarded_flag,
cancelled_flag,
income_tax_region,
type_1099,
corrected_inv_id,
corrected_line_number,
po_header_id,
po_line_id,
po_release_id,
po_line_location_id,
po_distribution_id,
rcv_transaction_id,
final_match_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
award_id,
awt_group_id,
pay_awt_group_id,--bug6817107
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,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
nvl(creation_date,sysdate),
nvl(created_by,5),
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
primary_intended_use,
ship_to_location_id,
product_type,
product_category,
product_fisc_classification,
user_defined_fisc_class,
trx_business_category,
summary_tax_line_id,
tax_regime_code,
tax,
tax_jurisdiction_code,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
wfapproval_status,
pa_quantity,
nvl(created_by,5),
nvl(creation_date,sysdate)
FROM ap_ppa_invoice_lines_gt
WHERE instruction_id = p_instruction_id
AND adj_type = 'ADJ';
debug_info := 'Insert_Zero_Amt_Adjustments Step 2. Insert into AP_INVOICE_DISTRIBUTIONS_ALL';
INSERT INTO ap_invoice_distributions_all(
accounting_date,
accrual_posted_flag,
amount,
asset_book_type_code,
asset_category_id,
assets_addition_flag,
assets_tracking_flag,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
award_id,
awt_flag,
awt_group_id,
pay_awt_group_id,--bug6817107
awt_tax_rate_id,
base_amount,
batch_id,
cancellation_flag,
cash_posted_flag,
corrected_invoice_dist_id,
corrected_quantity,
country_of_supply,
created_by,
creation_date,
description,
dist_code_combination_id,
dist_match_type,
distribution_class,
distribution_line_number,
encumbered_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
final_match_flag,
global_attribute_category,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
income_tax_region,
inventory_transfer_status,
invoice_distribution_id,
invoice_id,
invoice_line_number,
line_type_lookup_code,
match_status_flag,
matched_uom_lookup_code,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
org_id,
pa_addition_flag,
pa_quantity,
period_name,
po_distribution_id,
posted_flag,
project_id,
quantity_invoiced,
rcv_transaction_id,
reversal_flag,
rounding_amt,
set_of_books_id,
task_id,
type_1099,
unit_price,
--Freight and Special Charges
rcv_charge_addition_flag,
last_updated_by,
last_update_date)
SELECT d.accounting_date,
d.accrual_posted_flag,
d.amount,
d.asset_book_type_code,
d.asset_category_id,
d.assets_addition_flag,
d.assets_tracking_flag,
d.attribute_category,
d.attribute1,
d.attribute10,
d.attribute11,
d.attribute12,
d.attribute13,
d.attribute14,
d.attribute15,
d.attribute2,
d.attribute3,
d.attribute4,
d.attribute5,
d.attribute6,
d.attribute7,
d.attribute8,
d.attribute9,
d.award_id,
d.awt_flag,
d.awt_group_id,
d.pay_awt_group_id,--bug6817107
d.awt_tax_rate_id,
d.base_amount,
d.batch_id,
d.cancellation_flag,
d.cash_posted_flag,
d.corrected_invoice_dist_id,
d.corrected_quantity,
d.country_of_supply,
nvl(d.created_by,5),
SYSDATE,
d.description,
d.dist_code_combination_id,
d.dist_match_type,
d.distribution_class,
d.distribution_line_number,
d.encumbered_flag,
d.expenditure_item_date,
d.expenditure_organization_id,
d.expenditure_type,
d.final_match_flag,
d.global_attribute_category,
d.global_attribute1,
d.global_attribute10,
d.global_attribute11,
d.global_attribute12,
d.global_attribute13,
d.global_attribute14,
d.global_attribute15,
d.global_attribute16,
d.global_attribute17,
d.global_attribute18,
d.global_attribute19,
d.global_attribute2,
d.global_attribute20,
d.global_attribute3,
d.global_attribute4,
d.global_attribute5,
d.global_attribute6,
d.global_attribute7,
d.global_attribute8,
d.global_attribute9,
d.income_tax_region,
d.inventory_transfer_status,
ap_invoice_distributions_s.NEXTVAL, --d.invoice_distribution_id,
d.invoice_id,
d.invoice_line_number,
d.line_type_lookup_code,
d.match_status_flag,
d.matched_uom_lookup_code,
d.merchant_document_number,
d.merchant_name,
d.merchant_reference,
d.merchant_tax_reg_number,
d.merchant_taxpayer_id,
d.org_id,
d.pa_addition_flag,
d.pa_quantity,
d.period_name,
d.po_distribution_id,
d.posted_flag,
d.project_id,
d.quantity_invoiced,
d.rcv_transaction_id,
d.reversal_flag,
d.rounding_amt,
d.set_of_books_id,
d.task_id,
d.type_1099,
d.unit_price,
'N',
nvl(d.created_by,5),
SYSDATE
FROM ap_ppa_invoice_dists_gt d,
ap_ppa_invoice_lines_gt l
WHERE d.instruction_id = p_instruction_id
AND d.instruction_id = l.instruction_id
AND d.invoice_id = l.invoice_id
AND d.invoice_line_number = l.line_number
AND l.adj_type = 'ADJ';
END Insert_Zero_Amt_Adjustments;
| p_default_last_updated_by
| p_default_last_update_login
| p_instr_status_flag
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 29-JUL-2003 dgulraja Creation
|
*============================================================================*/
FUNCTION Validate_Temp_Ppa_Invoices(
p_instruction_id IN NUMBER,
p_base_currency_code IN VARCHAR2,
p_multi_currency_flag IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_default_exchange_rate_type IN VARCHAR2,
p_make_rate_mandatory_flag IN VARCHAR2,
p_gl_date_from_get_info IN DATE,
p_gl_date_from_receipt_flag IN VARCHAR2,
p_positive_price_tolerance IN NUMBER,
p_pa_installed IN VARCHAR2,
p_qty_tolerance IN NUMBER,
p_max_qty_ord_tolerance IN NUMBER,
p_base_min_acct_unit IN NUMBER,
p_base_precision IN NUMBER,
p_chart_of_accounts_id IN NUMBER,
p_freight_code_combination_id IN NUMBER,
p_purch_encumbrance_flag IN VARCHAR2,
p_calc_user_xrate IN VARCHAR2,
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_instr_status_flag OUT NOCOPY VARCHAR2,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR invoice_header IS
SELECT invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
NULL, --po_number should be NULL at the Invoice Header level
vendor_id,
NULL, --vendor_num,
NULL, --vendor_name,
vendor_site_id,
NULL, --vendor_site_code,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
NULL, --terms_name,
terms_date,
description,
awt_group_id,
NULL, --awt_group_name,
pay_awt_group_id, --bug6817107
NULL,--pay_awt_group_name --bug6817107
amount_applicable_to_discount,
NULL, --last_update_date,
NULL, --last_updated_by,
NULL, --last_update_login,
creation_date,
created_by,
NULL, --status,
trim(attribute_category) attribute_category,
trim(attribute1) attribute1,
trim(attribute2) attribute2,
trim(attribute3) attribute3,
trim(attribute4) attribute4,
trim(attribute5) attribute5,
trim(attribute6) attribute6,
trim(attribute7) attribute7,
trim(attribute8) attribute8,
trim(attribute9) attribute9,
trim(attribute10) attribute10,
trim(attribute11) attribute11,
trim(attribute12) attribute12,
trim(attribute13) attribute13,
trim(attribute14) attribute14,
trim(attribute15) attribute15,
trim(global_attribute_category) global_attribute_category,
trim(global_attribute1) global_attribute1,
trim(global_attribute2) global_attribute2,
trim(global_attribute3) global_attribute3,
trim(global_attribute4) global_attribute4,
trim(global_attribute5) global_attribute5,
trim(global_attribute6) global_attribute6,
trim(global_attribute7) global_attribute7,
trim(global_attribute8) global_attribute8,
trim(global_attribute9) global_attribute9,
trim(global_attribute10) global_attribute10,
trim(global_attribute11) global_attribute11,
trim(global_attribute12) global_attribute12,
trim(global_attribute13) global_attribute13,
trim(global_attribute14) global_attribute14,
trim(global_attribute15) global_attribute15,
trim(global_attribute16) global_attribute16,
trim(global_attribute17) global_attribute17,
trim(global_attribute18) global_attribute18,
trim(global_attribute19) global_attribute19,
trim(global_attribute20) global_attribute20,
payment_currency_code,
payment_cross_rate,
NULL, --payment_cross_rate_type,
NULL, --payment_cross_rate_date,
NULL, --doc_category_code,
NULL, --voucher_num,
payment_method_code, --4552701
pay_group_lookup_code,
goods_received_date,
invoice_received_date,
NULL, --gl_date,
accts_pay_code_combination_id,
NULL, --accts_pay_code_concatenated, -- bug 6603310
exclusive_payment_flag,
NULL, --prepay_num,
NULL, --prepay_line_num,
NULL, --prepay_apply_amount,
NULL, --prepay_gl_date,
NULL, --invoice_includes_prepay_flag,
NULL, --no_xrate_base_amount,
requester_id,
org_id,
NULL, --operating_unit,
source,
NULL, --group_id,
NULL, --request_id,
NULL, --workflow_flag,
NULL, --vendor_email_address,
NULL, --calc_tax_during_import_flag,
NULL, --control_amount,
NULL, --add_tax_to_inv_amt_flag,
NULL, --tax_related_invoice_id,
NULL, --taxation_country,
NULL, --document_sub_type,
NULL, --supplier_tax_invoice_number,
NULL, --supplier_tax_invoice_date,
NULL, --supplier_tax_exchange_rate,
NULL, --tax_invoice_recording_date,
NULL, --tax_invoice_internal_seq,
NULL, --legal_entity_id,
NULL, --set_of_books_id,
NULL, --tax_only_rcv_matched_flag,
NULL, --tax_only_flag,
NULL, --apply_advances_flag
NULL, --application_id
NULL, --product_table
NULL, --reference_key1
NULL, --reference_key2
NULL, --reference_key3
NULL, --reference_key4
NULL, --reference_key5
NULL, --reference_1
NULL, --reference_2
NULL, --net_of_retainage_flag
null, --4552701, added nulls below so this code would compile
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null, -- original_invoice_amount bug7357218
null, -- dispute_reason bug7357218
null, -- 7535348 adding nulls to compile code, after third party payments
null,
null,
null,
null,
null
FROM ap_ppa_invoices_gt
WHERE instruction_id = p_instruction_id;
SELECT NULL, --rowid
invoice_line_id, --invoice_line_id,
line_type_lookup_code,
line_number,
NULL, --line_group_number,
amount,
NULL, -- base amount
accounting_date,
NULL, --period name
deferred_acctg_flag,
NULL, --def_acctg_start_date,
NULL, --def_acctg_end_date,
NULL, --def_acctg_number_of_periods,
NULL, --def_acctg_period_type,
description,
prorate_across_all_items,
NULL, -- match_type
po_header_id,
NULL, --po_number,
po_line_id,
NULL, --po_line_number,
po_release_id,
NULL, --release_num,
po_line_location_id,
NULL, --po_shipment_num,
po_distribution_id,
NULL, --po_distribution_num,
unit_meas_lookup_code,
inventory_item_id,
item_description,
quantity_invoiced,
NULL, --ship_to_location_code,
unit_price,
final_match_flag,
NULL, --distribution_set_id,
NULL, --distribution_set_name,
NULL, -- partial segments
NULL, --dist_code_concatenated,
NULL, --dist_code_combination_id,
awt_group_id,
NULL, --awt_group_name,
pay_awt_group_id, --bug6817107
NULL,--pay_awt_group_name --bug6817107
NULL, --balancing_segment,
NULL, --cost_center_segment,
NULL, --account_segment,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
NULL, --pa_addition_flag,
pa_quantity,
NULL, --stat_amount,
type_1099,
income_tax_region,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
serial_number,
manufacturer,
model_number,
NULL, --warranty_number,
NULL, --price_correction_flag,
NULL, --price_correct_inv_num,
NULL, -- corrected_inv_id -- for price corrections via import
NULL, --price_correct_inv_line_num,
NULL, --receipt_number,
NULL, --receipt_line_number,
rcv_transaction_id,
NULL, --rcv_shipment_line_id --Bug7344899
NULL, --match_option,
NULL, --packing_slip,
NULL, --vendor_item_num,
NULL, --taxable_flag,
NULL, --pa_cc_ar_invoice_id,
NULL, --pa_cc_ar_invoice_line_num,
NULL, --pa_cc_processed_code,
NULL, --reference_1,
NULL, --reference_2,
credit_card_trx_id,
requester_id,
org_id,
NULL, -- program_application_id
NULL, -- program_id
NULL, -- request_id
NULL, -- program_update_date
NULL, --control_amount,
NULL, --assessable_value,
default_dist_ccid,
primary_intended_use,
ship_to_location_id,
product_type,
product_category,
product_fisc_classification,
user_defined_fisc_class,
trx_business_category,
tax_regime_code,
tax,
NULL, -- tax_jurisdiction_code,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
NULL, --incl_in_taxable_line_flag
NULL, --application_id
NULL, --product_table
NULL, --reference_key1
NULL, --reference_key2
NULL, --reference_key3
NULL, --reference_key4
NULL, --reference_key5
NULL, --purchasing_category
NULL, --purchasing_category_id
cost_factor_id, --cost_factor_id
NULL, --cost_factor_name
NULL, --source_application_id
NULL, --source_entity_code
NULL, --source_event_class_code
NULL, --source_trx_id
NULL, --source_line_id
NULL, --source_trx_level_type
NULL, --tax_classification_code
NULL, --retained_amount
NULL, --amount_includes_tax_flag -- Bug 5436859
--Bug6277609 starts Added the following columns to record
NULL, --cc_reversal_flag
NULL, --company_prepaid_invoice_id,
NULL, --expense_group
NULL, --justification
NULL, --merchant_document_number,
NULL, --merchant_name
NULL, --merchant_reference
NULL, --merchant_taxpayer_id
NULL, --merchant_tax_reg_number
NULL, --receipt_conversion_rate
NULL, --receipt_conversion_amount
NULL, --receipt_currency_code
NULL --country_of_supply
--Bug6277609 ends
FROM ap_ppa_invoice_lines_gt
WHERE invoice_id = c_invoice_id
ORDER BY line_number;
SELECT auto_calculate_interest_flag
INTO l_allow_interest_invoices
FROM ap_system_parameters
WHERE org_id = l_invoice_header_rec.org_id;
p_default_last_updated_by, -- IN
p_default_last_update_login, -- IN
l_fatal_error_flag, -- OUT NOCOPY
l_invoice_status, -- OUT NOCOPY
p_calc_user_xrate, -- IN
l_prepay_period_name, -- IN OUT
l_prepay_invoice_id, -- OUT
l_prepay_case_name, -- OUT
l_conc_request_id, --IN
l_allow_interest_invoices, --
current_calling_sequence) <> TRUE) THEN
--
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'v_check_invoice_validation<-'||current_calling_sequence);
p_default_last_updated_by, -- IN
p_default_last_update_login, -- IN
l_invoice_status, -- OUT NOCOPY
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'v_check_lines_validation<-'||current_calling_sequence);
l_invoice_header_list.DELETE;
l_invoice_lines_list.DELETE;
| FUNCTION - Insert_Ppa_Invoices()
|
| DESCRIPTION
| After validating a all proposed PPA's for a vendor(instruction), this
| function insert PPA documents in the Transaction Tables. It also creates payment schedules for the
| for all the PPA invoices for a valid insruction(Vendor).
|
| PARAMETERS
| p_instruction_id
| p_ppa_invoices_count
| p_ppa_invoices_total
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 29-JUL-2003 dgulraja Creation
|
*============================================================================*/
FUNCTION Insert_Ppa_Invoices(
p_instruction_id IN NUMBER,
p_ppa_invoices_count OUT NOCOPY NUMBER,
p_ppa_invoices_total OUT NOCOPY NUMBER,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR ppa_invoices IS
SELECT invoice_id,
vendor_id,
vendor_site_id,
terms_id,
terms_date,
payment_cross_rate,
invoice_currency_code,
payment_currency_code,
invoice_amount,
base_amount,
amount_applicable_to_discount,
payment_method_code, --4552701
exclusive_payment_flag,
FND_GLOBAL.user_id, --bugfix:4681253
FND_GLOBAL.user_id, --bugfix:4681253
batch_id
FROM ap_ppa_invoices_gt
WHERE instruction_id = p_instruction_id
AND instr_status_flag = 'Y';
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_api_name constant varchar2(200) := 'Insert_PPa_Invoices';
'AP_RETRO_PRICING_PKG.Insert_Ppa_Invoices<-'
||P_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Insert_Ppa_Invoices(+)');
debug_info := 'Insert_Ppa_Invoices Step 1. Open cursor Ppa_invoices';
l_last_updated_by, --Bugfix:4681253
l_batch_id;
debug_info := 'Insert_Ppa_Invoices Step 2. Get Info';
SELECT DECODE(l_invoice_type_lookup_code,
'CREDIT','N',
l_exclusive_payment_flag), --4552701 don't get this flag from vendors table
payment_priority,
invoice_amount_limit,
hold_future_payments_flag,
hold_reason
INTO l_exclusive_payment_flag,
l_payment_priority,
l_invoice_amount_limit,
l_hold_future_payments_flag,
l_supplier_hold_reason
FROM po_vendor_sites_all
WHERE vendor_id = l_vendor_id
AND vendor_site_id = l_vendor_site_id;
debug_info := 'Insert_Ppa_Invoices Step 3. Insert into AP_INVOICES_ALL, l_ppa_invoice_id is'||l_ppa_invoice_id;
INSERT INTO ap_invoices_All(
accts_pay_code_combination_id,
amount_applicable_to_discount,
approval_ready_flag,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
award_id,
awt_flag,
awt_group_id,
pay_awt_group_id,--bug6817107
base_amount,
description,
exchange_date,
exchange_rate,
exchange_rate_type,
exclusive_payment_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
global_attribute_category,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
goods_received_date,
invoice_amount,
invoice_currency_code,
invoice_date,
invoice_id,
invoice_num,
invoice_received_date,
invoice_type_lookup_code,
org_id,
pa_default_dist_ccid,
pay_group_lookup_code,
payment_cross_rate,
payment_currency_code,
payment_method_code,
payment_status_flag,
project_id,
requester_id,
set_of_books_id,
source,
task_id,
terms_date,
terms_id,
vendor_id,
vendor_site_id,
wfapproval_status,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
gl_date,
APPLICATION_ID ,
BANK_CHARGE_BEARER ,
DELIVERY_CHANNEL_CODE ,
DISC_IS_INV_LESS_TAX_FLAG ,
DOCUMENT_SUB_TYPE ,
EXCLUDE_FREIGHT_FROM_DISCOUNT ,
EXTERNAL_BANK_ACCOUNT_ID ,
LEGAL_ENTITY_ID ,
NET_OF_RETAINAGE_FLAG ,
PARTY_ID ,
PARTY_SITE_ID ,
PAYMENT_CROSS_RATE_DATE ,
PAYMENT_CROSS_RATE_TYPE ,
PAYMENT_FUNCTION ,
PAYMENT_REASON_CODE ,
PAYMENT_REASON_COMMENTS ,
PAY_CURR_INVOICE_AMOUNT ,
PAY_PROC_TRXN_TYPE_CODE ,
PORT_OF_ENTRY_CODE ,
POSTING_STATUS ,
PO_HEADER_ID ,
PRODUCT_TABLE ,
PROJECT_ACCOUNTING_CONTEXT ,
QUICK_PO_HEADER_ID ,
REFERENCE_1 ,
REFERENCE_2 ,
REFERENCE_KEY1 ,
REFERENCE_KEY2 ,
REFERENCE_KEY3 ,
REFERENCE_KEY4 ,
REFERENCE_KEY5 ,
REMITTANCE_MESSAGE1 ,
REMITTANCE_MESSAGE2 ,
REMITTANCE_MESSAGE3 ,
SETTLEMENT_PRIORITY ,
SUPPLIER_TAX_EXCHANGE_RATE ,
SUPPLIER_TAX_INVOICE_DATE ,
SUPPLIER_TAX_INVOICE_NUMBER ,
TAXATION_COUNTRY ,
TAX_INVOICE_INTERNAL_SEQ ,
TAX_INVOICE_RECORDING_DATE ,
TAX_RELATED_INVOICE_ID ,
TRX_BUSINESS_CATEGORY ,
UNIQUE_REMITTANCE_IDENTIFIER ,
URI_CHECK_DIGIT ,
USER_DEFINED_FISC_CLASS)
SELECT accts_pay_code_combination_id,
amount_applicable_to_discount,
approval_ready_flag,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
award_id,
awt_flag,
awt_group_id,
pay_awt_group_id,--bug6817107
base_amount,
description,
exchange_date,
exchange_rate,
exchange_rate_type,
exclusive_payment_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
global_attribute_category,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
goods_received_date,
invoice_amount,
invoice_currency_code,
invoice_date,
invoice_id,
invoice_num,
invoice_received_date,
invoice_type_lookup_code,
org_id,
pa_default_dist_ccid,
pay_group_lookup_code,
payment_cross_rate,
payment_currency_code,
payment_method_code,
payment_status_flag,
project_id,
requester_id,
set_of_books_id,
source,
task_id,
terms_date,
terms_id,
vendor_id,
vendor_site_id,
wfapproval_status,
SYSDATE, --creation_date
FND_GLOBAL.user_id, --created_by
FND_GLOBAL.user_id, --last_updated_by
SYSDATE, --last_update_date
FND_GLOBAL.conc_login_id, --last_update_login
SYSDATE, --4681253
APPLICATION_ID ,
BANK_CHARGE_BEARER ,
DELIVERY_CHANNEL_CODE ,
DISC_IS_INV_LESS_TAX_FLAG ,
DOCUMENT_SUB_TYPE ,
EXCLUDE_FREIGHT_FROM_DISCOUNT ,
EXTERNAL_BANK_ACCOUNT_ID ,
LEGAL_ENTITY_ID ,
NET_OF_RETAINAGE_FLAG ,
PARTY_ID ,
PARTY_SITE_ID ,
PAYMENT_CROSS_RATE_DATE ,
PAYMENT_CROSS_RATE_TYPE ,
PAYMENT_FUNCTION ,
PAYMENT_REASON_CODE ,
PAYMENT_REASON_COMMENTS ,
PAY_CURR_INVOICE_AMOUNT ,
PAY_PROC_TRXN_TYPE_CODE ,
PORT_OF_ENTRY_CODE ,
POSTING_STATUS ,
PO_HEADER_ID ,
PRODUCT_TABLE ,
PROJECT_ACCOUNTING_CONTEXT ,
QUICK_PO_HEADER_ID ,
REFERENCE_1 ,
REFERENCE_2 ,
REFERENCE_KEY1 ,
REFERENCE_KEY2 ,
REFERENCE_KEY3 ,
REFERENCE_KEY4 ,
REFERENCE_KEY5 ,
REMITTANCE_MESSAGE1 ,
REMITTANCE_MESSAGE2 ,
REMITTANCE_MESSAGE3 ,
SETTLEMENT_PRIORITY ,
SUPPLIER_TAX_EXCHANGE_RATE ,
SUPPLIER_TAX_INVOICE_DATE ,
SUPPLIER_TAX_INVOICE_NUMBER ,
TAXATION_COUNTRY ,
TAX_INVOICE_INTERNAL_SEQ ,
TAX_INVOICE_RECORDING_DATE ,
TAX_RELATED_INVOICE_ID ,
TRX_BUSINESS_CATEGORY ,
UNIQUE_REMITTANCE_IDENTIFIER ,
URI_CHECK_DIGIT ,
USER_DEFINED_FISC_CLASS
FROM ap_ppa_invoices_gt
WHERE instruction_id = p_instruction_id
AND invoice_id = l_ppa_invoice_id
AND instr_status_flag = 'Y';
debug_info := 'Insert_Ppa_Invoices Step 4. AP_Create_From_Terms';
|| ',p_last_updated_by : '||l_last_updated_by
|| ',p_created_by : '||l_created_by
|| ',p_payment_priority : '||l_payment_priority
||',p_batch_id : '||l_batch_id
||',p_terms_date : '||l_terms_date
||',p_invoice_amount : '||l_invoice_amount
||',p_pay_curr_invoice_amount : '||l_pay_curr_invoice_amount
||', p_payment_cross_rate : '||l_payment_cross_rate
||',p_amount_for_discount : '||NVL(l_amt_applicable_to_discount,
l_invoice_amount)
||',p_payment_method : '||l_payment_method_code
||',p_invoice_currency : '||l_invoice_currency_code
||',p_payment_currency : '||l_payment_currency_code;
p_last_updated_by =>l_last_updated_by,
p_created_by =>l_created_by,
p_payment_priority =>l_payment_priority,
p_batch_id =>l_batch_id,
p_terms_date =>l_terms_date,
p_invoice_amount =>l_invoice_amount,
p_pay_curr_invoice_amount =>l_pay_curr_invoice_amount,
p_payment_cross_rate =>l_payment_cross_rate,
p_amount_for_discount =>NVL(l_amt_applicable_to_discount,
l_invoice_amount),
p_payment_method =>l_payment_method_code,
p_invoice_currency =>l_invoice_currency_code,
p_payment_currency =>l_payment_currency_code,
p_calling_sequence =>current_calling_sequence);
debug_info := 'Insert_Ppa_Invoices Step 5. Insert into '
||'AP_INVOICE_LINES_ALL ';
INSERT INTO AP_INVOICE_LINES_ALL(
invoice_id,
line_number,
line_type_lookup_code,
requester_id,
description,
line_source,
org_id,
inventory_item_id,
item_description,
serial_number,
manufacturer,
model_number,
generate_dists,
match_type,
default_dist_ccid,
prorate_across_all_items,
accounting_date,
period_name,
deferred_acctg_flag,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_invoiced,
unit_meas_lookup_code,
unit_price,
discarded_flag,
cancelled_flag,
income_tax_region,
type_1099,
corrected_inv_id,
corrected_line_number,
po_header_id,
po_line_id,
po_release_id,
po_line_location_id,
po_distribution_id,
rcv_transaction_id,
final_match_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
award_id,
awt_group_id,
pay_awt_group_id,--bug6817107
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,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
primary_intended_use,
ship_to_location_id,
product_type,
product_category,
product_fisc_classification,
user_defined_fisc_class,
trx_business_category,
summary_tax_line_id,
tax_regime_code,
tax,
tax_jurisdiction_code,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
wfapproval_status,
pa_quantity,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id)
SELECT invoice_id,
line_number,
line_type_lookup_code,
requester_id,
description,
line_source,
org_id,
inventory_item_id,
item_description,
serial_number,
manufacturer,
model_number,
generate_dists,
match_type,
default_dist_ccid,
prorate_across_all_items,
accounting_date,
period_name,
deferred_acctg_flag,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_invoiced,
unit_meas_lookup_code,
unit_price,
discarded_flag,
cancelled_flag,
income_tax_region,
type_1099,
corrected_inv_id,
corrected_line_number,
po_header_id,
po_line_id,
po_release_id,
po_line_location_id,
po_distribution_id,
rcv_transaction_id,
final_match_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
award_id,
awt_group_id,
pay_awt_group_id,--bug6817107
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,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
primary_intended_use,
ship_to_location_id,
product_type,
product_category,
product_fisc_classification,
user_defined_fisc_class,
trx_business_category,
summary_tax_line_id,
tax_regime_code,
tax,
tax_jurisdiction_code,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
wfapproval_status,
pa_quantity,
SYSDATE, --creation_date
FND_GLOBAL.user_id, --created_by
FND_GLOBAL.user_id, --last_updated_by
SYSDATE, --last_update_date
FND_GLOBAL.conc_login_id, --last_update_login
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
FROM ap_ppa_invoice_lines_gt
WHERE instruction_id = p_instruction_id
AND invoice_id = l_ppa_invoice_id
AND adj_type = 'PPA';
debug_info := 'Insert_Ppa_Invoices Step 5. Insert into '
||'AP_INVOICE_DISTRIBUTIONS_ALL ';
INSERT INTO ap_invoice_distributions_all(
accounting_date,
accrual_posted_flag,
amount,
asset_book_type_code,
asset_category_id,
assets_addition_flag,
assets_tracking_flag,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
award_id,
awt_flag,
awt_group_id,
pay_awt_group_id,--bug6817107
awt_tax_rate_id,
base_amount,
batch_id,
cancellation_flag,
cash_posted_flag,
corrected_invoice_dist_id,
corrected_quantity,
country_of_supply,
description,
dist_code_combination_id,
dist_match_type,
distribution_class,
distribution_line_number,
encumbered_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
final_match_flag,
global_attribute_category,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
income_tax_region,
inventory_transfer_status,
invoice_distribution_id,
invoice_id,
invoice_line_number,
line_type_lookup_code,
match_status_flag,
matched_uom_lookup_code,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
org_id,
pa_addition_flag,
pa_quantity,
period_name,
po_distribution_id,
posted_flag,
project_id,
quantity_invoiced,
rcv_transaction_id,
reversal_flag,
rounding_amt,
set_of_books_id,
task_id,
type_1099,
unit_price,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
--Freight and Special Charges
rcv_charge_addition_flag)
SELECT accounting_date,
accrual_posted_flag,
amount,
asset_book_type_code,
asset_category_id,
assets_addition_flag,
assets_tracking_flag,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
award_id,
awt_flag,
awt_group_id,
pay_awt_group_id,--bug6817107
awt_tax_rate_id,
base_amount,
batch_id,
cancellation_flag,
cash_posted_flag,
corrected_invoice_dist_id,
corrected_quantity,
country_of_supply,
description,
dist_code_combination_id,
dist_match_type,
distribution_class,
distribution_line_number,
encumbered_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
final_match_flag,
global_attribute_category,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
income_tax_region,
inventory_transfer_status,
ap_invoice_distributions_s.NEXTVAL, --invoice_distribution_id,
invoice_id,
invoice_line_number,
line_type_lookup_code,
match_status_flag,
matched_uom_lookup_code,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
org_id,
pa_addition_flag,
pa_quantity,
period_name,
po_distribution_id,
posted_flag,
project_id,
quantity_invoiced,
rcv_transaction_id,
reversal_flag,
rounding_amt,
set_of_books_id,
task_id,
type_1099,
unit_price,
SYSDATE, --creation_date,
FND_GLOBAL.user_id, --created_by,
FND_GLOBAL.user_id, --last_updated_by,
SYSDATE, --last_update_date,
FND_GLOBAL.conc_login_id, --last_update_login,
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,
'N' --rcv_charge_addition_flag
FROM ap_ppa_invoice_dists_gt
WHERE instruction_id = p_instruction_id
AND invoice_id = l_ppa_invoice_id;
UPDATE ap_invoices_all
set invoice_amount = (select sum(amount)
from ap_invoice_lines_all
where invoice_id = l_ppa_invoice_id)
where invoice_id = l_ppa_invoice_id;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Insert_Ppa_Invoices(-)');
END Insert_Ppa_Invoices;
| p_default_last_updated_by
| p_default_last_update_login
| p_instr_status_flag -- status of the Instruction
| p_invoices_count --OUT Count of PPA Invoices Created
| p_invoices_total --OUT PPA Invoice Total --to be updated in the Inv Batch
| p_invoices_base_amt_total --OUT PPA Invoice Total
| P_calling_sequence - Calling Sequence
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 29-JUL-2003 dgulraja Creation
|
*============================================================================*/
FUNCTION Import_Retroprice_Adjustments(
p_instruction_rec IN AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
p_base_currency_code IN VARCHAR2,
p_multi_currency_flag IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_default_exchange_rate_type IN VARCHAR2,
p_make_rate_mandatory_flag IN VARCHAR2,
p_gl_date_from_get_info IN DATE,
p_gl_date_from_receipt_flag IN VARCHAR2,
p_positive_price_tolerance IN NUMBER,
p_pa_installed IN VARCHAR2,
p_qty_tolerance IN NUMBER,
p_max_qty_ord_tolerance IN NUMBER,
p_base_min_acct_unit IN NUMBER,
p_base_precision IN NUMBER,
p_chart_of_accounts_id IN NUMBER,
p_freight_code_combination_id IN NUMBER,
p_purch_encumbrance_flag IN VARCHAR2,
p_calc_user_xrate IN VARCHAR2,
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_instr_status_flag OUT NOCOPY VARCHAR2,
p_invoices_count OUT NOCOPY NUMBER,
p_invoices_total OUT NOCOPY NUMBER,
P_calling_sequence IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR instruction_lines IS
SELECT invoice_id,
invoice_line_id,
po_line_location_id,
accounting_date,
unit_price,
requester_id,
description,
award_id,
created_by
FROM ap_invoice_lines_interface
WHERE invoice_id = p_instruction_rec.invoice_id;
l_last_update_login NUMBER;
SELECT pd.line_location_id,
SUM(d.amount)
FROM ap_ppa_invoice_dists_gt d,
ap_ppa_invoice_lines_gt l,
po_distributions_all pd
WHERE l.instruction_id = d.instruction_id
AND l.adj_type = 'PPA'
AND d.instruction_id = p_instruction_rec.invoice_id
AND d.po_distribution_id = pd.po_distribution_id
AND pd.invoice_adjustment_flag = 'S'
GROUP BY pd.line_location_id;
SELECT d.po_distribution_id,
d.matched_uom_lookup_code,
d.amount,
FND_GLOBAL.conc_login_id,
FND_GLOBAL.conc_request_id
FROM ap_ppa_invoice_dists_gt d,
ap_ppa_invoice_lines_gt l,
po_distributions_all pd
WHERE l.instruction_id = d.instruction_id
AND l.adj_type = 'PPA'
AND d.instruction_id = p_instruction_rec.invoice_id
AND d.po_distribution_id = pd.po_distribution_id
AND pd.invoice_adjustment_flag = 'S'
AND pd.line_location_id = c_po_line_location_id;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
'AP_INVOICES_INTERFACE',
p_instruction_rec.invoice_id,
'ORIGINAL INVOICE NOT VALIDATED',
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
current_calling_sequence) <> TRUE) THEN
--
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
END IF; -- Insert rejections
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
('AP_INVOICES_INTERFACE',
p_instruction_rec.invoice_id,
'ORIGINAL INVOICE HAS A HOLD',
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
END IF; -- Insert rejections
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
('AP_INVOICES_INTERFACE',
p_instruction_rec.invoice_id,
'NO SEQUENCE DEFINED FOR PPA',
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
END IF; -- Insert rejections
l_base_match_lines_list.DELETE;
l_instruction_lines_list.DELETE;
debug_info := 'Import_Retroprice_Adjustments 5. Update Invoice Amounts, p_instruction_rec.invoice_id is'||p_instruction_rec.invoice_id;
UPDATE AP_ppa_invoices_gt H
SET invoice_amount = AP_RETRO_PRICING_UTIL_PKG.get_invoice_amount(
invoice_id,
invoice_currency_code)
WHERE instruction_id = p_instruction_rec.invoice_id;
p_default_last_updated_by,
p_default_last_update_login,
l_instr_status_flag1, --Bug5769161
current_calling_sequence) <> TRUE) THEN
--
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'Validate_Temp_Ppa_Invoices<- '||current_calling_sequence);
debug_info := 'Import_Retroprice_Adjustments 8. Update Instruction Status';
UPDATE ap_ppa_invoices_gt
SET instr_status_flag = 'Y'
WHERE instruction_id = p_instruction_rec.invoice_id;
UPDATE ap_ppa_invoices_gt
SET instr_status_flag = 'N'
WHERE instruction_id = p_instruction_rec.invoice_id;
debug_info := 'Import_Retroprice_Adjustments 8.1. delete from '
||'ap_ppa_invoices_gt';
DELETE FROM ap_ppa_invoices_gt
WHERE invoice_amount = 0
AND instruction_id = p_instruction_rec.invoice_id
RETURNING invoice_id
BULK COLLECT INTO l_invoice_id_list;
debug_info := 'Import_Retroprice_Adjustments 8.2. delete from '
||'ap_ppa_invoice_lines_gt';
DELETE FROM ap_ppa_invoice_lines_gt
WHERE invoice_id = l_invoice_id_list(i)
AND instruction_id = p_instruction_rec.invoice_id;
debug_info := 'Import_Retroprice_Adjustments 8.3. delete from '
||'ap_ppa_invoice_dists_gt';
DELETE FROM ap_ppa_invoice_dists_gt D
WHERE invoice_id = l_invoice_id_list(i)
AND instruction_id = p_instruction_rec.invoice_id;
IF (Insert_Zero_Amt_Adjustments(
p_instruction_rec.invoice_id,
current_calling_sequence) <> TRUE) THEN
--
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'Insert_Zero_Amt_Adjustments<- '||current_calling_sequence);
FOR invs in( SELECT distinct invoice_id
FROM ap_ppa_invoice_lines_gt l
WHERE l.instruction_id = p_instruction_rec.invoice_id) loop
AP_Accounting_Events_Pkg.Create_Events(
p_event_type => 'INVOICES',
p_doc_type => NULL,
p_doc_id => invs.invoice_id,
p_accounting_date => NULL,
p_accounting_event_id => l_accounting_event_id,
p_checkrun_name => NULL,
p_calling_sequence => current_calling_sequence);
debug_info := 'Import_Retroprice_Adjustments 10. Insert_Ppa_Invoices l_instr_status_flag is '||l_instr_status_flag;
IF (Insert_Ppa_Invoices(
p_instruction_rec.invoice_id,
p_invoices_count,
p_invoices_total,
current_calling_sequence) <> TRUE) THEN
--
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'Insert_Ppa_Invoices<- '||current_calling_sequence);
debug_info := 'Import_Retroprice_Adjustments 11. Update PO_DISTRIBUTIONS';
l_last_update_login,
l_request_id;
p_last_update_login => l_last_update_login,
p_request_id => l_request_id);
p_last_update_login => l_last_update_login,
p_request_id => l_request_id
);
PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
P_Api_Version => 1.0,
P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
P_Dist_Changes_Rec => l_po_ap_dist_rec,
X_Return_Status => l_return_status,
X_Msg_Data => l_msg_data);
UPDATE po_distributions_all pd
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
request_id = FND_GLOBAL.conc_request_id,
invoice_adjustment_flag = 'R'
WHERE invoice_adjustment_flag = 'S'
AND po_distribution_id IN (
SELECT d.po_distribution_id
FROM ap_ppa_invoice_dists_gt d,
ap_ppa_invoice_lines_gt l
WHERE l.instruction_id = d.instruction_id
AND l.adj_type = 'PPA'
AND d.instruction_id = p_instruction_rec.invoice_id
AND d.po_distribution_id = pd.po_distribution_id);