Retrieving Fields from Manual Supplier Invoice Screen Database

Hi @Seko,

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.

@Seko these views are for sure available in the DB, but if the user that you used to conenct to the DB doesn’t have access to the ifsapp schema then probably you can see the view definition.

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;