Hi, everybody
How can I retrieve the fields displayed on the manual supplier invoice screen from the database?
"SQL Column" field is disable and not available in “LOV Reference” field.
Retrieving Fields from Manual Supplier Invoice Screen Database
Page 1 / 1
Hi
The fields you refer to are Client fields that do not show SQL Column in System Info. But you can use MAN_SUPP_INVOICE and MAN_SUPP_INVOICE_ITEM views to get the corresponding columns for these fields.
Hope this will help.
Regards,
Akila
I couldn't find the MAN_SUPP_INVOICE and MAN_SUPP_INVOICE_ITEM views. I believe some fields above are default, and some fields are also calculated. I'm wondering if I can retrieve these fields from anywhere else in the database.
Below is the view definition for MAN_SUPP_INVOICE in IFS Cloud 24R1:
CREATE OR REPLACE VIEW MAN_SUPP_INVOICE AS
SELECT
t0."COMPANY",t0."INVOICE_ID",t0."IDENTITY",t0."PARTY_TYPE",t0."PARTY_TYPE_DB",t0."SERIES_ID",t0."INVOICE_NO",t0."INVOICE_DATE",t0."DUE_DATE",t0."CREATOR",t0."DELIVERY_DATE",t0."ARRIVAL_DATE",t0."CURR_RATE",t0."DIV_FACTOR",t0."INVOICE_TYPE",t0."PAY_TERM_ID",t0."SERIES_REFERENCE",t0."NUMBER_REFERENCE",t0."DELIVERY_IDENTITY",t0."DELIVERY_ADDRESS_ID",t0."INVOICE_ADDRESS_ID",t0."CREATORS_REFERENCE",t0."NATIONAL_BANK_CODE",t0."NATURE_OF_BUSINESS",t0."CODE_A",t0."CODE_B",t0."CODE_C",t0."CODE_D",t0."CODE_E",t0."CODE_F",t0."CODE_G",t0."CODE_H",t0."CODE_I",t0."CODE_J",t0."PROJECT_ID",t0."PROJECT_ACTIVITY_ID",t0."AFF_LINE_POST",t0."PRELIM_CODE",t0."CREATION_DATE",t0."DB_PRE_ACC_CODE_STRING",t0."PAYMENT_ADDRESS_ID",t0."WAY_PAY_ID",t0."VOUCHER_NO_REF",t0."VOUCHER_TYPE_REF",t0."VOUCHER_DATE_REF",t0."LANGUAGE_CODE",t0."PL_PAY_DATE",t0."INV_ACTUAL_NET_CURR_AMT",t0."INV_VAT_CURR_AMT",t0."ACTUAL_NET_CURR_AMOUNT",t0."ACTUAL_NET_DOM_AMOUNT",t0."ACTUAL_NET_PARALLEL_AMOUNT",t0."NON_DEDUCT_TAX_CURR_AMOUNT",t0."NON_DEDUCT_TAX_DOM_AMOUNT",t0."NON_DED_TAX_PARALLEL_AMT",t0."NET_CURR_AMOUNT",t0."NET_DOM_AMOUNT",t0."VAT_CURR_AMOUNT",t0."VAT_DOM_AMOUNT",t0."TOTAL_TAX_CURR_AMOUNT",t0."TOTAL_TAX_DOM_AMOUNT",t0."TOTAL_TAX_PARALLEL_AMOUNT",t0."CURRENCY",t0."CHK_HANDLING_CODE",t0."SENT",t0."MULTI_COMPANY_INVOICE",t0."TRANSFER_STATUS",t0."TRANSFER_STATUS_DB",t0."AUTOMATIC_INVOICE",t0."TRANSFER_ERROR",t0."INVOICE_VERSION",t0."PAYER_IDENTITY",t0."DEDUCTION_GROUP",t0."NCF_REFERENCE",t0."BRANCH",t0."PAY_TERM_BASE_DATE",t0."ADV_INV",t0."INVOICE_RECIPIENT",t0."PROPOSAL_EXIST",t0."BATCH_ID",t0."SELF_BILLING_REF",t0."BANK_ACCOUNT",t0."TAX_CURR_RATE",t0."TAX_INVOICE_SERIES_ID",t0."TAX_INVOICE_NUMBER",t0."TAX_INVOICE_DATE",t0."JS_INVOICE_STATE",t0."JS_INVOICE_STATE_DB",t0."PRICE_ADJUSTMENT",t0."ORIGINAL_RESULT_KEY",t0."TAX_CURR_TYPE",t0."CORRECTION_INVOICE_ID",t0."SUB_CON_NO",t0."INVOICING_ADVICE_ID",t0."MSG_SEQUENCE_NO",t0."MSG_VERSION_NO",t0."MEDIA_CODE",t0."SEND_STATUS",t0."SEND_STATUS_DB",t0."SEND_TIME",t0."SEND_ERROR",t0."ATTACHMENTS",t0."SEND_FLAG_ATTR",t0."OLD_ADV_INV",t0."SUPPLY_COUNTRY",t0."SUPPLY_COUNTRY_DB",t0."TAX_LIABILITY",t0."TAX_ID_TYPE",t0."TAX_ID_NUMBER",t0."LOAD_TYPE",t0."ONE_TIME_ADDRESS_ID",t0."ADDITIONAL_REFERENCE",t0."NET_PARALLEL_AMOUNT",t0."PARALLEL_CURR_RATE",t0."PARALLEL_DIV_FACTOR",t0."VAT_PARALLEL_AMOUNT",t0."VOUCHER_TEXT",t0."POST_PREL_TAX_WITH",t0."POST_PREL_TAX_WITH_DB",t0."WHT_AMOUNT_BASE",t0."WHT_AMOUNT_BASE_DB",t0."PREPAY_BASED_INV",t0."PREPAY_BASED_INV_DB",t0."USE_PROJ_ADDRESS_FOR_TAX",t0."USE_PROJ_ADDRESS_FOR_TAX_DB",t0."CORRECTION_REASON_ID",t0."CORRECTION_REASON",t0."INVOICE_TEXT_ID",t0."INVOICE_TEXT",t0."CUSTOMS_DECLARATION_DATE",t0."LATEST_RESULT_KEY",t0."SII_PROPOSAL",t0."SII_PROPOSAL_DB",t0."OPERATIONAL_KEY",t0."OPERATIONAL_KEY_DB",t0."INVOICE_REASON_ID",t0."COMPONENT_A",t0."COMPONENT_B",t0."COMPONENT_C",t0."SEQUENCE_NUMBER",t0."OFFICIAL_DOCUMENT_NO",t0."SERIAL_NUMBER",t0."OFFICIAL_INVOICE_NO",t0."CREDIT_INVOICE_ID",t0."SERVICE_CODE",t0."POSTED_DATE",t0."XML_FILE_NAME_SUFFIX",t0."OUT_INV_VOU_DATE_BASE",t0."OUT_INV_VOU_DATE_BASE_DB",t0."OUT_INV_CURR_RATE_BASE",t0."OUT_INV_CURR_RATE_BASE_DB",t0."TAX_SELL_CURR_RATE_BASE",t0."TAX_SELL_CURR_RATE_BASE_DB",t0."PREPAYMENT_TYPE_CODE",t0."PREPAY_ADV_INV_ID",t0."PREPAY_LEDGER_ITEM_SERIES",t0."PREPAY_LEDGER_ITEM_ID",t0."PREPAY_LEDGER_ITEM_VERSION",t0."INTERNAL_SERIES_ID",t0."INTERNAL_INVOICE_NO",t0."ABOVE_TAX_CONTROL_LIMIT",t0."ABOVE_TAX_CONTROL_LIMIT_DB",t0."DOCUMENT_TYPE_CODE",t0."EINVOICE_SENT",t0."EINVOICE_SENT_DB",t0."RELATED_UUID_NUMBER",t0."IRN",t0."QR_DATA",t0."TAX_WITHH_CURR_RATE",t0."INVOICE_TAX_ID_NUMBER",t0."DELIVERY_TAX_ID_NUMBER",t0."SUPPLY_DELIVERY_ADDRESS_ID",t0."DELIVERY_INV_ADDRESS_ID",t0."USE_DELIVERY_INV_ADDRESS",t0."USE_DELIVERY_INV_ADDRESS_DB",t0."EXCLUDE_POSTING_AUTH",t0."EXCLUDE_POSTING_AUTH_DB",t0."EINVOICE_REFERENCE_DATE",t0."BUSINESS_TRANSACTION_ID",t0."CURR_DIFFERENCE_INVOICE",t0."CURR_DIFFERENCE_INVOICE_DB",t0."SUPPLY_TYPE_CODE",t0."CONSOLIDATED_SERIES_ID",t0."CONSOLIDATED_INVOICE_NO",t0."INV_GROSS_CURR_AMT",t0."GROSS_CURR_AMOUNT",t0."GROSS_DOM_AMOUNT",t0."AUTH_ID",t0."POSTED",t0."AMOUNT_METHOD",t0."FINALY_POSTED",t0."CASH",t0."INVOICE_DUE_DATE",t0."PREPOSTING_EXIST",t0."VOU_DATE",t0."PO_REF_NUMBER",t0."INVOICE_PL_PAY_DATE",t0."ACCOUNTING_YEAR_REF",t0."ACCOUNTING_PERIOD_REF",t0."AUTHORIZE_FOR_PAY",t0."PAY_PLAN",t0."RECEIPT_REF",t0."POST_ERROR",t0."ERROR_TEXT",t0."GROUP_ID",t0."LS_ADV_INV",t0."DEBIT_INVOICE_ID",t0."AMORTIZED_AMOUNT",t0."OBJSTATE",t0."OBJEVENTS",t0."STATE",t0."OBJKEY",t0."OBJTYPE",t0."OBJVERSION",t0."OBJID",
t35.party comp_party
FROM (SELECT
company company,
invoice_id invoice_id,
identity identity,
Party_Type_API.Decode(party_type) party_type,
party_type party_type_db,
series_id series_id,
invoice_no invoice_no,
invoice_date invoice_date,
Payment_Plan_API.Get_Open_Original_Due_Date(company, invoice_id) due_date,
creator creator,
delivery_date delivery_date,
arrival_date arrival_date,
curr_rate curr_rate,
div_factor div_factor,
invoice_type invoice_type,
pay_term_id pay_term_id,
series_reference series_reference,
number_reference number_reference,
delivery_identity delivery_identity,
delivery_address_id delivery_address_id,
invoice_address_id invoice_address_id,
creators_reference creators_reference,
national_bank_code national_bank_code,
nature_of_business nature_of_business,
code_a code_a,
code_b code_b,
code_c code_c,
code_d code_d,
code_e code_e,
code_f code_f,
code_g code_g,
code_h code_h,
code_i code_i,
code_j code_j,
project_id project_id,
project_activity_id project_activity_id,
aff_line_post aff_line_post,
prelim_code prelim_code,
creation_date creation_date,
pre_acc_code_string db_pre_acc_code_string,
payment_address_id payment_address_id,
way_pay_id way_pay_id,
voucher_no_ref voucher_no_ref,
voucher_type_ref voucher_type_ref,
voucher_date_ref voucher_date_ref,
language_code language_code,
Payment_Plan_API.Get_First_Open_Due_Date(company, invoice_id) pl_pay_date,
inv_actual_net_curr_amt inv_actual_net_curr_amt,
inv_vat_curr_amt inv_vat_curr_amt,
actual_net_curr_amount actual_net_curr_amount,
actual_net_dom_amount actual_net_dom_amount,
actual_net_parallel_amount actual_net_parallel_amount,
non_deduct_tax_curr_amount non_deduct_tax_curr_amount,
non_deduct_tax_dom_amount non_deduct_tax_dom_amount,
non_ded_tax_parallel_amt non_ded_tax_parallel_amt,
net_curr_amount net_curr_amount,
net_dom_amount net_dom_amount,
vat_curr_amount vat_curr_amount,
vat_dom_amount vat_dom_amount,
(non_deduct_tax_curr_amount + vat_curr_amount) total_tax_curr_amount,
(non_deduct_tax_dom_amount + vat_dom_amount) total_tax_dom_amount,
(non_ded_tax_parallel_amt + vat_parallel_amount) total_tax_parallel_amount,
currency currency,
chk_handling_code chk_handling_code,
sent sent,
multi_company_invoice multi_company_invoice,
Transfer_Status_API.Decode(transfer_status) transfer_status,
transfer_status transfer_status_db,
automatic_invoice automatic_invoice,
transfer_error transfer_error,
invoice_version invoice_version,
payer_identity payer_identity,
deduction_group deduction_group,
ncf_reference ncf_reference,
branch branch,
pay_term_base_date pay_term_base_date,
adv_inv adv_inv,
invoice_recipient invoice_recipient,
proposal_exist proposal_exist,
batch_id batch_id,
self_billing_ref self_billing_ref,
bank_account bank_account,
tax_curr_rate tax_curr_rate,
tax_invoice_series_id tax_invoice_series_id,
tax_invoice_number tax_invoice_number,
tax_invoice_date tax_invoice_date,
Js_Invoice_State_API.Decode(js_invoice_state) js_invoice_state,
js_invoice_state js_invoice_state_db,
price_adjustment price_adjustment,
original_result_key original_result_key,
tax_curr_type tax_curr_type,
correction_invoice_id correction_invoice_id,
sub_con_no sub_con_no,
invoicing_advice_id invoicing_advice_id,
msg_sequence_no msg_sequence_no,
msg_version_no msg_version_no,
media_code media_code,
Send_Status_API.Decode(send_status) send_status,
send_status send_status_db,
send_time send_time,
send_error send_error,
attachments attachments,
send_flag_attr send_flag_attr,
old_adv_inv old_adv_inv,
Iso_Country_API.Decode(supply_country) supply_country,
supply_country supply_country_db,
tax_liability tax_liability,
tax_id_type tax_id_type,
tax_id_number tax_id_number,
load_type load_type,
one_time_address_id one_time_address_id,
additional_reference additional_reference,
net_parallel_amount net_parallel_amount,
parallel_curr_rate parallel_curr_rate,
parallel_div_factor parallel_div_factor,
vat_parallel_amount vat_parallel_amount,
voucher_text voucher_text,
Fnd_Boolean_API.Decode(post_prel_tax_with) post_prel_tax_with,
post_prel_tax_with post_prel_tax_with_db,
Withholding_Base_Amount_API.Decode(wht_amount_base) wht_amount_base,
wht_amount_base wht_amount_base_db,
Fnd_Boolean_API.Decode(prepay_based_inv) prepay_based_inv,
prepay_based_inv prepay_based_inv_db,
Fnd_Boolean_API.Decode(use_proj_address_for_tax) use_proj_address_for_tax,
use_proj_address_for_tax use_proj_address_for_tax_db,
correction_reason_id correction_reason_id,
correction_reason correction_reason,
invoice_text_id invoice_text_id,
invoice_text invoice_text,
customs_declaration_date customs_declaration_date,
latest_result_key latest_result_key,
Fnd_Boolean_API.Decode(sii_proposal) sii_proposal,
sii_proposal sii_proposal_db,
Man_Supp_Invoice_API.Decode_Operational_Key(operational_key) operational_key,
operational_key operational_key_db,
invoice_reason_id invoice_reason_id,
component_a component_a,
component_b component_b,
component_c component_c,
sequence_number sequence_number,
official_document_no official_document_no,
serial_number serial_number,
official_invoice_no official_invoice_no,
credit_invoice_id credit_invoice_id,
service_code service_code,
posted_date posted_date,
xml_file_name_suffix xml_file_name_suffix,
Base_Date_API.Decode(out_inv_vou_date_base) out_inv_vou_date_base,
out_inv_vou_date_base out_inv_vou_date_base_db,
Base_Date_API.Decode(out_inv_curr_rate_base) out_inv_curr_rate_base,
out_inv_curr_rate_base out_inv_curr_rate_base_db,
Base_Date_API.Decode(tax_sell_curr_rate_base) tax_sell_curr_rate_base,
tax_sell_curr_rate_base tax_sell_curr_rate_base_db,
prepayment_type_code prepayment_type_code,
prepay_adv_inv_id prepay_adv_inv_id,
prepay_ledger_item_series prepay_ledger_item_series,
prepay_ledger_item_id prepay_ledger_item_id,
prepay_ledger_item_version prepay_ledger_item_version,
internal_series_id internal_series_id,
internal_invoice_no internal_invoice_no,
Fnd_Boolean_API.Decode(above_tax_control_limit) above_tax_control_limit,
above_tax_control_limit above_tax_control_limit_db,
document_type_code document_type_code,
Fnd_Boolean_API.Decode(einvoice_sent) einvoice_sent,
einvoice_sent einvoice_sent_db,
related_uuid_number related_uuid_number,
irn irn,
qr_data qr_data,
tax_withh_curr_rate tax_withh_curr_rate,
invoice_tax_id_number invoice_tax_id_number,
delivery_tax_id_number delivery_tax_id_number,
supply_delivery_address_id supply_delivery_address_id,
delivery_inv_address_id delivery_inv_address_id,
Fnd_Boolean_API.Decode(use_delivery_inv_address) use_delivery_inv_address,
use_delivery_inv_address use_delivery_inv_address_db,
Fnd_Boolean_API.Decode(exclude_posting_auth) exclude_posting_auth,
exclude_posting_auth exclude_posting_auth_db,
einvoice_reference_date einvoice_reference_date,
business_transaction_id business_transaction_id,
Fnd_Boolean_API.Decode(curr_difference_invoice) curr_difference_invoice,
curr_difference_invoice curr_difference_invoice_db,
supply_type_code supply_type_code,
consolidated_series_id consolidated_series_id,
consolidated_invoice_no consolidated_invoice_no,
inv_vat_curr_amt + inv_actual_net_curr_amt inv_gross_curr_amt,
net_curr_amount + vat_curr_amount gross_curr_amount,
net_dom_amount + vat_dom_amount gross_dom_amount,
MAN_SUPP_INVOICE_PLAN_API.Get_Auth_Id_For_Invoice(company,invoice_id) auth_id,
DECODE(rowstate,'Preliminary','TRUE','PrelPosted','TRUE','PrelPostedAuth','TRUE','PaidPrelPosted','TRUE','PartlyPaidPrelPosted','TRUE','FALSE') posted,
Company_Tax_Control_Invoic_API.Get_Amount_Method_Db(company) amount_method,
DECODE(rowstate,'Posted','TRUE','PostedAuth','TRUE','PaidPosted','TRUE','PartlyPaidPosted','TRUE','FALSE') finaly_posted,
cash cash,
DUE_DATE invoice_due_date,
DECODE(code_a||code_b||code_c||code_d||code_e||code_f||code_g||code_h||code_i||code_j||TO_CHAR(project_activity_id), NULL,'FALSE', 'TRUE') preposting_exist,
to_date(NULL) vou_date,
po_ref_number po_ref_number,
PL_PAY_DATE invoice_pl_pay_date,
accounting_year_ref accounting_year_ref,
Accounting_Period_API.Get_Accounting_Period(company,voucher_date_ref) accounting_period_ref,
Identity_Invoice_Info_API.Get_Automatic_Pay_Auth_Flag(company, identity, Party_Type_API.Decode(party_type)) authorize_for_pay,
Payment_Plan_API.Is_Pay_Plan(company,invoice_id) pay_plan,
receipt_ref receipt_ref,
post_error post_error,
error_text error_text,
SUBSTR(Identity_Invoice_Info_API.Get_Group_Id(company,identity,Party_Type_API.Decode(party_type)),1,20) group_id,
-1 ls_adv_inv,
debit_invoice_id debit_invoice_id,
Man_Supp_Invoice_API.Get_Tot_Amortized_Amount(company,identity,party_type,series_id,invoice_no,invoice_version) amortized_amount,
rowstate objstate,
INVOICE_API.Finite_State_Events__(rowstate) objevents,
INVOICE_API.Finite_State_Decode__(rowstate) state,
rowkey objkey,
rowtype objtype,
to_char(rowversion) objversion,
rowid objid
FROM invoice_tab
WHERE rowtype LIKE '%ManSuppInvoice'
AND rowstate <> 'Cancelled'
AND EXISTS (SELECT 1 FROM user_finance_auth_pub WHERE invoice_tab.company = company)) t0
LEFT OUTER JOIN company_tab t35
ON t0.company = t35.company
WITH READ ONLY;
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.