SET SCHEMA XXX_5DTA;
CREATE OR REPLACE VIEW K_POLISTER AS
(SELECT 'O' AS TY, ID_COMP AS COMP, ID_PO# AS PO#, ID_PROD AS PROD, ID_LOCN AS LOCN FROM K_INTORDD
UNION
SELECT 'L' AS TY, PL_COMP AS COMP, PL_PO# AS PO#, PL_PROD AS PROD, PL_LOCN AS LOCN FROM K_PRODLTM
UNION
SELECT 'A' AS TY, PJ_COMP AS COMP, PJ_PO# AS PO#, PJ_PROD AS PROD, PJ_LOCN AS LOCN FROM K_PRODAPP
INNER JOIN K_INTORDD on PJ_PO# = ID_PO# and PJ_LOCN = ID_LOCN and PJ_PROD = ID_PROD);
This view is used in the Purchase Orders Log to review what products were ordered, how much was received, and what changes were made to the order after approval (products added on, order quantities changed, products cancelled.
File K_INTORDD is the source of truth for the product order quantity (ID_SOQACT); it is populated at the time of PO approval.
File K_PRODAPP is populated in the night job; it records what the system suggested for order (PQ_SOQORIG) as well as what was actually ordered (PQ_SOQACT). In theory ID_SOQACT and PQ_SOQACT will match; but using ID_SOQACT as the product order quantity is preferred.
File K_PRODLTM data is sent from the customer. It tells us how much of the prouct was receieved as well as if there was a change to the order quantity. The order quantity at the time of receipt is captured in PL_QTYORDR.
Some customers also use K_PRODLTM to tell us if a product is cancelled from an order (PL_USEFLAG = 7, PL_SKIPLT = 1, PL_QTYRECV = 0, PL_QTYORDR = order quantity) but this is not required.
