[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Insert_Invoice_Line(
X_Invoice_Id IN NUMBER,
X_Invoice_Line_Number IN NUMBER,
X_Line_Type_Lookup_Code IN VARCHAR2,
X_Cost_Factor_Id IN NUMBER DEFAULT NULL,
X_Single_Dist_Flag IN VARCHAR2 DEFAULT 'N',
X_Po_Distribution_Id IN NUMBER DEFAULT NULL,
X_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
X_Rcv_Transaction_Id IN NUMBER,
X_Amount IN NUMBER,
X_Quantity_Invoiced IN NUMBER DEFAULT NULL,
X_Unit_Price IN NUMBER DEFAULT NULL,
X_Item_Line_Number IN NUMBER,
X_Charge_Line_Description IN VARCHAR2,
X_Retained_Amount IN NUMBER DEFAULT NULL,
X_Calling_Sequence IN VARCHAR2);
PROCEDURE Insert_Invoice_Distributions (
X_Invoice_ID IN NUMBER,
X_Invoice_Line_Number IN NUMBER,
X_Dist_Tab IN OUT NOCOPY AP_MATCHING_PKG.DIST_TAB_TYPE,
X_Rcv_Transaction_Id IN NUMBER,
X_Total_Amount IN NUMBER,
X_Calling_Sequence IN VARCHAR2);
Procedure Update_PO_Shipments_Dists(
X_Dist_Tab IN OUT NOCOPY AP_MATCHING_PKG.Dist_Tab_Type,
X_Po_Line_Location_Id IN NUMBER,
X_Match_Amount IN NUMBER,
X_Match_Quantity IN NUMBER,
X_Uom_Lookup_Code IN VARCHAR2,
X_Calling_Sequence IN VARCHAR2) ;
PROCEDURE Update_RCV_Transaction(
X_Rcv_Transaction_Id IN NUMBER,
X_Match_Amount IN NUMBER,
X_Match_Quantity IN NUMBER,
X_Uom_Lookup_Code IN VARCHAR2,
X_Calling_Sequence IN VARCHAR2);
PROCEDURE Insert_Corr_Invoice_Line(
X_Invoice_Id IN NUMBER,
X_Invoice_Line_Number IN NUMBER,
X_Corrected_Invoice_Id IN NUMBER,
X_Corrected_Line_Number IN NUMBER,
X_Quantity IN NUMBER,
X_Amount IN NUMBER,
X_Unit_Price IN NUMBER,
X_Correction_Type IN VARCHAR2,
X_Po_Distribution_Id IN NUMBER,
X_Retained_Amount IN NUMBER DEFAULT NULL,
X_Calling_Sequence IN VARCHAR2);
PROCEDURE Insert_Corr_Invoice_Dists(
X_Invoice_Id IN NUMBER,
X_Invoice_Line_Number IN NUMBER,
X_Corrected_Invoice_id IN NUMBER,
X_Corr_Dist_Tab IN OUT NOCOPY
AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
X_Correction_Type IN VARCHAR2,
X_Total_Amount IN NUMBER,
X_Calling_Sequence IN VARCHAR2);
PROCEDURE Update_Corr_Po_Shipments_Dists(
X_Corr_Dist_Tab IN AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
X_Po_Line_Location_Id IN NUMBER,
X_Quantity IN NUMBER,
X_Amount IN NUMBER,
X_Correction_Type IN VARCHAR2,
X_Uom_Lookup_Code IN VARCHAR2,
X_Calling_Sequence IN VARCHAR2);
SELECT AMOUNT
INTO l_line_amt
FROM ap_invoice_lines_all
WHERE invoice_id = x_invoice_id
AND line_number = x_invoice_line_number;
UPDATE ap_invoice_lines_all
SET primary_intended_use = nvl(primary_intended_use, g_intended_use)
,product_type = nvl(product_type, g_product_type)
,product_category = nvl(product_category, g_product_category)
,product_fisc_classification = nvl(product_fisc_classification, g_product_fisc_class)
,user_defined_fisc_class = nvl(user_defined_fisc_class, g_user_defined_fisc_class)
,assessable_value = nvl(assessable_value,
decode(l_full_mtch_amt,l_line_amt,
g_assessable_value,NULL)) /*Introduced decode for bug#13505998*/
,tax_classification_code = nvl(tax_classification_code, g_dflt_tax_class_code)
WHERE invoice_id = x_invoice_id
AND line_number = x_invoice_line_number;
Insert_Invoice_Line(X_Invoice_ID => x_invoice_id,
X_Invoice_Line_Number => g_max_invoice_line_number + 1,
X_Line_Type_Lookup_Code => 'ITEM',
X_Single_Dist_Flag => l_single_dist_flag,
X_Po_Distribution_Id => l_po_distribution_id,
X_Po_Line_Location_id => x_po_line_location_id,
X_Rcv_Transaction_id => x_rcv_transaction_id,
X_Amount => x_amount,
X_Quantity_Invoiced => x_quantity,
X_Unit_Price => x_unit_price,
X_Item_Line_Number => NULL,
X_Charge_Line_Description => NULL,
X_Retained_Amount => l_retained_amount,
X_Calling_Sequence => current_calling_sequence);
Insert_Invoice_Distributions(
X_Invoice_ID => x_invoice_id,
X_Invoice_Line_Number => nvl(x_invoice_line_number,
g_max_invoice_line_number),
X_Dist_Tab => x_dist_tab,
X_rcv_Transaction_Id => x_rcv_transaction_id,
X_Total_Amount => x_amount,
X_Calling_Sequence => current_calling_sequence);
update the line with po_distribution_id, award_id,requester_id,
,projects related information and generate_dists';
UPDATE ap_invoice_lines ail
SET (generate_dists ,
requester_id ,
po_distribution_id ,
rcv_shipment_line_id,
project_id ,
task_id ,
expenditure_type ,
expenditure_item_date ,
expenditure_organization_id ,
pa_quantity ,
award_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
retained_amount,
retained_amount_remaining) =
(SELECT 'D',
pd.deliver_to_person_id,
aid.po_distribution_id,
rcv.shipment_line_id,
aid.project_id,
aid.task_id,
aid.expenditure_type,
aid.expenditure_item_date,
aid.expenditure_organization_id,
aid.pa_quantity,
gms_ap_api.get_distribution_award(aid.award_id),
/* Bug 7483260. If the attribute field is populated in the
* interface, take that value. If the attribute field from
* the interface is null and the transfer_desc_flex_flag is
* Y, take the value from the purchase order.
*/
nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
--end Bug 7483260
l_retained_amount,
-1 * l_retained_amount
FROM ap_invoice_distributions aid,
po_distributions pd,
po_line_locations pll,
rcv_transactions rcv
WHERE aid.invoice_distribution_id = l_invoice_distribution_id
AND pd.po_distribution_id = aid.po_distribution_id
AND pll.line_location_id = pd.line_location_id
AND rcv.po_line_location_id = pll.line_location_id
AND rcv.transaction_id = x_rcv_transaction_id )
WHERE ail.invoice_id = x_invoice_id
AND ail.line_number = x_invoice_line_number;
l_debug_info := 'Update the generate_dists to D after the distributions are created';
UPDATE ap_invoice_lines ail
SET (generate_dists ,
rcv_shipment_line_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
retained_amount,
retained_amount_remaining)
=
(SELECT 'D',
rcv.shipment_line_id,
/* Bug 7483260. If the attribute field is populated in the
* interface, take that value. If the attribute field from
* the interface is null and the transfer_desc_flex_flag is
* Y, take the value from the purchase order.
*/
nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
--end Bug 7483260
l_retained_amount,
-1 * l_retained_amount
FROM ap_invoice_lines ail1,
po_line_locations pll,
rcv_transactions rcv
WHERE ail1.invoice_id = x_invoice_id
AND ail1.line_number =x_invoice_line_number
AND pll.line_location_id = ail1.po_line_location_id
AND rcv.po_line_location_id = pll.line_location_id
AND rcv.transaction_id = x_rcv_transaction_id)
WHERE invoice_id = x_invoice_id
AND line_number = x_invoice_line_number;
P_Last_Update_Login => g_login_id ,
P_Error_Message => l_error_message,
P_Calling_Sequence => current_calling_sequence);
l_debug_info := 'Update Quantity/Amount Billed on the PO/Shipments Distributions';
Update_PO_Shipments_Dists(X_Dist_Tab => x_dist_tab,
X_Po_Line_Location_id => x_po_line_location_id,
X_Match_Amount => x_amount,
X_Match_Quantity => x_quantity,
X_Uom_Lookup_Code => x_uom_lookup_code,
X_Calling_Sequence => current_calling_sequence);
l_debug_info := 'Update Quantity/Amount Billed on the Receipt';
Update_RCV_Transaction (X_Rcv_Transaction_Id => x_rcv_transaction_id,
X_Match_Amount => x_amount,
X_Match_Quantity => x_quantity,
X_Uom_Lookup_code => x_uom_lookup_code,
X_Calling_Sequence => current_calling_sequence);
x_dist_tab.delete;
x_dist_tab.delete;
SELECT ai.gl_date,
ai.batch_id,
ai.set_of_books_id,
ai.awt_group_id,
ai.invoice_type_lookup_code,
ai.exchange_rate,
fc.precision,
fc.minimum_accountable_unit,
nvl(asp.allow_awt_flag,'N'),
nvl(pvs.allow_awt_flag,'N'),
nvl(asp.transfer_desc_flex_flag,'N'),
asp.base_currency_code,
ai.invoice_currency_code,
nvl(pvs.prepay_code_combination_id,
asp.prepay_code_combination_id),
nvl(asp.build_prepayment_accounts_flag,'N'),
decode(pv.type_1099,'','',
decode(combined_filing_flag,'N',NULL,
decode(nvl(asp.income_tax_region_flag,'N'),'Y',pvs.state,
asp.income_tax_region))),
ai.project_id,
ai.task_id,
ai.expenditure_type,
ai.invoice_date,
ai.expenditure_organization_id,
fsp.inventory_organization_id,
nvl(asp.allow_flex_override_flag,'N'),
nvl(fsp.purch_encumbrance_flag,'N'),
nvl(asp.approval_workflow_flag,'N'),
-- ai.ussgl_transaction_code, -- Removed for bug 4277744
ai.award_id,
ai.org_id,
--ETAX: Invwkb
ai.trx_business_category,
ai.vendor_id,
ai.vendor_site_id,
ai.source,
ai.pay_awt_group_id --bug 9689194
INTO g_accounting_date,
g_batch_id,
g_set_of_books_id,
g_awt_group_id,
g_invoice_type_lookup_code,
g_exchange_rate,
g_precision,
g_min_acct_unit,
g_system_allow_awt_flag,
g_site_allow_awt_flag,
g_transfer_flag,
g_base_currency_code,
g_invoice_currency_code,
g_prepay_ccid,
g_build_prepay_accts_flag,
g_income_tax_region,
g_project_id,
g_task_id,
g_expenditure_type,
g_invoice_date,
g_expenditure_organization_id,
g_inventory_organization_id,
g_allow_account_override,
g_encumbrance_flag,
g_approval_workflow_flag,
-- g_ussgl_transaction_code, -- Removed for bug 4277744
g_award_id,
g_org_id,
--ETAX: Invwkb
g_trx_business_category,
g_vendor_id,
g_vendor_site_id,
g_source,
g_pay_awt_group_id --bug 9689194
/* Bug 5572876, using base tables */
FROM ap_invoices_all ai ,
ap_system_parameters_all asp,
ap_suppliers pv,
ap_supplier_sites_all pvs,
financials_system_params_all fsp,
fnd_currencies fc
WHERE ai.invoice_id = x_invoice_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND pv.vendor_id = pvs.vendor_id
AND ai.org_id = asp.org_id
AND asp.org_id = fsp.org_id
AND ai.set_of_books_id = asp.set_of_books_id
AND asp.set_of_books_id = fsp.set_of_books_id
AND ai.invoice_currency_code = fc.currency_code (+);
SELECT ail.account_segment,
ail.balancing_segment,
ail.cost_center_segment,
ail.overlay_dist_code_concat,
ail.default_dist_ccid,
ail.project_id,
ail.task_id,
ail.award_id,
ail.expenditure_type,
ail.expenditure_item_date,
ail.expenditure_organization_id,
ail.awt_group_id,
ail.accounting_date,
ail.pay_awt_group_id
INTO g_account_segment,
g_balancing_segment,
g_cost_center_segment,
g_overlay_dist_code_concat,
g_default_dist_ccid,
g_line_project_id,
g_line_task_id,
g_line_award_id,
g_line_expenditure_type,
g_line_expenditure_item_date,
g_line_expenditure_org_id,
g_line_awt_group_id,
g_line_accounting_date,
g_line_pay_awt_group_id --bug 9689194
FROM ap_invoice_lines ail
WHERE ail.invoice_id = x_invoice_id
AND ail.line_number = x_invoice_line_number;
SELECT nvl(max(ail.line_number),0)
INTO g_max_invoice_line_number
FROM ap_invoice_lines ail
WHERE ail.invoice_id = x_invoice_id;
SELECT pll.match_option,pll.shipment_type, pll.po_line_id, pl.recoupment_rate
INTO g_match_option,g_shipment_type, g_po_line_id, g_recoupment_rate
FROM po_line_locations pll, po_lines pl
WHERE pll.line_location_id = x_po_line_location_id
AND pll.po_line_id = pl.po_line_id;
SELECT book_type_code
INTO g_asset_book_type_code
FROM fa_book_controls fc
WHERE fc.book_class = 'CORP0RATE'
AND fc.set_of_books_id = g_set_of_books_id
AND fc.date_ineffective IS NULL;
l_debug_info := 'select period for accounting date';
SELECT pd.po_distribution_id,
pd.code_combination_id,
accrue_on_receipt_flag ,
DECODE(PD.destination_type_code,'EXPENSE',
NVL(RT.project_id,PD.project_id),
NULL), --project_id
DECODE(PD.destination_type_code,'EXPENSE',
NVL(RT.task_id,PD.task_id),
NULL), --task_id
DECODE(PD.destination_type_code,'EXPENSE',
PD.expenditure_type,NULL), --expenditure_type
DECODE(PD.destination_type_code,
'EXPENSE',PD.expenditure_item_date,
NULL), --expenditure_item_date
DECODE(PD.destination_type_code,
'EXPENSE',PD.expenditure_organization_id,
NULL), --expenditure_organization_id
DECODE(PD.destination_type_code,
'EXPENSE', PD.award_id), --award_id
ap_invoice_distributions_s.nextval
FROM po_distributions_ap_v PD,
rcv_transactions RT
WHERE PD.line_location_id = x_po_line_location_id
and Pd.line_location_id = RT.po_line_location_id
and Pd.po_distribution_id = NVL(RT.po_distribution_id, Pd.po_distribution_id) -- bug 8771037
and RT.TRANSACTION_ID= X_Rcv_Transaction_Id;
SELECT nvl(sum(DECODE(aid.dist_match_type,
'PRICE_CORRECTION', 0,
'AMOUNT_CORRECTION',0,
'ITEM_TO_SERVICE_PO',0,
'ITEM_TO_SERVICE_RECEIPT',0,
NVL(aid.corrected_quantity, 0) +
NVL(aid.quantity_invoiced, 0))),0)
INTO l_billed_txn_qty
FROM AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE aid.PO_DISTRIBUTION_ID = l_po_distribution_id;
x_dist_tab.delete(l_po_distribution_id);
SELECT pd.code_combination_id,
pd.accrue_on_receipt_flag,
NVL(rt.project_id,pd.project_id), --Modified for bug 13845471
NVL(rt.task_id,pd.task_id), --Modified for bug 13845471
pd.award_id,
pd.expenditure_type,
pd.expenditure_item_date,
pd.expenditure_organization_id,
ap_invoice_distributions_s.nextval
INTO x_dist_tab(i).po_ccid,
x_dist_tab(i).accrue_on_receipt_flag,
x_dist_tab(i).project_id,
x_dist_tab(i).task_id,
l_award_set_id,
x_dist_tab(i).expenditure_type,
x_dist_tab(i).expenditure_item_date,
x_dist_tab(i).expenditure_organization_id,
x_dist_tab(i).invoice_distribution_id
FROM po_distributions_ap_v pd
,rcv_transactions RT --Added for bug 13845471
WHERE pd.po_distribution_id = x_dist_tab(i).po_distribution_id
AND pd.po_distribution_id = NVL(rt.po_distribution_id,pd.po_distribution_id) --Added for bug 13845471
AND rt.TRANSACTION_ID= X_Rcv_Transaction_Id; --Added for bug 13845471
x_dist_tab.delete;
SELECT quantity_billed
INTO x_total_quantity
FROM po_line_locations
WHERE line_location_id = x_po_line_location_id;
Procedure Insert_Invoice_Line (X_Invoice_Id IN NUMBER,
X_Invoice_Line_Number IN NUMBER,
X_Line_Type_Lookup_Code IN VARCHAR2,
X_Cost_Factor_Id IN NUMBER DEFAULT NULL,
X_Single_Dist_Flag IN VARCHAR2 DEFAULT 'N',
X_Po_Distribution_Id IN NUMBER DEFAULT NULL,
X_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
X_Rcv_Transaction_Id IN NUMBER,
X_Amount IN NUMBER,
X_Quantity_Invoiced IN NUMBER,
X_Unit_Price IN NUMBER DEFAULT NULL,
X_Item_Line_Number IN NUMBER,
X_Charge_Line_Description IN VARCHAR2,
X_Retained_Amount IN NUMBER DEFAULT NULL,
X_Calling_Sequence IN VARCHAR2) IS
current_calling_sequence VARCHAR2(2000);
l_api_name := 'Insert_Invoice_line';
current_calling_sequence := 'Insert_Invoice_Line<-'||x_calling_sequence;
l_debug_info := 'Inserting Item Line Matched to a RCV';
select nvl(ai.source, 'DUMMY') into l_source
from ap_invoices_all ai
where ai.invoice_id = X_Invoice_Id;
INSERT INTO AP_INVOICE_LINES
(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,
DISTRIBUTION_SET_ID,
ACCOUNT_SEGMENT,
BALANCING_SEGMENT,
COST_CENTER_SEGMENT,
OVERLAY_DIST_CODE_CONCAT,
DEFAULT_DIST_CCID,
PRORATE_ACROSS_ALL_ITEMS,
LINE_GROUP_NUMBER,
ACCOUNTING_DATE,
PERIOD_NAME,
DEFERRED_ACCTG_FLAG,
DEF_ACCTG_START_DATE,
DEF_ACCTG_END_DATE,
DEF_ACCTG_NUMBER_OF_PERIODS,
DEF_ACCTG_PERIOD_TYPE,
SET_OF_BOOKS_ID,
AMOUNT,
BASE_AMOUNT,
ROUNDING_AMT,
QUANTITY_INVOICED,
UNIT_MEAS_LOOKUP_CODE,
UNIT_PRICE,
WFAPPROVAL_STATUS,
-- USSGL_TRANSACTION_CODE, -- Removed for bug 4277744
DISCARDED_FLAG,
ORIGINAL_AMOUNT,
ORIGINAL_BASE_AMOUNT,
ORIGINAL_ROUNDING_AMT,
CANCELLED_FLAG,
INCOME_TAX_REGION,
TYPE_1099,
STAT_AMOUNT,
PREPAY_INVOICE_ID,
PREPAY_LINE_NUMBER,
INVOICE_INCLUDES_PREPAY_FLAG,
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,
RCV_SHIPMENT_LINE_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,
PA_QUANTITY,
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
PA_CC_PROCESSED_CODE,
AWARD_ID,
AWT_GROUP_ID,
REFERENCE_1,
REFERENCE_2,
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, */
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
RETAINED_AMOUNT,
RETAINED_AMOUNT_REMAINING,
--BUGFIX:5565310
SHIP_TO_LOCATION_ID,
PRIMARY_INTENDED_USE,
PRODUCT_FISC_CLASSIFICATION,
TRX_BUSINESS_CATEGORY,
PRODUCT_TYPE,
PRODUCT_CATEGORY,
USER_DEFINED_FISC_CLASS,
ASSESSABLE_VALUE,
TAX_CLASSIFICATION_CODE,
PAY_AWT_GROUP_ID) --bug 9689194
SELECT X_INVOICE_ID, --invoice_id
X_INVOICE_LINE_NUMBER, --invoice_line_number
X_LINE_TYPE_LOOKUP_CODE, --line_type_lookup_code
DECODE(X_SINGLE_DIST_FLAG,'Y',
PD.DELIVER_TO_PERSON_ID,NULL), --requester_id
RCV.ITEM_DESCRIPTION, --description
'HEADER MATCH', --line_source
G_ORG_ID, --org_id
RCV.ITEM_ID, --inventory_item_id
RCV.ITEM_DESCRIPTION, --item_description
NULL, --serial_number
NULL, --manufacturer
NULL, --model_number
'D', --generate_dists
/* Modified for bug#14502266 'ITEM_TO_RECEIPT' */
decode(g_match_option,'R',
'ITEM_TO_RECEIPT',
'ITEM_TO_PO'), --match_type
NULL, --distribution_set_id
NULL, --account_segment
NULL, --balancing_segment
NULL, --cost_center_segment
NULL, --overlay_dist_code_concat
--Bug6908977
NULL, --default_dist_ccid
'N', --prorate_across_all_items
NULL, --line_group_number
G_ACCOUNTING_DATE, --accounting_date
G_PERIOD_NAME, --period_name
'N', --deferred_acctg_flag
NULL, --def_acctg_start_date
NULL, --def_acctg_end_date
NULL, --def_acctg_number_of_periods
NULL, --def_acctg_period_type
G_SET_OF_BOOKS_ID, --set_of_books_id
X_AMOUNT, --amount
AP_UTILITIES_PKG.Ap_Round_Currency(
NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
G_BASE_CURRENCY_CODE), --base_amount
NULL, --rounding_amount
X_QUANTITY_INVOICED, --quantity_invoiced
RCV.RECEIPT_UOM_LOOKUP_CODE,--unit_meas_lookup_code
X_UNIT_PRICE, --unit_price
DECODE(l_source, 'RTS', 'NOT REQUIRED',
DECODE(G_APPROVAL_WORKFLOW_FLAG,'Y',
'REQUIRED','NOT REQUIRED')), --wf_approval_status, Bug 9160534
-- RCV.USSGL_TRANSACTION_CODE,--ussgl_transaction_code - Bug 4277744
'N', --discarded_flag
NULL, --original_amount
NULL, --original_base_amount
NULL, --original_rounding_amt
'N', --cancelled_flag
G_INCOME_TAX_REGION, --income_tax_region
RCV.TYPE_1099, --type_1099
NULL, --stat_amount
NULL, --prepay_invoice_id
NULL, --prepay_line_number
NULL, --invoice_includes_prepay_flag
NULL, --corrected_inv_id
NULL, --corrected_line_number
RCV.PO_HEADER_ID, --po_header_id
RCV.PO_LINE_ID, --po_line_id
RCV.PO_RELEASE_ID, --po_release_id
RCV.PO_LINE_LOCATION_ID, --po_line_location_id
DECODE(X_SINGLE_DIST_FLAG,'Y',
X_PO_DISTRIBUTION_ID,NULL),--po_distribution_id
/*Modified for bug#14502266
X_RCV_TRANSACTION_ID,
RCV.RCV_SHIPMENT_LINE_ID, */
decode(g_match_option,'R',
x_rcv_transaction_id,NULL), --rcv_transaction_id
decode(g_match_option,'R',
RCV.RCV_SHIPMENT_LINE_ID,NULL),--rcv_shipment_line_id
NULL, --final_match_flag
'N', --assets_tracking_flag
G_ASSET_BOOK_TYPE_CODE, --asset_book_type_code
MSI.ASSET_CATEGORY_ID, --asset_category_id
DECODE(X_SINGLE_DIST_FLAG,'Y',
DECODE(PD.destination_type_code,
'EXPENSE',NVL(RT.PROJECT_ID,PD.project_id),
G_PROJECT_ID),
NULL), --project_id modified for bug #7015553
DECODE(X_SINGLE_DIST_FLAG,'Y',
DECODE(PD.destination_type_code,
'EXPENSE',NVL(RT.TASK_ID,PD.task_id),
G_TASK_ID),
NULL), --task_id modified for bug #7015553
DECODE(X_SINGLE_DIST_FLAG,'Y',
DECODE(PD.destination_type_code,
'EXPENSE',PD.expenditure_type,
G_EXPENDITURE_TYPE),
NULL), --expenditure_type
DECODE(X_SINGLE_DIST_FLAG,'Y',
-- Bug 5294998. Calling project API
PA_AP_INTEGRATION.Get_Si_Cost_Exp_Item_Date (
g_invoice_date,
g_accounting_date,
NULL,
sysdate,
x_po_distribution_id,
'PO-MATCH'),
NULL), --expenditure_item_date
/*DECODE(g_pa_expenditure_date_default,
'PO Expenditure Item Date/Transaction Date',
DECODE(PD.Destination_type_code,
'EXPENSE',PD.EXPENDITURE_ITEM_DATE,
G_INVOICE_DATE),
'PO Expenditure Item Date/Transaction GL Date',
DECODE(PD.destination_type_code,
'EXPENSE', PD.EXPENDITURE_ITEM_DATE,
G_ACCOUNTING_DATE),
'PO Expenditure Item Date/Transaction System Date',
DECODE(PD.destination_type_code,
'EXPENSE',PD.EXPENDITURE_ITEM_DATE,
SYSDATE),
'Receipt Date/Transaction Date',G_INVOICE_DATE,
'Receipt Date/Transaction GL Date',G_ACCOUNTING_DATE,
'Receipt Date/Transaction System Date',SYSDATE,
'Transaction Date',G_INVOICE_DATE,
'Transaction GL Date',G_ACCOUNTING_DATE,
'Transaction System Date', SYSDATE),
NULL), */
DECODE(X_SINGLE_DIST_FLAG,'Y',
DECODE(PD.destination_type_code,
'EXPENSE',PD.expenditure_organization_id,
G_EXPENDITURE_ORGANIZATION_ID),
NULL), --expenditure_organization_id
DECODE( DECODE(X_SINGLE_DIST_FLAG,'Y',
DECODE(PD.destination_type_code,
'EXPENSE',PD.project_id,
G_PROJECT_ID),
NULL),
'','',x_quantity_invoiced), --pa_quantity
NULL, --pa_cc_ar_invoice_id
NULL, --pa_cc_ar_invoice_line_num
NULL, --pa_cc_processed_code
DECODE(X_SINGLE_DIST_FLAG,
'Y', nvl(gms_ap_api.get_distribution_award(PD.AWARD_ID), G_AWARD_ID),
NULL), --award_id
G_AWT_GROUP_ID, --awt_group_id
NULL, --reference_1
NULL, --reference_2
NULL, --receipt_verified_flag
NULL, --receipt_required_flag
NULL, --receipt_missing_flag
NULL, --justification
NULL, --expense_group
NULL, --start_expense_date
NULL, --end_expense_date
NULL, --receipt_currency_amount
NULL, --receipt_conversion_rate
NULL, --receipt_currency_amount
NULL, --daily_amount
NULL, --web_parameter_id
NULL, --adjustment_reason
NULL, --merchant_document_number
NULL, --merchant_name
NULL, --merchant_reference
NULL, --merchant_tax_reg_number
NULL, --merchant_taxpayer_id
NULL, --country_of_supply
NULL, --credit_card_trx_id
NULL, --company_prepaid_invoice_id
NULL, --cc_reversal_flag
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute_category),''),--attribute_category
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute1),''), --attribute1
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute2),''), --attribute2
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute3),''), --attribute3
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute4),''), --attribute4
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute5),''), --attribute5
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute6),''), --attribute6
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute7),''), --attribute7
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute8),''), --attribute8
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute9),''), --attribute9
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute10),''), --attribute10
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute11),''), --attribute11
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute12),''), --attribute12
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute13),''), --attribute13
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute14),''), --attribute14
NVL(DECODE(g_transfer_flag,'Y',RSL.attribute15),''), --attribute15
/* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
X_GLOBAL_ATTRIBUTE1, --global_attribute1
X_GLOBAL_ATTRIBUTE2, --global_attribute2
X_GLOBAL_ATTRIBUTE3, --global_attribute3
X_GLOBAL_ATTRIBUTE4, --global_attribute4
X_GLOBAL_ATTRIBUTE5, --global_attribute5
X_GLOBAL_ATTRIBUTE6, --global_attribute6
X_GLOBAL_ATTRIBUTE7, --global_attribute7
X_GLOBAL_ATTRIBUTE8, --global_attribute8
X_GLOBAL_ATTRIBUTE9, --global_attribute9
X_GLOBAL_ATTRIBUTE10, --global_attribute10
X_GLOBAL_ATTRIBUTE11, --global_attribute11
X_GLOBAL_ATTRIBUTE12, --global_attribute12
X_GLOBAL_ATTRIBUTE13, --global_attribute13
X_GLOBAL_ATTRIBUTE14, --global_attribute14
X_GLOBAL_ATTRIBUTE15, --global_attribute15
X_GLOBAL_ATTRIBUTE16, --global_attribute16
X_GLOBAL_ATTRIBUTE17, --global_attribute17
X_GLOBAL_ATTRIBUTE18, --global_attribute18
X_GLOBAL_ATTRIBUTE19, --global_attribute19
X_GLOBAL_ATTRIBUTE20, */ --global_attribute20
SYSDATE, --creation_date
G_USER_ID, --created_by
G_USER_ID, --last_update_by
SYSDATE, --last_update_date
G_LOGIN_ID, --last_update_login
NULL, --program_application_id
NULL, --program_id
NULL, --program_update_date
NULL, --request_id
X_RETAINED_AMOUNT, --retained_amount
(-X_RETAINED_AMOUNT), --retained_amount_remaining
--bugfix:5565310
RCV.SHIP_TO_LOCATION_ID, --ship_to_location_id
G_INTENDED_USE, --primary_intended_use
G_PRODUCT_FISC_CLASS, --product_fisc_classification
G_TRX_BUSINESS_CATEGORY, --trx_business_category
G_PRODUCT_TYPE, --product_type
G_PRODUCT_CATEGORY, --product_category
G_USER_DEFINED_FISC_CLASS, --user_defined_fisc_clas
--Modified for bug#13505998 G_ASSESSABLE_VALUE,
DECODE(X_Amount,l_full_mtch_amt,G_ASSESSABLE_VALUE, NULL),
G_dflt_tax_class_code,
G_PAY_AWT_GROUP_ID --bug 9689194
FROM PO_DISTRIBUTIONS_AP_V PD,
MTL_SYSTEM_ITEMS MSI,
PO_AP_RECEIPT_MATCH_V RCV,
RCV_SHIPMENT_LINES RSL,
RCV_TRANSACTIONS RT --for 7015553
WHERE RCV.RCV_TRANSACTION_ID = X_RCV_TRANSACTION_ID
AND RCV.PO_LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND RSL.SHIPMENT_LINE_ID = RCV.RCV_SHIPMENT_LINE_ID
AND PD.PO_DISTRIBUTION_ID = NVL(X_PO_DISTRIBUTION_ID,PD.PO_DISTRIBUTION_ID)
AND MSI.INVENTORY_ITEM_ID(+) = RCV.ITEM_ID
AND MSI.ORGANIZATION_ID(+) = G_INVENTORY_ORGANIZATION_ID
AND RT.TRANSACTION_ID = RCV.RCV_TRANSACTION_ID --7015553
AND PD.PO_DISTRIBUTION_ID = NVL(RT.PO_DISTRIBUTION_ID,PD.PO_DISTRIBUTION_ID) --Added for bug#13845471
AND ROWNUM = 1;
l_debug_info := 'Inserting Charge Line';
INSERT INTO AP_INVOICE_LINES
(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,
DISTRIBUTION_SET_ID,
ACCOUNT_SEGMENT,
BALANCING_SEGMENT,
COST_CENTER_SEGMENT,
OVERLAY_DIST_CODE_CONCAT,
DEFAULT_DIST_CCID,
PRORATE_ACROSS_ALL_ITEMS,
LINE_GROUP_NUMBER,
ACCOUNTING_DATE,
PERIOD_NAME,
DEFERRED_ACCTG_FLAG,
DEF_ACCTG_START_DATE,
DEF_ACCTG_END_DATE,
DEF_ACCTG_NUMBER_OF_PERIODS,
DEF_ACCTG_PERIOD_TYPE,
SET_OF_BOOKS_ID,
AMOUNT,
BASE_AMOUNT,
ROUNDING_AMT,
QUANTITY_INVOICED,
UNIT_MEAS_LOOKUP_CODE,
UNIT_PRICE,
WFAPPROVAL_STATUS,
-- USSGL_TRANSACTION_CODE, -- Removed for bug 4277744
DISCARDED_FLAG,
ORIGINAL_AMOUNT,
ORIGINAL_BASE_AMOUNT,
ORIGINAL_ROUNDING_AMT,
CANCELLED_FLAG,
INCOME_TAX_REGION,
TYPE_1099,
STAT_AMOUNT,
PREPAY_INVOICE_ID,
PREPAY_LINE_NUMBER,
INVOICE_INCLUDES_PREPAY_FLAG,
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,
PA_QUANTITY,
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
PA_CC_PROCESSED_CODE,
AWARD_ID,
AWT_GROUP_ID,
REFERENCE_1,
REFERENCE_2,
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, */
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
--bugfix:5565310
SHIP_TO_LOCATION_ID,
PRIMARY_INTENDED_USE,
PRODUCT_FISC_CLASSIFICATION,
TRX_BUSINESS_CATEGORY,
PRODUCT_TYPE,
PRODUCT_CATEGORY,
USER_DEFINED_FISC_CLASS,
ASSESSABLE_VALUE,
TAX_CLASSIFICATION_CODE,
COST_FACTOR_ID,
PAY_AWT_GROUP_ID --bug 9689194
)
SELECT X_INVOICE_ID, --invoice_id
X_INVOICE_LINE_NUMBER, --invoice_line_number
X_LINE_TYPE_LOOKUP_CODE, --line_type_lookup_code
AIL.REQUESTER_ID, --requester_id
--bug 5102208
-- RTRIM added in BUG 10329867
RTRIM(SUBSTRB(X_CHARGE_LINE_DESCRIPTION || AIL.description, 1, 240)),--description
'CHRG ITEM MATCH', --line_source
AIL.ORG_ID, --org_id
NULL, --inventory_item_id
NULL, --item_description
NULL, --serial_number
NULL, --manufacturer
NULL, --model_number
'Y', --generate_dists
'NOT_MATCHED', --match_type
NULL, --distribution_set_id
NULL, --account_segment
NULL, --balancing_segment
NULL, --cost_center_segment
NULL, --overlay_dist_code_concat
--Bug6908977
NULL, --default_dist_ccid
'N', --prorate_across_all_items
NULL, --line_group_number
AIL.ACCOUNTING_DATE, --accounting_date
AIL.PERIOD_NAME, --period_name
'N', --deferred_acctg_flag
NULL, --deferred_acctg_start_date
NULL, --deferred_acctg_end_date
NULL, --def_acctg_number_of_periods
NULL, --def_acctg_period_type
AIL.SET_OF_BOOKS_ID, --set_of_books_id
X_AMOUNT, --amount
AP_UTILITIES_PKG.AP_Round_Currency(
NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
G_BASE_CURRENCY_CODE), --base_amount
NULL, --rounding_amount
NULL, --quantity_invoiced
NULL, --unit_meas_lookup_code
NULL, --unit_price
AIL.WFAPPROVAL_STATUS, --wf_approval_status
-- NULL, --ussgl_transaction_code -- Bug 4277744
'N', --discarded_flag
NULL, --original_amount
NULL, --original_base_amount
NULL, --original_rounding_amt
'N', --cancelled_flag
AIL.INCOME_TAX_REGION, --income_tax_region
AIL.TYPE_1099, --type_1099
NULL, --stat_amount
NULL, --prepay_invoice_id
NULL, --prepay_line_number
NULL, --invoice_includes_prepay_flag
NULL, --corrected_inv_id
NULL, --corrected_line_number
NULL, --po_header_id
NULL, --po_line_id
NULL, --po_release_id
NULL, --po_line_location_id
NULL, --po_distribution_id
NULL, --rcv_transaction_id
'N', --final_match_flag
'N', --assets_tracking_flag
NULL, --asset_book_type_code
NULL, --asset_category_id
AIL.PROJECT_ID, --project_id
AIL.TASK_ID, --task_id
AIL.EXPENDITURE_TYPE, --expenditure_type
AIL.EXPENDITURE_ITEM_DATE,--expenditure_item_date
AIL.EXPENDITURE_ORGANIZATION_ID, --expenditure_organization_id
NULL, --pa_quantity
NULL, --pa_cc_Ar_invoice_id
NULL, --pa_cc_Ar_invoice_line_num
NULL, --pa_cc_processed_code
AIL.AWARD_ID, --award_id
AIL.AWT_GROUP_ID, --awt_group_id
NULL, --reference_1
NULL, --reference_2
NULL, --receipt_verified_flag
NULL, --receipt_required_flag
NULL, --receipt_missing_flag
NULL, --justification
NULL, --expense_group
NULL, --start_expense_date
NULL, --end_expense_date
NULL, --receipt_currency_code
NULL, --receipt_conversion_rate
NULL, --receipt_currency_amount
NULL, --daily_amount
NULL, --web_parameter_id
NULL, --adjustment_reason
NULL, --merchant_document_number
NULL, --merchant_name
NULL, --merchant_reference
NULL, --merchant_tax_reg_number
NULL, --merchant_taxpayer_id
NULL, --country_of_supply
NULL, --credit_card_trx_id
NULL, --company_prepaid_invoice_id
NULL, --cc_reversal_flag
AIL.attribute_category, --attribute_category
AIL.attribute1, --attribute1
AIL.attribute2, --attribute2
AIL.attribute3, --attribute3
AIL.attribute4, --attribute4
AIL.attribute5, --attribute5
AIL.attribute6, --attribute6
AIL.attribute7, --attribute7
AIL.attribute8, --attribute8
AIL.attribute9, --attribute9
AIL.attribute10, --attribute10
AIL.attribute11, --attribute11
AIL.attribute12, --attribute12
AIL.attribute13, --attribute13
AIL.attribute14, --attribute14
AIL.attribute15, --attribute15
/* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
X_GLOBAL_ATTRIBUTE1, --global_attribute1
X_GLOBAL_ATTRIBUTE2, --global_attribute2
X_GLOBAL_ATTRIBUTE3, --global_attribute3
X_GLOBAL_ATTRIBUTE4, --global_attribute4
X_GLOBAL_ATTRIBUTE5, --global_attribute5
X_GLOBAL_ATTRIBUTE6, --global_attribute6
X_GLOBAL_ATTRIBUTE7, --global_attribute7
X_GLOBAL_ATTRIBUTE8, --global_attribute8
X_GLOBAL_ATTRIBUTE9, --global_attribute9
X_GLOBAL_ATTRIBUTE10, --global_attribute10
X_GLOBAL_ATTRIBUTE11, --global_attribute11
X_GLOBAL_ATTRIBUTE12, --global_attribute12
X_GLOBAL_ATTRIBUTE13, --global_attribute13
X_GLOBAL_ATTRIBUTE14, --global_attribute14
X_GLOBAL_ATTRIBUTE15, --global_attribute15
X_GLOBAL_ATTRIBUTE16, --global_attribute16
X_GLOBAL_ATTRIBUTE17, --global_attribute17
X_GLOBAL_ATTRIBUTE18, --global_attribute18
X_GLOBAL_ATTRIBUTE19, --global_attribute19
X_GLOBAL_ATTRIBUTE20, */ --global_attribute20
SYSDATE, --creation_date
G_USER_ID, --created_by
G_USER_ID, --last_updated_by
SYSDATE, --last_updated_date
G_LOGIN_ID, --last_update_login
NULL, --program_application_id
NULL, --program_id
NULL, --program_update_date
NULL, --request_id
--bugfix:5565310
AIL.SHIP_TO_LOCATION_ID, --ship_to_location_id
G_INTENDED_USE, --primary_intended_use
G_PRODUCT_FISC_CLASS, --product_fisc_classification
G_TRX_BUSINESS_CATEGORY, --trx_business_category
G_PRODUCT_TYPE, --product_type
G_PRODUCT_CATEGORY, --product_category
G_USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
--G_ASSESSABLE_VALUE, --Bug#13505998
DECODE(X_Amount,l_full_mtch_amt,X_Amount, NULL),
G_dflt_tax_class_code,
X_COST_FACTOR_ID, --cost_factor_id
AIL.PAY_AWT_GROUP_ID --bug 9689194
FROM AP_INVOICE_LINES AIL
WHERE AIL.INVOICE_ID = X_INVOICE_ID
AND AIL.LINE_NUMBER = X_ITEM_LINE_NUMBER;
END Insert_Invoice_Line;
PROCEDURE Insert_Invoice_Distributions (X_Invoice_ID IN NUMBER,
X_Invoice_Line_Number IN NUMBER,
X_Dist_Tab IN OUT NOCOPY
AP_MATCHING_PKG.Dist_Tab_Type,
X_Rcv_Transaction_Id IN NUMBER,
X_Total_Amount IN NUMBER,
X_Calling_Sequence IN VARCHAR2) IS
l_distribution_line_number NUMBER := 1;
l_api_name := 'Insert Invoice Distributions';
current_calling_sequence := 'Insert_Invoice_Distributions <-'||current_calling_sequence;
l_debug_info := 'Insert Invoice Distributions';
INSERT INTO ap_invoice_distributions (
batch_id,
invoice_id,
invoice_line_number,
invoice_distribution_id,
distribution_line_number,
line_type_lookup_code,
description,
dist_match_type,
distribution_class,
org_id,
dist_code_combination_id,
accounting_date,
period_name,
amount_to_post,
base_amount_to_post,
posted_amount,
posted_base_amount,
je_batch_id,
cash_je_batch_id,
posted_flag,
accounting_event_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
set_of_books_id,
amount,
base_amount,
rounding_amt,
match_status_flag,
encumbered_flag,
packet_id,
-- ussgl_transaction_code, -- Removed for bug 4277744
-- ussgl_trx_code_context, -- Removed for bug 4277744
reversal_flag,
parent_reversal_id,
cancellation_flag,
income_tax_region,
type_1099,
stat_amount,
charge_applicable_to_dist_id,
prepay_amount_remaining,
prepay_distribution_id,
parent_invoice_id,
corrected_invoice_dist_id,
corrected_quantity,
other_invoice_id,
po_distribution_id,
rcv_transaction_id,
unit_price,
matched_uom_lookup_code,
quantity_invoiced,
final_match_flag,
related_id,
assets_addition_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
pa_quantity,
pa_addition_flag,
pa_cc_ar_invoice_id,
pa_cc_ar_invoice_line_num,
pa_cc_processed_code,
award_id,
gms_burdenable_raw_cost,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
awt_invoice_payment_id,
awt_withheld_amt,
inventory_transfer_status,
reference_1,
reference_2,
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,*/
--Bugfix:4674194
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,*/
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
intended_use,
accrual_posted_flag,
cash_posted_flag,
--Freight and Special Charges
rcv_charge_addition_flag,
pay_awt_group_id --bug 9689194
)
SELECT g_batch_id, --batch_id
x_invoice_id, --invoice_id
x_invoice_line_number, --invoice_line_number
x_dist_tab(i).invoice_distribution_id, --invoice_distribution_id
l_distribution_line_number, --distribution_line_number
decode(g_invoice_type_lookup_code, 'PREPAYMENT', 'ITEM', -- bug 9081676: modify
decode (pd.accrue_on_receipt_flag,'Y',
'ACCRUAL','ITEM')), --line_type_lookup_code
--Modified ail.item_description as description for bug#10200174
nvl(ail.description, ail.item_description), --description
/*Modified for bug#14502266 'ITEM_TO_RECEIPT', */
decode(g_match_option,'R',
'ITEM_TO_RECEIPT',
'ITEM_TO_PO'), --dist_match_type --Modified for bug#10200174
'PERMANENT', --distribution_class
ail.org_id, --org_id
x_dist_tab(i).dist_ccid, --dist_code_combination_id
ail.accounting_date, --accounting_date
ail.period_name, --period_name
NULL, --amount_to_post
NULL, --base_amount_to_post
NULL, --posted_amount
NULL, --posted_base_amount
NULL, --je_batch_id
NULL, --cash_je_batch_id
'N', --posted_flag
NULL, --accounting_event_id
NULL, --upgrade_posted_amt
NULL, --upgrade_base_posted_amt
g_set_of_books_id, --set_of_books_id
x_dist_tab(i).amount, --amount
x_dist_tab(i).base_amount, --base_amount
x_dist_tab(i).rounding_amt, --rounding_amount
--bugfix:4959567
NULL, --match_status_flag
'N', --encumbered_flag
NULL, --packet_id
-- NVL(PD.ussgl_transaction_code, -- Bug 4277744
-- ail.ussgl_transaction_code),--ussgl_transaction_code
-- NULL, --ussgl_trx_code_context
'N', --reversal_flag
NULL, --parent_reversal_id
'N', --cancellation_flag
DECODE(ail.type_1099,'','',ail.income_tax_region),--income_tax_region
ail.type_1099, --type_1099
NULL, --stat_amount
NULL, --charge_applicable_to_dist_id
NULL, --prepay_amount_remaining
NULL, --prepay_distribution_id
NULL, --parent_invoice_id
NULL, --corrected_invoice_dist_id
NULL, --corrected_quantity
NULL, --other_invoice_id
x_dist_tab(i).po_distribution_id,--po_distribution_id
decode(g_match_option,'R',
x_rcv_transaction_id,NULL), --rcv_transaction_id
x_dist_tab(i).unit_price, --unit_price
rcv.receipt_uom_lookup_code, --matched_uom_lookup_code
x_dist_tab(i).quantity_invoiced,--quantity_invoiced
NULL, --final_match_flag
NULL, --related_id
'U', --assets_addition_flag
decode(gcc.account_type,'E',
ail.assets_tracking_flag,'A','Y','N'), --assets_tracking_flag
decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,'A','Y','N'),
'Y',ail.asset_book_type_code,NULL), --asset_book_type_code
decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,'A','Y','N'),
'Y',ail.asset_category_id), --asset_category_id
x_dist_tab(i).project_id, --project_id
x_dist_tab(i).task_id, --task_id
x_dist_tab(i).expenditure_type, --expenditure_type
x_dist_tab(i).expenditure_item_date, --expenditure_item_date
x_dist_tab(i).expenditure_organization_id, --expenditure_organization_id
x_dist_tab(i).pa_quantity, --pa_quantity
decode(PD.project_id,NULL, 'E',
decode(pd.destination_type_code,'SHOP FLOOR','M',
'INVENTORY','M','N')), --pa_addition_flag
NULL, --pa_cc_ar_invoice_id
NULL, --pa_cc_ar_invoice_line_num
NULL, --pa_cc_processed_code
NULL, --award_id
NULL, --gms_burdenable_raw_cost
NULL, --awt_flag
x_dist_tab(i).awt_group_id, --awt_group_id
NULL, --awt_tax_rate_id
NULL, --awt_gross_amount
NULL, --awt_invoice_id
NULL, --awt_origin_group_id
NULL, --awt_invoice_payment_id
NULL, --awt_withheld_amt
'N', --inventory_transfer_status
NULL, --reference_1
NULL, --reference_2
NULL, --receipt_verified_flag
NULL, --receipt_required_flag
NULL, --receipt_missing_flag
NULL, --justification
NULL, --expense_group
NULL, --start_expense_date
NULL, --end_expense_date
NULL, --receipt_currency_code
NULL, --receipt_conversion_rate
NULL, --receipt_currency_amount
NULL, --daily_amount
NULL, --web_parameter_id
NULL, --adjustment_reason
NULL, --merchant_document_number
NULL, --merchant_name
NULL, --merchant_reference
NULL, --merchant_tax_reg_number
NULL, --merchant_taxpayer_id
NULL, --country_of_supply
NULL, --credit_card_trx_id
NULL, --company_prepaid_invoice_id
NULL, --cc_reversal_flag
-- Bug 6837035 Start
NVL(DECODE(g_transfer_flag,'Y',pd.attribute_category,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute_category))), ''),--attribute_category
NVL(DECODE(g_transfer_flag,'Y',pd.attribute1,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute1))), ''), --attribute1
NVL(DECODE(g_transfer_flag,'Y',pd.attribute2,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute2))), ''), --attribute2
NVL(DECODE(g_transfer_flag,'Y',pd.attribute3,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute3))), ''), --attribute3
NVL(DECODE(g_transfer_flag,'Y',pd.attribute4,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute4))), ''), --attribute4
NVL(DECODE(g_transfer_flag,'Y',pd.attribute5,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute5))), ''), --attribute5
NVL(DECODE(g_transfer_flag,'Y',pd.attribute6,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute6))), ''), --attribute6
NVL(DECODE(g_transfer_flag,'Y',pd.attribute7,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute7))), ''), --attribute7
NVL(DECODE(g_transfer_flag,'Y',pd.attribute8,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute8))), ''), --attribute8
NVL(DECODE(g_transfer_flag,'Y',pd.attribute9,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute9))), ''), --attribute9
NVL(DECODE(g_transfer_flag,'Y',pd.attribute10,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute10))), ''), --attribute10
NVL(DECODE(g_transfer_flag,'Y',pd.attribute11,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute11))), ''), --attribute11
NVL(DECODE(g_transfer_flag,'Y',pd.attribute12,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute12))), ''), --attribute12
NVL(DECODE(g_transfer_flag,'Y',pd.attribute13,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute13))), ''), --attribute13
NVL(DECODE(g_transfer_flag,'Y',pd.attribute14,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute14))), ''), --attribute14
NVL(DECODE(g_transfer_flag,'Y',pd.attribute15,DECODE(line_source,'IMPORTED',
DECODE(l_copy_line_dff_flag,'Y',ail.attribute15))), ''), --attribute15
-- Bug 6837035 End
/* X_GLOBAL_ATTRIBUTE_CATEGORY
X_GLOBAL_ATTRIBUTE1,
X_GLOBAL_ATTRIBUTE2,*/
--Bugfix:4674194
DECODE(AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_OPTION,
'Y',ail.ship_to_location_id, ''),
/*X_GLOBAL_ATTRIBUTE4,
X_GLOBAL_ATTRIBUTE5,
X_GLOBAL_ATTRIBUTE6,
X_GLOBAL_ATTRIBUTE7,
X_GLOBAL_ATTRIBUTE8,
X_GLOBAL_ATTRIBUTE9,
X_GLOBAL_ATTRIBUTE10,
X_GLOBAL_ATTRIBUTE11,
X_GLOBAL_ATTRIBUTE12,
X_GLOBAL_ATTRIBUTE13,
X_GLOBAL_ATTRIBUTE14,
X_GLOBAL_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE16,
X_GLOBAL_ATTRIBUTE17,
X_GLOBAL_ATTRIBUTE18,
X_GLOBAL_ATTRIBUTE19,
X_GLOBAL_ATTRIBUTE20, */
ail.created_by, --created_by
sysdate, --creation_date
ail.last_updated_by, --last_updated_by
sysdate, --last_update_date
ail.last_update_login, --last_update_login
NULL, --program_application_id
NULL, --program_id
NULL, --program_update_date
NULL, --request_id
--bugfix:5565310
g_intended_use, --intended_use
'N', --accrual_posted_flag
'N', --cash_posted_flag
'N', --rcv_charge_addition_flag
x_dist_tab(i).pay_awt_group_id --pay_awt_group_id bug9689194
FROM po_distributions pd,--bug6683010
ap_invoice_lines ail,
po_ap_receipt_match_v rcv,
gl_code_combinations gcc
WHERE ail.invoice_id = x_invoice_id
AND ail.line_number = x_invoice_line_number
AND ail.po_line_location_id = pd.line_location_id
AND pd.po_distribution_id = x_dist_tab(i).po_distribution_id
--bug6683010
AND gcc.code_combination_id = decode(pd.accrue_on_receipt_flag, 'Y',
pd.code_combination_id,x_dist_tab(i).dist_ccid)
AND pd.line_location_id = rcv.po_line_location_id
AND rcv.rcv_transaction_id = x_rcv_transaction_id;
x_dist_tab.delete;
END Insert_Invoice_Distributions;
Procedure Update_PO_Shipments_Dists(
X_Dist_Tab IN OUT NOCOPY AP_MATCHING_PKG.Dist_Tab_Type,
X_Po_Line_Location_Id IN NUMBER,
X_Match_Amount IN NUMBER,
X_Match_Quantity IN NUMBER,
X_Uom_Lookup_Code IN VARCHAR2,
X_Calling_Sequence IN VARCHAR2) IS
current_calling_sequence VARCHAR2(2000);
l_api_name := 'Update_PO_Shipments_Dists';
current_calling_sequence := 'Update_Po_Shipments_Dists<-'||x_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Update_PO_Distributions(+)');
l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
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);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Update_PO_Shipments_Dists(-)');
END Update_PO_Shipments_Dists;
PROCEDURE Update_RCV_Transaction(X_Rcv_Transaction_Id IN NUMBER,
X_Match_Amount IN NUMBER,
X_Match_Quantity IN NUMBER,
X_Uom_lookup_code IN VARCHAR2,
X_Calling_Sequence IN VARCHAR2) IS
current_calling_sequence VARCHAR2(2000);
l_api_name := 'Update_Rcv_Transaction';
current_calling_sequence := 'Update_Rcv_Transaction<-'||x_calling_sequence;
l_debug_info := 'Call RCV api to update amount/quantity billed on the
rcv_transaction';
RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
X_rcv_transaction_id => x_rcv_transaction_id,
X_quantity_billed => x_match_quantity,
X_uom_lookup_code => x_uom_lookup_code,
X_amount_billed => x_match_amount,
X_matching_basis => 'QUANTITY');
END Update_RCV_Transaction;
Insert_Invoice_Line
(X_Invoice_Id => x_invoice_id,
X_Rcv_Transaction_id => x_rcv_transaction_id,
X_Invoice_Line_Number => g_max_invoice_line_number + 1,
X_Line_Type_Lookup_Code => 'FREIGHT',
X_Cost_Factor_Id => x_freight_cost_factor_id,
X_Amount => x_freight_amount,
X_Item_Line_Number => x_item_line_number,
X_Charge_Line_Description => x_freight_description,
X_Calling_Sequence => current_calling_sequence);
AP_ALLOCATION_RULES_PKG.Insert_Percentage_Alloc_Rule(
X_Invoice_id => x_invoice_id,
X_Chrg_Line_Number => g_max_invoice_line_number,
X_To_Line_Number => x_item_line_number,
X_Percentage => 100,
X_Calling_Sequence => x_calling_sequence);
Insert_Invoice_Line
(X_Invoice_Id => x_invoice_id,
X_Rcv_Transaction_id => x_rcv_transaction_id,
X_Invoice_Line_Number => g_max_invoice_line_number + 1,
X_Line_Type_Lookup_Code => 'MISCELLANEOUS',
X_Cost_Factor_Id => x_misc_cost_factor_id,
X_Amount => x_misc_amount,
X_Item_Line_Number => x_item_line_number,
X_Charge_Line_Description => x_misc_description,
X_Calling_Sequence => current_calling_sequence);
AP_ALLOCATION_RULES_PKG.Insert_Percentage_Alloc_Rule(
X_Invoice_id => x_invoice_id,
X_Chrg_Line_Number => g_max_invoice_line_number,
X_To_Line_Number => x_item_line_number,
X_Percentage => 100,
X_Calling_Sequence => x_calling_sequence);
Insert_Corr_Invoice_Line(x_invoice_id => x_invoice_id,
x_invoice_line_number => g_max_invoice_line_number +1,
x_corrected_invoice_id => x_corrected_invoice_id,
x_corrected_line_number => x_corrected_line_number,
x_quantity => x_correction_quantity,
x_amount => x_correction_amount,
x_unit_price => x_correction_price,
x_correction_type => x_correction_type,
x_po_distribution_id => l_po_distribution_id,
x_retained_amount => l_retained_amount,
x_calling_sequence => current_calling_sequence);
Insert_Corr_Invoice_Dists(x_invoice_id => x_invoice_id,
x_invoice_line_number => nvl(x_invoice_line_number,
g_max_invoice_line_number),
x_corrected_invoice_id => x_corrected_invoice_id,
x_corr_dist_tab => x_corr_dist_tab,
x_correction_type => x_correction_type,
x_total_amount => x_correction_amount,
x_calling_sequence => current_calling_sequence);
UPDATE ap_invoice_lines ail
SET (generate_dists ,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
retained_amount,
retained_amount_remaining)
=
(SELECT 'D',
/* Bug 7483260. If the attribute field is populated in the
* interface, take that value. If the attribute field from
* the interface is null and the transfer_desc_flex_flag is
* Y, take the value from the purchase order.
*/
nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
--end Bug 7483260
l_retained_amount,
-1 * l_retained_amount
FROM ap_invoice_lines ail1,
po_line_locations pll
WHERE ail1.invoice_id = x_invoice_id
AND ail1.line_number =x_invoice_line_number
AND pll.line_location_id = ail1.po_line_location_id)
WHERE invoice_id = x_invoice_id
AND line_number = x_invoice_line_number;
P_Last_Update_Login => g_login_id ,
P_Error_Message => l_error_message,
P_Calling_Sequence => current_calling_sequence);
l_debug_info := 'Calling Update_Corr_Po_Shipments_Dists';
Update_Corr_Po_Shipments_Dists(x_corr_dist_tab => x_corr_dist_tab,
x_po_line_location_id => x_po_line_location_id,
x_quantity => x_correction_quantity,
x_amount => x_correction_amount,
x_correction_type => x_correction_type,
x_uom_lookup_code => x_uom_lookup_code,
x_calling_sequence => current_calling_sequence);
Update_Rcv_Transaction(x_rcv_transaction_id => x_rcv_transaction_id,
x_match_quantity => 0,
x_match_amount => x_correction_amount,
x_uom_lookup_code => x_uom_lookup_code,
x_calling_sequence => current_calling_sequence);
Update_Rcv_Transaction(x_rcv_transaction_id => x_rcv_transaction_id,
x_match_quantity => x_correction_quantity,
x_match_amount => x_correction_amount,
x_uom_lookup_code => x_uom_lookup_code,
x_calling_sequence => current_calling_sequence);
x_corr_dist_tab.delete;
x_corr_dist_tab.delete;
SELECT aid1.invoice_distribution_id corrected_inv_dist_id,
aid1.po_distribution_id,
decode(g_min_acct_unit,'',
round(x_correction_amount * (aid1.amount + /*c_price_variance*/nvl(aid2.amount,0))/ail.amount,
g_precision),
round((x_correction_amount * (aid1.amount + /*c_price_variance*/nvl(aid2.amount,0))/ail.amount)
/g_min_acct_unit) * g_min_acct_unit
) amount,
round(x_correction_quantity * (aid1.amount + /*c_price_variance*/nvl(aid2.amount,0))/ail.amount,15) corrected_quantity,
--bugfix:5606697
DECODE(pd.destination_type_code,
'EXPENSE', DECODE(pd.accrue_on_receipt_flag,
'Y', pd.code_combination_id,
aid1.dist_code_combination_id),
pd.variance_account_id),
ap_invoice_distributions_s.nextval
FROM ap_invoice_lines ail,
ap_invoice_distributions aid1,
po_distributions pd,
ap_invoice_distributions aid2
WHERE ail.invoice_id = x_corrected_invoice_id
AND ail.line_number = x_corrected_line_number
AND aid1.invoice_id = ail.invoice_id
AND aid1.invoice_line_number = ail.line_number
AND aid1.po_distribution_id = pd.po_distribution_id --bugfix:5606697
-- Bug 5585744, Modified the condition below
AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND aid1.prepay_distribution_id IS NULL
AND aid1.invoice_distribution_id = aid2.related_id(+)
AND aid2.line_type_lookup_code(+) = 'IPV';
Corrected Quantity: Quantity_Invoiced selected prorated as done
for the Amount.
------------------------------------------------------------*/
CURSOR Quantity_Correction_Cursor(p_total_quantity_billed IN NUMBER) IS
SELECT aid.invoice_distribution_id corrected_inv_dist_id,
aid.po_distribution_id,
decode(g_min_acct_unit,'',
round((x_correction_quantity *
pd.quantity_billed/p_total_quantity_billed
--bugfix:4749294, modified ail.unit_price to x_correction_price
) * x_correction_price,
g_precision),
round(((x_correction_quantity * pd.quantity_billed/
--bugfix:4749294, modified ail.unit_price to x_correction_price
p_total_quantity_billed)*x_correction_price)
/g_min_acct_unit) * g_min_acct_unit
) amount,
round((x_correction_quantity * pd.quantity_billed)/
p_total_quantity_billed , 15) corrected_quantity,
aid.dist_code_combination_id,
ap_invoice_distributions_s.nextval
FROM ap_invoice_distributions aid,
ap_invoice_lines ail,
po_distributions pd
WHERE ail.invoice_id = x_corrected_invoice_id
AND ail.line_number = x_corrected_line_number
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND pd.po_distribution_id = aid.po_distribution_id
-- Bug 5585744, Modified the condition below
AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND aid.prepay_distribution_id IS NULL;
SELECT nvl(sum(amount),0) -- Bug 5629985. Added the NVL
INTO l_price_variance
FROM ap_invoice_distributions_all
WHERE invoice_id = x_corrected_invoice_id
AND invoice_line_number = x_corrected_line_number
AND line_type_lookup_code = 'IPV';
SELECT sum(pd.quantity_billed)
INTO l_total_quantity_billed
FROM po_distributions pd,
ap_invoice_distributions aid
WHERE pd.po_distribution_id = aid.po_distribution_id
AND aid.invoice_id = x_corrected_invoice_id
AND aid.invoice_line_number = x_corrected_line_number
-- Bug 5585744, Modified the condition below
AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND aid.prepay_distribution_id IS NULL;
SELECT ap_invoice_distributions_s.nextval
INTO x_corr_dist_tab(i).invoice_distribution_id
FROM dual ;
x_corr_dist_tab.delete;
PROCEDURE Update_Corr_Po_Shipments_Dists(
X_Corr_Dist_Tab IN AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
X_Po_Line_Location_Id IN NUMBER,
X_Quantity IN NUMBER,
X_Amount IN NUMBER,
X_Correction_Type IN VARCHAR2,
X_Uom_Lookup_Code IN VARCHAR2,
X_Calling_Sequence IN VARCHAR2 ) IS
i NUMBER;
l_api_name := 'Update_Corr_Po_Shipments_Dists';
current_calling_sequence := 'Update_Corr_Po_Shipments_Dists<-'||x_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Distributions(+)');
l_debug_info := 'Call PO api to update po_distributions and po_shipments table'
||' with quantity/amount billed information.';
l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
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);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Shipments_Dists(-)');
END Update_Corr_Po_Shipments_Dists;
PROCEDURE Insert_Corr_Invoice_Line(x_invoice_id IN NUMBER,
x_invoice_line_number IN NUMBER,
x_corrected_invoice_id IN NUMBER,
x_corrected_line_number IN NUMBER,
x_quantity IN NUMBER,
x_amount IN NUMBER,
x_unit_price IN NUMBER,
x_correction_type IN VARCHAR2,
x_po_distribution_id IN NUMBER,
x_retained_amount IN NUMBER DEFAULT NULL,
x_calling_sequence IN VARCHAR2 ) IS
l_debug_info VARCHAR2(2000);
current_calling_sequence := 'Insert_Corr_Invoice_Line<-'||x_calling_sequence;
INSERT INTO AP_INVOICE_LINES(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,
DISTRIBUTION_SET_ID,
ACCOUNT_SEGMENT,
BALANCING_SEGMENT,
COST_CENTER_SEGMENT,
OVERLAY_DIST_CODE_CONCAT,
DEFAULT_DIST_CCID,
PRORATE_ACROSS_ALL_ITEMS,
LINE_GROUP_NUMBER,
ACCOUNTING_DATE,
PERIOD_NAME,
DEFERRED_ACCTG_FLAG,
DEF_ACCTG_START_DATE,
DEF_ACCTG_END_DATE,
DEF_ACCTG_NUMBER_OF_PERIODS,
DEF_ACCTG_PERIOD_TYPE,
SET_OF_BOOKS_ID,
AMOUNT,
BASE_AMOUNT,
ROUNDING_AMT,
QUANTITY_INVOICED,
UNIT_MEAS_LOOKUP_CODE,
UNIT_PRICE,
WFAPPROVAL_STATUS,
-- USSGL_TRANSACTION_CODE, - Bug 4277744
DISCARDED_FLAG,
ORIGINAL_AMOUNT,
ORIGINAL_BASE_AMOUNT,
ORIGINAL_ROUNDING_AMT,
CANCELLED_FLAG,
INCOME_TAX_REGION,
TYPE_1099,
STAT_AMOUNT,
PREPAY_INVOICE_ID,
PREPAY_LINE_NUMBER,
INVOICE_INCLUDES_PREPAY_FLAG,
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,
PA_QUANTITY,
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
PA_CC_PROCESSED_CODE,
AWARD_ID,
AWT_GROUP_ID,
REFERENCE_1,
REFERENCE_2,
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,
/* OPEN ISSUE 1*/
/* 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, */
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
RETAINED_AMOUNT,
RETAINED_AMOUNT_REMAINING,
--ETAX: Invwkb
SHIP_TO_LOCATION_ID,
PRIMARY_INTENDED_USE,
PRODUCT_FISC_CLASSIFICATION,
TRX_BUSINESS_CATEGORY,
PRODUCT_TYPE,
PRODUCT_CATEGORY,
USER_DEFINED_FISC_CLASS,
PAY_AWT_GROUP_ID, --bug 9689194
RCV_SHIPMENT_LINE_ID) /*BUG :15946088*/
SELECT x_invoice_id, --invoice_id
x_invoice_line_number, --line_number
'ITEM', --line_type_lookup_code
ail.requester_id, --requester_id
ail.description, --description
'HEADER CORRECTION', --line_source
ail.org_id, --org_id
ail.inventory_item_id, --inventory_item_id
ail.item_description, --item_description
ail.serial_number, --serial_number
ail.manufacturer, --manufacturer
ail.model_number, --model_number
'D', --generate_dists
x_correction_type, --match_type
NULL, --distribution_set_id
ail.account_segment, --account_segment
ail.balancing_segment, --balancing_segment
ail.cost_center_segment, --cost_center_segment
ail.overlay_dist_code_concat, --overlay_dist_code_concat
ail.default_dist_ccid, --default_dist_ccid
'N', --prorate_across_all_items
NULL, --line_group_number
g_accounting_date, --accounting_date
g_period_name, --period_name
'N', --deferred_acctg_flag
NULL, --def_acctg_start_date
NULL, --def_acctg_end_date
NULL, --def_acctg_number_of_periods
NULL, --def_acctg_period_type
g_set_of_books_id, --set_of_books_id
x_amount, --amount
AP_UTILITIES_PKG.Ap_Round_Currency(
NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
G_BASE_CURRENCY_CODE), --base_amount
NULL, --rounding_amount
x_quantity, --quantity_invoiced
ail.unit_meas_lookup_code, --unit_meas_lookup_code
x_unit_price, --unit_price
decode(g_approval_workflow_flag,'Y'
,'REQUIRED','NOT REQUIRED'),--wf_approval_status
-- Removed for bug 4277744
-- g_ussgl_transaction_code, --ussgl_transaction_code
'N', --discarded_flag
NULL, --original_amount
NULL, --original_base_amount
NULL, --original_rounding_amt
'N', --cancelled_flag
g_income_tax_region, --income_tax_region
pll.type_1099, --type_1099
NULL, --stat_amount
NULL, --prepay_invoice_id
NULL, --prepay_line_number
NULL, --invoice_includes_prepay_flag
x_corrected_invoice_id, --corrected_invoice_id
x_corrected_line_number, --corrected_line_number
ail.po_header_id, --po_header_id
ail.po_line_id, --po_line_id
ail.po_release_id, --release_id
ail.po_line_location_id, --po_line_location_id
nvl(ail.po_distribution_id,
x_po_distribution_id), --po_distribution_id
ail.rcv_transaction_id, --rcv_transaction_id
NULL, --final_match_flag
ail.assets_tracking_flag, --assets_tracking_flag
ail.asset_book_type_code, --asset_book_type_code
ail.asset_category_id, --asset_category_id
ail.project_id, --project_id
ail.task_id, --task_id
ail.expenditure_type, --expenditure_type
ail.expenditure_item_date, --expenditure_item_date
ail.expenditure_organization_id, --expenditure_organization_id
decode(ail.project_id,'','',
decode(x_quantity,'',(ail.pa_quantity*x_amount/ail.amount),
x_quantity)), --pa_quantity
NULL, --pa_cc_ar_invoice_id
NULL, --pa_cc_ar_invoice_line_num
NULL, --pa_cc_processed_code
ail.award_id, --award_id
g_awt_group_id, --awt_group_id
ail.reference_1, --reference_1
ail.reference_2, --reference_2
ail.receipt_verified_flag, --receipt_verified_flag
ail.receipt_required_flag, --receipt_required_flag
ail.receipt_missing_flag, --receipt_missing_flag
ail.justification, --ail.justification
ail.expense_group, --ail.expense_group
ail.start_expense_date, --start_expense_date
ail.end_expense_date, --end_expense_date
ail.receipt_currency_code, --receipt_currency_code
ail.receipt_conversion_rate, --receipt_conversion_rate
ail.receipt_currency_amount, --receipt_currency_amount
ail.daily_amount, --daily_amount
ail.web_parameter_id, --web_parameter_id
ail.adjustment_reason, --adjustment_reason
ail.merchant_document_number, --merchant_document_number
ail.merchant_name, --merchant_name
ail.merchant_reference, --merchant_reference
ail.merchant_tax_reg_number, --merchant_tax_reg_number
ail.merchant_taxpayer_id, --merchant_taxpayer_id
ail.country_of_supply, --country_of_supply
ail.credit_card_trx_id, --credit_card_trx_id
ail.company_prepaid_invoice_id, --cpmany_prepaid_invoice_id
ail.cc_reversal_flag, --cc_reversal_flag
ail.attribute_category, --attribute_category
ail.attribute1, --attribute1
ail.attribute2, --attribute2
ail.attribute3, --attribute3
ail.attribute4, --attribute4
ail.attribute5, --attribute5
ail.attribute6, --attribute6
ail.attribute7, --attribute7
ail.attribute8, --attribute8
ail.attribute9, --attribute9
ail.attribute10, --attribute10
ail.attribute11, --attribute11
ail.attribute12, --attribute12
ail.attribute13, --attribute13
ail.attribute14, --attribute14
ail.attribute15, --attribute15
/*OPEN ISSUE 1*/
/* X_GLOBAL_ATTRIBUTE_CATEGORY,
X_GLOBAL_ATTRIBUTE1,
X_GLOBAL_ATTRIBUTE2,
X_GLOBAL_ATTRIBUTE3,
X_GLOBAL_ATTRIBUTE4,
X_GLOBAL_ATTRIBUTE5,
X_GLOBAL_ATTRIBUTE6,
X_GLOBAL_ATTRIBUTE7,
X_GLOBAL_ATTRIBUTE8,
X_GLOBAL_ATTRIBUTE9,
X_GLOBAL_ATTRIBUTE10,
X_GLOBAL_ATTRIBUTE11,
X_GLOBAL_ATTRIBUTE12,
X_GLOBAL_ATTRIBUTE13,
X_GLOBAL_ATTRIBUTE14,
X_GLOBAL_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE16,
X_GLOBAL_ATTRIBUTE17,
X_GLOBAL_ATTRIBUTE18,
X_GLOBAL_ATTRIBUTE19,
X_GLOBAL_ATTRIBUTE20, */
sysdate, --creation_date
g_user_id, --created_by
g_user_id, --last_updated_by
sysdate, --last_update_date
g_login_id, --user_login_id
NULL, --program_application_id
NULL, --program_id
NULL, --program_update_date
NULL, --request_id
X_RETAINED_AMOUNT, --retained_amount
(-X_RETAINED_AMOUNT), --retained_amount_remaining
--ETAX: Invwkb
PLL.SHIP_TO_LOCATION_ID, --ship_to_location_id
AIL.PRIMARY_INTENDED_USE, --primary_intended_use
AIL.PRODUCT_FISC_CLASSIFICATION, --product_fisc_classification
AIL.TRX_BUSINESS_CATEGORY, --trx_business_category --bug10085965
AIL.PRODUCT_TYPE, --product_type
AIL.PRODUCT_CATEGORY, --product_category
AIL.USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
G_PAY_AWT_GROUP_ID, --bug 9689194
AIL.RCV_SHIPMENT_LINE_ID /*BUG :15946088*/
FROM ap_invoices ai,
ap_invoice_lines ail,
po_line_locations_ap_v pll
WHERE ai.invoice_id = x_corrected_invoice_id
AND ail.invoice_id = ai.invoice_id
AND ail.line_number = x_corrected_line_number
AND pll.line_location_id = ail.po_line_location_id;
END Insert_Corr_Invoice_Line;
PROCEDURE Insert_Corr_Invoice_Dists(x_invoice_id IN NUMBER,
x_invoice_line_number IN NUMBER,
x_corrected_invoice_id IN NUMBER,
x_corr_dist_tab IN OUT NOCOPY
AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
x_correction_type IN VARCHAR2,
x_total_amount IN NUMBER,
x_calling_sequence IN VARCHAR2) IS
l_distribution_line_number ap_invoice_distributions.distribution_line_number%type := 1;
current_calling_sequence := 'Insert_Corr_Invoice_Dists<-'||x_calling_sequence;
l_api_name := 'Insert_Corr_Invoice_Dists';
INSERT INTO AP_INVOICE_DISTRIBUTIONS(
batch_id,
invoice_id,
invoice_line_number,
invoice_distribution_id,
distribution_line_number,
line_type_lookup_code,
description,
dist_match_type,
distribution_class,
org_id,
dist_code_combination_id,
accounting_date,
period_name,
amount_to_post,
base_amount_to_post,
posted_amount,
posted_base_amount,
je_batch_id,
cash_je_batch_id,
posted_flag,
accounting_event_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
set_of_books_id,
amount,
base_amount,
rounding_amt,
match_status_flag,
encumbered_flag,
packet_id,
-- ussgl_transaction_code, - Bug 4277744
-- ussgl_trx_code_context, - Bug 4277744
reversal_flag,
parent_reversal_id,
cancellation_flag,
income_tax_region,
type_1099,
stat_amount,
charge_applicable_to_dist_id,
prepay_amount_remaining,
prepay_distribution_id,
parent_invoice_id,
corrected_invoice_dist_id,
corrected_quantity,
other_invoice_id,
po_distribution_id,
rcv_transaction_id,
unit_price,
matched_uom_lookup_code,
quantity_invoiced,
final_match_flag,
related_id,
assets_addition_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
pa_quantity,
pa_addition_flag,
pa_cc_ar_invoice_id,
pa_cc_ar_invoice_line_num,
pa_cc_processed_code,
award_id,
gms_burdenable_raw_cost,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
awt_invoice_payment_id,
awt_withheld_amt,
inventory_transfer_status,
reference_1,
reference_2,
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,
/*OPEN ISSUE 1*/
/*global_attribute_category,
global_attribute1,
global_attribute2,*/
--Bugfix:4674194
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,*/
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
--ETAX: Invwkb
intended_use,
accrual_posted_flag,
cash_posted_flag,
--Freight and Special Charges
rcv_charge_addition_flag,
pay_awt_group_id) --bug 9689194
SELECT g_batch_id, --batch_id
x_invoice_id, --invoice_id
x_invoice_line_number, --invoice_line_number
x_corr_dist_tab(i).invoice_distribution_id, --invoice_distribution_id
l_distribution_line_number, --distribution_line_number
decode(x_correction_type,
'PRICE_CORRECTION','IPV',
'QTY_CORRECTION',aid.line_type_lookup_code), --line_type_lookup_code
ail.description, --description
x_correction_type, --dist_match_type
'PERMANENT', --distribution_class
ail.org_id, --org_id
x_corr_dist_tab(i).dist_ccid, --dist_code_combination_id
ail.accounting_date, --accounting_date
ail.period_name, --period_name
NULL, --amount_to_post
NULL, --base_amount_to_post
NULL, --posted_amount
NULL, --posted_base_amount
NULL, --je_batch_id
NULL, --cash_je_batch_id
'N', --posted_flag
NULL, --accounting_event_id
NULL, --upgrade_posted_amt
NULL, --upgrade_base_posted_amt
g_set_of_books_id, --set_of_books_id
x_corr_dist_tab(i).amount, --amount
x_corr_dist_tab(i).base_amount,--base_amount
x_corr_dist_tab(i).rounding_amt,--rounding_amount
--bugfix:4959567
NULL, --match_status_flag
'N', --encumbered_flag
NULL, --packet_id
-- Removed for bug 4277744
-- ail.ussgl_transaction_code, --ussgl_transaction_code
-- NULL, --ussgl_trx_code_context
'N', --reversal_flag
NULL, --parent_reversal_id
'N', --cancellation_flag
DECODE(ail.type_1099,'','',ail.income_tax_region), --income_tax_region
ail.type_1099, --type_1099
NULL, --stat_amount
NULL, --charge_applicable_to_dist_id
NULL, --prepay_amount_remaining
NULL, --prepay_distribution_id
ail.corrected_inv_id, --parent_invoice_id
x_corr_dist_tab(i).corrected_inv_dist_id, --corrected_invoice_dist_id
x_corr_dist_tab(i).corrected_quantity, --corrected_quantity
NULL, --other_invoice_id
x_corr_dist_tab(i).po_distribution_id, --po_distribution_id
ail.rcv_transaction_id /*NULL*/, --rcv_transaction_id /*Bug13691308*/
x_corr_dist_tab(i).unit_price, --unit_price
aid.matched_uom_lookup_code, --matched_uom_lookup_code
NULL, --quantity_invoiced
NULL, --final_match_flag
NULL, --related_id
'U', --assets_addition_flag
aid.assets_tracking_flag, --assets_tracking_flag
decode(aid.assets_tracking_flag,'Y',
ail.asset_book_type_code,NULL), --asset_book_type_code
decode(aid.assets_tracking_flag,'Y',
ail.asset_category_id,NULL), --asset_category_id
aid.project_id, --project_id
aid.task_id, --task_id
aid.expenditure_type, --expenditure_type
aid.expenditure_item_date, --expenditure_item_date
aid.expenditure_organization_id, --expenditure_organization_id
decode(aid.project_id,'','',
x_corr_dist_tab(i).pa_quantity), --pa_quantity
decode(aid.project_id,NULL,'E',
decode(pd.destination_type_code,'SHOP FLOOR','M',
'INVENTORY','M','N')), --pa_addition_flag
NULL, --pa_cc_ar_invoice_id
NULL, --pa_cc_ar_invoice_line_num
NULL, --pa_cc_processed_code
aid.award_id, --award_id
NULL, --gms_burdenable_raw_cost
NULL, --awt_flag
decode(g_system_allow_awt_flag,'Y',
decode(g_site_allow_awt_flag,'Y',ail.awt_group_id,NULL),
NULL), --awt_group_id
NULL, --awt_tax_rate_id
NULL, --awt_gross_amount
NULL, --awt_invoice_id
NULL, --awt_origin_group_id
NULL, --awt_invoice_payment_id
NULL, --awt_withheld_amt
'N', --inventory_transfer_status
ail.reference_1, --reference_1
ail.reference_2, --reference_2
ail.receipt_verified_flag, --receipt_verified_flag
ail.receipt_required_flag, --receipt_required_flag
ail.receipt_missing_flag, --receipt_missing_flag
ail.justification, --justification
ail.expense_group, --expense_group
ail.start_expense_date, --start_expense_date
ail.end_expense_date, --end_expense_date
ail.receipt_currency_code, --receipt_currency_code
ail.receipt_conversion_rate, --receipt_conversion_rate
ail.receipt_currency_amount, --receipt_currency_amount
ail.daily_amount, --daily_amount
ail.web_parameter_id, --web_parameter_id
ail.adjustment_reason, --adjustment_reason
ail.merchant_document_number, --merchant_document_number
ail.merchant_name, --merchant_name
ail.merchant_reference, --merchant_reference
ail.merchant_tax_reg_number, --merchant_tax_reg_number
ail.merchant_taxpayer_id, --merchant_taxpayer_id
ail.country_of_supply, --country_of_supply
ail.credit_card_trx_id, --credit_card_trx_id
ail.company_prepaid_invoice_id, --company_prepaid_invoice_id
ail.cc_reversal_flag, --cc_reversal_flag
aid.attribute_category, --attribute_category
aid.attribute1, --attribute1
aid.attribute2, --attribute2
aid.attribute3, --attribute3
aid.attribute4, --attribute4
aid.attribute5, --attribute5
aid.attribute6, --attribute6
aid.attribute7, --attribute7
aid.attribute8, --attribute8
aid.attribute9, --attribute9
aid.attribute10, --attribute10
aid.attribute11, --attribute11
aid.attribute12, --attribute12
aid.attribute13, --attribute13
aid.attribute14, --attribute14
aid.attribute15, --attribute15
/* X_GLOBAL_ATTRIBUTE_CATEGORY,
X_GLOBAL_ATTRIBUTE1,
X_GLOBAL_ATTRIBUTE2,*/
decode(ap_extended_withholding_pkg.ap_extended_withholding_option,
'Y',ail.ship_to_location_id,''),
/*X_GLOBAL_ATTRIBUTE4,
X_GLOBAL_ATTRIBUTE5,
X_GLOBAL_ATTRIBUTE6,
X_GLOBAL_ATTRIBUTE7,
X_GLOBAL_ATTRIBUTE8,
X_GLOBAL_ATTRIBUTE9,
X_GLOBAL_ATTRIBUTE10,
X_GLOBAL_ATTRIBUTE11,
X_GLOBAL_ATTRIBUTE12,
X_GLOBAL_ATTRIBUTE13,
X_GLOBAL_ATTRIBUTE14,
X_GLOBAL_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE16,
X_GLOBAL_ATTRIBUTE17,
X_GLOBAL_ATTRIBUTE18,
X_GLOBAL_ATTRIBUTE19,
X_GLOBAL_ATTRIBUTE20, */
ail.created_by, --created_by
sysdate, --creation_date
ail.last_updated_by, --last_updated_by
sysdate, --last_update_date
ail.last_update_login, --last_update_login
NULL, --program_application_id
NULL, --program_id
NULL, --program_update_date
NULL, --request_id
--ETAX: Invwkb
aid.intended_use, --intended_use
'N', --accrual_posted_flag
'N', --cash_posted_flag
'N', --rcv_charge_addition_flag
decode(g_system_allow_awt_flag,'Y',
decode(g_site_allow_awt_flag,'Y',ail.pay_awt_group_id,NULL),
NULL) --pay_awt_group_id Bug9689194
FROM ap_invoice_lines ail,
ap_invoice_distributions aid,
po_distributions pd
WHERE ail.invoice_id = x_invoice_id
AND ail.line_number = x_invoice_line_number
AND aid.invoice_id = ail.corrected_inv_id
AND aid.invoice_line_number = ail.corrected_line_number
AND aid.invoice_distribution_id = x_corr_dist_tab(i).corrected_inv_dist_id
AND pd.po_distribution_id = aid.po_distribution_id;
x_corr_dist_tab.delete;
END Insert_Corr_Invoice_Dists;