This appendix contains detailed information about the PO reconciliation history feed file.
This appendix covers the following topics:
After the appropriate purchase orders have been extracted using the selection criteria, the data elements for the purchase orders are compiled in the correct format and included in the output PO History Feed file. The PO History Feed file structure contains the following levels of information, called records:
Control Record: Transaction information about the complete PO History Feed file as well as summary information about all purchase orders included in the feed.
Header Record: Header-level information for each purchase order included in the PO History Feed.
Detail Record: Line-level and distribution-level information for each purchase order included in the PO History Feed.
The following sections describe the data elements that are included in each of the records in the PO History Feed file.
The following table shows the data elements that are the Control Record:
Field | Required | Length | Starting Position | Data Type | Action |
---|---|---|---|---|---|
1 - Record Type Indicator | Yes | 1 | 1 | Alpha | C (for Control) |
2 - Creation Date Prefix | Yes | 15 | 2 | Alpha | Creation Date |
3 - File Creation Date | Yes | 8 | 17 | Numeric | YYYYMMDD - File Creation Date (system date when the PO History Feed request was completed) |
4 - Total File Amount Prefix | Yes | 19 | 25 | Alpha | Total File Amount |
5 - Sum of All PO Amounts | Yes | 15 (12.2) | 44 | Numeric | Total Sum of all PO Amounts in file in functional currency of operating unit. Format for Number is 12.2. Send first 15 characters only and truncate if necessary. Should include purchase orders with all statuses (ON, CN, OH) for calculation. For more information, see the following business rules. |
6 - Credit/Debit Indicator | Yes | 1 | 59 | Alpha | D for Debit, C for Credit |
7 - Header Record Prefix | Yes | 14 | 60 | Alpha | Total Header |
8 - Total Header Records | Yes | 5 | 74 | Numeric | Total Number of Header Records in file. Should include purchase orders with all statuses (ON, CN, OH) for calculation. For more information, see the following business rules. |
9 - Detail Record Prefix | Yes | 14 | 79 | Alpha | Total Detail |
10 - Total Detail Records | Yes | 5 | 93 | Numeric | Total Number of Detail Records in file. Since canceled distributions on the PO are not included in the Detail Record, only non-canceled distributions should be counted during calculation. For more information, see the following business rules. |
11 - Start Date Prefix | Yes | 12 | 98 | Alpha | Start Date |
12 - Transaction Start Date | Yes | 8 | 110 | Numeric | YYYYMMDD - Earliest last_update_date among all purchase orders in the file. Should include purchase orders with all statuses (ON, CN, OH) for calculation. For more information, see the following business rules. |
13 - End Date Prefix | Yes | 10 | 118 | Alpha | End Date |
14 - Transaction End Date | 8 | 128 | Numeric | YYYYMMDD - Latest last_update_date among all purchase orders in the file. Should include purchase orders with all statuses (ON, CN, OH) for calculation. For more information, see the following business rules. |
The following apply to the data elements in the Control Record:
All fields of data type Alpha are left-aligned and padded (if required) on the right with spaces. All field of data type Numeric are right-aligned and padded on the left (if required) with zeroes.
Field #5 Sum of All PO Amounts sums up the PO Amount fields for all the Header Records in the functional currency of the operating unit. Calculation for field includes purchase orders with all statuses (ON, CN, OH) that are included in the feed, not only the purchase orders with a status ON. Calculation does not include the estimated tax for the purchase order.
Field #6 Credit/Debit Indicator: Debit means (+) or Credit means (-), depending on the value for Field #5 Sum Of All PO Amounts.
Calculation for Field #8 Total Header Records includes purchase orders with all statuses (ON, CN, OH) that are included in the feed, not only the purchase orders with a status ON.
For calculation of Field #10 Total Detail Records, since canceled distributions on the purchase orders are not included in the Detail Record, only non-canceled distributions are accounted for.
Field #12 Transaction Start Date: The date contains the earliest last_update_date among all the purchase orders in the feed. Calculation for field includes purchase orders with all statuses (ON, CN, OH) that are included in the feed, not only purchase orders with a status ON.
Field #14 Transaction End Date: The date contains the latest last_update_date among all the purchase orders in the feed. Calculation for field includes purchase orders with all statuses (ON, CN, OH), which are included in the feed, not only the purchase orders with a status ON.
The following table describes the data elements that are the Header Record:
Field | Required | Length | Starting Position | Data Type | Rules |
---|---|---|---|---|---|
1 - Record Type Indicator | Yes | 1 | 1 | Alpha | H (for Header) |
2 - CM Ref # PO Number |
Yes Yes |
2 15 |
2 4 |
Alpha Alpha |
AX Only purchase orders with numbers up to 15 characters in length are sent. Purchase orders with numbers greater than 15 characters in length are not included and a warning is generated. For more information, see the business rules. |
3 - Card Number | Yes | 16 | 19 | Alpha | Number of P-Card being charged. Numeric only. Non-numeric characters from the P-Card number are removed before sending. Only purchase orders with P-Card numbers up to 16 characters in length are sent. Purchase orders with P-Card numbers greater than 16 characters are not included and a warning is generated. See the business rules that follow this table. |
4 - Cardmember Name | Yes | 35 | 35 | Alpha | In case of employee P-Cards: Card Member Name. In case of supplier P-Cards: Supplier Name. For both, only first 35 characters are included. |
5 - Requestor Name | No | 25 | 70 | Alpha | In case of supplier P-Cards: send blank. In case of employee P-Cards: Card Member Name. Only first 25 characters are included. |
6 - Requestor ID | No | 10 | 95 | Alpha | Employee ID for requester. Only first 10 characters are included. |
7 - Payment Type Indicator | Yes | 1 | 105 | Alpha | P for Employee P-Card. O for Supplier P-Card. |
8 - Supplier Name | Yes | 100 | 106 | Alpha | Field that sends both Supplier Name and Supplier Site Name. Format of the field is Supplier Name-Supplier Site Name. Only first 100 characters are included. |
9 - Client Supplier Number 1 | No | 30 | 206 | Alpha | Vendor ID (po_headers.vendor_id) Only first 30 characters are included. |
10 - Client Supplier Number 2 | No | 25 | 236 | Alpha | Vendor Site ID (po_headers.vendor_site_id) Only first 25 characters are included. |
11 - Order Date | Yes | 8 | 261 | Numeric | last_update_date from PO Header in YYYYMMDD format. |
12 - Order Status | No | 2 | 269 | Alpha | Two-letter codes (ON, CN, OH). |
13 - Total Lines in PO | Yes | 4 | 271 | Numeric | Total number of details records included. Since canceled distributions on the purchase order are not included as Detail Records, only non-canceled distributions are accounted for. If purchase order has a control status of Canceled or Finally Closed, no distributions are included as detail records. See the business rules that follow this table. |
14 - PO Amount | Yes | 15 (12.2) | 275 | Numeric | Total Amount of purchase order in functional currency for operating unit. Even for purchase orders created in a currency different from functional currency for operating unit, the field is calculated in the functional currency. See the business rules that follow this table. Only first 15 characters are included. |
15 - Credit/Debit Indicator | Yes | 1 | 290 | Alpha | D for Debit, C for Credit. |
16 - PO Currency | Yes | 3 | 291 | Alpha | Always functional currency for operating unit. Uses three-letter ISO acronym. |
17 - Local Currency Amount | Yes | 15 (12.2) | 294 | Numeric | Purchase order amount in PO Header Currency. Only first 15 characters are included. |
18 - Local Currency Code | Yes | 3 | 309 | Alpha | Currency in which purchase order is created (po_headers_all.currency.code)· Uses three-letter ISO acronym. |
19 - Header Miscellaneous | No | 40 | 312 | Alpha | Field not used. |
The following business rules apply to the data elements in the Header Record:
All fields of data type Alpha are left-aligned and padded (if required) on the right with spaces. All field of data type Numeric are right-aligned and padded on the left (if required) with zeroes.
For Field #2 PO Number, only purchase orders with numbers of length equal to or smaller than 15 characters are included in the PO History Feed. If the purchase orders have numbers greater than 15 characters in length, then they are not included in the PO History Feed. In this case, the concurrent request completes with a Complete Phase with Warning Status and generates a warning message in the log file.
For field #3 Card Number, only numeric card numbers of length equal to or smaller than 16 characters are extracted. The card number on the purchase order document is stripped of all special characters or alphabets, which are used as separators (such as a space or '-'.), so that only the numeric portion of the card number for the card is included. If after stripping the non-numeric portion of the card number, the length of the card number is greater than 16 digits, then the associated purchase order is not included in the PO History Feed. In this case, the concurrent request completes with a Complete Phase with Warning Status and generates a warning message in the log file.
The logic to determine the values for field #12 Order Status is as indicated in the next table.
When the purchase order has a control status of Canceled or Finally Closed, only the Header Record is sent with the Order Status field in the Header Record containing the value CN. No Detail Record is sent for that particular purchase order.
Field # 13 Total Lines in PO contains the total number of Detail Records included for the purchase order. Only non-canceled Purchase Order Line Distributions in the purchase order are included as Detail Records for the purchase order.
For purchase orders that are created in a currency different from the functional currency for the operating unit, field #14 PO Amount remains calculated in the functional currency using the applicable conversion rates.
Field #14 PO Amount and field #17 Local Currency Amount do not include the estimated tax in the calculation.
The following table describes the data elements that are the Detail record.
Field | Required | Length | Starting Position | Data Type | Rules |
---|---|---|---|---|---|
1 - Record Type Indicator | Yes | 1 | 1 | Alpha | D (for Detail) |
2 - CM Ref # | Yes | 2 | 2 | Alpha | AX |
PO Number | Yes | 15 | 4 | Alpha | Only purchase orders with numbers up to 15 characters in length are sent. Purchase orders with numbers greater than 15 characters in length are not included and a warning is generated. |
3 - PO Line Number | Yes | 4 | 19 | Numeric | Contains the PO Line Distribution Number. Since canceled distributions on the purchase order are not included as Detail Records, only non-canceled distributions are accounted for. If purchase order has a control status of Canceled or Finally Closed, no distributions are included as detail records, and this value be 0. For more information, see the business rules. |
4 - Quantity | No | 10 (7.2) | 23 | Numeric | Quantity of items ordered. Only first 10 characters are included. |
5 - Unit of Measure | No | 25 | 33 | Alpha | Unit of Measure for ordered items (po_lines.unit_meas_lookup_code). Only first 25 characters are included. |
6 - Unit Price | No | 10 | 58 | Numeric | Price for each item ordered. Only first 10 characters are included. For more information, see the business rules. |
7 - Item Description | No | 40 | 68 | Alpha | Description of item ordered (mtl_system_items_kfv.description). Only first 40 characters are included. |
8 - Accounting Code | Yes | 180 | 108 | Alpha | Charge Account for distribution (po_distributions.code_combination_id). A semicolon separates each accounting segment. Only first 180 characters are included. Semicolons are included in the field length count. |
9 - Client Inventory Number | No | 40 | 288 | Alpha | Client Inventory Number (mtl_system_items_kfv.item_number). In case of non-catalog requests, field is blank. Only first 40 characters are included. |
10 - UN/SPSC Code | No | 16 | 328 | Alpha | Field not used |
11 - Receipt Indicator | No | 1 | 344 | Alpha | Field not used |
12 - Line Detail Miscellaneous | No | 40 | 345 | Alpha | Field not used |
13 - Credit/Debit Indicator | Yes | 1 | 385 | Alpha | D for Debit, C for Credit. For more information, see the business rules. |
14 - Vendor Part Number | No | 50 | 386 | Alpha | Field Not used |
The following rules apply to the data elements in the detail record:
All fields of data type Alpha are left-aligned and padded (if required) on the right with spaces. All field of data type Numeric are right-aligned and padded on the left (if required) with zeroes.
For field #3 PO Line Number, the field refers to the PO Line Distribution Number from the purchase order (and the not the PO Line Number as the name suggests). If the distributions are in the status Canceled or Finally Closed, the distributions are not included as detail records.
For Field #6 Unit Price, the amount is calculated in the functional currency of the operating unit. The calculation does not include the estimated tax.
Note: For fixed-price temporary labor, rate-based temporary labor, and fixed-price services lines, the #4 Quantity field contains the Amount from these lines. The #6 Unit Price field is set to 1. Temporary labor and fixed price services lines are used only if you have licensed and implemented Oracle Services Procurement.
Field #13 Credit/Debit Indicator is either a Debit (D = +) or a Credit (C = -) depending on the value of Quantity multiplied by Unit Price.
CCreation Date: 20020416Total File Amount: 000000000480.00DTotal Header: 00002Total Detail: 00007Start Date: 20020416End Date: 20020416
HAX4426-15 478904789047890 Andrew Smith Andrew Smith 0000005841PEPC TEST1 Supplier-SITE 1 000000000000000000000000001466000000000000000000000245020020416ON0002000000000060.00DUSL000000000060.00USL
DAX4426-15 00010000001.00Each 0000010.00SL Test Expense Item 1 01;510;7530;0000;000 SL Test Expense Item 1 D
DAX4426-15 00020000001.00Each 0000010.00SL Test Expense Item 1 01;740;7330;0000;000 SL Test Expense Item 1 D
HAX4680 849748497484974 SPC TEST1 Supplier 0000000000OSPC TEST1 Supplier-SITE 1 000000000000000000000000001511000000000000000000000249620020416ON0005000000000420.00DUSL000000000420.00USL
DAX4680 00010000001.00Each 0000040.00SL Test Expense Item 4 01;510;7530;0000;000 SL Test Expense Item 4 D
DAX4680 00020000001.00Each 0000040.00SL Test Expense Item 4 01;510;7530;0000;000 SL Test Expense Item 4 D
DAX4680 00030000001.00Each 0000050.00SL Test Expense Item 5 01;510;7530;0000;000 SL Test Expense Item 5 D
DAX4680 00040000001.00Each 0000050.00SL Test Expense Item 5 01;510;7530;0000;000 SL Test Expense Item 5 D
DAX4680 00050000001.00Each 0000050.00SL Test Expense Item 5 01;520;5360;0000;000 SL Test Expense Item 5 D