Standard Purchase Order Mode – Using the Extension Package
The Extension Package is called once per Header.
Extension package ‘validation errors’ encountered in any section of the Purchase Order will result in the entire record (Header, Children, Grandchildren…) being rejected. For example, just one distribution error will reject the entire Purchase Order.
All messages from the extension package are returned to the HEADER SECTION’s message column. Messages CAN NOT be returned to any of the child section’s message columns. However, you can include row numbers and section names in your message. Eg Extension Error: Forbidden Charge Account Distributions: Rows, 15,16
Procedure m4aps_pow_extension.stdpo
The procedure stdpo provides access to the uploaded data. Modify this procedure to suit your requirements.
The uploaded data parameters match the structure of the purchase order open interface tables:
· po_headers_interface
· po_lines_interface
· po_price_diff_interface
· po_distributions_interface
Note: Both the Lines and the Shipments are loaded into the po_lines_interface table. This means that line data is duplicated across shipments for the same line.
Parameters
|
Name
|
Mode
|
Type
|
Description
|
|
l_po_header
|
IN OUT
|
po_headers_interface%ROWTYPE
|
header record data to be inserted into po_headers_interface table
|
|
t_line_row
|
IN
|
m4aps_powizard.po_lines_row_tab
|
t_line_row(x) indicates the spreadsheet row for that shipment.
t_line_row and p_po_lines share the same binary index values. t_line_row holds a reference to the row in the wizard the shipment belongs to.
|
|
p_po_lines
|
IN OUT
|
m4aps_powizard.po_lines_tab
|
table of line/shipment data to be inserted into po_lines_interface table
|
|
t_pdif_row
|
IN
|
m4aps_powizard.po_price_diff_row_tab
|
t_pdif_row(x).l_line links to line record
t_pdif_row(x).l_row indicates the spreadsheet row for that price break
t_pdif_row and p_po_pdifs share the same binary index values. t_pdif_row(x).l_row holds a reference to the row in the wizard the price break belongs to.
t_pdif_row(x).l_line holds the binary index value for the price break’s associated line in the p_po_lines table. *Due to the denormalised nature of the p_po_lines table, there are some special considerations when generating line messages related to price breaks. See Price Break Peculiarities section.
|
|
p_po_pdifs
|
IN OUT
|
m4aps_powizard.po_price_diff_tab
|
table of price diff data to be inserted into po_price_diff_interface table
|
|
t_dist_row
|
IN
|
m4aps_powizard.po_distributions_row_tab
|
t_dist_row(x).l_line links to shipment record
t_dist_row(x).l_row indicates the spreadsheet row for that dist
t_dist_row and p_po_dists share the same binary index values. t_dist_row(x).l_row holds a reference to the row in the wizard the distribution belongs to.
t_dist_row(x).l_line holds the binary index value for the distributions associated shipment in the p_po_lines table.
|
|
p_po_dists
|
IN OUT
|
m4aps_powizard.po_distributions_tab
|
table of distribution data to be inserted into po_distributions_interface table
|
|
x_status
|
OUT
|
VARCHAR2
|
set to 'E' to error the requisition and stop it from being inserted into the interface table
|
|
x_message
|
OUT
|
VARCHAR2
|
Used to return a message to the wizard. Messages are returned to the header record in the wizard.
|
Relevant m4aps_powizard objects:
|
Name
|
Definition
|
|
po_lines_row_tab
|
TYPE po_lines_row_rec IS RECORD (
l_row NUMBER, --sheet row of data
l_id NUMBER –interface_id after insert, required for child inserts
);
TYPE po_lines_row_tab IS
TABLE OF po_lines_row_rec INDEX BY BINARY_INTEGER;
|
|
po_lines_tab
|
TYPE po_lines_tab IS
TABLE OF po_lines_interface%ROWTYPE;
|
|
po_price_diff_row_tab
|
TYPE po_price_diff_row_rec IS RECORD (
l_row NUMBER, --sheet row of data
p_count NUMBER –po_lines_interface_row_tab count of parent record
);
TYPE po_price_diff_row_tab IS
TABLE OF po_price_diff_row_rec INDEX BY BINARY_INTEGER;
|
|
po_price_diff_tab
|
TYPE po_price_diff_tab IS
TABLE OF po_price_diff_interface%ROWTYPE;
|
|
po_distributions_row_tab
|
TYPE po_distributions_row_rec IS RECORD (
l_row NUMBER, --sheet row of data
p_count NUMBER –po_lines_interface_row_tab count of parent record
);
TYPE po_distributions_row_tab IS
TABLE OF po_distributions_row_rec INDEX BY BINARY_INTEGER;
|
|
po_distributions_tab
|
TYPE po_distributions_tab IS
TABLE OF po_distributions_interface%ROWTYPE;
|
Using the Extension Package for Additional Validation
This example:
· Rejects a Purchase Order if any of its distributions have a NULL value for ATTRIBUTE1.
· Example return message in the Wizard for this code - Extension Error: Distribution Attribute1 is required. Row 13,14,15,16
Using the Extension Package to Update a Line Value
Because both the lines and shipments are loaded into the po_lines_interface table. The lines are stored in a de-normalised form. Line values are repeated for all its corresponding shipments. Any updates to line values must be applied to all records in the table for that line. If you do not update all lines, the update may not be reflected in the imported purchase order, or the import may fail.
Processing Distributions for Each Shipment
This example processes distributions for each line/shipment. Similar code would be used to update Price Breaks for each line.
Business Rule: If a line has a line type of ‘Goods’ return a message back to the wizard when a distribution does not have a value for Distribution Attribute1. This message includes the row number of the line and the distributions. This message is printed to the header section in the Wizard.
Example Message:
Extension Error: Distributions Attribute1 requires a value for Lines with a type of Goods: Shipment Row 14 – Dist Row 14, Shipment Row 15 – Dist Row 15, Shipment Row 16 – Dist Row 16, Shipment Row 16 – Dist Row 17
Price Break Peculiarities
The Price Breaks index is linked to the last combination of Line and Shipment for each individual line. This is because Price Breaks can't be loaded until Lines are loaded first. Since the po_lines table is denormalized (it stores data for both lines and shipments), Lines need to be loaded along with their corresponding Shipments.
When there are errors in price breaks, if including Line information in your message is required, it's easier to report the Line Number in your message instead of the Line’s row number in the spreadsheet.
Recommended messages:
· Line 1: Price Break 3
· Line 1: Price Break row 16
Rather than message: Line row 14: Price Break row 16
Refer to section:
