[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_PO_Shipments_Dists(
X_Dist_Tab IN OUT NOCOPY 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 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_Amount IN NUMBER,
X_Quantity_Invoiced IN NUMBER DEFAULT NULL,
X_Unit_Price IN NUMBER DEFAULT NULL,
X_Final_Match_Flag IN VARCHAR2 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 Dist_Tab_Type,
X_Final_Match_Flag IN VARCHAR2,
X_Unit_Price IN NUMBER,
X_Total_Amount IN NUMBER,
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_Final_Match_Flag 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 CORR_DIST_TAB_TYPE,
X_Correction_Type IN VARCHAR2,
X_Final_Match_Flag IN VARCHAR2,
X_Total_Amount IN NUMBER,
X_Calling_Sequence IN VARCHAR2);
PROCEDURE Update_Corr_Po_Shipments_Dists(
X_Corr_Dist_Tab IN 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_Amount => x_amount,
X_Quantity_Invoiced => x_quantity,
X_Unit_Price => x_unit_price,
X_Final_Match_Flag => x_final_match_flag,
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_Final_Match_Flag => x_final_match_flag,
X_Unit_Price => x_unit_price,
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 ,
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,
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
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)
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 ,
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 := 'Update Quantity/Amount Billed/Financed on the Po Shipments and 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);
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(asp.income_tax_region_flag,'Y',pvs.state,
asp.income_tax_region))),
ai.project_id,
ai.task_id,
ai.award_id,
ai.expenditure_type,
ai.invoice_date,
ai.expenditure_organization_id,
fsp.inventory_organization_id,
nvl(asp.approval_workflow_flag,'N'),
-- ai.ussgl_transaction_code,- Bug 4277744
asp.allow_flex_override_flag,
ai.org_id,
nvl(fsp.purch_encumbrance_flag,'N'),
ai.award_id,
ai.trx_business_category,
ai.vendor_id,
ai.vendor_site_id,
ai.release_amount_net_of_tax,
ai.source,
ai.recurring_payment_id, -- Bug 7305223
ai.pay_awt_group_id --bug 8222382
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_award_id,
g_expenditure_type,
g_invoice_date,
g_expenditure_organization_id,
g_inventory_organization_id,
g_approval_workflow_flag,
-- g_ussgl_transaction_code,- Bug 4277744
g_allow_flex_override_flag,
g_org_id,
g_encumbrance_flag,
g_award_id,
g_trx_business_category,
g_vendor_id,
g_vendor_site_id,
g_release_amount_net_of_tax,
g_source,
g_recurring_payment_id, -- Bug 7305223
/* Bug 5572876, using base tables */
g_pay_awt_group_id -- bug 8222382
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
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 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;
SELECT pll.shipment_type, pll.po_line_id, pl.recoupment_rate
INTO 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;
l_debug_info := 'select period for accounting date';
SELECT po_distribution_id,
/*PRORATED AMOUNT*/
DECODE(g_min_acct_unit,
'', ROUND( X_match_amount * DECODE(X_match_mode,
'STD-PS',DECODE(X_overbill_flag,
'Y', NVL(PD.quantity_ordered, 0),
NVL(DECODE(SIGN(PD.quantity_ordered -
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
) -
NVL(PD.quantity_cancelled,0)),
-1, 0,
PD.quantity_ordered -
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
) -
NVL(PD.quantity_cancelled,0)
)
,0)
),
NVL(PD.quantity_billed, 0)
)
/ p_total_quantity,
g_precision),
ROUND(((X_match_amount * DECODE(X_match_mode,
'STD-PS',DECODE(X_overbill_flag,
'Y', NVL(PD.quantity_ordered, 0),
NVL(DECODE(SIGN(PD.quantity_ordered -
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
) -
NVL(PD.quantity_cancelled,0)),
-1, 0,
PD.quantity_ordered -
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
) -
NVL(PD.quantity_cancelled,0)
)
,0)
),
NVL(PD.quantity_billed, 0)
)
/ p_total_quantity)
/ g_min_acct_unit) * g_min_acct_unit)),
/*PRORATED QUANTITY*/
ROUND( X_match_quantity * DECODE(X_match_mode,
'STD-PS',DECODE(X_overbill_flag,
'Y', NVL(PD.quantity_ordered, 0),
NVL(DECODE(SIGN(PD.quantity_ordered -
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
) -
NVL(PD.quantity_cancelled,0)),
-1, 0,
PD.quantity_ordered -
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
) -
NVL(PD.quantity_cancelled,0))
, 0)),
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
)
)
/ p_total_quantity,15),
PD.code_combination_id,
PD.accrue_on_receipt_flag,
DECODE(PD.destination_type_code,'EXPENSE',
PD.project_id,NULL), --project_id
DECODE(PD.destination_type_code,'EXPENSE',
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
WHERE line_location_id = x_po_line_location_id;
SELECT accrue_on_receipt_flag,
code_combination_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
ap_invoice_distributions_s.nextval
INTO x_dist_tab(i).accrue_on_receipt_flag,
x_dist_tab(i).po_ccid,
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
WHERE po_distribution_id = x_dist_tab(i).po_distribution_id;
SELECT SUM(DECODE(X_Match_Mode,
'STD-PS',DECODE(X_overbill_flag,
'Y', NVL(pd.quantity_ordered, 0),
NVL(DECODE(SIGN(pd.quantity_ordered
- DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
)
- NVL(pd.quantity_cancelled,0)
),
-1, 0,
quantity_ordered -
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
) -
NVL(pd.quantity_cancelled, 0)
)
,0)
),
DECODE(PD.distribution_type,'PREPAYMENT',
NVL(PD.quantity_financed,0),
NVL(PD.quantity_billed,0)
)
)
)
INTO X_Total_Quantity
FROM po_distributions_ap_v PD
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_Amount IN NUMBER,
X_Quantity_Invoiced IN NUMBER DEFAULT NULL,
X_Unit_Price IN NUMBER DEFAULT NULL,
X_Final_Match_Flag IN VARCHAR2 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;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Invoice_Line(+)');
l_debug_info := 'Inserting Item Line Matched to a PO';
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,
/* 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,
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) --bug8222382
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
--bug 5061826,5601344 (added nvl)
NVL(PLL.DESCRIPTION,PL.ITEM_DESCRIPTION), --description
'HEADER MATCH', --line_source
PLL.ORG_ID, --org_id
PL.ITEM_ID, --inventory_item_id --bug 5061826-PLL to PL
NVL(PLL.DESCRIPTION,PL.ITEM_DESCRIPTION), --item_description
NULL, --serial_number
NULL, --manufacturer
NULL, --model_number
'D', --generate_dists
'ITEM_TO_PO', --match_type
NULL, --distribution_set_id
NULL, --account_segment
NULL, --balancing_segment
NULL, --cost_center_segment
NULL, --overlay_dist_code_concat
--Bug6965650
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
PLL.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
-- PLL.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
PL.TYPE_1099, --type_1099 -- bug 5061826-PLL to PL
NULL, --stat_amount
NULL, --prepay_invoice_id
NULL, --prepay_line_number
NULL, --invoice_includes_prepay_flag
NULL, --corrected_inv_id
NULL, --corrected_line_number
PLL.PO_HEADER_ID, --po_header_id
PLL.PO_LINE_ID, --po_line_id
PLL.PO_RELEASE_ID, --po_release_id
PLL.LINE_LOCATION_ID, --po_line_location_id
DECODE(X_SINGLE_DIST_FLAG,'Y',
X_PO_DISTRIBUTION_ID,NULL),--po_distribution_id
NULL, --rcv_transaction_id
X_FINAL_MATCH_FLAG, --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',PD.project_id,
G_PROJECT_ID),
NULL), --project_id
DECODE(X_SINGLE_DIST_FLAG,'Y',
DECODE(PD.destination_type_code,
'EXPENSE',PD.task_id,
G_TASK_ID),
NULL), --task_id
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_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
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute_category),''),--attribute_category
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute1),''), --attribute1
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute2),''), --attribute2
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute3),''), --attribute3
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute4),''), --attribute4
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute5),''), --attribute5
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute6),''), --attribute6
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute7),''), --attribute7
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute8),''), --attribute8
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute9),''), --attribute9
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute10),''), --attribute10
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute11),''), --attribute11
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute12),''), --attribute12
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute13),''), --attribute13
NVL(DECODE(g_transfer_flag,'Y',PLL.attribute14),''), --attribute14
NVL(DECODE(g_transfer_flag,'Y',PLL.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
PLL.SHIP_TO_LOCATION_ID, --ship_to_location_id
--bugfix:5565310
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
--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 --pay_awt_group_id bug8222382
-- bug 5061826 -- new FROM clause that goes to base tables
FROM PO_LINES_ALL PL,
PO_LINE_LOCATIONS_ALL PLL,
po_distributions pd,
mtl_system_items msi
WHERE pll.line_location_id = x_po_line_location_id
and pd.line_location_id = pll.line_location_id
AND PLL.PO_LINE_ID = PL.PO_LINE_ID
and pd.po_distribution_id = nvl(x_po_distribution_id,pd.po_distribution_id)
and msi.inventory_item_id(+) = pl.item_id
and msi.organization_id(+) = g_inventory_organization_id
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, - 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,
RETAINED_AMOUNT,
RETAINED_AMOUNT_REMAINING,
SHIP_TO_LOCATION_ID,
--bugfix:5565310
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 8222382
)
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
--Bug6965650
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
-- Removed for bug 4277744
-- NULL, --ussgl_transaction_code
'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
X_RETAINED_AMOUNT, --retained_amount
-(X_RETAINED_AMOUNT), --retained_amount_remaining
AIL.SHIP_TO_LOCATION_ID, --ship_to_location_id
--bugfix:5565310
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 --pay_awt_group_id bug8222382
FROM AP_INVOICE_LINES AIL
WHERE AIL.INVOICE_ID = X_INVOICE_ID
AND AIL.LINE_NUMBER = X_ITEM_LINE_NUMBER;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Invoice_Line(-)');
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 Dist_Tab_Type,
X_Final_Match_Flag IN VARCHAR2,
X_Unit_Price IN NUMBER,
X_Total_Amount IN NUMBER,
X_Calling_Sequence IN VARCHAR2) IS
i NUMBER;
l_api_name := 'Insert_Invoice_Distributions';
current_calling_sequence := 'Insert_Invoice_Distributions <-'||x_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Invoice_Distributions(+)');
l_debug_info := 'Insert Invoice Distributions';
l_debug_info := 'Insert invoice distribution corresponding to po_distribution : '||x_dist_tab(i).po_distribution_id;
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,
/*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 8222382
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
--ail.item_description, --description
nvl(ail.description,ail.item_description), --description /*Added NVL for bug 9780743 */
'ITEM_TO_PO', --dist_match_type
'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
-- Removed for bug 4277744
-- NVL(PD.ussgl_transaction_code,
-- 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
NULL, --rcv_transaction_id
x_dist_tab(i).unit_price, --unit_price
ail.unit_meas_lookup_code, --matched_uom_lookup_code
x_dist_tab(i).quantity_invoiced,--quantity_invoiced
x_final_match_flag, --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,NULL), --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,
'INVENTORY','M','SHOP FLOOR','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 bug8222382
FROM po_distributions pd,
ap_invoice_lines ail,
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
AND gcc.code_combination_id = decode(pd.accrue_on_receipt_flag, 'Y', --Bug6014884
pd.code_combination_id,x_dist_tab(i).dist_ccid);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Invoice_Distributions(-)');
X_DIST_TAB.DELETE;
END Insert_Invoice_Distributions;
Procedure Update_PO_Shipments_Dists(
X_Dist_Tab IN OUT NOCOPY 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_MATCHING_PKG.Update_PO_Shipments_Dists(+)');
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_PO_Shipments_Dists(-)');
X_DIST_TAB.DELETE;
END Update_PO_Shipments_Dists;
Insert_Invoice_Line(
X_Invoice_Id => x_invoice_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_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);
l_debug_info := 'Calling Insert_Corr_Invoice_Line';
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_final_match_flag => x_final_match_flag,
x_po_distribution_id => l_po_distribution_id,
x_retained_amount => l_retained_amount,
x_calling_sequence => current_calling_sequence);
l_debug_info := 'Calling Insert_Corr_Invoice_Dists';
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_final_match_flag => x_final_match_flag,
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);
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.po_distribution_id = pd.po_distribution_id /*bugfix:5606697*/
AND aid1.invoice_line_number = ail.line_number
-- 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 *
decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0))/p_total_quantity_billed
) * x_correction_price,
g_precision),
round(((x_correction_quantity * decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0))/
p_total_quantity_billed)*x_correction_price)
/g_min_acct_unit) * g_min_acct_unit
) amount,
round((x_correction_quantity * decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0)))/
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(decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0)))
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 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 Update_Corr_Po_Line_Locations(
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
l_debug_info VARCHAR2(2000);
l_api_name := 'Update_Corr_Po_Line_Locations';
current_calling_sequence := ' Update_Corr_Po_Line_Locations<-'||x_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Line_Locations(+)');
l_debug_info := 'Call PO api to update the po_line_location with quantity/amount billed
information';
RCV_BILL_UPDATING_SV.ap_update_po_line_locations(
x_po_line_location_id => x_po_line_location_id,
x_quantity_billed => x_quantity,
x_uom_lookup_code => x_uom_lookup_code,
x_amount_billed => x_amount, --Modified for bug#9718642 --NULL,
x_matching_basis => 'QUANTITY');
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Line_Locations(-)');
END Update_Corr_Po_Line_Locations;
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_final_match_flag 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);
l_api_name := 'Insert_Corr_Invoice_Line';
current_calling_sequence := 'Insert_Corr_Invoice_Line<-'||x_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Corr_Invoice_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,- 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
)
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
NULL, --rcv_transaction_id
x_final_match_flag, --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 --pay_awt_group_id bug8222382
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;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Corr_Invoice_Line(-)');
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 CORR_DIST_TAB_TYPE,
x_correction_type IN VARCHAR2,
x_final_match_flag IN VARCHAR2,
x_total_amount IN NUMBER,
x_calling_sequence IN VARCHAR2) IS
i NUMBER;
l_api_name := 'Insert_Corr_Invoice_Dists';
current_calling_sequence := 'Insert_Corr_Invoice_Dists<-'||x_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.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
--bug8222382
)
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
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
NULL, --rcv_transaction_id
x_corr_dist_tab(i).unit_price, --unit_price
aid.matched_uom_lookup_code, --matched_uom_lookup_code
NULL, --quantity_invoiced
x_final_match_flag, --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,*/
--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
--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 bug8222382
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 aid.po_distribution_id = pd.po_distribution_id;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Corr_Invoice_Dists(-)');
x_corr_dist_tab.delete;
END Insert_Corr_Invoice_Dists;
l_shipment_table.delete;
l_shipment_table.delete;
SELECT pll.line_location_id,
pll.po_line_id,
pll.po_release_id,
decode(pll.matching_basis_lookup_code,'AMOUNT',
(pll.amount - decode(pll.shipment_type,'PREPAYMENT',
nvl(pll.amount_financed,0),
nvl(pll.amount_billed,0)
)
- nvl(pll.amount_cancelled,0)
),
ap_utilities_pkg.ap_round_currency(
(pll.quantity - decode(pll.shipment_type,'PREPAYMENT',
nvl(pll.quantity_financed,0),
nvl(pll.quantity_billed,0)
)
- nvl(pll.quantity_cancelled,0)
) * pll.price_override, g_invoice_currency_code
)
) amount_available,
pll.item_id,
pll.unit_meas_lookup_code,
pll.price_override,
pll.item_description,
msi.asset_category_id,
-- pll.ussgl_transaction_code, - Bug 4277744
pll.type_1099,
pll.matching_basis_lookup_code,
decode(g_transfer_flag,'Y',pll.attribute_category,NULL) attribute_category,
decode(g_transfer_flag,'Y',pll.attribute1,NULL) attribute1,
decode(g_transfer_flag,'Y',pll.attribute2,NULL) attribute2,
decode(g_transfer_flag,'Y',pll.attribute3,NULL) attribute3,
decode(g_transfer_flag,'Y',pll.attribute4,NULL) attribute4,
decode(g_transfer_flag,'Y',pll.attribute5,NULL) attribute5,
decode(g_transfer_flag,'Y',pll.attribute6,NULL) attribute6,
decode(g_transfer_flag,'Y',pll.attribute7,NULL) attribute7,
decode(g_transfer_flag,'Y',pll.attribute8,NULL) attribute8,
decode(g_transfer_flag,'Y',pll.attribute9,NULL) attribute9,
decode(g_transfer_flag,'Y',pll.attribute10,NULL) attribute10,
decode(g_transfer_flag,'Y',pll.attribute11,NULL) attribute11,
decode(g_transfer_flag,'Y',pll.attribute12,NULL) attribute12,
decode(g_transfer_flag,'Y',pll.attribute13,NULL) attribute13,
decode(g_transfer_flag,'Y',pll.attribute14,NULL) attribute14,
decode(g_transfer_flag,'Y',pll.attribute15,NULL) attribute15
--ETAX: Invwkb
--OPEN ISSUE 1
,pll.ship_to_location_id ship_to_location_id
,decode(pll.matching_basis_lookup_code,'AMOUNT',
(pll.amount - decode(pll.shipment_type,'PREPAYMENT',
nvl(pll.amount_financed,0),
nvl(pll.amount_billed,0)
)
- nvl(pll.amount_cancelled,0)),
(pll.quantity - decode(pll.shipment_type,'PREPAYMENT',
nvl(pll.quantity_financed,0),
nvl(pll.quantity_billed,0)
)- nvl(pll.quantity_cancelled,0)) * decode(pll.price_override,0,1,pll.price_override)) amount_available_unrounded /*bug 8973086*//*9559298*/
/*
pll.primary_intended_use primary_intended_use,
pll.product_fisc_classification product_fisc_classification,
pll.product_type product_type,
pll.product_category product_category,
pll.user_defined_fisc_class user_defined_fisc_class
*/
FROM po_line_locations_ap_v pll,
mtl_system_items msi
WHERE pll.po_header_id = x_po_header_id
AND msi.inventory_item_id(+) = pll.item_id
AND msi.organization_id(+) = g_inventory_organization_id
AND pll.match_option = 'P'
AND pll.closed_code NOT IN ('FINALLY CLOSED') /*, 'CLOSED', 'CLOSED FOR INVOICE')*/ --Bug9323877 --bug 8899681
--Make sure there is some quantity or amount left to be billed
--based on the matching_basis of the shipment
AND ((g_invoice_type_lookup_code <> 'PREPAYMENT'
and (
(pll.matching_basis_lookup_code = 'QUANTITY' and
pll.quantity - nvl(pll.quantity_billed,0) - nvl(pll.quantity_cancelled,0) > 0
) or
(pll.matching_basis_lookup_code = 'AMOUNT' and
pll.amount - nvl(pll.amount_billed,0) - nvl(pll.amount_cancelled,0) > 0
)
)
) OR
(g_invoice_type_lookup_code = 'PREPAYMENT'
and (
(pll.matching_basis_lookup_code = 'QUANTITY' and pll.shipment_type = 'PREPAYMENT' and
pll.quantity - nvl(pll.quantity_financed,0) - nvl(pll.quantity_cancelled,0) > 0
) or
(pll.matching_basis_lookup_code = 'AMOUNT' and pll.shipment_type = 'PREPAYMENT' and
pll.amount - nvl(pll.amount_financed,0) - nvl(pll.amount_cancelled,0) > 0
) or
(pll.matching_basis_lookup_code = 'QUANTITY' and pll.shipment_type <> 'PREPAYMENT' and
pll.quantity - nvl(pll.quantity_billed,0) - nvl(pll.quantity_cancelled,0) > 0
) or
(pll.matching_basis_lookup_code = 'AMOUNT' and pll.shipment_type <> 'PREPAYMENT' and
pll.amount - nvl(pll.amount_billed,0) - nvl(pll.amount_cancelled,0) > 0
)
)
)
)
--make sure the correct shipment type is matched to correct invoice type
AND
((g_invoice_type_lookup_code <> 'PREPAYMENT' and
pll.shipment_type <> 'PREPAYMENT'
) OR
(g_invoice_type_lookup_code = 'PREPAYMENT' and
((pll.payment_type IS NOT NULL and
pll.shipment_type = 'PREPAYMENT') or
(pll.payment_type IS NULL)
)
)
)
ORDER BY pll.line_location_id ;
SELECT rcv.po_line_id,
rcv.po_release_id,
rcv.po_line_location_id,
rcv.rcv_transaction_id,
rcv.receipt_uom_lookup_code,
rcv.po_uom_lookup_code,
rcv.po_unit_price,
rcv.item_id,
rcv.item_description,
msi.asset_category_id,
-- rsl.ussgl_transaction_code, - Bug 4277744
rcv.type_1099,
rcv.matching_basis_lookup_code,
decode(g_transfer_flag,'Y',rsl.attribute_category,NULL) attribute_category,
decode(g_transfer_flag,'Y',rsl.attribute1,NULL) attribute1,
decode(g_transfer_flag,'Y',rsl.attribute2,NULL) attribute2,
decode(g_transfer_flag,'Y',rsl.attribute3,NULL) attribute3,
decode(g_transfer_flag,'Y',rsl.attribute4,NULL) attribute4,
decode(g_transfer_flag,'Y',rsl.attribute5,NULL) attribute5,
decode(g_transfer_flag,'Y',rsl.attribute6,NULL) attribute6,
decode(g_transfer_flag,'Y',rsl.attribute7,NULL) attribute7,
decode(g_transfer_flag,'Y',rsl.attribute8,NULL) attribute8,
decode(g_transfer_flag,'Y',rsl.attribute9,NULL) attribute9,
decode(g_transfer_flag,'Y',rsl.attribute10,NULL) attribute10,
decode(g_transfer_flag,'Y',rsl.attribute11,NULL) attribute11,
decode(g_transfer_flag,'Y',rsl.attribute12,NULL) attribute12,
decode(g_transfer_flag,'Y',rsl.attribute13,NULL) attribute13,
decode(g_transfer_flag,'Y',rsl.attribute14,NULL) attribute14,
decode(g_transfer_flag,'Y',rsl.attribute15,NULL) attribute15
--ETAX: Invwkb
--OPEN ISSUE 1
,rcv.ship_to_location_id ship_to_location_id
/*
rcv.primary_intended_use primary_intended_use,
rcv.product_fisc_classification product_fisc_classification,
rcv.product_type product_type,
rcv.product_category product_category,
rcv.user_defined_fisc_class user_defined_fisc_class
*/
FROM po_ap_receipt_match_v rcv,
mtl_system_items msi,
rcv_shipment_lines rsl
WHERE rcv.po_header_id = x_po_header_id
AND msi.inventory_item_id(+) = rcv.item_id
AND msi.organization_id(+) = g_inventory_organization_id
AND rcv.rcv_shipment_line_id = rsl.shipment_line_id
AND rcv.po_match_option = 'R'
ORDER BY rcv.rcv_transaction_id;
x_shipment_table.delete;
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,
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,
--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)
VALUES( X_INVOICE_ID, --invoice_id
X_SHIPMENT_TABLE(i).line_number, --line_number
'ITEM', --line_type_lookup_code
NULL, --requester_id
X_SHIPMENT_TABLE(i).item_description,--description
'HEADER MATCH', --line_source
G_ORG_ID, --org_id
X_SHIPMENT_TABLE(i).inventory_item_id,--inventory_item_id
X_SHIPMENT_TABLE(i).item_description,--item_description
NULL, --serial_number
NULL, --manufacturer
NULL, --model_number
'Y', --generate_dists
DECODE(X_MATCH_OPTION,'P',
(DECODE(X_SHIPMENT_TABLE(i).matching_basis,
'QUANTITY', 'ITEM_TO_PO', 'ITEM_TO_SERVICE_PO')),
(DECODE(X_SHIPMENT_TABLE(i).matching_basis,
'QUANTITY', 'ITEM_TO_RECEIPT', 'ITEM_TO_SERVICE_RECEIPT'))), --match_type
NULL, --distribution_set_id
NULL, --account_segment
NULL, --balancing_segment
NULL, --cost_center_segment
NULL, --overlay_dist_code_concat
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_SHIPMENT_TABLE(i).AMOUNT, --amount
X_SHIPMENT_TABLE(i).BASE_AMOUNT, --base_amount
X_SHIPMENT_TABLE(i).rounding_amt, --rounding_amount
X_SHIPMENT_TABLE(i).QUANTITY_INVOICED,--quantity_invoiced
X_SHIPMENT_TABLE(i).UOM, --unit_meas_lookup_code
X_SHIPMENT_TABLE(i).UNIT_PRICE, --unit_price
DECODE(G_APPROVAL_WORKFLOW_FLAG,'Y'
,'REQUIRED','NOT REQUIRED'),--wf_approval_status
-- Removed for bug 4277744
-- X_SHIPMENT_TABLE(i).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
X_SHIPMENT_TABLE(i).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
X_SHIPMENT_TABLE(i).PO_HEADER_ID, --po_header_id
X_SHIPMENT_TABLE(i).PO_LINE_ID, --po_line_id
X_SHIPMENT_TABLE(i).PO_RELEASE_ID,--po_release_id
X_SHIPMENT_TABLE(i).PO_LINE_LOCATION_ID,--po_line_location_id
NULL, --po_distribution_id
DECODE(X_MATCH_OPTION,'P',NULL,
X_SHIPMENT_TABLE(i).RCV_TRANSACTION_ID), --rcv_transaction_id
NULL, --final_match_flag
'N', --assets_tracking_flag
G_ASSET_BOOK_TYPE_CODE, --asset_book_type_code
X_SHIPMENT_TABLE(i).ASSET_CATEGORY_ID,--asset_category_id
NULL, --project_id
NULL, --task_id
NULL, --expenditure_type
NULL, --expenditure_item_date
NULL, --expenditure_organization_id
NULL, --pa_quantity
NULL, --pa_cc_ar_invoice_id
NULL, --pa_cc_ar_invoice_line_num
NULL, --pa_cc_processed_code
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
X_SHIPMENT_TABLE(i).ATTRIBUTE_CATEGORY,--attribute_category
X_SHIPMENT_TABLE(i).ATTRIBUTE1, --attribute1
X_SHIPMENT_TABLE(i).ATTRIBUTE2, --attribute2
X_SHIPMENT_TABLE(i).ATTRIBUTE3, --attribute3
X_SHIPMENT_TABLE(i).ATTRIBUTE4, --attribute4
X_SHIPMENT_TABLE(i).ATTRIBUTE5, --attribute5
X_SHIPMENT_TABLE(i).ATTRIBUTE6, --attribute6
X_SHIPMENT_TABLE(i).ATTRIBUTE7, --attribute7
X_SHIPMENT_TABLE(i).ATTRIBUTE8, --attribute8
X_SHIPMENT_TABLE(i).ATTRIBUTE9, --attribute9
X_SHIPMENT_TABLE(i).ATTRIBUTE10, --attribute10
X_SHIPMENT_TABLE(i).ATTRIBUTE11, --attribute11
X_SHIPMENT_TABLE(i).ATTRIBUTE12, --attribute12
X_SHIPMENT_TABLE(i).ATTRIBUTE13, --attribute13
X_SHIPMENT_TABLE(i).ATTRIBUTE14, --attribute14
X_SHIPMENT_TABLE(i).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_SHIPMENT_TABLE(i).retained_amount, -- retained_amount
(-X_SHIPMENT_TABLE(i).retained_amount), -- retained_amount_remaining
--bugfix:5565310
X_SHIPMENT_TABLE(i).SHIP_TO_LOCATION_ID, --ship_to_location_id
X_SHIPMENT_TABLE(i).PRIMARY_INTENDED_USE, --primary_intended_use
X_SHIPMENT_TABLE(i).PRODUCT_FISC_CLASSIFICATION, --product_fisc_classification
G_TRX_BUSINESS_CATEGORY --trx_business_category
,X_SHIPMENT_TABLE(i).PRODUCT_TYPE, --product_type
X_SHIPMENT_TABLE(i).PRODUCT_CATEGORY, --product_category
X_SHIPMENT_TABLE(i).USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
--Introduced DECODE for bug#13505998
DECODE(X_SHIPMENT_TABLE(i).AMOUNT,l_full_mtch_amt,X_SHIPMENT_TABLE(i).ASSESSABLE_VALUE,NULL),
X_SHIPMENT_TABLE(i).TAX_CLASSIFICATION_CODE,
G_PAY_AWT_GROUP_ID --pay_awt_group_id bug8222382
);
Select pll.*
From po_line_locations_all pll
Where pll.po_header_id = c_po_header_id
And nvl(retainage_withheld_amount,0) - nvl(retainage_released_amount,0) <> 0;
SELECT set_of_books_id
INTO l_primary_sob_id
FROM ap_system_parameters;
SELECT chart_of_accounts_id
INTO l_chart_of_accts_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_primary_sob_id;