--- name: upload-api-patterns description: Oracle EBS module API specifics for Blitz Uploads (ONT, PO, INV, WIP, CST, AP, AR, HR, FA, CE, EAM, OPM, PA, QA and more). Use when an upload package calls an Oracle public API. user-invocable: false --- # Upload API Patterns Module-specific API facts. General rules are in `upload-development` skill. ## General - Never direct DML on application base tables; interface tables are the exception. Highest-level public API first (`*_PUB`/`*_API`/`*_GRP` → interface + concurrent program → `*_PKG`); supplement with targeted updates only for fields the API cannot set. - Value→id mapping belongs in the LOV/`VALUE_TO_ID_QUERY`, never in PL/SQL. - Combine entities the user enters together into one upload; process a repeating parent once (uploads run top-down). ## ONT Order (`XXEN_ONT_UPLOAD`, + `_11I`) A line split goes through `oe_order_pub.process_order` in one call: the original line as an update and each new shipment as a create with `split_from_line_id`, all carrying `split_action_code='SPLIT'` and `split_by='USER'`. `OE_SPLIT_UTIL.Check_split_Course` rejects the call unless the original's new quantity plus the new shipments equal its current quantity (`OE_INVALID_SPLIT_QTY`), and a split needs no change reason on a booked line although a plain quantity decrease does. `OE_SPLIT_UTIL.Default_Attributes` rebuilds each new shipment from the original line and keeps only its quantity, plus request date, warehouse and ship to when `split_request_date`/`split_ship_from`/`split_ship_to` are `'Y'`; those three flags exist on every R12 level but not on 11i, where a new shipment takes only its quantity. The upload maps returned line ids back to rows by table index, never through `orig_sys_line_ref`, which the api copies from the original line onto a split and which is the order's own source reference. A line price adjustment is passed to `oe_order_pub.process_order` through `line_adj_tbl`. A record left at `oe_order_pub.g_miss_line_adj_rec` because the modifier lookup found nothing is rejected by `OE_VALIDATE_LINE_ADJ` with `OE_ATTRIBUTE_REQUIRED` / token `List_header` (`list_line_type_code not in ('COST','TAX') and list_header_id is null`) — resolve the modifier line before building the record and error the row yourself when it does not resolve. Take `list_line_type_code` from `qp_list_lines`, never hardcode `'DIS'`: the Discount Modifier LOV offers surcharge lists (`list_type_code in ('DLT','SLT')`), whose lines are `SUR`, and the download subqueries read adjustments back with the same set. Back-calculating a `%` operand from a typed selling price divides by the unit list price, so a zero list price is a validation error, not a guard. The customer contact (`sold_to_contact_id`) is a contact record (`hz_cust_account_roles`), not a person: a person who is a contact at account level and at a site has two records sharing one name and one `hz_org_contacts.contact_number`, and the contact number can be null. `header_val_rec.sold_to_contact` resolves the name with `rownum=1` and is converted only when `<> fnd_api.g_miss_char` (`OE_HEADER_UTIL.Get_Ids`), so a blank never clears it: set `sold_to_contact_id` directly. `line_rec.override_atp_date_code` (`'Y'` or null) needs profile `OM: Authorized To Override ATP` (`OE_SCH_OVER_ATP_NO_AUTH`), a schedule ship date or scheduling action on the line (`OE_SCH_OVER_ATP_NO_SCH_ACT`), and is refused on service, return, drop-ship and quote lines; clearing it reschedules the line with an availability check, which can move the schedule ship date. Both exist on 11i. `oe_order_pub.process_order` returns S with warnings left on its message list, among them the duplicate customer PO check (`OE_VAL_DUP_PO_NUMBER`, profile `ONT_ENFORCE_DUP_PO`) and credit check holds, so the upload appends that list to its success message; the message type cannot separate warnings from notes (the duplicate PO message has none, credit holds are `NOTE` like "Order has been booked."). Call it with `p_init_msg_list` true: the list is not cleared between calls, so every later order of an upload would otherwise report the earlier orders' messages too. A salesperson is valid for an order when it is date-effective in the operating unit: the Sales Order form's list (`ra_salesreps`) and `OE_VALIDATE.salesrep` test `start_date_active`/`end_date_active` and ignore `jtf_rs_salesreps.status`, which is null or `I` on many records. Filter salesperson lists by the dates, never by status. Names repeat (an end-dated and an active record can share one); `oe_value_to_id.salesrep` then takes the date-effective one. ## OTL Timecard (`XXEN_OTL_UPLOAD`) `hxc_time_building_blocks` is one versioned self-referencing table (TIMECARD→DAY→DETAIL, latest = `date_to=31-DEC-4712`); attributes via `hxc_time_attribute_usages` (Projects: category `PROJECTS`, a1 project_id, a2 task_id, a3 expenditure type NAME; Payroll: category `ELEMENT - `); never author SECURITY attributes. Status = `hxc_timecard_summary.approval_status` (`HXC_APPROVAL_STATUS`, view_application_id 3). Transferred = `hxc_transactions`/`_details` SUCCESS rows. Create: `hxc_timestore_deposit.create_time_entry` → `create_attribute` (`'Dummy Element Context'` = `'ELEMENT - '||id`) → `execute_deposit_process('SAVE'|'SUBMIT','OTL Deposit Process')` (no commit, clears collections). Update: `get_timecard_tables` → `update_building_block`/`update_attribute`; never `delete_detail_bb` when batching (it reloads from DB, wiping pending edits) — mark `date_to`/`new='N'`/`changed='Y'` on the loaded collection. `delete_timecard` is immediate. Errors via `translate_message_table(convert_to_dpwr_messages(p_messages))`. Projects validation uses the SESSION OU; time-entry rules fire at SUBMIT. Eligibility `hxc_preference_evaluation.resource_preferences(...,'TS_PER_APPLICATION_SET',...)`. `full_name` can be ambiguous — require employee number. Write ids back to `_chr` shadow columns. ## FND Responsibility (`XXEN_FND_UPLOAD.create_update_responsibility`) Row types: Responsibility (`FND_RESPONSIBILITY_PKG.INSERT_ROW/UPDATE_ROW`, then `resp_synch`; UPDATE_ROW is a full-row write — gate on a real change) and Menu/Function Exclusion (`FND_RESP_FUNCTIONS_PKG`, key app+resp+rule_type+action_id, add idempotent). Business key = Application + Responsibility Key. `VERSION` 4/W/M (labels hardcoded, no lookup). Request Group needs `request_group_id`+`group_application_id` (id alone not unique). No delete — end-date. Multi-entity UNION filters: primary row on `1=1` + sub-row on `2=2`. Assignment `FND_FNDSCRSP`. Linter I7 flags only non-Function assignment levels. ## FND Profile Option Values (`XXEN_FND_UPLOAD.upload_profile_option_value`) - `xxen_util.profile_option_value_sql` turns `fnd_profile_options.sql_validation` into a select of `profile_option_value` and `visible_option_value`; the display function, the upload list of values (`profile_option_value_lov`) and the value resolution all use it. Validations needing the profile form's context (`:PROFILE_QF.*`, `:LEVEL_CONTEXT.*`) or binding the entered value (format checks) yield no list, so the value is saved as entered. - Id-list validations return the stored value as a NUMBER (`MO: Operating Unit` selects `organization_id`): compare `to_char(x.profile_option_value)` with a text value, or the comparison raises `ORA-01722`. - `fnd_profile.save` returns false for a level the profile's hierarchy type or enabled flags do not allow, and a null value deletes the row. The server and responsibility hierarchy (`SERVRESP`) stores both levels at 10007: a responsibility value with `level_value2` -1 or the server node, a server value with `level_value` and `level_value_application_id` -1. ## HR - Assignment update: `update_emp_asg` → re-query the object version number (OVN) → `update_emp_asg_criteria`. `primary_flag` not updatable. `hr_positions_f` (not `_vl`). Person queries datetracked on `sysdate`. - Organisation: `create_hr_organization`, `update_organization`, `create_org_class_internal` (not `create_org_classification`), `enable/disable_org_classification`, `create/update_org_information` (update blocked for Business Group Information etc.). - Person EIT (`XXEN_PER_UPLOAD.create_update_person_eit`): API `hr_person_extra_info_api`; types/segments live under DFF **`Extra Person Info DDF`** (context column `PEI_INFORMATION_CATEGORY`, `pei_information1..30`), not `PER_PEOPLE_EXTRA_INFO`. Segment columns call `xxen_util.dff_attribute_lov(800,'Extra Person Info DDF',:$column$.information_type,'PEI_INFORMATIONn',...)` directly; `validate_dff_attributes('PER','Extra Person Info DDF','V','I')`. COMMENTS bounded to the filtered context (the all-contexts `listagg` overflows at 176 contexts). `employee_number` unique only per business group. An EIT invisible in the People form is responsibility security (`per_info_type_security`), not a bug. - Person Extra Information LOV context: `l_context_code` declared `descriptive_flex_context_name%type` (an unresolved >30-byte context aborted the whole upload with `ORA-06502`). - A person's types are the rows of `per_person_type_usages_f`; `per_all_people_f.person_type_id` of a contingent worker is the generic `OTHER` type (External), so a filter or display on it drops or mislabels every contingent worker. Pick one usage per person by system type priority (EMP, CWK, APL, EX_EMP, EX_CWK, EX_APL), as `XXEN_PER_UPLOAD.create_update_person` does. - `hr_assignment_api.update_emp_asg` (12.1.3) ignores `p_concat_segments`: the soft coded flexfield change is silently dropped while the call succeeds. Select or create the combination with `hr_kflex_utility.ins_or_sel_keyflex_comb('PER','SCL',,p_concat_segments_in=>...)` and pass `p_soft_coding_keyflex_id`. `update_emp_asg_criteria` does honour `p_concat_segments` for the people group. - Concatenated key flexfield values passed to the HR apis (`'V'` mode) hold the DISPLAYED segments only. `pay_people_groups.group_name` and `hr_soft_coding_keyflex.concatenated_segments` are written when a combination is created and go stale when the structure changes later (most Vision people groups), so re-sending a stored string can fail with `FLEX-MISSING CONCAT VALUES`. - HR flexfield errors raise `ORA-20001: FLEX-: N, , ..., N, ERRMSG, ` with `hr_utility.get_message` empty; the row message is the text after `ERRMSG, `. ## PO - Receiving interface: `pie.interface_line_id=rti.interface_transaction_id`; `org_id` required on both interface tables (else RVCTP silently skips); `RVCTP` args `('BATCH',group_id,org_id)`; `auto_transact_code='DELIVER'`; receiving subinventory/deliver-to come from `po_distributions_all`. - Purchasing Document (`XXEN_PO_UPLOAD`): new lines → PDOI; existing Standard PO edits/splits/header-only → `po_document_update_grp.update_document`, gated on `l_interface_line_id is null` (not `l_po_line_id`). "Cannot be changed" checks use `value_changed` (a copy-pasted split flags every cell), falling back to `value_modified` when the distribution cannot be resolved. Seeded object types (`po_pub_*_rec_type`) gain attributes across patch levels: construct an empty instance dynamically from the `all_type_attrs` count (`pub_null_args`) and set fields by name; never positional constructors. - Ship-to and deliver-to location LOVs filtered by inventory organization must keep the shared locations (`hr_locations.inventory_organization_id` null, the majority on most sites): the PO form and PDOI accept a location when `nvl(inventory_organization_id,ship_to_org)=ship_to_org`. Ship-to additionally requires `ship_to_site_flag='Y'` and lists every ship-to location while the ship-to organization is blank; deliver-to has no flag check and lists nothing without an organization. Both exclude inactive locations. Reference: the two LOVs of the PO Purchasing Document Upload and `PO Requisition Deliver To Location (active)`. - Requisition Import writes `SQL Error In Account Generator wf` into `po_interface_errors` (with a follow-on `PO_RI_INVALID_CHARGE_ACC_ID`, "Charge account is invalid") whenever the PO Requisition Account Generator raises anything at all: `POR_UTIL_PKG.req_imp_act_up_frm_wf` holds the generator's message in a `varchar2(50)` local, and `interface_start_workflow` assigns the decoded FND message into that `in out nocopy` formal, so any message over 50 bytes dies as ORA-06502 in the same `when others`. The real reason is only recoverable by replaying the interface row through `por_util_pkg.interface_start_workflow` with a wide `fb_error_msg`, or by keying the line in the Requisition form, which reaches the same workflow through `PO_ACCOUNT_HELPER.req_build_accounts` with a `varchar2(2000)` buffer and shows the message. The generator only runs for interface rows whose charge account segments are ALL null, so supplying the segment columns bypasses it. - `por_util_pkg.interface_start_workflow` takes 102 arguments on 12.1.3 and 103 on 12.2 (`P_MISC_LOA`, no default), so a static call compiles on one release only. Build the call dynamically and append `p_misc_loa=>null` when `user_arguments` reports it; declare the `in out` formals inside the dynamic block and bind only the inputs and the outputs you read. - `po_headers_interface.supply_agreement_flag` does not exist on 12.2.4 (`po_headers_all` has it). Set it only through the dynamic update after the header insert, which errors the row on `-904`. Never add it to the static insert or to the Error SQL. ## WIP (`XXEN_WIP_UPLOAD`) `wip_interface_errors.error` (type 1 = error); `load_type` 1/4/3 encodes job type; lookups `WIP_DISCRETE_JOB`/`WIP_JOB_STATUS` app 700; numeric flags via `BOM_SYS_YES_NO` (1/2). Mass Load update null handling: most columns `nvl(new,old)` (cannot clear); `schedule_group_id`, `build_sequence`, completion sub/locator, `attribute1-15` accept `g_miss_*` to clear; project/task gated on project. On create they must never receive `g_miss_*`: Mass Load stores the sentinel literally (9.99e125 lands in `wip_discrete_jobs.schedule_group_id`/`build_sequence`) and the ASCP Memory-Based Snapshot then fails with `ORA-01455: converting column overflows integer datatype` in `mslsnj_snp_jobs`, whose log blames an unrelated `msc_plan_buckets` statement. Non-standard jobs without assembly/routing cannot be updated (`reexplodeJob` returns U). `allow_explosion='N'` on updates; for creates detect routing/BOM existence to choose `substitution_type` 3 vs 2, default `'Y'` only for header-only creates. Change records on routing-derived operations are dropped on Create+explosion. Pre-validate resource–department. Post-procedure cascades errors per job, cleans interface rows, allocates `group_id` from `wip_job_schedule_interface_s` per org (`g_miss_num` placeholder + `request_id` scope), stamps `source_code='BLITZ:'||request_id`/`source_line_id=upload_row` for post-WICMLP linkage, updates `xu.job_name` for all creates; `request_id_` = the upload request. Phantoms excluded (`wip_supply_type<>6`). ## CST cost import (`XXEN_CST_UPLOAD`) `CSTPCIMP` selects by GROUP ID only and stamps its cost-type parameter on every row — one group per (run, cost type): row proc stamps `group_id=conc_request_id`, post proc re-stamps `cst_lists_s.nextval` per cost type; skip the import when nothing was staged. Delete-successful-rows leaves errors with `error_flag='E'`; hidden in/out `ROW_ID` joins the error result and updates in place on re-upload. Org LOVs filter `cost_organization_id=organization_id` and non-process orgs. `BASED_ON_ROLLUP` defaults Yes when shrinkage ≠ 0 (import rule). Mode 1 insert / 2 replace per item. DFF via `validate_dff_attributes('BOM',...)`. Resource rates: `cost_element_id in (3,4)`, `functional_currency_flag<>1`; overhead (option 3) serves both dept and resource interfaces, dept duplicate key excludes basis; associations `cost_element_id` 3/4 with overhead 5 or 2. Deletes are direct (`cst_resource_costs`) or table handlers (`CST_DEPARTMENT_OVERHEADS_PKG`/`CST_RESOURCE_OVERHEADS_PKG.Delete_Row`), resolved by natural key, refused for frozen/non-updateable cost types; Success SQL restores per-row delete messages (`case when xu.delete_col is not null`) and null-safes the interface-error exclusion. Zoom assignments target the launching form function (never `_UPDATE` test functions), only cost-type-bearing blocks, mapping the form's cost type to BOTH Target and Source Cost Type. `RESOURCE` is reserved (`RESOURCE_CODE`). Cost migration: point the rollup cost type's Default Cost Type at itself while purging/re-uploading, or the rollup back-fills from the default. ## INV - Stock Locator: `inv_loc_wms_pub.create/update/delete_locator`; build `l_disabled_date` case-by-case (`g_miss_date` trips the disable check). `process_kff_combination_1('ALL')` returns the escaped concat (`\`→`\\`, delimiter → `\delim`), `''` the raw segment; `_kfv.concatenated_segments` is raw; decode with `kff_segment_dsp`. PJM projects via `pjm_projects_all_v`+`pjm_project_parameters`. - Item revisions: stage in `mtl_item_revisions_interface` (same INCOIN run); clear with `'!'`, null = no change (API and interface); local `value_changed` honours `p_upd_on_cre_null_mode`; a revision recurring across cross-product rows is processed once (`g_item_tbl('R:...')`); Success SQL sources bare `mir.col` (custom Error SQL, no delete). Revision DFF `MTL_ITEM_REVISIONS` (app 401, 15 attrs) via `mtl_item_revisions_vl`. Never use the mode-blind package `get_new_value` for the revision merge. - Cross Reference: `MTL_CROSS_REFERENCES_PUB.PROCESS_XREF` (CREATE/UPDATE/DELETE, id mandatory for update/delete); NULL clears on update (`new_value` contract fits exactly — no nvl guard); read old description from `_vl` (`_b` goes stale). Field applicability mirrored in `validate_item_xref_row` (org vs applicable-to-all, UOM only for the GTIN type, revision needs org). Resolve the item at its master org. `ORA-01416` in a Success SQL = self outer join from mechanical `(+)`. Assignments `INV_INVIDXRT`, `ENG_INVIDXRT`, `INV_INVIDITM`, `INV_INVIDITM_ORG`. - Item categories in an assigned organization: Item Import copies the master organization's assignment into a newly assigned organization only for **default** category sets (`mtl_default_category_sets`), and as a point-in-time copy of the master's category at that moment, never refreshed afterwards. A user-defined set is never propagated, so `Assign To Organizations` stages its own `mtl_item_categories_interface` CREATE row per organization for org control level sets (`mtl_category_sets.control_level=2`); a master controlled set is held once on the master organization and needs none. An explicit row for an org controlled default set does not collide with Item Import's own defaulting in the same process set. - Transaction Upload: for source type 3 (Account issue/receipt) `mtl_transactions_interface.transaction_source_id` is the distribution account ccid and the transaction manager rejects segments without it (`Invalid transaction source segments`); Account Alias is a GL shorthand alias (`fnd_shorthand_flex_aliases`) that only defaults the Distribution Account, so validate the Distribution Account, not the alias. Source type 6 (Account alias issue/receipt) takes its alias (`mtl_generic_dispositions`) through Transaction Source. - Transaction Upload quantity sign: `mtl_transactions_interface.transaction_quantity` must be negative for issue from stores (`transaction_action_id` 1) and for Intransit Shipment (21), and `upload_inv_trx` writes `-abs(...)` for both on the transaction and on the lot line, so the user may enter either sign. Every other action reaches the interface as entered; a wrong sign there surfaces as `Transaction quantity has an incorrect sign for the given transaction action`. A Transportation Account that is not the freight code's own `org_enabled_freight_val_v.distribution_account` is rejected with `The transportation account is not valid for the given freight code`, and most Vision freight codes have none. - Locator control is a three-level hierarchy, never an item attribute on its own: `decode(mtl_parameters.stock_locator_control_code,4,decode(mtl_secondary_inventories.locator_type,5,mtl_system_items_b.location_control_code,mtl_secondary_inventories.locator_type),mtl_parameters.stock_locator_control_code)`. Org holds 1/2/3/4 (4 = defer to subinventory), subinventory 1/2/3/5 (5 = defer to item), item 1/2/3; effective 2 or 3 means a locator is required, 1 means the field is disabled. So a prespecified-locator item in a subinventory with no locator control needs NO locator, and testing only the item's `location_control_code` rejects rows the form accepts. Oracle's own copies of the rule: `inv_material_status_grp.get_locator_control(p_org_id,p_inventory_item_id,p_sub_code)` (R12 only, absent on 11i, and its `when others` returns 1 so a bad subinventory silently reads as "no control") and `mtl_inv_validate_grp.locator_control`, which additionally collapses 3 to 2 under `no_neg_balance` - irrelevant when the question is only whether a locator is required. `XXEN_INV_UPLOAD.locator_control` is the in-package helper; evaluate the To-Locator of a transfer against the DESTINATION organization and subinventory, not the source. - Item Upload: an item attribute whose control level is Master (Item) is silently dropped when the row targets a CHILD organization - `set_item_att` returns false, so the value never reaches the interface and `g_entity_changed_flag` stays unset; a row changing only such attributes reports no change rather than an error. Updates only: a create with a non-null value is always taken. The master-controlled set is built at runtime by `populate_master_attribs_table` from each column's COMMENTS query over `mtl_item_attributes.control_level`, so it follows the site's Item Attribute Controls setup - the same attribute can be org-controlled on one instance and master-controlled on another, and a fixed list must never be assumed. - Item Upload automatic numbering: a blank Item takes its number from `XXEN_UPLOAD_CUSTOM_EXTENSIONS.inv_next_item_number` first, then (R12 only) from `EGO_ITEM_PVT.Get_Seq_Gen_Item_Nums` when the nearest catalog up the hierarchy whose `item_num_gen_method` is not `I` is `S` (sequence generated: prefix || sequence || suffix, skipping numbers already used). INCOIN never generates these itself: Product Hub numbers items only in its own import, where `EGO_IMPORT_UTIL_PVT.Preprocess_Import` runs the item interface with `default_flag` 2 (`INVPAGI2.assign_item_header_recs`), and that step matches rows on `source_system_id`, which upload rows leave blank. Function generated (`F`) numbers run in the Java import program from Product Hub user-defined attributes, so the upload cannot support them. A master-org create in a catalog whose inherited `new_item_request_reqd` resolves to `Y` is rejected first (R12 only): Item Import (`INVNIRIS.mtl_validate_nir_item`) would create it unapproved as a Pending engineering item that Master Items cannot query, failing its category and child-org assignments with no `mtl_interface_errors` rows. 11i Item Import has no such step. - Lot Upload (`upload_lot`): decision: create through `inv_lot_api_pub.create_inv_lot` and update through the table handler `mtl_lot_numbers_pkg.update_row`, as the Lot Numbers form (INVTDILT) does. `update_inv_lot` omits `description`, `curl_wrinkle_fold` and `vendor_id`, skips the global lot attribute propagation (profile `INV_GLOBAL_LOT_ATTRIBUTES`) and applies `expiration_date` only for shelf life codes 2 and 4 with null meaning keep; the handler writes the status and grade history itself, so the upload performs the form's validations before calling it. - `create_inv_lot` with `p_source=>inv_lot_api_pub.inv` and full validation rejects every shelf life days item whose derived expiry is not `sysdate+shelf_life_days` (`INV_EXP_DATE_NOT_CONSIDER`), i.e. any lot with an origination date other than today. The OSFM sources skip that check; `osfm_form_validate` keeps the `Populate_Lot_Records` item and parent defaulting and the remaining validation. - The `Lot Attributes` flexfield (application 401, context `LOT_ATTRIBUTE_CATEGORY`) maps segments to named `mtl_lot_numbers` columns (`COLOR`, `VOLUME_UOM`, `DESCRIPTION`, ...) as well as `C_/N_/D_ATTRIBUTEn`. `xxen_util.dff_attribute_value_to_id` resolves only the numbered columns; `inv_lot_api_pub.wms_lot_attr_validate` validates the stored values of all of them. ## WSH Pick Confirm (`XXEN_WSH_UPLOAD`) Rows are pick allocations (`mtl_material_transactions_temp` keyed by `transaction_temp_id`), one per lot (`mtl_transaction_lots_temp`), joined to the delivery line by `move_order_line_id`. No Create mode: a Yes parameter pre-flags downloaded rows (`:confirm_all`), a second one (`:ship_confirm`) rides along as a hidden column into the post procedure. The row procedure only validates, creates a new LPN (`wms_container_pub.create_lpn`, context pregenerated; existing LPNs accepted only in context 5 or 11) and stages the row's values in a package-global table keyed by task; the post procedure runs once per task over those globals, so a sibling lot row in error blocks the whole task. Confirm sequence: `inv_mo_line_detail_util.query_rows(p_line_detail_id)` -> set quantities, subinventory, locator, staging location and `transfer_lpn_id` -> `inv_mo_line_detail_util.update_row`; lots replaced wholesale with `inv_trx_util_pub.delete_lot_ser_trx` + `insert_lot_trx` (a function returning the row id, error text in `x_proc_msg`); then `inv_pick_wave_pick_confirm_pub.pick_confirm` (move order type `inv_globals.g_move_order_pick_wave`, transaction mode 1, `inv_trolin_util.query_rows(p_line_id)` for the line table). Serial controlled items (control codes 2 and 5) are rejected. Pick confirm splits the delivery detail per lot and keeps the short-pick remainder on the ORIGINAL detail id as backordered; the confirmed material transactions get NEW ids (an issue/receipt pair, `transaction_action_id=28`, quantities negative on the issue side), so the Success SQL joins the latest negative row by `move_order_line_id` + source subinventory + locator (`mtl_material_transactions_n17`) and lots via `mtl_transaction_lot_numbers`. Ship confirm `wsh_deliveries_pub.delivery_action('CONFIRM')` only when no line of the delivery is still in `R`, `S` or `N` (backordered `B` lines are unassigned and do not block); action flag `S` (ship unspecified quantities): a non-WMS pick leaves `shipped_quantity` null and flag `B` backorders the staged line, while a WMS pick fills it. The API returns `W` for the OTM outbound-interface notice on Vision; treat `W` as success and append the text. `pick_confirm` accepts an over-pick (6 of 5) and ship confirm then fails on the over-ship tolerance with the line stuck as over-picked, so reject a picked total above `mtl_txn_request_lines.quantity-quantity_delivered` before touching the allocation. A row pre-flagged by the Confirm All parameter is processed only once its status is `Valid` (the add-in validates on upload); a programmatic test file must set Status/Message itself. ## WSH Container Items (`XXEN_WSH_UPLOAD.upload_container_item`) - No API or table handler writes `wsh_container_items`; the form WSHFCNIR writes it directly, so the upload does too (decision). `master_organization_id` is the organization chosen in the form (child organizations included), `master_location_id` is always -1. - `wsh_details_validations.check_decimal_quantity` sets its E/W text with `fnd_message` only (wrapped with line feeds), never on the `fnd_msg_pub` stack, so `wsh_util_core.get_messages` returns nothing; the buffer survives into the next call, so read `fnd_message.get` on status E or W only. - The `CONTAINER_TYPE` lookup is view application 3: `xxen_util.meaning(code,'CONTAINER_TYPE',3)`; 401 returns null. - One preferred container per organization and load item: saving a preferred row sets the other row's flag to N without WHO columns, as the form does after asking. ## AP - Invoice Hold: `AP_HOLDS_PKG.UPDATE_ROW` is a full-column overwrite by rowid (read the row, pass everything back incl. `wf_status`); `INSERT_SINGLE_HOLD` silently inserts 0 rows when the hold exists (pre-check, re-query id); never `RELEASE_SINGLE_HOLD` (ORA-01422 on multiple rows). Resolve holds to a ROWID by PO/line/shipment or invoice line; refuse non-releasable (`user_releaseable_flag='N'`) and invoices under validation. Release Name LOV per hold type/matching. Hidden `HOLD_ID` guards: already-released → message; visible discriminators override a stale id. 11i twin: 31-arg `UPDATE_ROW`, ROWID handle, `po_vendors`, `fnd_client_info.set_org_context` before both calls, pass `x_held_by` explicitly. - `ap_vendor_pub_pkg.update_vendor_public` updates the supplier's organization party (name, alternate name, taxpayer id, tax registration, CEO) only when `p_vendor_rec.employee_id` is null, so `fnd_api.g_miss_num` there silently skips it while the call returns S. Pass the unchanged employee id or null, never the clear sentinel. - Invoice Payment (`upload_invoice_payment`): no public api pays an installment partially or several invoices on one payment, and `AP_PAY_SINGLE_INVOICE_PKG.ap_pay_invoice_in_full` (one invoice in full) validates `p_internal_bank_acct_id` as a bank account id but stores it as `ap_checks_all.ce_bank_acct_use_id`. The upload calls what the Payments window (APXPAWKB) calls: `AP_CHECKS_PKG.insert_row` -> `AP_RECONCILIATION_PKG.insert_payment_history` (PAYMENT CREATED event) -> `AP_PAY_INVOICE_PKG.ap_pay_invoice` per installment (mode PAY; updates the org striped schedule and invoice views, so `mo_global.set_policy_context('S',org)` first) -> `ap_pay_update_check_amount` -> `IBY_DISBURSE_SINGLE_PMT_PKG.submit_single_payment`, then writes back `payment_id` and the IBY number with `xla_events_pub_pkg.update_transaction_number`. Never insert the invoice payments or update the schedules directly. Void: `AP_VOID_PKG.ap_reverse_check(...,p_calling_module=>'SQLAP')` (it voids the IBY payment) followed by `status_lookup_code='VOIDED'`/`void_date`, which the form sets itself. `iby_payment_methods_vl.inactive_date`. - Commit after each `submit_single_payment`: IBY keeps the documents of a payment in the transaction temporary table `iby_docs_in_pmt_gt` and never clears it, so a second single payment in the same transaction fails in the post hook with `Document count mismatch after hook operation` (row message `Exception occured when performing payment creation.`). - `xla_accounting_pub_pkg.accounting_program_document` returns retcode 2 on the development instance for every payment, form-created ones included; check accounting readiness from the unprocessed `xla_events` row instead. - Distribution Sets (`upload_distribution_set`): no API or table handler exists; the form APXSUMDS writes `ap_distribution_sets_all`/`ap_distribution_set_lines_all` directly, so the upload does too (decision). The line percentages must total 0 (Skeleton) or 100 (Full) per set, stored in the header's `total_percent_distribution`; `project_accounting_context` holds the literal `Yes`/`No` (untranslated form parameters); a project line's account comes from `pa_acc_gen_wf_pkg.ap_inv_generate_account` and is read-only in the form. - AR/AP bank APIs (`ce_bank_pub`, `iby_ext_bankacct_pub`): NULL = no change, `g_miss_char` = clear — assign params raw (no `nvl(p,stored)`); do not switch bank identity columns to `value_modified` (read-only columns are never flagged). Bank/branch update blocks run only when an instrument payment use exists. Japan requires alternate bank/branch names (`bank_alt_name`, `alternate_branch_name`). The external bank account's own dates are the exception to NULL = no change: `update_ext_bank_acct` writes `START_DATE=nvl(trunc(p.start_date),sysdate)` and `END_DATE=trunc(p.end_date)` unconditionally, so a null start date is stamped with sysdate INCLUDING its time and a null end date reactivates an end dated account. `iby_disbursement_comp_pub.get_applicable_payee_acc_list` tests `trunc(sysdate) between start_date and nvl(end_date-1,trunc(sysdate))`, so that stamp silently drops the account from payment defaulting and from the unpaid invoice propagation below for the rest of the day. Read the stored dates and pass them back (`coalesce(p_start,stored,trunc(sysdate))`, `nvl(p_end,stored)`); the api truncs what it is given, so a stored value that already carries a time is repaired on the next update. The create path truncs correctly, so only updates are affected. - Preserving an UNCHANGED bank account attribute: take the stored value from the `iby_ext_bank_accounts` row, never from `iby_ext_bankacct_pub.get_ext_bank_acct`. Called without a security key that getter returns `bank_account_num` MASKED and does not return the iban at all, so feeding its record back as the unchanged value stores `XXXXXXXXXXX` as the number, which `update_ext_bank_acct` then rehashes, and blanks the iban; both fail silently and the real number is unrecoverable from the row. Contrary to the bullet above, `update_ext_bank_acct` assigns most attributes straight from the record (`DESCRIPTION = p_rec.description`, the contacts, account type, suffix, check digits, secondary reference, and `IBAN` through a `g_miss_char` decode), so for those a NULL CLEARS rather than meaning no change - only `BANK_ACCOUNT_NUM` and its two hashes are `nvl` guarded. `iby_ext_bank_accounts` has no `multi_currency_allowed_flag` column and the update does not write it, so a `%rowtype` read cannot supply it. The update also re-formats the number through CE country validation: an Italian account keyed `1223/03/93` comes back `001223/03/93`, so an api round trip is not value preserving and is the wrong tool for restoring an exact value. - Supplier bank assignment end date → unpaid invoices: only the Supplier page's Update End Date dialog (`IBY_UPDATE_END_DATE_WARNING`) repoints them, through `AP_AUTOMATIC_PROPAGATION_PKG.Update_Payment_Schedules`; no API, concurrent program or business event does (`oracle.apps.iby.bankaccount.assignment_inactivated` is never raised). The routine ignores `p_to_bank_account_id` and takes, per schedule, the first account `IBY_DISBURSEMENT_COMP_PUB.Get_Applicable_Payee_Acc_list` returns for the invoice currency (effective today, site → address/OU → address → supplier, then priority). It keeps the old account while that is still applicable, so a future end date changes nothing. It writes NULL to the schedule and invoice header when no account applies, skips schedules with a `checkrun_id`, swallows every error and does not commit. Pass `p_party_id`: when it is null every lookup fails and the accounts are blanked. Scope follows the ids passed: none = supplier, party site = address, plus org = address/OU. The site page also passes the supplier site, which lands in the address/OU branch. ## AR - Transaction Upload uses `ar_invoice_api_pub.create_invoice` synchronously (not AutoInvoice); return `S` does not mean success — check `ar_trx_errors_gt`. `ar_trx_header_gt.customer_trx_id` is taken from `ra_customer_trx_s.nextval` when the row goes INTO the GT, before validation, so a header the api rejected still carries an id and has no `ra_customer_trx_all` row: every post-creation step drives from a join to `ra_customer_trx_all`, never from the GT id alone. A `select ... into` on it raises `ORA-01403` that escapes `submit_ar_inv_import` and replaces the row's real AR message with a raw stack for the whole batch; a tolerant cursor instead hands the table handler an empty record and updates nothing. Multi-header + explicit sales credits → pass empty and post-process `ra_cust_trx_line_salesreps_all`; "No Sales Credit" = salesrep_id -3. - AutoAccounting (`upload_autoaccounting`): no API or table handler writes `ra_account_defaults_all`/`ra_account_default_segments` (checked on 12.2.15); the form RAXSUAGL writes them directly, so the upload does too (decision). Changing the Revenue or Receivable balancing segment overwrites the balancing segment of the TAX, FREIGHT, UNBILL, UNEARN and SUSPENSE types, as the form does. - `ra_cust_trx_types_all`/`ra_batch_sources_all` ids repeat across OUs: every join carries `org_id` (defaults, LOVs, Success SQL AND validation cursors). - `ra_customer_trx_all` has one unique key, `customer_trx_id`. `trx_number` repeats across batch sources, within a source, and within an operating unit (auto-numbering sources each run their own sequence, so overlap is structural), and `ra_customer_trx_lines_all` has no unique key on (`customer_trx_id`, `line_number`, `line_type`) either — so neither number identifies a row. An id/value LOV on one of them must qualify the colliding values (see `upload-development` skill § LOVs); the Excel row validator (`validate_ar_inv_excel_row`: flow-gated, LOV-context-scoped, `count(distinct pk)`, new params `default null`) reports what is still unresolvable, it does not make the value unique. - `ra_batch_sources_all` is keyed on (`batch_source_id`, `org_id`) — the id alone repeats per OU — and `name` is unique per OU, which makes the name the discriminator for a transaction-number list. Outer-join it: transactions referencing a source row that no longer exists are present in Vision. - The Reference field (`ra_customer_trx_all.ct_reference`) has exactly ONE writer in the standard table handler: `arp_ct_pkg.insert_p`/`update_p` run `decode(ra_batch_sources.default_reference,'1',interface_header_attribute1,...,'15',...,null,p_trx_rec.ct_reference,null)` against the transaction's own batch source and overwrite the column with the result. `generic_update` does not carry the column, and the corrective statement is skipped when `batch_source_id` arrives as the no-change sentinel, so the column is then not written at all. Consequences: with a Reference Field Default Value the interface header attribute is the only channel, which is what the Transactions form itself does (typing in Reference changes the matching attribute); without one, `arp_process_header_updte_cover.update_header_cover(p_ct_reference=>...)` sets it and it survives. `ar_invoice_api_pub.trx_header_rec_type.reference_number` is a dead field - nothing on the invoice-create path reads it, so a Reference passed there is silently dropped. Leave `interface_header_context` null: the form does, `RA_INTERFACE_HEADER` has only seeded contexts (a batch source name is not one, though AutoInvoice writes one into the column without validating it), and both `xxen_fnd_upload.validate_dff_attributes` and Oracle's own `Validate_int_Desc_Flex` reject an undefined context. On 11i `update_header_cover` is the R12 signature minus `p_ct_reference`, `p_legal_entity_id`, `p_payment_trxn_extension_id` and `p_billing_date`, so the direct route does not exist and a source without a Reference Field Default Value cannot carry a Reference at all. - `ar_credit_memo_api_pub.create_request` copies the credited invoice's `interface_header_context` AND all 15 interface header attributes whenever the caller's context is null - unconditional assignment, so attribute values the caller passes are discarded; a non-null context suppresses the copy entirely and sends the record through DFF validation instead. `ra_batch_sources.copy_inv_tidff_to_cm_flag` reads like the control for this and is not: it governs the other flexfield (`RA_CUSTOMER_TRX` `attribute1-15`, and only when the caller's `attribute_category` is null). A credit memo whose batch source has no `default_reference` therefore comes out with the invoice's attribute copied and a BLANK Reference - not a rare setup, 161 of 1355 invoice-to-credit-memo source links on the development instance, including `Manual` -> `MANUAL-OTHER`. Set the Reference on the created credit memo with `update_header_cover` rather than trying to pass it in. - `ar_payment_schedules_all` holds one row per INSTALLMENT, so every per-transaction read of `amount_due_remaining` / `amount_line_items_remaining` / `tax_remaining` / `freight_remaining` must aggregate. A scalar subquery over it raises `ORA-01427` on a split-terms invoice and a top-level select silently returns one arbitrary installment; both pass every test until someone credits an invoice with split payment terms. - "A rule start date must be provided" fires on a MISMATCH with the derived date (Advance = transaction date, Arrears = `trunc(sysdate)`), and needs a `gl_periods` row of type = accounting rule `frequency`; replay the `IF` in SQL. - `Please correct the revenue account assignment ()` (AR message 1360; 1350 receivable, 1370 freight) is `arp_auto_accounting` reporting that a source named in the operating unit's AutoAccounting definition (`ra_account_defaults_all` + `ra_account_default_segments`) carries no account: Standard Lines with neither item nor memo line, a memo line without revenue account or an item without sales account, a salesperson (No Sales Credit, -3, when none is entered) or transaction type without one. `AR_INAPI_RULE_INFO_UNNECESSARY` = accounting rule id or duration on a line whose header has no invoicing rule. `autoaccounting_message` pre-checks the REV and REC sources from the Excel row validator and in `submit_ar_inv_import`; keep it a read-only check, never derive the account. Whole-transaction checks belong in the post procedure: a line's distributions may follow on later rows, so a per-row check misfires. The batch API returns its errors in `ar_trx_errors_gt`; the `(x)` suffix is its `invalid_value`, and on a non-success return the post procedure stages nothing and every still-successful row carries the batch message. - `get_applied_cm_message_` is the first choice in the result SQL's coalesce and answers only for applied credit memo indexes (above `g_applied_cm_trx_offset`); for invoices a header placeholder there would hide every line message, which `get_ar_inv_upload_message_` composes. - 11i `create_invoice` completes only the last transaction of a batch (Oracle bug): `XXEN_AR_UPLOAD_11I.submit_ar_inv_import` completes stragglers from `ar_trx_header_gt` (replaying `post_commit` → `complete_flag='Y'` → `post_commit`); never call `create_invoice` per invoice (`clean_gt` wipes the GTTs); result Success SQL outer-joins `ar_payment_schedules_all` and surfaces `complete_flag`. - 11i `create_invoice` rejects a `LOCATION` tax code: leave the line tax code blank under `SALES_TAX`; a tax-code default helper nulls a LOCATION winner; the LOV excludes `tax_type='LOCATION'`; never make the line tax code required. - Result status is decided at render by getters that DELETE their input (`xxen_ar_upload_results_11i`): read the result XLSM, and seed synthetic state to test the getters. Pass the LINE index to `get_ar_inv_upload_status_` from the message getter. - Customer Upload: party fields via `hz_party_v2pub.update_organization/person` (which re-validates the stored `created_by_module` — add orphaned codes to `HZ_CREATED_BY_MODULES`); `province` vs `state` by address style (Province/Contact Province columns; contact block cannot clear fields — pre-existing); never add new location columns to the site-matching predicate; address columns blank = `Update Level` (a USER-level saved default overrides the template's) — read the run's stored parameters. Template `REQUIRED` flags follow the package's "is required" checks, never OR-alternative or auto-generated fields. - Declare every API OUT variable as `.%type`. A `number` OUT receiving a varchar2 column (`x_party_site_number`) works while the value is numeric and fails on the first alphanumeric one with `ORA-06502 character to number conversion error` at the call line, `ORA-06512: at line 0` and no API frame: the API succeeded, the copy-back failed. Site, party and account numbers are alphanumeric wherever the customer disables their automatic generation (`HZ_GENERATE_PARTY_SITE_NUMBER`, `HZ_GENERATE_PARTY_NUMBER`, AR system option `generate_customer_number`), which the development instance has enabled; set the profile to `N` at user level to test a supplied number. - On the development instance every new account fails in `create_cust_account` with "Provide a positive integer for minimum customer balance amount or percent when balance amount overdue type is amount or percent" because the Vision DEFAULT profile class amounts (`hz_cust_prof_class_amts`, `profile_class_id=0`) carry `min_fc_balance_percent=5` with a null `min_fc_balance_overdue_type`. For a create E2E set the type to `PERCENT` on those rows and restore null afterwards. - `hz_location_v2pub.get_location_rec` returns `fnd_api.g_miss_char` for every column that is NULL in the database, EXCEPT `sales_tax_geocode`, which comes back as a true NULL. Never pass those values on to `fnd_flex_descval`: the flexfield engine treats the marker as a real value and validates it against the segment's value set, so an untouched US address whose `state` is null fails with `Value for the flexfield segment State does not exist in the value set HZ_US_STATES` even though a null state is valid (the segment is not required in the seeded `POSTAL_ADDR_US` style). Strip with `nullif(v,fnd_api.g_miss_char)` before validating and put the marker back on the way out, so the api still clears the column; any g_miss-vs-null comparison must normalise BOTH sides or the `sales_tax_geocode` asymmetry alone reports a change on every row. - Decide whether a location changed by comparing the resolved record against the one `get_location_rec` returned, never by `g_parameter_tbl.exists('p_l_country')`: that only says the Country column is in the template, and Country is `READ_ONLY` so it is in every site template. Validating and calling `update_location` on column presence re-validates and rewrites an address the user never touched. - `hz_cust_account_site_v2pub.update_cust_acct_site` re-derives `hz_locations.state` and `county` from the location's resolved geography, and writes them WITHOUT touching the location's `object_version_number` or WHO columns. So an unchanged `last_update_date` on `hz_locations` does NOT prove the row was left alone, and a state that reappears after an upload is not evidence that the location update ran: bisect by calling the apis individually. `update_location` (only when an address field actually changes), `update_party_site` and `update_cust_site_use` do not do this. It fires only where the geography resolves - a location with `geometry_status_code='DIRTY'` never re-derives, so an invented test address cannot reproduce it and a test fixture has to be a real one that already carries a resolved geography. ## IEX Collections notes (`XXEN_IEX_UPLOAD`) The Unpaid Reason on the Collections notes window is stored on the transaction's DELINQUENCY (`iex_delinquencies_all.unpaid_reason_code`, one row per payment schedule - unique index `IEX_DELINQUENCIES_ALL_U2`), never on the note: it applies to every note on that transaction and exists only at `IEX_INVOICES` (Transaction) level. `IEXNTDTL.pll` enables the item only for a NEW note record, and `populate_unpaid_reason` disables it again right after querying it, so the `UPDATE_ROW` call to `set_unpaid_reason` is unreachable - the reason is create-only. `IEX_DELINQUENCY_PUB.Create_Ind_Delinquency` DISCARDS its own insert unless `p_commit` is true (`ELSE ROLLBACK TO IEX_IND_DEL`), which is why the form passes `'T'`. Its `WHEN OTHERS` handler rolls back, COMMITs, and neither re-raises nor sets `x_return_status` (initialised to `S`), so a failure returns SUCCESS with an empty `x_del_id_tbl` - test `x_del_id_tbl.count` as well as the status, or `x_del_id_tbl(1)` raises `NO_DATA_FOUND`. Its `p_object_code` is `IEX_INVOICE`, singular, unlike the note's `IEX_INVOICES`. `IEX_TRX_GRID_PVT.SET_UNPAID_REASON` is a bare `update iex_delinquencies_all set unpaid_reason_code=...` assembled with `dbms_sql`, with no check against `IEX_UNPAID_REASON` - validate the code before calling it. `NOTE_CONTROL.UNPAID_REASON_FLAG` ('Y' mandatory, 'W' warn, anything else optional) is never assigned by the shipped forms, so a reason is optional out of the box; it is a Forms Personalization hook, not a profile option. ## PIM item UDA (`XXEN_EGO_UPLOAD.upload_item_uda`, + `_11I`) `ego_item_pub.process_user_attrs_for_item` per attribute-group row instance (`SYNC`/`DELETE`; keys by `unique_key_flag`); pass `attr_disp_value` for value-set attrs; metadata `ego_attr_groups_v`/`ego_attrs_v` joined by type+name+application; F-type value sets translate id→value from `fnd_flex_validation_tables` (neutralise EGO bind comparisons to `1=1`); exclude the seeded ItemDetailDesc/ItemDetailImage groups; org LOV "INV Organization Code (including master)" (`0B22DF18E239D64AE0630100007FBD85`); resolve group display names within the association scope; capture `upload_rowid` before first-row validation; numeric attrs with a `uom_class` need a UOM; errors via `error_handler.get_message_list` (also when status S but count>0). No Function assignment (OAF only). 11i: no data-level model, revision in `data_level_1`, `mtl_item_revisions`. ## CE Bank Statement (`XXEN_CE_UPLOAD`) `CE_AUTO_BANK_IMPORT.import_process` after setting `CE_AUTO_BANK_REC.G_*` (`G_intra_day_flag='N'`, `G_option='ZALL'` + `match_process` for AutoReconciliation, `G_gl_date` set or every match errors `CE_INVALID_AP_PERIOD`); one call per branch (package-global done set); `set_policy_context('S',owner org)`; correction re-upload keys error deletes off the OLD row's account/statement; `bank_account_num` not unique; `ce_transaction_codes` has date-effective duplicates; amounts positive, direction from `trx_type`; Success SQL timestamp-gated (`csl.creation_date>=fcr.actual_start_date`). Bank/branch: `ce_bank_pub`, `p_country` = territory_code, `BANK_BRANCH_TYPE` lookup, OVNs from `hz_organization_profiles`/`hz_code_assignments`/`hz_contact_points`. ## EAM Assets are CSI instances (`EAM_ASSETNUMBER_PUB`), linked to FA via `csi_i_assets`; asset groups/activities/rebuildables are `mtl_system_items_b` with `eam_item_type` (use INV Item Upload); asset BOMs via BOM Bill of Materials Upload. `EAM_PMDef_Pub.Create_PM_Def` needs `current_cycle/seq/wo_seq`; `Update_PM_Def` REPLACES all rules; users run `XMLSG` after. INV org context via `fnd_profile.put('MFG_ORGANIZATION_ID',...)`. `csi_counters_tl` for names; `mtl_eam_locations.start_date/end_date`; failure code tables have no org_id; no `Delete_FailureSet`/`Delete_ItemActivity` (end-date). Asset Number Upload: read-only context columns from `mtl_eam_asset_numbers_all_v` (`maintenance_object_id`). The parent asset is a `mtl_object_genealogy` link of genealogy type 5 between the assets' `mtl_serial_numbers.gen_object_id` (the view's `gen_object_id` and `parent_gen_object_id`); the asset APIs have no parent argument, so maintain it with `inv_genealogy_pub` (object type 2, origin 3): `update_genealogy` end-dates the open link (name `p_org_id` to select that overload) and `insert_genealogy` adds the new one with a start date later than that end date, which the API otherwise rejects as an overlap; it also rejects the asset itself or one of its descendants as parent (`INV_EAM_GENEALOGY_LOOP`). `csi_ii_relationships` is not involved, and the standard hierarchy page writes no asset log entry. Asset attribute values (the attribute groups of an asset or rebuildable number; DFF `MTL_EAM_ASSET_ATTR_VALUES`, application 401, columns `C_ATTRIBUTE1-20`, `N_ATTRIBUTE1-10`, `D_ATTRIBUTE1-10`) go through `eam_assetattr_value_pub.insert_assetattr_value`/`update_assetattr_value`, identical 57 arguments on 11i: pass item, serial and `p_creation_organization_id` (no maintenance object) plus the `association_id` from `mtl_eam_asset_attr_groups` (item, org, context). The API validates the segments in id mode, so translate the character display values with `xxen_util.dff_attribute_value_to_id` first and pass number and date columns typed. There is no delete API. On 11i the API additionally checks the organization through `eam_common_utilities_pvt.verify_org` against the hard-coded responsibility 20539 (Oracle Inventory, application 401) and `wip_eam_parameters`; without that organization access every row fails with `Creation organization Id invalid`. ## OPM (`XXEN_OPM_UPLOAD`) Upper-case document numbers. Per-procedure `api_version` (formula/recipe header 1; formula detail, recipe vr/routing steps/orgn ops/resources 2). `gmd_recipe_detail.recipe_dtl` fields: `usage_uom`, `process_um`, `oprn_line_id`, `routingstep_id`. Operation line = `gmd_operation_activities.oprn_line_id` by routing step + activity. `create_recipe_mtl` is insert-only — step/material association edits are base DML (documented). Token names in GMD messages can be misleading — read the `IF` above the `SET_TOKEN`. Routing updates need an OPM responsibility with org access (`GMD_ROUTING_INVALID`/"does not have access to organization" is security, not a bug); dependency inserts need `routingstep_no_uom` from the dependent step's operation. 12.2.4 lacks `fm_rout_dtl.oprn_yield`/`recipe_yield` (accepted drop). ## BOM Standard Operations (`XXEN_BOM_UPLOAD.upload_std_op`) Table handlers only (`bom_std_op_pkg`/`bom_std_op_res_pkg`, direct DML for substitutes); row-by-row with `g_last_*` cache. Bucket from `(line_id presence, operation_type)`: STD (standard routing operation, no line) / FLE (flow line event) / FLP (flow line process) / FLL (flow line operation). Existence id-first; three hidden rowid round-trips; two savepoints. Applicability gate `validate_std_op_excel_row` (client + server backstop; accumulates failures space-joined): Value Added/Critical → FLE; Count Point/Autocharge/Backflush/Check Skill → STD; Display Sequence editable FLP/FLL; resources not FLP/FLL, substitutes not FLE; Replacement Group required for substitutes, forbidden on primaries; subinventory/locator, yield, mandatory flags, track dates → STD + WSM org; Shutdown → STD + eAM; Task Type → WMS installed (`mfg_lookups WMS_TASK_TYPES`, never a value set). The five WSM flags (Operation Yield Enabled, Use Org Settings, Show Next Op, Show Scrap Codes, Show Lot Attributes) default Yes for all types and are editable only in a WSM org (`applicable_flag_code`); standard-op flags default Yes, editable only in their bucket; `count_point_type` composed from Count Point+Autocharge. Department read-only except FLP/FLL; Operation Type null = Event (`nvl(...,1)`); rename detection explicit; Schedule defaults No; substitute-group delete cascades only when no other primary uses the group. Download params: Organization Code (bind), Manufacturing Mode (matching value), Operation Type, Operation Code (grouped LOV), Department, Contains Resource (operation-level `in (select ...)`, still multi-select). ## FA - Mass Additions (`XXEN_FA_UPLOAD.create_update_mass_addition`): targeted `UPDATE fa_mass_additions SET ` (never `FA_MASSADD_PREPARE_PKG.update_mass_additions` — full-row `SET ROW` replace); Split/Unsplit via `SPLIT_MASS_ADDITIONS_PKG`; Create = controlled insert (`FA_MASS_ADDITIONS_S`, `feeder_system_name='BLITZ UPLOAD'`) then UPDATE the trigger-created `fa_massadd_distributions` row (never insert a second); Merge Group + Primary Line + Sum Units (order-independent); match by hidden id else business key, ambiguity errors; Split/Merge/Add-to-Asset only on NEW/POST/ON HOLD; Queue = `fa_lookups QUEUE NAME`; Asset Type meaning from `fa_lookups`. - `fa_mass_additions.depreciate_flag`/`amortize_flag` hold `YES`/`NO` (`fa_lookups YESNO`), not `Y`/`N`: Post Mass Additions rejects a `Y` with `Depreciate flag &DEPRECIATE_FLAG must be YES or NO`. - The mass addition `attribute_category_code`/`attribute1-30` are the asset DFF `FA_ADDITIONS` (not the `FA_MASS_ADDITIONS` DFF on `CONTEXT`). Its context is the category's concatenated segments (context not user-overridable); `FA_ADDITION_PVT` defaults it from the category when null and nothing on the post path validates the values, so the upload derives the context from the category and validates with `validate_dff_attributes('OFA','FA_ADDITIONS','V','I')`. `display_flexfield_value` must get the value via `p_value`, since the DFF's table is `FA_ADDITIONS_B`. - The Queue column displays blank on the development instance (`xxen_util.meaning` finds no `QUEUE NAME` rows) and that is what keeps unchanged rows uploadable: the Queue list offers only Post/On Hold/Delete/On-Review, so a displayed `New` fails id translation (`Queue is invalid`) on every downloaded row. The 11i twin shows the `fa_lookups` meaning and has that failure. - Asset Upload: DFF via `asset_cat_rec.desc_flex` (never `global_desc_flex`, varchar2(30)); `update_desc` replaces both flex records wholesale (pass current values for uncarried attributes); `depreciate_flag` change is its own `do_adjustment`; dangling `lease_id` fails every update (data corruption); tax-book download returns zero rows (open gap — join `fdh` via `distribution_source_book`). `fa_adjustment_pub.do_adjustment` defaults `transaction_subtype` to EXPENSED — set `'AMORTIZED'` when `fa_asset_val_pvt.validate_exp_after_amort(asset_id,book)` is false (Oracle rejects expensed once a prior amortized adjustment exists) or the user entered an Amortization Start Date; pass that date only when `value_modified` (the downloaded value is the LAST adjustment's date and would amortize retroactively — unmodified, FA defaults the current period). Assignment changes (expense account/location/employee) go through `fa_transfer_pub.do_transfer` on the single active distribution; rows of multi-distribution assets carry no distribution identifier, so a changed assignment there is refused. Asset key combinations are created on the fly via `fnd_flex_ext.get_ccid('OFA','KEY#',fa_system_controls.asset_key_flex_structure,...)`; the ASSET_KEY column LOV is value-only for that reason. Category-driven LIFE/PRORATE defaults are guarded with `not exists` on `fa_additions_b` so a category edit does not re-default an existing asset's life. ## Discoverer selection upload (`XXEN_DIS_UPLOAD`) Post procedure with `WORKBOOK_ID` among the IN params for per-workbook results; echo pattern for the no-submit path; `submit_discoverer_import` fences both dedup view levels with `/*+ no_merge */` and calls `helper_sheet` per distinct sheet. ## PA Event (`XXEN_PA_UPLOAD`) `pa_event_pub.create_event` populates its out table at index 0 on 11i and 1 on r12 — read `l_out.first`. A row missing from the result with API status S = empty hidden id column. ## MRP/MSC Sourcing Rule Assignment (`XXEN_MRP_UPLOAD`, `XXEN_MSC_UPLOAD`, + `_11I`) `mrp_src_assignment_pub.process_assignment` on UPDATE keeps a `G_MISS` field and clears a NULL one, and rewrites every column with fresh who columns without comparing: send an unchanged set with operation `mrp_globals.g_opr_none` (no write, the id passes through) and leave an unchanged assignment out of the table. The API validates no DFF segment and stores what it is given. The MSC planning tables have no API or table handler (the forms use direct DML), so the upload merges into the fetched `%rowtype` and skips the update when nothing changed. `msc_sr_assignments.item_type_value` is NULL on form-created rows and means Usable (1): compare it as `nvl(col,1)`, like the key cursor and `msc_sr_assignments_v`. The set columns repeated on every row are applied by the first row of that set in the file only; a Delete Option 'Assignment Set' on a later row is rejected with a message, and decision: a set-level edit on a later row stays silently ignored, as in every other upload with a repeated parent. ## QA collection results (`XXEN_QA_UPLOAD.collection_result`) - One procedure serves every collection plan: a plan's upload names each column after its element as Oracle names the `Q__V` columns (`upper(translate(qa_chars.name,' ''*{}','_____'))`) and maps it to a typed slot (`p_char1..40`, `p_number1..20`, `p_date1..10`); the procedure reads that mapping from `xxen_upload_parameters` for `xxen_upload.run_id`. A new plan needs only an upload XML; the download and Success SQL read the plan view. - Write through `qa_results_pub.insert_row`/`update_row` with `p_commit=>fnd_api.g_false` and `p_enabled_flag=>2`; status 1 stores a pending result that the plan views do not show. Pass `p_who_last_update_login=>fnd_global.login_id` (the default is the user id). - The API checks a mandatory element only when it is in the element array, so an insert passes every enabled plan element; an element that is not enabled in the plan ends the call with status U and `ORA-01403`. - `update_row` writes a `qa_results_update_history` row for every element passed, unchanged ones included: pass only values that differ from the stored ones. A dependent element (`qa_chars.dependent_element='Y'`: Lot, Revision, Serial, UOM, Locator, ...) is validated against package globals set while its parent (Item, Subinventory, ...) is validated in the same array, so a changed dependent element must travel with its parents. - Pass dates as `'YYYY/MM/DD'` and numbers through `fnd_number.number_to_canonical`. The API rounds numbers silently to the plan or element precision and cuts text at 150 bytes without checking the display length, so text columns take `DATA_LENGTH` 150. - The API does not check plan effective dates, collection plan security (`qa_web_txn_api.allowed_for_plan('QA_RESULTS_ENTER'|'QA_RESULTS_UPDATE',plan_id)`), e-signatures or element defaults, and leaves background actions (QLTACTWB, submitted only with `p_commit` true), sequence generation (`qa_sequence_api.generate_seq_for_txn`) and datetime timezone conversion to its other callers. The upload refuses plans with actions and with Sequence or Date and Time elements. - `fnd_msg_pub` holds the API's errors with a `(CHAR_ID=n)` suffix; build row messages from `x_error_array` with `qa_validation_api.get_error_message` and `&CHAR_PROMPT` replaced by `qa_plan_element_api.get_prompt`. `x_message_array` is indexed from 0. - Assign the uploads to `QARSMDF` (Enter Quality Results) and `QARSUPD` (Update Quality Results); `QARSINF` is view only. ## OKC/OKL contract records Fields default to `OKC_API.G_MISS_*`: `G_MISS` = keep, NULL = set null. Never `rec := null` (nulls every column); a sparse record is safe; a cleared cell (`fnd_api.g_miss_num = okc_api.g_miss_num`) reads as keep — detect and refuse explicitly.