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.
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.