[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
in AP_INVOICE_LINES for Receipt/Receipt Line the user selects to match to
in the Other Charge Matching Window. Invoice Distributions will be generated
immediately during the matching, 1 per invoice line.
Either the total amount is prorated (if
prorate_flag is 'Y') or the user specified amounts are stored for each
rcv_transaction_id checked in the form. No allocations will be created
for this charge line .
Description of the input parameters:
------------------------------------
X_invoice_id Id of Invoice that needs to be matched(CM or STD)
X_invoice_line_number Invoice Line number when the charge match is done
from a invoice line or from the import.
X_line_type Line Type of the charge line. Can be either
FREIGHT or MISC
X_prorate_flag Flag which indicates whether x_total amount needs
to be prorated across all the rcv_transactions
X_account_id The dist_code_combination_id to be used when creating
the distributions. Can be NULL.
X_description Description to be stored on the invoice distributions
X_total_amount The total amount that needs to be matched(linked)
to the receipts
X_othr_chrg_tab Pl/SQL table containing the rcv_transaction_id,
charge_amount and rcv_transaction_qty for each
row checked in the form.
X_row_count Number of rows in thr pl/sql table
X_calling_sequence Calling Sequence */
--Local Procedures
Procedure Get_Info(x_invoice_id IN NUMBER,
x_calling_sequence IN VARCHAR2);
Procedure Insert_Invoice_Line( x_invoice_id IN NUMBER,
x_invoice_line_number IN NUMBER,
x_line_type IN VARCHAR2,
x_cost_factor_id IN NUMBER,
x_amount IN NUMBER,
x_base_amount IN NUMBER,
x_rounding_amt IN NUMBER,
x_rcv_transaction_id IN NUMBER,
x_ccid IN NUMBER,
x_description IN VARCHAR2,
x_calling_sequence IN VARCHAR2);
Procedure Insert_Invoice_dist (X_invoice_id IN NUMBER,
X_invoice_line_number IN NUMBER,
X_description IN VARCHAR2,
X_calling_sequence IN VARCHAR2) ;
l_debug_info := 'Select information from ap_invoices';
Select RCV.po_line_location_id,
RCV.vendor_id,
RCV.vendor_site_id,
RSL.ship_to_location_id
Into l_po_line_location_id,
l_vendor_id,
l_vendor_site_id,
l_ship_to_location_id
From rcv_transactions RCV,
rcv_shipment_lines RSL
Where RCV.transaction_id = l_othr_chrg_tab(1).rcv_txn_id
And RCV.shipment_line_id = RSL.shipment_line_id
And rownum < 2;
Select invoice_type_lookup_code
Into l_invoice_type_lookup_code
From ap_invoices_all ai
Where ai.invoice_id = x_invoice_id;
Insert_Invoice_Dist(x_invoice_id => x_invoice_id,
x_invoice_line_number => x_invoice_line_number,
x_description => x_description,
x_calling_sequence => current_calling_sequence);
UPDATE ap_invoice_lines
SET generate_dists ='D'
WHERE invoice_id = x_invoice_id
AND line_number = x_invoice_line_number;
SELECT ai.batch_id,
ai.invoice_currency_code,
ai.exchange_rate,
ai.vendor_id,
ai.vendor_site_id,
ai.invoice_date,
asp.base_currency_code,
ai.gl_date,
ai.set_of_books_id,
pv.type_1099,
decode(pv.type_1099,'','',
decode(combined_filing_flag,'N',NULL,
decode(asp.income_tax_region_flag,'Y',pvs.state,
asp.income_tax_region))),
asp.approval_workflow_flag,
asp.transfer_desc_flex_flag,
ai.trx_business_category,
ai.org_id
INTO g_batch_id,
g_invoice_currency_code,
g_exchange_rate,
g_vendor_id,
g_vendor_site_id,
g_invoice_date,
g_base_currency_code,
g_accounting_date,
g_set_of_books_id,
g_type_1099,
g_income_tax_region,
g_approval_workflow_flag,
g_transfer_flag,
g_trx_business_category,
g_org_id
FROM ap_invoices_all ai, --bug 5056051
ap_system_parameters asp,
ap_suppliers pv, --bug 5056051
ap_supplier_sites pvs --bug 5056051
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;
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 = 'CORPORATE'
AND fc.set_of_books_id = g_set_of_books_id
AND fc.date_ineffective IS NULL;
l_debug_info := 'select period for accounting date';
l_debug_info := 'Calling Insert_Invoice_Line';
Insert_Invoice_Line(x_invoice_id => x_invoice_id,
x_invoice_line_number => l_invoice_line_number,
x_line_type => x_line_type,
x_cost_factor_id => x_cost_factor_id,
x_amount => x_othr_chrg_tab(i).charge_amt,
x_base_amount => x_othr_chrg_tab(i).base_amt,
x_rounding_amt => x_othr_chrg_tab(i).rounding_amt,
x_rcv_transaction_id => x_othr_chrg_tab(i).rcv_txn_id,
x_ccid => x_ccid,
x_description => x_description,
x_calling_sequence => current_calling_sequence);
l_debug_info := 'Calling Insert_Invoice_Dist';
Insert_Invoice_Dist(x_invoice_id => x_invoice_id,
x_invoice_line_number => l_invoice_line_number,
x_description => x_description,
x_calling_sequence => current_calling_sequence);
x_othr_chrg_tab.delete;
Procedure Insert_Invoice_Line( x_invoice_id IN NUMBER,
x_invoice_line_number IN NUMBER,
x_line_type IN VARCHAR2,
x_cost_factor_id IN NUMBER,
x_amount IN NUMBER,
x_base_amount IN NUMBER,
x_rounding_amt IN NUMBER,
x_rcv_transaction_id IN NUMBER,
x_ccid IN NUMBER,
x_description IN VARCHAR2,
x_calling_sequence IN VARCHAR2) IS
l_debug_info VARCHAR2(100);
current_calling_sequence := 'Insert_Invoice_Line<-'||x_calling_sequence;
INSERT INTO AP_INVOICE_LINES
(INVOICE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
/*OPEN ISSUE 2*/
--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,
/*OPEN ISSUE 2*/
/*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,
/*OPEN ISSUE 2 */
--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,
--ETAX: Invwkb
--OPEN ISSUE 2
--bug5708602
SHIP_TO_LOCATION_ID,
PRIMARY_INTENDED_USE,
PRODUCT_FISC_CLASSIFICATION,
TRX_BUSINESS_CATEGORY
,PRODUCT_TYPE,
PRODUCT_CATEGORY,
USER_DEFINED_FISC_CLASS
,COST_FACTOR_ID
)
SELECT X_INVOICE_ID, --invoice_id
X_INVOICE_LINE_NUMBER, --invoice_line_number
X_LINE_TYPE, --line_type_lookup_code
/*OPEN ISSUE 2*/
--NULL, --requester_id
x_description, --description
'HEADER MATCH', --line_source
rcv.org_id, --org_id
NULL, --inventory_item_id
NULL, --item_Description
NULL, --serial_number
NULL, --manufacturer
NULL, --model_number
'D', --generate_dists
'OTHER_TO_RECEIPT', --match_type
NULL, --distribution_set_id
NULL, --account_segment
NULL, --balancing_Segment
NULL, --cost_center_segment
NULL, --overlay_dist_code_concat
x_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
x_base_amount, --base_amount
x_rounding_amt, --rounding_amt
NULL, --quantity_invoiced
NULL, --unit_meas_lookup_code
NULL, --unit_price
decode(g_approval_workflow_flag,'Y'
,'REQUIRED','NOT REQUIRED'), --wfapproval_status
-- Removed for bug 4277744
-- rsl.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
g_type_1099, --type_1099
NULL, --stat_amount
NULL, --prepay_invoice_id
NULL, --prepay_line_number
NULL, --invoice_includes_prepay_flag
NULL, --corrected_inv_id
NULL, --corrected_line_number
rcv.po_header_id, --po_header_id
rcv.po_line_id, --po_line_id
rcv.po_release_id, --po_release_id
rcv.po_line_location_id, --po_line_location_id
NULL, --po_distribution_id
x_rcv_transaction_id, --rcv_transaction_id
NULL, --final_match_flag
'N', --assets_tracking_flag
g_asset_book_type_code, --asset_book_type_code
NULL, --asset_category_id
/*OPEN ISSUE 2*/
/*
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
/*OPEN ISSUE 2*/
/* NULL, */ --award_id
NULL, --awt_group_id
NULL, --reference_1
NULL, --reference_2
NULL, --receipt_verified_flag
NULL, --receipt_required_flag
NULL, --receipt_missing_flag
NULL, --justification
NULL, --expense_group
NULL, --start_expense_date
NULL, --end_expense_date
NULL, --receipt_currency_amount
NULL, --receipt_conversion_rate
NULL, --receipt_currency_amount
NULL, --daily_amount
NULL, --web_parameter_id
NULL, --adjustment_reason
NULL, --merchant_document_number
NULL, --merchant_name
NULL, --merchant_reference
NULL, --merchant_tax_reg_number
NULL, --merchant_taxpayer_id
NULL, --country_of_supply
NULL, --credit_card_trx_id
NULL, --company_prepaid_invoice_id
NULL, --cc_reversal_flag
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute_category),''),--attribute_category
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute1),''), --attribute1
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute2),''), --attribute2
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute3),''), --attribute3
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute4),''), --attribute4
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute5),''), --attribute5
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute6),''), --attribute6
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute7),''), --attribute7
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute8),''), --attribute8
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute9),''), --attribute9
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute10),''), --attribute10
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute11),''), --attribute11
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute12),''), --attribute12
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute13),''), --attribute13
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute14),''), --attribute14
NVL(DECODE(g_transfer_flag,'Y',rsl.attribute15),''), --attribute15
/* OPEN ISSUE 1 */
/* 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, */
sysdate, --creation_date
g_user_id, --created_by
g_user_id, --last_updated_by
sysdate, --last_update_date
g_login_id, --last update login
NULL, --program_application_id
NULL, --program_id
NULL, --program_update_date
NULL, --request_date
--ETAX: Invwkb
--OPEN ISSUE 2
--bug5708602
RCV.SHIP_TO_LOCATION_ID, --ship_to_location_id
G_intended_use, --primary_intended_use
G_product_fisc_class, --product_fisc_classification
G_TRX_BUSINESS_CATEGORY, --trx_business_category
G_product_type, --product_type
G_product_category, --product_category
G_user_defined_fisc_class, --user_defined_fisc_class
X_COST_FACTOR_ID --cost_factor_id
FROM po_ap_receipt_match_v rcv,
rcv_shipment_lines rsl
WHERE rcv.rcv_transaction_id = x_rcv_transaction_id
AND rsl.shipment_line_id = rcv.rcv_shipment_line_id;
END Insert_Invoice_Line;
INSERT_INVOICE_DIST
This procedure inserts a distribution into ap_invoice distributions.
--------------------------------------------------------------------------*/
Procedure Insert_Invoice_dist (
X_invoice_id IN NUMBER,
X_invoice_line_number IN NUMBER,
X_description IN VARCHAR2,
x_calling_sequence IN VARCHAR2) IS
l_invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
current_calling_sequence := 'Insert_Invoice_Dist <-' ||X_calling_sequence;
l_debug_info := 'insert into ap_invoice_distributions';
INSERT INTO ap_invoice_distributions (
batch_id,
invoice_id,
invoice_line_number,
invoice_distribution_id,
distribution_line_number,
line_type_lookup_code,
description,
dist_match_type,
distribution_class,
org_id,
dist_code_combination_id,
accounting_date,
period_name,
accrual_posted_flag,
cash_posted_flag,
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,
pa_cc_ar_invoice_id,
pa_cc_ar_invoice_line_num,
pa_cc_processed_code,
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,
/*Invoice Lines: OPEN ISSUE2*/
/*attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15, */
/* Invoice Lines */
/*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,*/
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
--OPEN ISSUE 1
/*,intended_use*/
--Freight and Special Charges
rcv_charge_addition_flag)
SELECT g_batch_id, --batch_id
x_invoice_id, --invoice_id
x_invoice_line_number, --invoice_line_number
ap_invoice_distributions_s.nextval, --invoice_distribution_id
1, --distribution_line_number
ail.line_type_lookup_code, --line_type_lookup_code
ail.description, --description
'OTHER_TO_RECEIPT', --dist_match_type
'PERMANENT', --distribution_class
ail.org_id, --org_id
ail.default_dist_ccid, --dist_code_combination_id
ail.accounting_date, --accounting_date
ail.period_name, --period_name
'N', --accrual_posted_flag
'N', --cash_posted_flag
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
ail.amount, --amount
ail.base_amount, --base_amount
ail.rounding_amt, --rounding_amt
NULL, --match_status_flag
'N', --encumbered_flag
NULL, --packet_id
-- ail.ussgl_transaction_code, --ussgl_transaction_code - Bug 4277744
-- NULL, --ussgl_trx_code_context - Bug 4277744
'N', --reversal_flag
NULL, --parent_reversal_id
'N', --cancellation_flag
decode(g_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
NULL, --po_distribution_id
ail.rcv_transaction_id, --rcv_transaction_id
NULL, --unit_price
NULL, --matched_uom_lookup_code
NULL, --quantity_invoiced
NULL, --final_match_flag
NULL, --related_id
'U', --assets_addition_flag
decode(gcc.account_type,'E',
ail.assets_tracking_flag,
'A','Y','N'), --assets_tracking_flag
decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,
'A','Y','N'),'Y',ail.asset_book_type_code,NULL), --asset_book_type_code
decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,
'A','Y','N'),'Y',ail.asset_category_id,NULL), --asset_category_id
NULL, --pa_cc_ar_invoice_id
NULL, --pa_cc_ar_invoice_line_num
NULL, --pa_cc_processed_code
NULL, --gms_burdenable_raw_cost
NULL, --awt_flag
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
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
/*OPEN ISSUE2*/
/*
NULL, --attribute_category
NULL, --attribute1
NULL, --attribute2
NULL, --attribute3
NULL, --attribute4
NULL, --attribute5
NULL, --attribute6
NULL, --attribute7
NULL, --attribute8
NULL, --attribute9
NULL, --attribute10
NULL, --attribute11
NULL, --attribute12
NULL, --attribute13
NULL, --attribute14
NULL, --attribute15
*/
/*OPEN ISSUE1*/
/*
NULL, --global_attribute_category
NULL, --global_attribute1
NULL, --global_attribute2
NULL, --global_attribute3
NULL, --global_attribute4
NULL, --global_attribute5
NULL, --global_attribute6
NULL, --global_attribute7
NULL, --global_attribute8
NULL, --global_attribute9
NULL, --global_attribute10
NULL, --global_attribute11
NULL, --global_attribute12
NULL, --global_attribute13
NULL, --global_attribute14
NULL, --global_attribute15 */
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
--OPEN ISSUE 1
/*,rcv.intended_use */
'N' --rcv_charge_addition_flag
FROM ap_invoice_lines AIL,
gl_code_combinations GCC,
rcv_transactions rcv
WHERE ail.invoice_id = x_invoice_id
AND ail.line_number = x_invoice_line_number
AND ail.rcv_transaction_id = rcv.transaction_id
AND gcc.code_combination_id = ail.default_dist_ccid
AND rcv.transaction_id = ail.rcv_transaction_id;
UPDATE ap_invoice_distributions_all id
SET (project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
award_id) =
(SELECT
DECODE(PD.destination_type_code,'EXPENSE',
PD.project_id,'SHOP FLOOR',PD.project_id,
'INVENTORY',PD.project_id), --project_id
DECODE(PD.destination_type_code,'EXPENSE',
PD.task_id,'SHOP FLOOR',PD.task_id,
'INVENTORY',PD.task_id), --task_id
DECODE(PD.destination_type_code,'EXPENSE',
PD.expenditure_type,
'SHOP FLOOR',PD.expenditure_type,
'INVENTORY', PD.expenditure_type), --expenditure_type
DECODE(PD.destination_type_code,
'EXPENSE',PD.expenditure_item_date,
'SHOP FLOOR', PD.expenditure_item_date,
'INVENTORY',PD.expenditure_item_date), --expenditure_item_date
DECODE(PD.destination_type_code,
'EXPENSE',PD.expenditure_organization_id,
'SHOP FLOOR', PD.expenditure_organization_id,
'INVENTORY', PD.expenditure_organization_id), --expenditure_organization_id
DECODE(PD.destination_type_code,
'EXPENSE', PD.award_id) --award_id
FROM ap_invoice_distributions_all aid,
rcv_transactions rcv,
po_distributions_all pd
WHERE aid.invoice_distribution_id = l_invoice_distribution_id
AND aid.rcv_transaction_id = rcv.transaction_id
AND rcv.po_distribution_id = pd.po_distribution_id)
WHERE id.invoice_distribution_id = l_invoice_distribution_id;
End Insert_Invoice_dist;