EBS API Patterns for Uploads

SKILL.md

# 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 return line's reference (Returns tab) is the flexfield `OE_LINE_RETURN_ATTRIBUTE` on `return_context` and `return_attribute1`/`2`: `ORDER` and `PO` hold header id and line id, `INVOICE` `customer_trx_id` and `customer_trx_line_id`, `SERIAL` item id and serial number. Pass the context and both ids to `oe_order_pub.process_order`, which derives the `reference_*` columns; `line_val_rec.ref_*` are filled from ids only, never converted to ids. When `return_attribute2` changes, `OE_DEFAULT_LINE.Return_Attributes` resets the line record and defaults it again from the referenced line, keeping only line type, return reason, quantity, request date, warehouse, customer PO and the flexfields: the item, unit of measure and price passed are replaced. A different item on an existing referenced line follows profile `ONT_RETURN_ITEM_MISMATCH_ACTION` (`OE_RETURN_ITEM_MISMATCH_REJECT`). A serial reference needs a serial issued on a sales order line of the customer (transaction types 33, 34, 50, 62 from the line's warehouse, line not cancelled: `OE_DEFAULT_LINE.C_REF_LINE`); a serial that is only picked fails with `OE_NO_LINES_FOR_SERIAL_NUMBER`. The flexfield's value sets read Sales Orders form fields, so the upload lists and resolves the references itself (`return_reference_ids`).

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 - <element_type_id>`); 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.

## FND User (`XXEN_FND_UPLOAD.create_update_user`)

- `fnd_user_pkg.updateuser` keeps a column passed as null and clears one passed as `fnd_user_pkg.null_char`, `null_date` or `null_number`, so a cell cleared on update (`new_value` returns `g_miss`) is passed as those. It truncates the start and end date.
- `fnd_user_resp_groups_api.update_assignment` writes the who columns only with `update_who_columns=>'Y'`, so an assignment's `last_update_date` does not show whether an upload changed it.

## 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',<pay_legislation_rules rule_type 'S'>,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-<NAME>: N, <token>, ..., N, ERRMSG, <text>` with `hr_utility.get_message` empty; the row message is the text after `ERRMSG, `.
- `hr_position_api.update_position` and `hr_job_api.create_job`/`update_job` ignore `p_concat_segments` (a job is created named `.`): split the name into its key flexfield segments and pass them individually. For a rename pass a null name to the position api and a null job definition id to the job api, or the old name is kept.
- Pass the stored `cost_allocation_keyflex_id` to `hr_organization_api.update_organization`: without it the api compares its default segments with the stored combination and revalidates the organization's partial costing combination as a full one, so every update of an organization with costing fails with `FLEX-NULL REQUIRED SEGMENT`.
- `hr_person_absence_api` sets the duration only through an absence duration formula; without one a changed date leaves it blank. Calculate it with `per_abs_bus.calculate_absence_duration`, as the Absence Detail form does.
- Several rows of one person in `per_all_people_f` can carry the same `object_version_number`, so select the row to correct by its dates (the latest row is `effective_end_date=hr_api.g_eot`), never by person id and object version number.

## PO

- Receiving interface: `pie.interface_line_id=rti.interface_transaction_id`; `org_id` required on both interface tables (else RVCTP silently skips); `auto_transact_code='DELIVER'`; receiving subinventory/deliver-to come from `po_distributions_all`.
- `RVCTP` takes mode, group id and operating unit. With an operating unit it processes only that operating unit's rows, without one the rows of every operating unit the responsibility can access; rows of any other operating unit stay `PENDING` with no error. A receiving organization receives purchase orders of other operating units, so submit it without an operating unit and limit the upload's operating units to the responsibility's (`xxen_report_org_access` type `OU`).
- A blanket or scheduled release shipment shares PO number, line number and shipment number with the line's other releases: identify it by the release number too (`plla.po_release_id=pra.po_release_id(+) and decode(pra.release_num,p_release_num,1)=1`, the release empty for a standard purchase order), in the procedure and in every shipment-level list and default, as the ASN upload does. On R12 the receiving open interface derives `po_release_id` from `po_line_location_id`; on 11i it ignores the line location and receives the first release with that line and shipment number, so pass `po_release_id`. A PO number is unique only within an operating unit (`po_headers_u2`), which the Receipts form's Find window asks for next to the release.
- 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.
- The PO Change API writes every shipment field as `nvl(<new>,<stored>)` and has no null-date sentinel, so it cannot clear a Need-By or Promised Date. Write the clear through `po_line_locations_pkg_s2.update_row` (with `last_accept_date`), then still send that shipment to `update_document`: the API takes its approval status, Requires Reapproval and revision from the comparison with the archive (`po_document_revision_grp.check_new_revision`), which sees the cleared date. Set a savepoint before the handler write and roll back to it when the API returns an error, since the API rolls back only its own work. A planned item's shipment (`mrp_planning_code` 3, 4, 7, 8, 9 or `inventory_planning_code` 1, 2 in the ship-to organization) needs one of the two dates (`PO_PO_PLANNED_ITEM_DATE_REQ`), which the API does not check and the submission check catches only at approval.
- Removing a standard purchase order line follows the form: delete when `po_lines_sv.check_line_deletion_allowed` allows it (never approved, no reserved funds), else cancel when `po_document_control_pvt.val_control_action(...,p_action=>'CANCEL',p_agent_id=>fnd_global.employee_id,...)` returns success (a pure validation with Oracle's messages on `fnd_msg_pub`), else reject with that message. Delete through `po_document_update_grp.update_document` with `po_changes_rec_type.create_object(p_po_header_id,p_po_release_id=>null,p_line_changes=>po_lines_rec_type.create_object(p_po_line_id=>po_tbl_number(id),p_delete_record=>po_tbl_varchar1('Y')))` (`p_po_release_id` has no default on 11i), cancel through `po_document_control_pub.control_document`. Run both after all rows, in the post procedure: a call from the row procedure locks the header against an earlier row's uncommitted change on the same order. On 11i `check_line_deletion_allowed` is missing; `po_lines_sv.val_line_delete` raises its reason through `po_message_s.app_error`, which raises only once per session until `po_message_s.clear`.
- The item of a saved standard purchase order line cannot be changed (form and every API); the replacement is a new line through Import Standard Purchase Orders (header action `UPDATE`, R12 only) followed by removing the old line. On 11i that import cannot add a line to an existing standard purchase order: it fails with `Derivation Error: VENDOR_NUM (VALUE= &VALUE) specified is invalid`.
- 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)`.
- PDOI checks a shipment's or price break's ship-to organization only for the item and revision being defined there (`PO_VAL_SHIPMENTS2.ship_to_organization_id`, in the validation set common to standard, blanket and quotation), and the PO Change API does not check a split shipment at all; neither checks whether the item is purchasable there. The Purchase Orders and Quotations forms list only organizations where the item is purchasing enabled (record groups `ORGS_ITEM_NO_REV`/`ORGS_ITEM_WITH_REV`, list validation on) and check it only when the organization is entered, so a queried shipment keeps its organization and its price stays changeable after the item's status changes. The Purchasing Document Upload reproduces the check in its procedure (`validate_ship_to_organization`) for a new shipment or price break, a quotation price break moved to another organization and a split shipment (which keeps the organization of the shipment it is split from), on the organization the import will use: the entered one, else the inventory organization of the ship-to location, on a standard purchase order of the header ship-to location when the shipment has none. Never put it into the column's list: an id list translates every processed row and would reject price changes on existing breaks.
- PDOI takes a staged shipment's ship-to organization and location ids from its `po_lines_interface` row whenever the shipment row carries none (`NVL(intf_locs.ship_to_organization_id, intf_lines.ship_to_organization_id)`, bug 19264990) and derives an id from a code only while it is still null, so the line row's ship-to overrides the codes of every shipment staged under it. On a standard purchase order PDOI fills the line row's ids from the header ship-to location itself, so leaving them empty is not enough: stage each shipment's ids, derived as PDOI does (`org_organization_definitions` active and inventory enabled for the code, `po_locations_val_v` with `ship_to_site_flag` Y for the location, a blank organization from the location's inventory organization), and give the line row no ship-to, so a shipment without one gets PDOI's default instead of the first row's.
- PDOI rejects receipt controls on a blanket price break (`Column RECEIPT_REQUIRED_FLAG (VALUE=N) must be NULL`). The Purchasing Document Upload downloads 2-Way for a blanket break's empty flags, so it sends no flags for 2-Way on a blanket and passes 3-Way and 4-Way on for PDOI to reject.
- 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.
- Approval assignments (`po_position_controls_all`) are identified by rowid. `position_control_id` is an upgrade artifact that Oracle never reads or writes: the Approval Assignments form POXSTDPC leaves it null on every assignment it creates, and a fresh 12.2 installation has neither the column nor the 11i-era `po_control_functions_all` synonym; read document types from `po_control_functions`.

## 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`). On 11i Mass Load stores `g_miss_char` literally in `attribute1-15` and a null keeps the old value, so a segment cannot be cleared there; and a reschedule with `allow_explosion='N'` needs both start and completion dates or neither.

Assembly completions and returns go through `mtl_transactions_interface` (source type 5) and `inv_txn_manager_pub.process_transactions` per header in an autonomous transaction: the manager commits after `wip_mti_pub.preInvWIPProcessing`, so delete the header's interface rows on failure, and set `completion_transaction_id` yourself to find the material transaction. A job with a non-null `serialization_start_op` cannot be completed this way (the form's job list excludes it), and `wip_parameters.default_serialization_start_op` makes every new job of a serial-controlled assembly serialized.

The move interface (`wip_move_txn_interface` with `wip_movproc_pub.processInterface`) refuses a Move and completion that starts at To move of the last operation (`WIP_EZ_NO_CMP_LAST_OP2`), and so does the Move Transactions form, which fixes To at that step and refuses a move within one operation step (`WIP_SAME_OP_AND_STEP`); assemblies already there are completed through the completion path above.

## 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`), `'<N>'` 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.
- ABC assignments, valid categories of a category set and item description translations have no server api: the forms (ABC Assignment Updates `INVADACA`, Category Sets `INVIDSCS`, the Master Items Translations window) do the DML client side, so the uploads write `mtl_abc_assignments` and `mtl_system_items_tl` directly. Category codes go through `inv_item_category_pub.create_category/update_category`, category sets through `mtl_category_sets_pkg`, valid categories through `inv_item_category_pub.create/delete_valid_category`. `inv_item_category_pub` and `mtl_category_sets_pkg` treat `g_miss_*` as keep, so a cleared cell must reach them as null. A catalog with a category hierarchy (`hierarchy_enabled='Y'`) is Product Hub only. The form stores a ticked Viewable by Supplier as null and an unticked one as `N`, and `N` is accepted only for the purchasing category structure.
- Kanban cards: `inv_kanbancard_pkg.insert_row/update_row/delete_row` are the form's handlers (`INVKBCRD`); a card takes its source from the pull sequence of item, subinventory and locator (`kanban_plan_id=-1`), and `insert_row` numbers a card with a blank number from its id. Supply status changes follow `inv_kanbancard_pkg.supply_status_change_ok`; the form allows a card status change from Active only with supply status 1,2,3,4,6 and from Hold with 1-6, never from Canceled.
- Move order requisitions: `inv_move_order_pub.process_move_order` does not default the line number (`ORA-01400` on `LINE_NUMBER`): pass max+1. The form numbers a header with `inv_transfer_order_pvt.get_next_header_id(org)` and checks `unique_order`; its Approve button is `invtroap.start_to_approval`, which approves at once when the organization has no approval timeout. Cancel and close lines with `inv_mo_admin_pub.cancel_line/close_line`.
- Cycle counts: `mtl_cceoi_action_pub.import_countrequest` (action 14, process mode 1) needs the entry's count list sequence and SKU (item, subinventory, locator, lot, revision) besides `cycle_count_entry_id`, else `Count List Sequence does not match`, and the secondary quantity for a dual UOM item. `mtl_cc_interface_errors` also holds the informational `Count request processed without errors.`: the record failed only when `mtl_cc_entries_interface.error_flag=1`. The count date must fall in an open inventory period, and a count without a counter (employee id and name both null) is rejected with `INV_CCEOI_NO_COUNTER`. Processed rows stay in the interface with `status_flag` 0: the import program picks up only `status_flag` 4 (or null), and Purge Cycle Count Entries Open Interface removes 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.
- `ap_vendor_pub_pkg` copies `vat_registration_num` into `zx_party_tax_profile.rep_registration_number` (the Default Reporting Registration Number of the Tax Details page): the supplier's into its `THIRD_PARTY` profile, a site's into the `THIRD_PARTY_SITE` profile of its party site, which every supplier site on that party site shares. Where a party site serves more than one supplier site, the AP Supplier Upload puts the shared profile back after a site update, so the Site Tax Registration Number is stored on the supplier site only and is read back from `ap_supplier_sites_all.vat_registration_num` before the profile, and from the profile only while its number is not the default tax registration's.
- 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.`).
- Payables Open Interface has no distribution interface (no `ap_invoice_dists_interface` on 12.1.3 or 12.2). An imported line with an account, distribution set, project, PO or receipt gets `generate_dists='Y'` and no distributions; invoice validation (`ap_approval_pkg`) generates them only for `Y` and sets `D`, and a line left at `N` without distributions gets the `DIST VARIANCE` hold. Manual distributions go in between import and validation exactly as the Invoices form (APXINWKB) enters them: `AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Row` with class `CANDIDATE` and `pa_addition_flag` `N` for a project distribution, `E` otherwise, then `Make_Distributions_Permanent` and `generate_dists='D'` on the line. Tax calculated at validation prorates over them.
- A project line's distribution account at validation follows profile PA: Allow Override of PA Distributions in AP/PO (`PA_ALLOW_FLEXBUILDER_OVERRIDES`): Y keeps the line's account, N replaces it with the project account generator's; distribution set lines keep their own accounts either way. A distribution inserted directly takes its account as the Invoices window (distribution PRE-INSERT, and PRE-UPDATE when the account or a project field changed; there the profile only makes the Account field editable) and the import give it: `pa_acc_gen_wf_pkg.ap_inv_generate_account` for every project distribution whatever the profile, with the entered account as `p_input_ccid`, which the seeded workflow ignores, so the generated account replaces an entered one; a blank expenditure item date takes `ap_invoices_pkg.get_expenditure_item_date`, as in the import. Decision: the AP Invoice Upload's split rows follow the profile as validation does, keeping an entered account on a project row when it is Yes, so a row gets the same account whether its line is split or not. `PA_TRANSACTIONS_PUB.validate_transaction` returns `x_msg_type` `E` on success too: test `x_msg_data`, as Oracle's callers do.
- Distribution set amounts are `ap_utilities_pkg.ap_round_currency(percent*line amount/100)` with the remainder on the distribution with the largest absolute amount (the later one on a tie), and the base amount remainder stored in its `rounding_amt` (`AP_INVOICE_LINES_PKG.Generate_Dist_Tab_For_Dist_Set`).
- Invoice validation prorates a Freight or Miscellaneous line with Prorate Flag Yes only when the Invoice Amount equals the lines including the tax Payables calculates: otherwise the line keeps no distributions and the invoice gets the holds CANNOT EXECUTE ALLOCATION (`Allocation generates an invalid account`, which says nothing about the cause), DIST VARIANCE and LINE VARIANCE. Test prorated charges with a tax-inclusive Invoice Amount.
- Lines added to an existing invoice: Payables Open Interface rejects an existing invoice number and has no mode for adding lines, and there is no public api. The AP Invoice Upload makes the Invoices window's server calls: `AP_AIL_TABLE_HANDLER_PKG.Insert_Row` (a plain insert: the caller supplies every default the form sets on a new line), then for the amount `AP_INVOICES_PKG.invoice_pre_update` with the new values while the row still holds the old ones (it returns the payment schedule recalculation flag), `Update_Row`, `invoice_post_update` (recreates the schedules from the terms), `AP_ACCOUNTING_EVENTS_PKG.update_invoice_events_status` and `post_forms_commit` with update base `Y`. `Update_Row` writes every column and defaults many arguments to null, so pass every one from the locked row: a left-out argument clears its column.
- 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.
- 11i suppliers (`po_vendors`, `po_vendor_sites_all`, `po_vendor_contacts`) have no public api. Do what `AP_PO_VENDORS_APIS_PKG.update_vendor`/`update_vendor_site` do internally, with every column: read the row, overlay the changed values, call `validate_update_vendor`/`validate_update_vendor_site` (`validate_insert_*` for a create; validation only, no DML), then the Suppliers form's table handlers `AP_VENDORS_PKG`/`AP_VENDOR_SITES_PKG.insert_row`/`update_row`. The package's own `insert_new_*`/`update_vendor*` take only a handful of fields; `insert_new_vendor_contact`/`update_vendor_contact` cover the 14 name, title, mail stop, phone, fax, email and url fields, and `update_vendor_contact` overwrites all 14, so pass the stored value of an unchanged one. Nothing covers a contact's `inactive_date`, `department`, flexfield or alternate names. Bank accounts and branches (`ap_bank_accounts_all`, `ap_bank_branches`) have no database api (their handlers are in the form); assignments do (`AP_BANK_ACCOUNT_USES_PKG`). The Supplier Open Interface (`ap_suppliers_int`, `ap_supplier_sites_int`, `ap_sup_site_contact_int`) only creates, runs as a concurrent request and has no bank account columns.
- Decision: the 11i AP Supplier Upload creates and updates synchronously through those validators and table handlers (the internal `AP_PO_VENDORS_APIS_PKG` is accepted), limits contacts to the 14 api fields plus `inactive_date` (direct update, to deactivate and reactivate), and supports bank accounts only as an assignment of an existing account.
- 11i supplier defaults: `ap_apxvdmvd_pkg.initialize` returns the Suppliers form's defaults from the Financials, Payables, Purchasing and Receiving options for a new supplier (numbering method, terms, pay group, receiving controls, tax), for the operating unit of the session's org context. It returns the default withholding tax group whatever the Allow Withholding Tax default, and the validators reject a group with the flag `N` (`ALLOW_AWT_FLAG and AWT_GROUP_ID do not match`), so clear the group whenever the flag is `N`, as the form does with the check box. `validate_insert_vendor_site` fills a site's accounts, tax calculation, offset tax and tolerance itself when they are passed null (in out arguments), from the supplier values passed in its `x_sup_*` arguments and the Financials options. The validators return `x_status` `F` with a message text, or for some checks a message name, and table handler errors arrive as `ORA-20001` with the message still on the `fnd_message` stack.
- `validate_update_vendor_site` checks the site code for duplicates without excluding the site itself, so passing the stored code with `x_org_id` fails every update with `AP_VEN_DUPLICATE_VEN_SITE`; pass `x_vendor_site_code` only when the code changes (Oracle's own `update_vendor_site` passes no org, which skips the check). Older 11i supplier and site records hold null instead of `N` in their check box columns, so compare null as `N` or every unchanged re-upload reports a change. Nothing in the database enforces the primary bank account rule (`AP_PO_VENDORS_APIS_PKG`'s own checks are never called). The Suppliers form checks it at commit (`VNDR_POST_COMMIT.Check_Vndr_Primary_Acct`/`Check_Site_Primary_Acct`): every assignment that has not ended needs exactly one primary assignment of its currency at the same level whose dates overlap its own (`AP_ONE_PRIMARY_REQUIRED`, `AP_ONE_PRIMARY_REQUIRED_SITE`). Check it once per supplier or site, at the last row of the run that carries one of its assignments: switching the primary between two assignments takes two rows and is invalid after either one alone. Where a currency has no primary assignment overlapping an assignment created in the run, make the first one created in that currency primary, as there is only one valid choice or the file has not made one; report the error only where no assignment of the currency was created in the run.
- 11i unpaid invoices of an ended bank account assignment: `AP_AUTOMATIC_PROPAGATION_PKG.update_payment_schedules('SUPPLIER ASSIGNMENTS',<bank account id>,<vendor id>,<vendor site id or null>)` moves the payment schedules of the unpaid and partially paid invoices to `get_primary_bank_account`, the primary assignment of the invoice's site or of the supplier that is active today, of the payment currency, other than the ended account; schedules without one keep the account (`AP_NO_ACTIVE_ACCOUNTS`). The form offers it after the save (`AP_INVALID_ACCOUNTS`), so call it from the post procedure; a replacement that starts after today is not found yet.
- 11i site rules the handlers and `validate_update_vendor_site` leave to the form: one active primary pay site per supplier in the operating unit (`AP_PRIMARY_PAY_SITE_EXISTS`, checked after the insert or update); `create_debit_memo_flag` only on a pay site or a site with an alternate pay site (`AP_ACTIVE_PAY_SITE`; a new site takes the supplier's flag only then); the address style of the country (`fnd_territories.address_style`); and, at every save of a federally reportable supplier whose organization type is not foreign, `ap_vendor_sites_pkg.check_state_codes` (`AP_VEN_WARN_INV_STATE`, also for a US site with no state). The Shipping Network Location is a `po_location_associations` row (org-striped view), which the site handlers insert and update from `x_shipping_location_id`; clearing it needs `ap_po_locn_association_pkg.delete_row`. The supplier's `hold_by` (`fnd_user.employee_id`) and `hold_date` are set when Purchase Order Hold is set and cleared with it, and the hold reasons are dependent fields: Purchasing Hold Reason of Purchase Order Hold, Payment Hold Reason of the three payment hold check boxes.
- 11i EDI Payment Format goes only with EDI Payment Method `ACH`: `validate_insert_vendor` rejects any other combination (`EDI_PAYMENT_METHOD and EDI_PAYMENT_FORMAT do not match`), while the update and site validators check only the lookup code. The form makes the format enterable only for ACH, and its site block clears the format when the method changes. So restrict the format list to an ACH method, and clear a stored format when an update changes the method away from ACH.

## ZX tax registrations (`XXEN_ZX_UPLOAD`)

- `XXEN_ZX_UPLOAD.upload_tax_registration` maintains the `zx_registrations` of a `THIRD_PARTY` or `THIRD_PARTY_SITE` tax profile for the AR Customer Upload and the AP Supplier Upload. A supplier site's registrations belong to its party site, so every supplier site on that address shares them.
- `zx_registrations_pkg.update_row` keeps the stored value of every column passed null and has no clear sentinel, so the upload writes cleared columns with a targeted update after it. `insert_row` and `update_row` copy a default registration's number into the profile's `rep_registration_number`, but not its type.
- `zx_party_tax_profile_pkg.update_row` clears a column on `fnd_api.g_miss_char` only for the tax classification, reporting registration number, country, attribute and authority/provider columns. For `allow_offset_tax_flag`, `process_for_applicability_flag`, `inclusive_tax_flag`, `self_assess_flag`, `rounding_level_code`, `rounding_rule_code` and `registration_type_code` it stores the sentinel literally as `chr(0)`, after which every supplier site update fails with `OFFSET_TAX_FLAG is invalid`. Pass those columns their stored value or a real one, never the sentinel.
- Setting a default registration on the Tax Details page sets the profile's reporting registration number and type from it, the supplier's `ap_suppliers.vat_registration_num` (`ZX_TCM_BES_REGISTRATION_PVT.update_suppliers`) and, for an organization party, `hz_parties.tax_reference` (`zx_party_tax_profile_pkg.sync_tax_reg_num`); a supplier site's own `vat_registration_num` is not changed. Decision: the AP Supplier Upload does the same, so the default registration takes priority over the Tax Registration Number columns.
- Validate a registration as the page does: `zx_trn_validation_pkg.validate_trn` for the number and the ZX messages `ZX_DATE_RANGE_INVALID`, `ZX_TAX_MANDATORY`, `ZX_PTP_JUST_ONE_REGIME`/`_TAX`/`_JURISDICTION` (an overlapping registration of the same regime, tax and jurisdiction), `ZX_REGISTRATION_DUPLICATE` and `ZX_REG_DEFAULT_INVALID` (one default per profile). Regime, type and number cannot change on an existing registration; it is ended with Effective To, never deleted.

## 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 (<partial account>)` (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 customer bank accounts: call the 13-argument `arp_bank_pkg.process_cust_bank_account` (no `p_api_version`/`p_org_id`, takes the org context, raises through `app_exception` with the message on `fnd_message`); the api overloads exist only from `ARCUSBAS.pls` 115.12, so a call to them fails to compile on older 11.5.10 instances with `PLS-00306`. Its `p_exp_date` is the bank account's inactive date, and it creates the assignment starting `least(p_trx_date,sysdate)` with no end date and its own primary flag, so set the assignment's dates and primary flag afterwards through `arh_bau_pkg.update_row`. That handler writes every column (flexfield included) and rejects a second primary assignment of the same currency and date range.
- 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 `<table>.<column>%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 <blank> 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`.

- `Attribute Group is invalid` means the group has no enabled association at the row's data level to the item's catalog category or one of its parents; an item without a catalog category gets no user-defined group. The PIM item pages apply the same rule (`EGO_PAGES_V` and `EGO_PAGE_ENTRIES_V` over the item's category chain plus `-1`, whose pages hold only the seeded operational attribute groups), so the fix is setup: assign the item's catalog category or associate the group. `EGO_USER_ATTRS_DATA_PVT.Is_Data_Level_Correct` is looser: it tests the data level against the group's enabled levels (`ego_attr_group_dl`) and ignores the association's data level and enabled flag.

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

## ENG Engineering Change Order (`XXEN_ENG_UPLOAD`)

- A revised item of a Scheduled ECO (`eng_engineering_changes.status_type` or the revised item's own `status_type` 4) can only be rescheduled: `ENG_ECO_PVT` runs `ENG_Validate_Revised_Item.Check_Access_Scheduled`, which rejects the record with `ENG_RIT_NO_UPDATE_SCHEDULED` when any field other than the keys and `new_effective_date` is not null, and with `ENG_RIT_NO_CHILD_IN_SCHEDULED` when components travel in the same call. Send a fresh `eng_eco_pub.g_miss_revised_item_rec` with the keys, `start_effective_date` and `new_effective_date` only. On an ECO that is not scheduled, an update resets the revised item status and the new revision's description unless their stored values are passed.

## 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. PM schedules are written as the Preventive Maintenance form (EAMPMDEF) writes them, never through `EAM_PMDef_Pub.Create_PM_Def`/`Update_PM_Def`: those overwrite every header column from the record, recreate the activities without their next service dates and the rules under new ids (11i: a null `rule_id` and `last_service_reading`), and 11i rewrites `created_by`/`creation_date`. The form inserts and updates `eam_pm_schedulings`, `eam_pm_activities` and each rule by `rule_id` through its own table handlers, then (ON-COMMIT) validates the posted rows with `validate_pm_header_and_rules` and, on R12, `validate_pm_activities`, and calls `update_pm_last_cyclic_act` and `update_pm_last_service_reading`; 11i only validates. It never writes the header's `activity_association_id` or `day_tolerance` (R12), `current_wo_seq`, the next service dates or a rule's `last_service_reading`, and a new schedule starts at `current_cycle` 1, `current_seq` 0. The schedule name is unique (`EAM_PM_SCHEDULINGS_U2`). Read the form logic from `$AU_TOP/resource/EAMPMDEF.pll` (`frmcmp_batch module_type=library script=yes`) and the triggers from `frmf2xml` of the fmb; 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`). `eam_assetnumber_pub.update_asset_number` renames an asset when `p_instance_id` identifies it and `p_instance_number` carries the new number (Oracle rejects a number in use), so the upload finds an existing asset by its Asset Id, trusted on Update only; a copied row with the id creates a new asset. Its `p_location_type_code`/`p_location_id` set the current location: the 12.2 Update Asset page and the create API offer `hz_locations` only (type `HZ_LOCATIONS`), the API refuses the change for an asset located in inventory (`EAM_INVALID_LOCATION_UPDATE`), and nothing clears a location. The 11i API has neither argument and the 11i asset number is the serial number, so the twin keeps both read only. An asset's status is `csi_item_instances.instance_status_id` resolved through `csi_instance_statuses.name` (the base table exists on 12.1.3 and 12.2; `csi_instance_statuses_vl` and the view's `instance_status` column are 12.2 only); the view's `current_status`/`current_status_meaning` is the serial number status, which the asset pages do not show. 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).

`gmf_actual_cost_adjustment_pub` update and delete silently skip a `cm_adjs_dtl` row whose `gl_posted_ind` is null (validated as not posted, filtered by `gl_posted_ind<>1`) and still return S: set it to 0 first.

## 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 <changed cols>` (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.

- `pa_budget_pub.create_draft_budget` with a plan type and both the create-new and replace-current-working flags set to Y replaces the current working plan version; the new version takes the plan type's planning options for the project, not the old version's.
- Assigning project asset lines to assets has no api: as the Capital Projects form does, move the line cost with `pa_faxface.update_asset_cost` and update `pa_project_asset_lines_all` directly.
- Publish a workplan with `pa_project_pub.change_structure_status` and `p_commit` false; it runs summarization online and can take minutes.
- Transfer an expenditure item as the Expenditure Items window does: `pa_adjustments.adjust('PROJECT OR TASK CHANGE','PAXPRRPE',...,x_adjust_level=>'I')`, which reverses the item, creates the new one and writes the adjustment audit. Its user lock (`pa_debug.acquire_user_lock`/`release_user_lock`, through `dbms_lock.allocate_unique`) commits, so call it from an autonomous transaction, and release the lock yourself on success: `adjust` releases it only for a non-null message type. The window's own checks (`paeia_transfer.check_xfer_allowed` in `PAXEIADJ.pll`) are not in the api and are reproduced in `XXEN_PA_UPLOAD.upload_expenditure_transfer`.

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

## QP Price List Copy and Adjust (`XXEN_QP_UPLOAD.upload_price_list_copy_adjust`)

- The Copy Price List (QPXPRCPL) and Adjust Price List (QPXPRMPL) windows only submit the programs `QPXVCPLB` and `QPXVAPLB`, whose executables `QP_COPY_PRICELIST_PVT.Copy_Price_List` and `QP_ADJUST_PRICELIST_PVT.Adjust_Price_List` the upload calls directly, each from an autonomous procedure because both commit. Copy writes the header, header qualifiers, the lines active today, pricing attributes and price break relations with direct inserts; Adjust is a direct `update qp_list_lines set operand` that rounds to the list's rounding factor only when profile `QP_PRICE_ROUNDING` is set. Both report failure through `retcode` 2 and `errbuf`.
- Their item range arguments `p_segment1_lohi`..`p_segment20_lohi` are SQL text concatenated into `m.segmentN between <text>`: pass `'low' AND 'high'` per System Items segment with quotes doubled, and `'' AND ''` for an unused segment. Any item range, category or item status filter limits both programs to lines priced by item number (`PRICING_ATTRIBUTE1`); Adjust's creation date filters the price list line's creation date, not the item's. Copy's `p_discount_flag` copies only the `DLT` modifier lists qualified by the source list, which exist only for price lists carried over from Release 11. Copy keeps the source's `list_type_code` and copies header qualifiers only from a `PRL` source, so an `AGR` copy has no qualifiers and no agreement. It skips the source's Price List qualifiers that point to another price list (the source's secondary price list assignments) and re-points a qualifier on the source itself to the new list.
- The windows' price list lists exclude transportation price lists (`pte_code` `LOGSTX`), and Adjust offers only standard price lists (`PRL`) that are not end-dated and that the user may update (`qp_secu_list_headers_vl.update_flag`). On 11i `Copy_Price_List` has no `p_global_flag`/`p_org_id` (the copy takes the source's global flag and operating unit), and there are no continuous price breaks (`qp_list_lines.continuous_price_break_flag` is R12 only).
- Copy's category filter matches `mtl_item_categories` in any organization the item is assigned to, as Copy takes no organization; Adjust limits it to its `p_org_id`, which the upload passes as the item validation organization (`qp_util.get_item_validation_org`). Both releases.
- A price list name identifies a price list: `QP_Validate.Price_List_Name` (`SO_OTHER_NAME_ALREADY_IN_USE`) rejects a name that any list of the same `version_no` carries in the session language, and price lists carry version 1. Modifier and discount lists usually have none, so one can share a price list's name: restrict a lookup by name to `list_type_code in ('PRL','AGR')`.
- Decision: the upload's Effective To only end-dates a source price list that has no end date; a blank cell changes nothing and a date differing from an existing end date errors the row, so a pasted or csv row can never reactivate or extend a list.

## QA collection plans (`XXEN_QA_UPLOAD.collection_plan`)

- `qa_plans_pub.add_plan_element` stores `p_result_column_name` without any check: not whether another element of the plan uses the column (`qa_plan_chars` has no unique key on it), not the element's type, not even a seeded element's fixed column, which the value replaces. Left null, it takes the seeded element's fixed column, else the lowest free `CHARACTERn` for every element type, Comment and Sequence elements included (12.1.3 and 12.2 alike), while the Collection Plans form puts those in `COMMENT1-5` (2000 bytes) and `SEQUENCE1-15` (50 bytes). The column of an element already in a plan cannot be changed, and Oracle refuses to delete an element or plan while results hold data in its column.
- Seeded exceptions to the type-to-column pattern: the Character element Source Reference ID sits in `SEQUENCE2` in Oracle's disposition template plans, and Transaction Type is stored as `Character1`/`Character2` in two template plans, so compare result column names in upper case.
- On 11i (`qltpplnb.plb` 115.27.11510.3) `add_plan_element` fails with `ORA-01481: invalid number format model in Package qa_plans_pub Procedure add_element` whenever the plan already has an element in a `CHARACTER` column: its column cache reads `to_number(result_column_name, 10)`, which R12 corrects to `substr` (Oracle bug 5406294). A plan created earlier in the same session is not affected, as the cache keeps the new plan's id, so a test that creates the plan and its elements in one run passes. Decision: the 11i upload does not work around it; its description states the limitation.

## 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_<plan>_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.
- `QA Collection Result Upload` serves any plan through the Plan parameter: its columns `CHAR1..40` (text and comment elements), `NUMBER1..20` and `DATE1..10` take the elements by prompt sequence within each type (`c_plan_elements`), and `collection_result` treats an upload as generic when every slot parameter maps to the column of its own name. The headings come from a dynamic column translation rule binding the Plan parameter's `:plan_id` (a rule may bind any run parameter's bind); the column lists come from `qa_ss_lov_api.get_lov_sql` with `get_lov_bind_values` (comma separated values for the positional binds `:1..:n`, an empty value for a parent element without a value). A multi-select Plan would bind `<multiple_values>` into the rule, so the parameter has a bind anchor.
- A plan view can lack the columns of elements added after it was last generated (`ORA-00904` on the element's column): read only the columns `user_tab_columns` shows, and never pass such an element as an unchanged context element, as its value is unknown.
- 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.

## AGIS intercompany transactions (`XXEN_FUN_UPLOAD`)

- Import through the intercompany open interface (`fun_interface_*`) and `fun_open_interface_pkg.main`, never `fun_trx_pub.create_batch` alone: it reads `fun_interface_headers` to validate recipient distributions and commits mid-call.
- `main` overwrites `fun_interface_batches.initiator_id` from `initiator_name`, so `initiator_name` must be populated. The interface `batch_id` becomes `fun_trx_batches.batch_id`.
- The importing user's employee party must be an intercompany contact of the initiator with a `FUN_TRX_BATCHES_SET` grant; otherwise the import fails with `Combination of Source and Group ID not found`. A recipient needs a contact with `additional_information1='Y'` (`FUN_NO_ORG_ASSIGNMENT`).
- An import not sent validates at level 50, which does not check that distributions balance; only a sent import does.