VIEW: APPS.CST_AP_VARIANCE_V

Source

View Text - Preformatted

SELECT cavh.batch_id batch_id, cavb.description, cavb.adjustment_account adjustment_account, cavl.project_id project_id, ppa.segment1 project_number, ood.organization_code organization_code, ood.organization_code organization_name, ccg.cost_group cost_group, cavh.variance_header_id variance_header_id, cavl.variance_line_id variance_line_id, cavh.transaction_date orig_transaction_date, mti.transaction_date transaction_date, cql.layer_quantity current_quantity, cql.item_cost current_item_cost, mtcdi.value_change value_change, (mtcdi.value_change/(cql.layer_quantity * cql.item_cost) )*100 pcnt_value_change, pha.segment1 po_number, pla.line_num po_line_num, aia.invoice_num invoice_num, cavl.distribution_line_number invoice_dist_line_num, pda.distribution_num po_distribution_num, cavh.var_amount header_var_amount, cavl.var_amount line_var_amount, cavh.organization_id organization_id, cavh.inventory_item_id inventory_item_id, cavh.po_distribution_id po_distribution_id, pda.accrual_account_id FROM cst_ap_variance_headers cavh, cst_ap_variance_lines cavl, cst_ap_variance_batches cavb, cst_quantity_layers cql, po_distributions pda, po_line_locations_all plla, po_lines_all pla, po_headers_all pha, pa_projects_all ppa, cst_cost_groups ccg, org_organization_definitions ood, mtl_transactions_interface mti, mtl_txn_cost_det_interface mtcdi, ap_invoices_all aia WHERE cavl.variance_header_id = cavh.variance_header_id AND cavb.batch_id = cavh.batch_id AND cql.inventory_item_id = cavh.inventory_item_id AND cql.organization_id = cavh.organization_id AND cql.cost_group_id = cavh.cost_group_id AND pda.po_distribution_id = cavh.po_distribution_id AND plla.line_location_id = pda.line_location_id AND pla.po_line_id = plla.po_line_id AND pha.po_header_id = plla.po_header_id AND ppa.project_id (+) = cavl.project_id AND ccg.cost_group_id = cavh.cost_group_id AND ood.organization_id = cavh.organization_id AND mti.source_code = 'VARIANCE TRF' AND mti.source_header_id = cavh.batch_id AND mti.source_line_id = cavh.variance_header_id AND mtcdi.transaction_interface_id = mti.transaction_interface_id AND mtcdi.level_type = 1 AND mtcdi.cost_element_id = 1 AND aia.invoice_id = cavl.invoice_id

View Text - HTML Formatted

SELECT CAVH.BATCH_ID BATCH_ID
, CAVB.DESCRIPTION
, CAVB.ADJUSTMENT_ACCOUNT ADJUSTMENT_ACCOUNT
, CAVL.PROJECT_ID PROJECT_ID
, PPA.SEGMENT1 PROJECT_NUMBER
, OOD.ORGANIZATION_CODE ORGANIZATION_CODE
, OOD.ORGANIZATION_CODE ORGANIZATION_NAME
, CCG.COST_GROUP COST_GROUP
, CAVH.VARIANCE_HEADER_ID VARIANCE_HEADER_ID
, CAVL.VARIANCE_LINE_ID VARIANCE_LINE_ID
, CAVH.TRANSACTION_DATE ORIG_TRANSACTION_DATE
, MTI.TRANSACTION_DATE TRANSACTION_DATE
, CQL.LAYER_QUANTITY CURRENT_QUANTITY
, CQL.ITEM_COST CURRENT_ITEM_COST
, MTCDI.VALUE_CHANGE VALUE_CHANGE
, (MTCDI.VALUE_CHANGE/(CQL.LAYER_QUANTITY * CQL.ITEM_COST) )*100 PCNT_VALUE_CHANGE
, PHA.SEGMENT1 PO_NUMBER
, PLA.LINE_NUM PO_LINE_NUM
, AIA.INVOICE_NUM INVOICE_NUM
, CAVL.DISTRIBUTION_LINE_NUMBER INVOICE_DIST_LINE_NUM
, PDA.DISTRIBUTION_NUM PO_DISTRIBUTION_NUM
, CAVH.VAR_AMOUNT HEADER_VAR_AMOUNT
, CAVL.VAR_AMOUNT LINE_VAR_AMOUNT
, CAVH.ORGANIZATION_ID ORGANIZATION_ID
, CAVH.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CAVH.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, PDA.ACCRUAL_ACCOUNT_ID
FROM CST_AP_VARIANCE_HEADERS CAVH
, CST_AP_VARIANCE_LINES CAVL
, CST_AP_VARIANCE_BATCHES CAVB
, CST_QUANTITY_LAYERS CQL
, PO_DISTRIBUTIONS PDA
, PO_LINE_LOCATIONS_ALL PLLA
, PO_LINES_ALL PLA
, PO_HEADERS_ALL PHA
, PA_PROJECTS_ALL PPA
, CST_COST_GROUPS CCG
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_TRANSACTIONS_INTERFACE MTI
, MTL_TXN_COST_DET_INTERFACE MTCDI
, AP_INVOICES_ALL AIA
WHERE CAVL.VARIANCE_HEADER_ID = CAVH.VARIANCE_HEADER_ID
AND CAVB.BATCH_ID = CAVH.BATCH_ID
AND CQL.INVENTORY_ITEM_ID = CAVH.INVENTORY_ITEM_ID
AND CQL.ORGANIZATION_ID = CAVH.ORGANIZATION_ID
AND CQL.COST_GROUP_ID = CAVH.COST_GROUP_ID
AND PDA.PO_DISTRIBUTION_ID = CAVH.PO_DISTRIBUTION_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PPA.PROJECT_ID (+) = CAVL.PROJECT_ID
AND CCG.COST_GROUP_ID = CAVH.COST_GROUP_ID
AND OOD.ORGANIZATION_ID = CAVH.ORGANIZATION_ID
AND MTI.SOURCE_CODE = 'VARIANCE TRF'
AND MTI.SOURCE_HEADER_ID = CAVH.BATCH_ID
AND MTI.SOURCE_LINE_ID = CAVH.VARIANCE_HEADER_ID
AND MTCDI.TRANSACTION_INTERFACE_ID = MTI.TRANSACTION_INTERFACE_ID
AND MTCDI.LEVEL_TYPE = 1
AND MTCDI.COST_ELEMENT_ID = 1
AND AIA.INVOICE_ID = CAVL.INVOICE_ID