CST_OPEN_PO_AMOUNTS_OTBI

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
COST_ORG_ID COST_BOOK_ID VAL_UNIT_ID PO_HEADER_ID PO_LINE_ID PO_DISTRIBUTION_ID PO_NUMBER TYPE_LOOKUP_CODE DOCUMENT_STATUS CREATION_DATE RATE_TYPE RATE_DATE RATE APPROVED_FLAG APPROVED_DATE VENDOR_ORDER_NUM COMMENTS UNIT_PRICE LINE_NUM QUANTITY VENDOR_ID VENDOR_SITE_ID PO_FUNCTIONAL_CURRENCY COST_CURRENCY_CODE PO_CURRENCY_CODE ITEM_ID PO_UOM_CODE DESTINATION_ORGANIZATION_ID DISTRIBUTION_NUM QUANTITY_ORDERED QUANTITY_DELIVERED QUANTITY_CANCELLED QUANTITY_OPEN_PO_UOM QUANTITY_OPEN_COST_UOM OPEN_PO_AMOUNT_PO_CURR OPEN_PO_AMOUNT_COST_CURR_UOM

Name

COST_ORG_ID

COST_BOOK_ID

VAL_UNIT_ID

PO_HEADER_ID

PO_LINE_ID

PO_DISTRIBUTION_ID

PO_NUMBER

TYPE_LOOKUP_CODE

DOCUMENT_STATUS

CREATION_DATE

RATE_TYPE

RATE_DATE

RATE

APPROVED_FLAG

APPROVED_DATE

VENDOR_ORDER_NUM

COMMENTS

UNIT_PRICE

LINE_NUM

QUANTITY

VENDOR_ID

VENDOR_SITE_ID

PO_FUNCTIONAL_CURRENCY

COST_CURRENCY_CODE

PO_CURRENCY_CODE

ITEM_ID

PO_UOM_CODE

DESTINATION_ORGANIZATION_ID

DISTRIBUTION_NUM

QUANTITY_ORDERED

QUANTITY_DELIVERED

QUANTITY_CANCELLED

QUANTITY_OPEN_PO_UOM

QUANTITY_OPEN_COST_UOM

OPEN_PO_AMOUNT_PO_CURR

OPEN_PO_AMOUNT_COST_CURR_UOM

Query

SQL_Statement
WITH IC AS (SELECT DISTINCT COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, LAST_VALUE(UOM_CODE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID ORDER BY EFFECTIVE_START_DATE DESC) UOM_CODE, LAST_VALUE(CURRENCY_CODE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID ORDER BY EFFECTIVE_START_DATE DESC) CURRENCY_CODE FROM CST_STD_COSTS WHERE CST_STD_COSTS.EFFECTIVE_END_DATE > SYSDATE ), CO AS (SELECT COST_ORG_CODE, COST_ORG_NAME, COST_ORG_ID FROM CST_COST_ORGS_V ) SELECT IC.COST_ORG_ID, IC.COST_BOOK_ID, VUD.VAL_UNIT_ID, PO.PO_HEADER_ID, POL.PO_LINE_ID, POD.PO_DISTRIBUTION_ID, PO.SEGMENT1 PO_NUMBER, PO.TYPE_LOOKUP_CODE, PO.DOCUMENT_STATUS, PO.CREATION_DATE, PO.RATE_TYPE, PO.RATE_DATE, PO.RATE, PO.APPROVED_FLAG, PO.APPROVED_DATE, PO.VENDOR_ORDER_NUM, PO.COMMENTS, POL.UNIT_PRICE, POL.LINE_NUM, POL.QUANTITY, PO.VENDOR_ID, PO.VENDOR_SITE_ID, GL.CURRENCY_CODE PO_FUNCTIONAL_CURRENCY, IC.CURRENCY_CODE COST_CURRENCY_CODE, PO.CURRENCY_CODE PO_CURRENCY_CODE, POL.ITEM_ID, POL.UOM_CODE PO_UOM_CODE, POD.DESTINATION_ORGANIZATION_ID, POD.DISTRIBUTION_NUM, SUM(POD.QUANTITY_ORDERED) QUANTITY_ORDERED, SUM(POD.QUANTITY_DELIVERED) QUANTITY_DELIVERED, SUM(POD.QUANTITY_CANCELLED) QUANTITY_CANCELLED, SUM(POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED)) QUANTITY_OPEN_PO_UOM, SUM((POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED)) * CASE WHEN POL.UOM_CODE = IC.UOM_CODE THEN 1 ELSE INV_CONVERT.INV_UM_CONVERT (POL.ITEM_ID,NULL,1,POL.UOM_CODE,IC.UOM_CODE,NULL,NULL) END ) QUANTITY_OPEN_COST_UOM, SUM((POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED)) * POL.UNIT_PRICE * NVL(POD.RATE, 1)) OPEN_PO_AMOUNT_PO_CURR, SUM(((POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED)) * POL.UNIT_PRICE * NVL(POD.RATE, 1)) * CASE WHEN GL.CURRENCY_CODE = IC.CURRENCY_CODE THEN 1 ELSE gl_currency_api.get_closest_rate_sql(GL.CURRENCY_CODE, IC.CURRENCY_CODE, trunc(sysdate), cob.conversion_type, 365) END ) OPEN_PO_AMOUNT_COST_CURR_UOM FROM PO_HEADERS_ALL PO, PO_LINES_ALL POL, PO_DISTRIBUTIONS_ALL POD, GL_LEDGERS GL, CST_COST_INV_ORGS CIO, CST_COST_ORG_BOOKS COB, CST_VAL_UNIT_DETAILS VUD , CST_VAL_UNIT_COMBINATIONS VUC, CST_VAL_UNITS_B VUB , CST_VAL_STRUCTURES_B VSB, INV_ORG_PARAMETERS IOP, IC, CO WHERE PO.DOCUMENT_STATUS = 'OPEN' AND PO.TYPE_LOOKUP_CODE = 'STANDARD' AND PO.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.LINE_STATUS = 'OPEN' AND POL.PO_LINE_ID = POD.PO_LINE_ID AND POL.ITEM_ID IS NOT NULL AND POL.PO_HEADER_ID = POD.PO_HEADER_ID AND POD.SET_OF_BOOKS_ID = GL.LEDGER_ID (+) AND POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED) > 0 AND POD.DESTINATION_ORGANIZATION_ID = CIO.INV_ORG_ID AND IC.COST_ORG_ID = COB.COST_ORG_ID AND IC.COST_BOOK_ID = COB.COST_BOOK_ID AND CIO.COST_ORG_ID = IC.COST_ORG_ID AND IC.INVENTORY_ITEM_ID = POL.ITEM_ID AND CO.COST_ORG_ID = CIO.COST_ORG_ID AND IOP.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID AND VUC.COST_ORG_CODE = CO.COST_ORG_CODE AND NVL(VUC.INV_ORG_CODE,IOP.ORGANIZATION_CODE) = IOP.ORGANIZATION_CODE AND VUC.SUBINVENTORY_CODE IS NULL AND VUC.LOCATOR_ID IS NULL AND VUC.LOT_NUMBER IS NULL AND VUC.SERIAL_NUMBER IS NULL AND VUC.GRADE_CODE IS NULL AND VUD.VAL_UNIT_COMBINATION_ID = VUC.VAL_UNIT_COMBINATION_ID AND VUB.VAL_UNIT_ID = VUD.VAL_UNIT_ID AND VUB.COST_ORG_ID = COB.COST_ORG_ID AND VUB.COST_BOOK_ID = COB.COST_BOOK_ID AND VUB.VAL_STRUCTURE_ID = VSB.VAL_STRUCTURE_ID AND VSB.VAL_STRUCTURE_TYPE_CODE = 'ASSET' GROUP BY IC.COST_ORG_ID, IC.COST_BOOK_ID, VUD.VAL_UNIT_ID, cob.conversion_type, PO.PO_HEADER_ID, POL.PO_LINE_ID, POD.PO_DISTRIBUTION_ID, PO.SEGMENT1, PO.TYPE_LOOKUP_CODE, PO.DOCUMENT_STATUS, PO.CREATION_DATE, PO.RATE_TYPE, PO.RATE_DATE, PO.RATE, PO.APPROVED_FLAG, PO.APPROVED_DATE, PO.VENDOR_ORDER_NUM, PO.COMMENTS, POL.UNIT_PRICE, POL.LINE_NUM, POL.QUANTITY, PO.VENDOR_ID, PO.VENDOR_SITE_ID, GL.CURRENCY_CODE, IC.CURRENCY_CODE, PO.CURRENCY_CODE, POL.ITEM_ID, POL.UOM_CODE, POD.DESTINATION_ORGANIZATION_ID, DISTRIBUTION_NUM

SQL_Statement

WITH IC AS

(SELECT DISTINCT COST_ORG_ID,

COST_BOOK_ID,

INVENTORY_ITEM_ID,

LAST_VALUE(UOM_CODE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID ORDER BY EFFECTIVE_START_DATE DESC) UOM_CODE,

LAST_VALUE(CURRENCY_CODE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID ORDER BY EFFECTIVE_START_DATE DESC) CURRENCY_CODE

FROM CST_STD_COSTS

WHERE CST_STD_COSTS.EFFECTIVE_END_DATE > SYSDATE

),

CO AS

(SELECT COST_ORG_CODE, COST_ORG_NAME, COST_ORG_ID

FROM CST_COST_ORGS_V

)

SELECT

IC.COST_ORG_ID,

IC.COST_BOOK_ID,

VUD.VAL_UNIT_ID,

PO.PO_HEADER_ID,

POL.PO_LINE_ID,

POD.PO_DISTRIBUTION_ID,

PO.SEGMENT1 PO_NUMBER,

PO.TYPE_LOOKUP_CODE,

PO.DOCUMENT_STATUS,

PO.CREATION_DATE,

PO.RATE_TYPE,

PO.RATE_DATE,

PO.RATE,

PO.APPROVED_FLAG,

PO.APPROVED_DATE,

PO.VENDOR_ORDER_NUM,

PO.COMMENTS,

POL.UNIT_PRICE,

POL.LINE_NUM,

POL.QUANTITY,

PO.VENDOR_ID,

PO.VENDOR_SITE_ID,

GL.CURRENCY_CODE PO_FUNCTIONAL_CURRENCY,

IC.CURRENCY_CODE COST_CURRENCY_CODE,

PO.CURRENCY_CODE PO_CURRENCY_CODE,

POL.ITEM_ID,

POL.UOM_CODE PO_UOM_CODE,

POD.DESTINATION_ORGANIZATION_ID,

POD.DISTRIBUTION_NUM,

SUM(POD.QUANTITY_ORDERED) QUANTITY_ORDERED,

SUM(POD.QUANTITY_DELIVERED) QUANTITY_DELIVERED,

SUM(POD.QUANTITY_CANCELLED) QUANTITY_CANCELLED,

SUM(POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED)) QUANTITY_OPEN_PO_UOM,

SUM((POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED))

*

CASE

WHEN POL.UOM_CODE = IC.UOM_CODE THEN 1

ELSE INV_CONVERT.INV_UM_CONVERT (POL.ITEM_ID,NULL,1,POL.UOM_CODE,IC.UOM_CODE,NULL,NULL)

END

) QUANTITY_OPEN_COST_UOM,

SUM((POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED)) * POL.UNIT_PRICE * NVL(POD.RATE, 1)) OPEN_PO_AMOUNT_PO_CURR,

SUM(((POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED)) * POL.UNIT_PRICE * NVL(POD.RATE, 1))

*

CASE

WHEN GL.CURRENCY_CODE = IC.CURRENCY_CODE THEN 1

ELSE gl_currency_api.get_closest_rate_sql(GL.CURRENCY_CODE, IC.CURRENCY_CODE, trunc(sysdate), cob.conversion_type, 365)

END

)

OPEN_PO_AMOUNT_COST_CURR_UOM

FROM PO_HEADERS_ALL PO,

PO_LINES_ALL POL,

PO_DISTRIBUTIONS_ALL POD,

GL_LEDGERS GL,

CST_COST_INV_ORGS CIO,

CST_COST_ORG_BOOKS COB,

CST_VAL_UNIT_DETAILS VUD ,

CST_VAL_UNIT_COMBINATIONS VUC,

CST_VAL_UNITS_B VUB ,

CST_VAL_STRUCTURES_B VSB,

INV_ORG_PARAMETERS IOP,

IC,

CO

WHERE PO.DOCUMENT_STATUS = 'OPEN'

AND PO.TYPE_LOOKUP_CODE = 'STANDARD'

AND PO.PO_HEADER_ID = POL.PO_HEADER_ID

AND POL.LINE_STATUS = 'OPEN'

AND POL.PO_LINE_ID = POD.PO_LINE_ID

AND POL.ITEM_ID IS NOT NULL

AND POL.PO_HEADER_ID = POD.PO_HEADER_ID

AND POD.SET_OF_BOOKS_ID = GL.LEDGER_ID (+)

AND POD.QUANTITY_ORDERED - (POD.QUANTITY_DELIVERED + POD.QUANTITY_CANCELLED) > 0

AND POD.DESTINATION_ORGANIZATION_ID = CIO.INV_ORG_ID

AND IC.COST_ORG_ID = COB.COST_ORG_ID

AND IC.COST_BOOK_ID = COB.COST_BOOK_ID

AND CIO.COST_ORG_ID = IC.COST_ORG_ID

AND IC.INVENTORY_ITEM_ID = POL.ITEM_ID

AND CO.COST_ORG_ID = CIO.COST_ORG_ID

AND IOP.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID

AND VUC.COST_ORG_CODE = CO.COST_ORG_CODE

AND NVL(VUC.INV_ORG_CODE,IOP.ORGANIZATION_CODE) = IOP.ORGANIZATION_CODE

AND VUC.SUBINVENTORY_CODE IS NULL

AND VUC.LOCATOR_ID IS NULL

AND VUC.LOT_NUMBER IS NULL

AND VUC.SERIAL_NUMBER IS NULL

AND VUC.GRADE_CODE IS NULL

AND VUD.VAL_UNIT_COMBINATION_ID = VUC.VAL_UNIT_COMBINATION_ID

AND VUB.VAL_UNIT_ID = VUD.VAL_UNIT_ID

AND VUB.COST_ORG_ID = COB.COST_ORG_ID

AND VUB.COST_BOOK_ID = COB.COST_BOOK_ID

AND VUB.VAL_STRUCTURE_ID = VSB.VAL_STRUCTURE_ID

AND VSB.VAL_STRUCTURE_TYPE_CODE = 'ASSET'

GROUP BY IC.COST_ORG_ID,