Business Intelligence (BI): 2016

Tuesday, 27 December 2016

Oracle AP Invoice Distributions Query

Oracle AP Invoice Distributions Query

/* Formatted on 12/28/2016 8:53:03 AM (QP5 v5.126.903.23003) */
SELECT AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,
AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY,
AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE,
AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID,
AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY,
AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE,
AP_INVOICES_ALL.INVOICE_ID,
AP_INVOICES_ALL.INVOICE_NUM,
AP_INVOICES_ALL.INVOICE_CURRENCY_CODE,
AP_INVOICES_ALL.VENDOR_ID,
AP_INVOICES_ALL.VENDOR_SITE_ID,
AP_INVOICES_ALL.INVOICE_DATE,
AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,
AP_INVOICES_ALL.INVOICE_RECEIVED_DATE,
AP_INVOICES_ALL.CREATION_DATE,
AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,
PO_HEADERS_ALL.SEGMENT1,
PO_LINES_ALL.LINE_NUM,
PO_HEADERS_ALL.CREATION_DATE,
AP_INVOICES_ALL.LAST_UPDATE_DATE,
PO_HEADERS_ALL.LAST_UPDATE_DATE,
PO_LINES_ALL.LAST_UPDATE_DATE,
PO_LINES_ALL.ITEM_DESCRIPTION,
AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION,
AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,
AP_INVOICES_ALL.WFAPPROVAL_STATUS,
AP_INVOICES_ALL.PAYMENT_STATUS_FLAG,
AP_INVOICES_ALL.CANCELLED_DATE,
AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE,
PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID,
AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID,
AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG,
AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID,
AP_INVOICES_ALL.TERMS_ID,
AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE,
AP_INVOICES_ALL.EXCHANGE_DATE,
AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE,
AP_INVOICES_ALL.EXCHANGE_RATE_TYPE,
AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE,
AP_INVOICES_ALL.EXCHANGE_RATE,
AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID,
AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID,
AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID,
AP_INVOICES_ALL.SET_OF_BOOKS_ID,
AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED,
AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT,
AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT,
CASE
WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL
THEN
PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE
ELSE
AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE
END,
AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE,
AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE,
PO_VENDORS.RECEIPT_REQUIRED_FLAG,
PO_VENDORS.INSPECTION_REQUIRED_FLAG,
PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG,
PO_VENDORS.LAST_UPDATE_DATE,
AP_INVOICES_ALL.SOURCE,
AP_INVOICES_ALL.PROJECT_ID,
AP_INVOICES_ALL.TASK_ID,
AP_INVOICES_ALL.EXPENDITURE_TYPE,
AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID,
AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE,
AP_INVOICES_ALL.PAYMENT_CROSS_RATE,
AP_INVOICES_ALL.REQUESTER_ID,
AP_INVOICE_LINES_ALL.LINE_NUMBER,
AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE,
AP_INVOICE_LINES_ALL.REQUESTER_ID,
AP_INVOICE_LINES_ALL.DESCRIPTION,
AP_INVOICES_ALL.LEGAL_ENTITY_ID,
AP_INVOICE_DISTRIBUTIONS_ALL.DIST_MATCH_TYPE
FROM AP_INVOICE_DISTRIBUTIONS_ALL,
AP_INVOICE_LINES_ALL,
AP_INVOICES_ALL,
PO_LINES_ALL,
PO_HEADERS_ALL,
PO_VENDORS,
PO_VENDOR_SITES_ALL,
PO_DISTRIBUTIONS_ALL
WHERE AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID
AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+)
AND AP_INVOICES_ALL.VENDOR_SITE_ID =
PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+)
AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID =
PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+)
AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID =
PO_HEADERS_ALL.PO_HEADER_ID(+)
AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+)
AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+)
AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID =
AP_INVOICE_LINES_ALL.INVOICE_ID
AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER =
AP_INVOICE_LINES_ALL.LINE_NUMBER

Oracle Purchase Receipts Query

Oracle Purchase Receipts Query:

/* Formatted on 12/28/2016 8:49:32 AM (QP5 v5.126.903.23003) */
SELECT RCV_TRANSACTIONS.TRANSACTION_ID,
PO_HEADERS_ALL.SEGMENT1,
RCV_TRANSACTIONS.VENDOR_ID,
RCV_TRANSACTIONS.VENDOR_SITE_ID,
RCV_TRANSACTIONS.CURRENCY_CONVERSION_RATE,
PO_LINE_LOCATIONS_ALL.CREATION_DATE,
RCV_SHIPMENT_LINES.ITEM_ID,
PO_LINES_ALL.UNIT_PRICE,
PO_LINES_ALL.LINE_NUM,
RCV_TRANSACTIONS.ORGANIZATION_ID,
PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM,
PO_LINE_LOCATIONS_ALL.ORG_ID,
RCV_TRANSACTIONS.DELIVER_TO_PERSON_ID,
RCV_TRANSACTIONS.INV_TRANSACTION_ID,
RCV_TRANSACTIONS.TRANSACTION_TYPE,
NVL (RCV_TRANSACTIONS.LOCATION_ID,
PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID)
LOCATION_ID,
RCV_TRANSACTIONS.ORGANIZATION_ID,
RCV_TRANSACTIONS.CREATED_BY,
RCV_TRANSACTIONS.LAST_UPDATED_BY,
DECODE (RCV_TRANSACTIONS.TRANSACTION_TYPE,
'MATCH', PARENT.TRANSACTION_DATE,
RCV_TRANSACTIONS.TRANSACTION_DATE)
TRANSACTION_DATE,
RCV_TRANSACTIONS.CREATION_DATE,
RCV_TRANSACTIONS.LAST_UPDATE_DATE,
RCV_TRANSACTIONS.QUANTITY,
RCV_TRANSACTIONS.UNIT_OF_MEASURE,
RCV_TRANSACTIONS.SOURCE_DOC_UNIT_OF_MEASURE,
CASE
WHEN RCV_TRANSACTIONS.TRANSACTION_TYPE = 'RECEIVE'
THEN
RCV_SHIPMENT_LINES.QUANTITY_RECEIVED
WHEN RCV_TRANSACTIONS.TRANSACTION_TYPE = 'MATCH'
THEN
RCV_SHIPMENT_LINES.QUANTITY_RECEIVED
ELSE
RCV_TRANSACTIONS.SOURCE_DOC_QUANTITY
END
SOURCE_DOC_QUANTITY,
RCV_TRANSACTIONS.PO_UNIT_PRICE,
RCV_TRANSACTIONS.CURRENCY_CODE,
RCV_TRANSACTIONS.INSPECTION_STATUS_CODE,
RCV_TRANSACTIONS.COMMENTS,
RCV_TRANSACTIONS.REASON_ID,
RCV_SHIPMENT_HEADERS.RECEIPT_NUM,
RCV_SHIPMENT_LINES.LINE_NUM,
RCV_TRANSACTIONS.CURRENCY_CONVERSION_DATE,
PO_HEADERS_ALL.AGENT_ID,
CONTRACT.SEGMENT1,
RCV_TRANSACTIONS.PO_LINE_LOCATION_ID,
RCV_TRANSACTIONS.EMPLOYEE_ID,
PO_LINES_ALL.LAST_UPDATE_DATE,
PO_LINE_LOCATIONS_ALL.LAST_UPDATE_DATE,
RCV_SHIPMENT_HEADERS.LAST_UPDATE_DATE,
PO_HEADERS_ALL.LAST_UPDATE_DATE,
PO_LINES_ALL.LINE_TYPE_ID,
RCV_SHIPMENT_LINES.CATEGORY_ID,
RCV_TRANSACTIONS.AMOUNT,
PO_LINE_LOCATIONS_ALL.PAYMENT_TYPE,
PO_LINE_LOCATIONS_ALL.SHIPMENT_TYPE,
CASE
WHEN PO_HEADERS_ALL.TYPE_LOOKUP_CODE = 'BLANKET'
THEN
PO_HEADERS_ALL.SEGMENT1
WHEN BLANKET_AGREEMENT.TYPE_LOOKUP_CODE = 'BLANKET'
THEN
BLANKET_AGREEMENT.SEGMENT1
ELSE
NULL
END,
PO_LINE_LOCATIONS_ALL.CONSIGNED_FLAG,
'0' AS X_CUSTOM,
NVL (PARENT.TRANSACTION_TYPE, 0) PARENT_TRANSACTION_TYPE,
PO_LINE_LOCATIONS_ALL.DAYS_EARLY_RECEIPT_ALLOWED
DAYS_EARLY_RECEIPT_ALLOWED,
PO_LINE_LOCATIONS_ALL.DAYS_LATE_RECEIPT_ALLOWED
DAYS_LATE_RECEIPT_ALLOWED,
PO_LINE_LOCATIONS_ALL.NEED_BY_DATE,
PO_LINE_LOCATIONS_ALL.PROMISED_DATE,
PO_REQUISITION_LINES_ALL.NEED_BY_DATE REQUISITION_NEED_BY_DATE,
RCV_TRANSACTIONS.SOURCE_DOCUMENT_CODE,
PO_RELEASES_ALL.RELEASE_NUM,
RCV_SHIPMENT_LINES.UNIT_OF_MEASURE RCV_UOM
FROM PO_HEADERS_ALL,
PO_LINES_ALL,
PO_RELEASES_ALL,
PO_LINE_LOCATIONS_ALL,
RCV_TRANSACTIONS,
RCV_SHIPMENT_LINES,
RCV_SHIPMENT_HEADERS,
PO_HEADERS_ALL CONTRACT,
PO_HEADERS_ALL BLANKET_AGREEMENT,
PO_REQUISITION_LINES_ALL,
RCV_TRANSACTIONS PARENT
WHERE RCV_TRANSACTIONS.PO_LINE_LOCATION_ID =
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID(+)
AND PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID =
PO_RELEASES_ALL.PO_RELEASE_ID(+)
AND RCV_TRANSACTIONS.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+)
AND RCV_TRANSACTIONS.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+)
AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =
RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID
AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =
RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID
AND RCV_TRANSACTIONS.SHIPMENT_LINE_ID =
RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID
AND PO_LINES_ALL.CONTRACT_ID = CONTRACT.PO_HEADER_ID(+)
AND PO_LINES_ALL.FROM_HEADER_ID = BLANKET_AGREEMENT.PO_HEADER_ID(+)
AND RCV_TRANSACTIONS.REQUISITION_LINE_ID =
PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID(+)
AND RCV_TRANSACTIONS.TRANSACTION_TYPE IN
('RECEIVE', 'RETURN TO VENDOR', 'CORRECT', 'MATCH')
AND RCV_TRANSACTIONS.PARENT_TRANSACTION_ID =
PARENT.TRANSACTION_ID(+)
AND NOT (RCV_SHIPMENT_LINES.PO_HEADER_ID IS NULL
AND RCV_SHIPMENT_LINES.SOURCE_DOCUMENT_CODE = 'PO')

Oracle Purchase Requisition Query

Oracle Purchase Requisition Query :

/* Formatted on 12/28/2016 8:46:36 AM (QP5 v5.126.903.23003) */
SELECT PO_REQUISITION_LINES_ALL.TO_PERSON_ID,
PO_REQUISITION_LINES_ALL.VENDOR_SITE_ID,
PO_REQUISITION_LINES_ALL.ITEM_ID,
PO_REQUISITION_HEADERS_ALL.AUTHORIZATION_STATUS,
PO_REQUISITION_LINES_ALL.CLOSED_CODE,
PO_REQUISITION_HEADERS_ALL.TYPE_LOOKUP_CODE,
PO_REQUISITION_LINES_ALL.DELIVER_TO_LOCATION_ID,
PO_REQUISITION_LINES_ALL.ORG_ID,
PO_REQUISITION_LINES_ALL.CREATED_BY,
PO_REQUISITION_LINES_ALL.LAST_UPDATED_BY,
PO_LINE_LOCATIONS_ALL.CREATION_DATE,
NVL (
PO_LINE_LOCATIONS_ALL.PROMISED_DATE,
NVL (PO_LINE_LOCATIONS_ALL.NEED_BY_DATE,
PO_REQUISITION_LINES_ALL.NEED_BY_DATE)
),
PO_REQUISITION_LINES_ALL.CREATION_DATE,
PO_REQUISITION_LINES_ALL.LAST_UPDATE_DATE,
PO_REQUISITION_LINES_ALL.QUANTITY,
NVL (PO_REQUISITION_LINES_ALL.CURRENCY_UNIT_PRICE,
PO_REQUISITION_LINES_ALL.UNIT_PRICE),
PO_REQUISITION_LINES_ALL.UNIT_MEAS_LOOKUP_CODE,
NVL (PO_REQUISITION_LINES_ALL.CURRENCY_CODE, GSB.CURRENCY_CODE)
AS PR_DOC_CURR,
PO_REQUISITION_LINES_ALL.RATE,
PO_REQUISITION_HEADERS_ALL.SEGMENT1,
PO_REQUISITION_LINES_ALL.LINE_NUM,
PO_REQUISITION_LINES_ALL.ITEM_DESCRIPTION,
PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID,
PO_REQUISITION_LINES_ALL.DESTINATION_ORGANIZATION_ID,
PR_DATE.LAST_SUBMIT_DATE AS SUBMIT_DATE,
NVL (PO_REQUISITION_HEADERS_ALL.APPROVED_DATE,
PR_DATE.LAST_APPROVED_DATE)
APPROVAL_DATE,
PO_REQUISITION_LINES_ALL.VENDOR_ID,
PO_REQUISITION_LINES_ALL.CANCEL_FLAG,
PO_REQUISITION_HEADERS_ALL.LAST_UPDATE_DATE,
PO_LINE_LOCATIONS_ALL.LAST_UPDATE_DATE,
PO_REQUISITION_LINES_ALL.LINE_TYPE_ID,
PO_REQUISITION_LINES_ALL.CATEGORY_ID,
PO_REQUISITION_LINES_ALL.MODIFIED_BY_AGENT_FLAG,
PO_REQUISITION_LINES_ALL.MATCHING_BASIS,
PO_REQUISITION_LINES_ALL.AMOUNT,
'0' AS X_CUSTOM,
PO_REQUISITION_LINES_ALL.ON_RFQ_FLAG,
PO_REQUISITION_LINES_ALL.AUCTION_HEADER_ID,
PO_REQUISITION_LINES_ALL.REQS_IN_POOL_FLAG,
PO_REQUISITION_LINES_ALL.AT_SOURCING_FLAG,
PO_REQUISITION_LINES_ALL.URGENT_FLAG,
PO_REQUISITION_LINES_ALL.LINE_LOCATION_ID,
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID,
PO_LINE_LOCATIONS_ALL.APPROVED_FLAG,
PO_LINE_LOCATIONS_ALL.RECEIPT_REQUIRED_FLAG,
PO_LINE_LOCATIONS_ALL.MATCHING_BASIS,
PO_LINE_LOCATIONS_ALL.INSPECTION_REQUIRED_FLAG,
PO_LINE_LOCATIONS_ALL.SHIPMENT_CLOSED_DATE,
PO_LINE_LOCATIONS_ALL.CLOSED_FOR_RECEIVING_DATE,
PO_LINE_LOCATIONS_ALL.CLOSED_FOR_INVOICE_DATE,
PO_LINE_LOCATIONS_ALL.CONSIGNED_FLAG,
PO_LINE_LOCATIONS_ALL.VMI_FLAG,
PO_DOC_STYLE_HEADERS.PROGRESS_PAYMENT_FLAG,
PO_REQUISITION_LINES_ALL.QUANTITY_CANCELLED,
DECODE (PO_LINE_LOCATIONS_ALL.APPROVED_FLAG,
'Y', PO_LINE_LOCATIONS_ALL.APPROVED_DATE,
NULL)
PO_APPROVED_ON_DT,
DECODE (PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID,
NULL, PO_HEADERS_ALL.SUBMIT_DATE,
PO_RELEASES_ALL.SUBMIT_DATE)
PO_SUMBIT_ON_DT,
DECODE (PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID,
NULL, PO_HEADERS_ALL.DOCUMENT_CREATION_METHOD,
PO_RELEASES_ALL.DOCUMENT_CREATION_METHOD)
PO_CREATION_METHOD_ID,
DECODE (PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID,
NULL, PO_HEADERS_ALL.REVISION_NUM,
PO_RELEASES_ALL.REVISION_NUM)
PO_REVISIONS,
PO_HEADERS_ALL.RATE_TYPE
FROM PO_REQUISITION_LINES_ALL,
PO_REQUISITION_HEADERS_ALL,
PO_LINE_LOCATIONS_ALL,
( SELECT PO_ACTION_HISTORY.OBJECT_ID,
MAX(CASE
WHEN PO_ACTION_HISTORY.ACTION_CODE = 'APPROVE'
THEN
PO_ACTION_HISTORY.LAST_UPDATE_DATE
END)
LAST_APPROVED_DATE,
MIN(CASE
WHEN PO_ACTION_HISTORY.ACTION_CODE = 'SUBMIT'
THEN
PO_ACTION_HISTORY.LAST_UPDATE_DATE
WHEN PO_ACTION_HISTORY.ACTION_CODE = 'IMPORT'
THEN
PO_ACTION_HISTORY.LAST_UPDATE_DATE
END)
LAST_SUBMIT_DATE
FROM PO_ACTION_HISTORY
WHERE PO_ACTION_HISTORY.OBJECT_TYPE_CODE = 'REQUISITION'
AND PO_ACTION_HISTORY.ACTION_CODE IN
('APPROVE', 'SUBMIT', 'IMPORT')
GROUP BY OBJECT_ID) PR_DATE,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
GL_SETS_OF_BOOKS GSB,
PO_DOC_STYLE_HEADERS,
PO_HEADERS_ALL,
PO_RELEASES_ALL
WHERE PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID =
PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID
AND PO_REQUISITION_LINES_ALL.LINE_LOCATION_ID =
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID(+)
AND PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID =
PR_DATE.OBJECT_ID(+)
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND NVL (FSP.ORG_ID, -99) =
NVL (PO_REQUISITION_LINES_ALL.ORG_ID, -99)
--(PO_REQUISITION_LINES_ALL.CREATION_DATE > TO_DATE('$$INITIAL_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS'))
AND PO_LINE_LOCATIONS_ALL.PO_HEADER_ID =
PO_HEADERS_ALL.PO_HEADER_ID(+)
AND PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID =
PO_RELEASES_ALL.PO_RELEASE_ID(+)
AND PO_HEADERS_ALL.STYLE_ID = PO_DOC_STYLE_HEADERS.STYLE_ID(+)

Thursday, 22 December 2016

Oracle Inventory Transaction Query

To get the whole Oracle Inventory transaction details , below query can be used.

/* Formatted on 10/10/2016 12:53:40 PM (QP5 v5.126.903.23003) */

SELECT MMT.ORGANIZATION_ID,

MMT.INVENTORY_ITEM_ID ITEM_ID,

MMT.SUBINVENTORY_CODE SUBINVENTORY_CODE,

MMT.TRANSACTION_DATE,

CID.TRANSACTION_TYPE_NAME,

CID.TRANSACTION_SOURCE_TYPE_NAME,

MMT.PRIMARY_QUANTITY,

CID.UNIT_COST,

CID.BASE_TRANSACTION_VALUE,

REFERENCE_ACCOUNT,

DISTRIBUTION_ACCOUNT_ID,

ENCUMBRANCE_ACCOUNT,

SHIPMENT_NUMBER,

MMT.LOCATOR_ID,

MMT.TRANSACTION_UOM,

MMT.TRANSACTION_REFERENCE,

DECODE (

CID.TRANSACTION_SOURCE_TYPE_NAME,

'Move order',

(SELECT DISTINCT

CONCAT (CONCAT (REQUEST_NUMBER, '--'), DESCRIPTION)

FROM mtl_txn_request_headers mtrh

WHERE HEADER_ID = mmt.TRANSACTION_SOURCE_ID AND ROWNUM = 1),

'Purchase order',

(SELECT DISTINCT CONCAT (CONCAT (segment1, '--'), COMMENTS)

FROM po_headers_all pha

WHERE po_header_id = mmt.TRANSACTION_SOURCE_ID AND ROWNUM = 1),

'Job or Schedule',

(SELECT DISTINCT

CONCAT (CONCAT (WIP_ENTITY_NAME, '--'), DESCRIPTION)

FROM wip_entities

WHERE WIP_ENTITY_ID = mmt.TRANSACTION_SOURCE_ID

AND ROWNUM = 1),

CAST (mmt.SOURCE_CODE AS VARCHAR2 (100))

)

TRANSACTION_DESC

FROM MTL_MATERIAL_TRANSACTIONS MMT, CST_INV_DISTRIBUTION_V CID

WHERE MMT.TRANSACTION_ID = CID.TRANSACTION_ID

AND CID.ACCOUNTING_LINE_TYPE = 1

-- AND MMT.INVENTORY_ITEM_ID IN (41041, 42496)

--and TRANSACTION_SOURCE_TYPE_NAME='Purchase order'

Enjoy and Stay Well.

BPA Master List

Sometime users wants to have the master BPA (Blanket Purchase Agreement) list irrespective of ay release is being created or not. Below query will help to get the same.

/* Formatted on 11/6/2016 12:55:53 PM (QP5 v5.126.903.23003) */
SELECT a.SEGMENT1 BPA_NO,
COMMENTS BPA_DESC,
CURRENCY_CODE,
APPROVED_DATE,
(SELECT APPROVED_DATE
FROM PO_HEADERS_ARCHIVE_ALL
WHERE REVISION_NUM = 0
AND APPROVED_FLAG = 'Y'
AND po_header_id = a.po_header_id)
PO_APPROVED_DATE_ORG,
REVISION_NUM,
START_DATE,
END_DATE,
AMOUNT_LIMIT Updated_ACV_Amount,
(CASE
WHEN a.ATTRIBUTE_CATEGORY = 'ACV Details' THEN a.ATTRIBUTE1
END)
Initial_ACV_Amount,
a.RATE,
(SELECT DESCRIPTION
FROM fnd_lookup_values
WHERE a.FOB_LOOKUP_CODE = LOOKUP_CODE AND lookup_type = 'FOB')
SHIPMENT_TERMS,
c.SEGMENT1 SUPPLIER_NUM,
c.VENDOR_NAME SUPPLIER_NAME,
b.address_line1,
b.country,
SYSDATE
FROM PO_HEADERS_ALL a, ap_supplier_sites_all b, ap_suppliers c
WHERE a.vendor_id = b.vendor_SITE_id(+)
AND b.vendor_id = c.vendor_id(+)
AND TYPE_LOOKUP_CODE = 'BLANKET'

Enjoy and Stay Well !!

Sunday, 11 December 2016

OBIEE 11g: Conditional Formatting on a Column based on other column is not working

Issue: Conditional Formatting on a Column based on other column is not working in OBIEE.

Resolution: Value Suppression has to be set as “Repeat”

Enjoy and stay well !!

Wednesday, 7 December 2016

PO Action History Query

This Query will give you Action History for Standard PO, Blanket Release & Requisition.

/* Formatted on 12/7/2016 12:39:28 PM (QP5 v5.126.903.23003) */
SELECT OBJECT_TYPE_CODE TYPE,
SEGMENT1,
COMMENTS DESCriptions,
RELEASE_NUM,
RELEASE_DATE,
POH.AGENT_ID BUYER_REQ_ID,
SEQUENCE_NUM,
ACTION_CODE,
ACTION_DATE,
NOTE,
POAH.EMPLOYEE_ID ACTION_OWNER_ID,
POH.CREATION_DATE CREATION_DATE,
POAH.LAST_UPDATE_DATE ACTION_LAST_UPDATE_DATE
FROM PO_RELEASES_ALL POR, po_headers_all POH, po_action_history POAH
WHERE POR.PO_HEADER_ID = POH.PO_HEADER_ID
AND POR.PO_RELEASE_ID = POAH.OBJECT_ID
AND OBJECT_TYPE_CODE = 'RELEASE'
UNION
SELECT OBJECT_TYPE_CODE,
SEGMENT1,
COMMENTS PO_DESC,
NULL RELEASE_NUM,
NULL RELEASE_DATE,
POH.AGENT_ID BUYER_ID,
SEQUENCE_NUM,
ACTION_CODE,
ACTION_DATE,
NOTE,
POAH.EMPLOYEE_ID ACTION_OWNER_ID,
POH.CREATION_DATE PO_CREATION_DATE,
POAH.LAST_UPDATE_DATE ACTION_LAST_UPDATE_DATE
FROM po_headers_all POH, po_action_history POAH
WHERE POH.PO_HEADER_ID = POAH.OBJECT_ID AND OBJECT_TYPE_CODE = 'PO'
UNION
SELECT OBJECT_TYPE_CODE,
SEGMENT1,
DESCRIPTION,
NULL RELEASE_NUM,
NULL RELEASE_DATE,
POH.PREPARER_ID,
SEQUENCE_NUM,
ACTION_CODE,
ACTION_DATE,
NOTE,
POAH.EMPLOYEE_ID ACTION_OWNER_ID,
POH.CREATION_DATE PO_CREATION_DATE,
POAH.LAST_UPDATE_DATE ACTION_LAST_UPDATE_DATE
FROM po_requisition_headers_all POH, po_action_history POAH
WHERE POH.REQUISITION_HEADER_ID = POAH.OBJECT_ID
AND OBJECT_TYPE_CODE = 'REQUISITION'

Enjoy and Stay Well !! :)

Tuesday, 6 December 2016

Item Master Query

/* Formatted on 11/15/2016 1:35:09 PM (QP5 v5.126.903.23003) */
SELECT   ORGANIZATION_CODE,
         ORGANIZATION_NAME,
         MTL_SYSTEM_ITEMS_B.LAST_UPDATE_DATE,
         --MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY,
         MTL_SYSTEM_ITEMS_B.CREATION_DATE,
         -- MTL_SYSTEM_ITEMS_B.CREATED_BY,
         MTL_SYSTEM_ITEMS_B.DESCRIPTION,
         MTL_SYSTEM_ITEMS_TL.LONG_DESCRIPTION,
         MTL_SYSTEM_ITEMS_B.SEGMENT1,
         MTL_SYSTEM_ITEMS_B.SHELF_LIFE_CODE,
         --         MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT,
         --         MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE,
         --         MTL_SYSTEM_ITEMS_B.VOLUME_UOM_CODE,
         --         MTL_SYSTEM_ITEMS_B.UNIT_VOLUME,
         MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE,
         MTL_SYSTEM_ITEMS_B.ITEM_TYPE,
         MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE,
         MTL_SYSTEM_ITEMS_B.PLANNING_MAKE_BUY_CODE,
         MTL_SYSTEM_ITEMS_B.START_DATE_ACTIVE,
         MTL_SYSTEM_ITEMS_B.RETURNABLE_FLAG,
         MTL_SYSTEM_ITEMS_B.PURCHASING_ENABLED_FLAG,
         MTL_SYSTEM_ITEMS_B.SERVICEABLE_PRODUCT_FLAG,
         MTL_SYSTEM_ITEMS_B.END_DATE_ACTIVE,
         MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_FLAG,
         MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_ENABLED_FLAG,
         MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE,
         MTL_SYSTEM_ITEMS_B.MIN_MINMAX_QUANTITY,
         MTL_SYSTEM_ITEMS_B.MAX_MINMAX_QUANTITY,
         MTL_SYSTEM_ITEMS_B.ATTRIBUTE1 INS_GROUP,
         MTL_SYSTEM_ITEMS_B.ATTRIBUTE2 INS_GRP_DESC,
         STOCK_ENABLED_FLAG,
         INSPECTION_REQUIRED_FLAG,
         RECEIPT_REQUIRED_FLAG,
         RCV_ROUTING_HEADERS.ROUTING_NAME,
         BUYER_ID
  FROM   MTL_SYSTEM_ITEMS_B,
         org_organization_definitions,
         RCV_ROUTING_HEADERS,
         MTL_SYSTEM_ITEMS_TL
 WHERE   org_organization_definitions.ORGANIZATION_ID =
            MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID
         AND ROUTING_HEADER_ID = RECEIVING_ROUTING_ID
         AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID =
               MTL_SYSTEM_ITEMS_TL.ORGANIZATION_ID
         AND MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID =
               MTL_SYSTEM_ITEMS_TL.INVENTORY_ITEM_ID

Supplier Master Query

/* Formatted on 11/27/2016 8:35:10 AM (QP5 v5.126.903.23003) */
SELECT   PO_VENDORS.SEGMENT1 SUPPLIER_NUMBER,
         PO_VENDORS.VENDOR_NAME SUPPLIER_NAME,
         PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
         PO_VENDORS.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,
         PO_VENDOR_SITES_ALL.ADDRESS_LINE1,
         PO_VENDOR_SITES_ALL.ADDRESS_LINE2,
         PO_VENDOR_SITES_ALL.ADDRESS_LINE3,
         PO_VENDOR_SITES_ALL.CITY,
         PO_VENDOR_SITES_ALL.STATE,
         PO_VENDOR_SITES_ALL.ZIP,
         PO_VENDOR_SITES_ALL.COUNTRY,
         PO_VENDOR_SITES_ALL.AREA_CODE,
         PO_VENDOR_SITES_ALL.PHONE,
         PO_VENDOR_SITES_ALL.FAX_AREA_CODE,
         PO_VENDOR_SITES_ALL.FAX,
         PO_VENDORS.CREATION_DATE,
         (SELECT   USER_NAME
            FROM   fnd_user
           WHERE   user_id = PO_VENDORS.CREATED_BY)
            created_By,
         PO_VENDORS.LAST_UPDATE_DATE,
         (SELECT   USER_NAME
            FROM   fnd_user
           WHERE   user_id = PO_VENDORS.LAST_UPDATED_BY)
            Last_Updated_By,
         PO_VENDORS.START_DATE_ACTIVE,
         PO_VENDORS.END_DATE_ACTIVE,
         PO_VENDOR_SITES_ALL.PAY_GROUP_LOOKUP_CODE,
         PO_VENDORS.ENABLED_FLAG,
         PO_VENDOR_SITES_ALL.attribute1 SUPPLIER_CLASSIFICATION,
         PO_VENDORS.TERMS_DATE_BASIS,
         PO_VENDORS.ATTRIBUTE1 PACER_VENDOR_NO,
         AP_TERMS_TL.NAME PAYMENT_TERMS,
         ieb.BANK_NUMBER,
         ieb.bank_name "BANK NAME",
         iebb.bank_branch_name "BANK BRANCH NAME",
         ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
         EFT_SWIFT_CODE BANK_SWIFT_CODE,
         IBAN,
         PO_VENDORS.INVOICE_CURRENCY_CODE,
         PO_VENDORS.PAYMENT_CURRENCY_CODE,
            PO_VENDOR_CONTACTS.PREFIX
         || ' '
         || PO_VENDOR_CONTACTS.FIRST_NAME
         || ' '
         || PO_VENDOR_CONTACTS.LAST_NAME
            SUPPLIER_CONTACT,
         PO_VENDOR_CONTACTS.EMAIL_ADDRESS
  FROM   PO_VENDOR_SITES_ALL,
         PO_VENDORS,
         AP_TERMS_TL,
         apps.iby_ext_bank_accounts ieba,
         apps.iby_account_owners iao,
         apps.iby_ext_banks_v ieb,
         apps.iby_ext_bank_branches_v iebb,
         PO_VENDOR_CONTACTS
 WHERE       PO_VENDOR_SITES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID
         AND PO_VENDORS.TERMS_ID = AP_TERMS_TL.TERM_ID
         AND iao.account_owner_party_id = PO_VENDORS.party_id
         AND ieba.ext_bank_account_id = iao.ext_bank_account_id
         AND ieb.bank_party_id = iebb.bank_party_id
         AND ieba.branch_id = iebb.branch_party_id
         AND ieba.bank_id = ieb.bank_party_id
         AND PO_VENDORS.ENABLED_FLAG = 'Y'
         AND PO_VENDOR_SITES_ALL.VENDOR_ID = PO_VENDOR_CONTACTS.VENDOR_ID(+)
         AND PO_VENDOR_SITES_ALL.VENDOR_SITE_ID =
               PO_VENDOR_CONTACTS.VENDOR_SITE_ID(+)

Friday, 21 October 2016

OBIEE 11g Compatibility With IE11 (Without Patches)

Generally we need to apply some bundle patches (Patch 23703041 & Patch 18277370) to make compatible of OBIEE 11g with IE11.

But we can avoid applying patches , instead small change in iecommon.xml file will make this work.

1. Please navigate to below path: [Middleware Home]\Oracle_BI1\bifoundation\web\display\featuretables\iecommon.xml

2. Take a backup of iecommon.xml file.

3. Change default value ie. "IE=100" to "IE=10"

from

<meta http-equiv="X-UA-Compatible" content="IE=100" >

to

<meta http-equiv="X-UA-Compatible" content="IE=10" >

4. Restart the Presentation Services and clear browser cache after making changes.

NOTE: Informatica Admin Console will work in IE11 if we enable compatibility mode for that server.

Enjoy and stay well!! :)

Monday, 19 September 2016

Informatica Error: ORA-26002

Sometime we may get an error while running a Informatica Session.

Error:

ORA-26002: Table XX_TABLE_NAME has index defined upon it.

Reason: Mode is set to Bulk.

Solution: Change the mode to Normal.

Enjoy and stay well !!

Thursday, 12 May 2016

OPMN - Process Status is STOP

Sometime OPMN process status can show as STOP as shown below.

The issue was occurring because OPMN state folder still had old process state and ID for OPMN process Cluster Controller due to abnormal stop OBIEE services or OS itself which leaves the old process/lock files, hence was failing to start the process and moving it to Stop state.

Solution:

1. Stop OPMN.

2. Rename State folder in $ORACLE_BI/instances/instance1/config/OPMN/opmn location.

3. Start OPMN.

Now it should work fine.

Enjoy and Stay Well !! :)

Wednesday, 11 May 2016

Oracle Apps : Cost Report (Transaction Details)

Every client wants to have a Cost Report which will actually show the transaction details of each entry in GL. For example users wants to know what is the source of the Actual entry in GL. Source means I meant Sub-ledgers ( Finance , HRMS , Procurement , EAM , Project , Etc.. )

Lets have a look into the SQL query which can be used for report either in EBS or BI.

/* Formatted on 5/12/2016 8:52:31 AM (QP5 v5.126.903.23003) */

SELECT LEDGER_ID,

CODE_COMBINATION_ID,

JE_SOURCE,

PO_NUMBER,

JE_NAME,

BATCH_NAME,

(REGEXP_REPLACE (

REGEXP_REPLACE (PO_DESCRIPTION,

'[^' || CHR (32) || '-' || CHR (126) || ']'),

' '

))

PO_DESCRIPTION,

functional_amount,

transaction_currency,

functional_currency,

transaction_amount,

transaction_gl_date,

user_je_category_name,

supplier_id,

NVL (

(REGEXP_REPLACE (

REGEXP_REPLACE (REF_DESCRIPTION,

'[^' || CHR (32) || '-' || CHR (126) || ']'),

' '

)),

description

)

REF_DESCRIPTION,

NVL (NVL (transaction_reference, DOC_SEQUENCE_VALUE), description)

transaction_reference,

PROJECT_ID

FROM ( SELECT (REGEXP_REPLACE (

REGEXP_REPLACE (

gjl.description,

'[^' || CHR (32) || '-' || CHR (126) || ']'

' '

))

description,

USER_JE_SOURCE_NAME "JE_SOURCE",

GL1.LEDGER_ID LEDGER_ID,

GCC.CODE_COMBINATION_ID CODE_COMBINATION_ID,

gcc.segment3 ac_code,

gcc.chart_of_accounts_id,

gjh.DOC_SEQUENCE_VALUE,

(SELECT --FFH.PARENT_FLEX_VALUE

DECODE (COUNT (1),

MAX (FFH.PARENT_FLEX_VALUE))

FROM FND_FLEX_VALUES_VL FFV,

FND_FLEX_VALUE_SETS FFS,

FND_FLEX_VALUE_HIERARCHIES FFH

WHERE FLEX_VALUE = GCC.SEGMENT3

AND FFS.FLEX_VALUE_SET_NAME =

'XXOLNG_GL_Accounts'

AND FFS.FLEX_VALUE_SET_ID =

FFV.FLEX_VALUE_SET_ID

AND FFV.SUMMARY_FLAG = 'N'

AND FFS.FLEX_VALUE_SET_ID =

FFH.FLEX_VALUE_SET_ID

AND GCC.SEGMENT3 BETWEEN FFH.CHILD_FLEX_VALUE_LOW

AND FFH.CHILD_FLEX_VALUE_HIGH)

Parent_Account,

apps.gl_flexfields_pkg.get_description_sql (

gcc.chart_of_accounts_id,

gcc.segment3

)

ac_name,

TRIM(TO_CHAR (

(SUM(NVL (XAL.ACCOUNTED_DR, 0)

- NVL (XAL.ACCOUNTED_CR, 0))),

'9999999999999999999999.00'

))

functional_amount,

GJH.CURRENCY_CODE transaction_currency,

'USD' functional_currency,

TRIM(TO_CHAR (

SUM(NVL (XAL.ENTERED_DR, 0)

- NVL (XAL.ENTERED_CR, 0)),

'9999999999999999999999.00'

))

transaction_amount,

TO_CHAR (GJH.DEFAULT_EFFECTIVE_DATE, 'DD-MON-YYYY')

transaction_gl_date,

GJC.user_je_category_name,

GJH.period_name,

GCC.SEGMENT2 department_code,

APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (

GCC.CHART_OF_ACCOUNTS_ID,

GCC.SEGMENT2

)

department_name,

GCC.SEGMENT4 loc_code,

APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (

GCC.CHART_OF_ACCOUNTS_ID,

GCC.SEGMENT4

)

loc_description,

DECODE (

GJH.JE_CATEGORY,

'Purchase Invoices',

(SELECT DISTINCT AI.INVOICE_NUM

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_DISTRIBUTIONS_ALL AID,

AP_INVOICES_ALL AI

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND AI.INVOICE_ID = AID.INVOICE_ID

AND AID.INVOICE_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_INV_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND ROWNUM = 1),

'Payments',

(SELECT DISTINCT ACA.CHECK_NUMBER

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_PAYMENTS_ALL AIPA,

AP_CHECKS_ALL ACA

WHERE AIPA.INVOICE_ID =

XDL.APPLIED_TO_SOURCE_ID_NUM_1

AND ACA.CHECK_ID = AIPA.CHECK_ID

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_PMT_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND ROWNUM = 1),

'Receipts',

(SELECT DISTINCT ACR.RECEIPT_NUMBER

FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH,

AR_CASH_RECEIPTS_ALL ACR

WHERE 1 = 1 AND ACRH.EVENT_ID = XAH.EVENT_ID

AND ACR.CASH_RECEIPT_ID =

ACRH.CASH_RECEIPT_ID),

'Sales Invoices',

(SELECT DISTINCT RCTA.TRX_NUMBER

FROM XLA_DISTRIBUTION_LINKS XDL,

RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,

RA_CUSTOMER_TRX_ALL RCTA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'RA_CUST_TRX_LINE_GL_DIST_ALL'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND RCTLG.CUST_TRX_LINE_GL_DIST_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND RCTLG.CUSTOMER_TRX_ID =

RCTA.CUSTOMER_TRX_ID),

'Debit Memos',

(SELECT DISTINCT RCTA.TRX_NUMBER

FROM RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,

RA_CUSTOMER_TRX_ALL RCTA

WHERE RCTLG.EVENT_ID = XAH.EVENT_ID

AND RCTLG.CODE_COMBINATION_ID =

XAL.CODE_COMBINATION_ID

AND RCTLG.CUSTOMER_TRX_ID =

RCTA.CUSTOMER_TRX_ID),

'Credit Memos',

(SELECT DISTINCT RCTA.TRX_NUMBER

FROM RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,

RA_CUSTOMER_TRX_ALL RCTA

WHERE RCTLG.EVENT_ID = XAH.EVENT_ID

AND RCTLG.CODE_COMBINATION_ID =

XAL.CODE_COMBINATION_ID

AND RCTLG.CUSTOMER_TRX_ID =

RCTA.CUSTOMER_TRX_ID),

'Addition',

(SELECT DISTINCT FAB.ASSET_NUMBER

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1),

'Adjustment',

(SELECT DISTINCT FAB.ASSET_NUMBER

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1),

'Retirement',

(SELECT DISTINCT FAB.ASSET_NUMBER

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1),

'Depreciation',

(SELECT DISTINCT FAB.ASSET_NUMBER

FROM FA_ADDITIONS_B FAB, FA_ADDITIONS_TL FTL

WHERE 1 = 1

AND FAB.ASSET_ID = FTL.ASSET_ID

AND FAB.ASSET_ID = XTE.SOURCE_ID_INT_1),

'Bank Transfers',

(SELECT DISTINCT TRXN_REFERENCE_NUMBER

FROM XLA_DISTRIBUTION_LINKS XDL, CE_CASHFLOWS CC

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE = 'CE_TRANS'

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

CC.CASHFLOW_ID),

'Receiving',

(SELECT DISTINCT RSH.RECEIPT_NUM

FROM XLA_DISTRIBUTION_LINKS XDL,

RCV_RECEIVING_SUB_LEDGER RRSL,

RCV_TRANSACTIONS RT,

RCV_SHIPMENT_HEADERS RSH

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'RCV_RECEIVING_SUB_LEDGER'

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

RRSL.RCV_SUB_LEDGER_ID

AND RRSL.RCV_TRANSACTION_ID =

RT.TRANSACTION_ID

AND RSH.SHIPMENT_HEADER_ID =

RT.SHIPMENT_HEADER_ID),

'Inventory',

(SELECT DISTINCT MTA.TRANSACTION_ID

FROM XLA_DISTRIBUTION_LINKS XDL,

MTL_TRANSACTION_ACCOUNTS MTA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'MTL_TRANSACTION_ACCOUNTS'

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

MTA.INV_SUB_LEDGER_ID),

'Purchases',

(SELECT DISTINCT POH.SEGMENT1

FROM PO_HEADERS_ALL POH,

PO_LINES_ALL POL,

PO_LINE_LOCATIONS_ALL POLL,

PO_DISTRIBUTIONS_ALL POD,

XLA_DISTRIBUTION_LINKS XDL

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'PO_DISTRIBUTIONS_ALL'

AND POD.PO_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND POD.LINE_LOCATION_ID =

POLL.LINE_LOCATION_ID

AND POLL.PO_LINE_ID = POL.PO_LINE_ID

AND POL.PO_HEADER_ID = POH.PO_HEADER_ID

AND ROWNUM = 1),

'Reconciled Payments',

(SELECT DISTINCT ACA.CHECK_NUMBER

FROM XLA_DISTRIBUTION_LINKS XDL, AP_CHECKS_ALL ACA

WHERE ACA.CHECK_ID = XDL.APPLIED_TO_SOURCE_ID_NUM_1

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_PMT_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID),

'Misc Receipts',

(SELECT DISTINCT ACR.RECEIPT_NUMBER

FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH,

AR_CASH_RECEIPTS_ALL ACR

WHERE 1 = 1 AND ACRH.EVENT_ID = XAH.EVENT_ID

AND ACR.CASH_RECEIPT_ID =

ACRH.CASH_RECEIPT_ID),

'Requisitions',

(SELECT DISTINCT PRHA.SEGMENT1

FROM XLA_DISTRIBUTION_LINKS XDL,

PO_REQ_DISTRIBUTIONS_ALL PRDA,

PO_REQUISITION_LINES_ALL PRLA,

PO_REQUISITION_HEADERS_ALL PRHA

WHERE XDL.SOURCE_DISTRIBUTION_TYPE =

'PO_REQ_DISTRIBUTIONS_ALL'

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

PRDA.DISTRIBUTION_ID

AND PRDA.REQUISITION_LINE_ID =

PRLA.REQUISITION_LINE_ID

AND PRLA.REQUISITION_HEADER_ID =

PRHA.REQUISITION_HEADER_ID),

'Release',

(SELECT DISTINCT PRHA.SEGMENT1

FROM XLA_DISTRIBUTION_LINKS XDL,

PO_DISTRIBUTIONS_ALL PRDA,

PO_HEADERS_ALL PRHA

WHERE XDL.SOURCE_DISTRIBUTION_TYPE =

'PO_REQ_DISTRIBUTIONS_ALL'

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

PRDA.REQ_DISTRIBUTION_ID

AND PRDA.PO_HEADER_ID = PRHA.PO_HEADER_ID),

'WIP',

(SELECT DISTINCT WE.WIP_ENTITY_NAME

FROM WIP_ENTITIES WE,

WIP_TRANSACTIONS WI,

WIP_TRANSACTION_ACCOUNTS WTA,

XLA_DISTRIBUTION_LINKS XDL

WHERE 1 = 1

AND WI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID

AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

WTA.WIP_SUB_LEDGER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID)

)

transaction_reference,

DECODE (

GJH.JE_CATEGORY,

'Receiving',

(SELECT DISTINCT PRHA.SEGMENT1

FROM apps.XLA_DISTRIBUTION_LINKS XDL,

apps.RCV_RECEIVING_SUB_LEDGER RRSL,

apps.RCV_TRANSACTIONS RT,

--PO_HEADERS_ALL POH,

apps.PO_DISTRIBUTIONS_ALL PDA,

apps.PO_REQ_DISTRIBUTIONS_ALL PRDA,

apps.PO_REQUISITION_LINES_ALL PRLA,

apps.PO_REQUISITION_HEADERS_ALL PRHA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = xal.aE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

RRSL.RCV_SUB_LEDGER_ID

AND RRSL.RCV_TRANSACTION_ID =

RT.TRANSACTION_ID

AND RT.PO_DISTRIBUTION_ID =

PDA.PO_DISTRIBUTION_ID

AND PDA.REQ_DISTRIBUTION_ID =

PRDA.DISTRIBUTION_ID

AND PRDA.REQUISITION_LINE_ID =

PRLA.REQUISITION_LINE_ID

AND PRHA.REQUISITION_HEADER_ID =

PRLA.REQUISITION_HEADER_ID

AND ROWNUM = 1),

'WIP',

(SELECT DISTINCT PRHA.SEGMENT1

FROM WIP_TRANSACTIONS WI,

WIP_TRANSACTION_ACCOUNTS WTA,

XLA_DISTRIBUTION_LINKS XDL,

RCV_TRANSACTIONS RT,

po_distributions_all PDA,

po_req_distributions_all PRDA,

po_REQUISITION_lines_all PRLA,

PO_REQUISITION_HEADERS_ALL PRHA

WHERE 1 = 1

AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

WTA.WIP_SUB_LEDGER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND WI.RCV_TRANSACTION_ID = RT.TRANSACTION_ID

AND RT.PO_DISTRIBUTION_ID =

PDA.PO_DISTRIBUTION_ID

AND PRDA.DISTRIBUTION_ID =

PDA.REQ_DISTRIBUTION_ID

AND PRDA.REQUISITION_LINE_ID =

PRLA.REQUISITION_LINE_ID

AND PRLA.REQUISITION_HEADER_ID =

PRHA.REQUISITION_HEADER_ID

AND ROWNUM = 1),

'Inventory',

(SELECT DISTINCT PHA.SEGMENT1

FROM XLA_DISTRIBUTION_LINKS XDL,

PO_HEADERS_ALL PHA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'MTL_TRANSACTION_ACCOUNTS'

AND PHA.po_header_id =

XDL.APPLIED_TO_SOURCE_ID_NUM_1

AND ROWNUM = 1),

'Purchase Invoices',

(SELECT DISTINCT PHA.SEGMENT1

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_DISTRIBUTIONS_ALL AID,

AP_INVOICES_ALL AI,

PO_HEADERS_ALL PHA,

PO_DISTRIBUTIONS_ALL PDA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND AI.INVOICE_ID = AID.INVOICE_ID

AND AID.INVOICE_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_INV_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND AID.PO_DISTRIBUTION_ID =

PDA.PO_DISTRIBUTION_ID

AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID

AND ROWNUM = 1)

)

PO_NUMBER,

DECODE (

GJH.JE_CATEGORY,

'Receiving',

(SELECT DISTINCT PRLA.ITEM_DESCRIPTION

FROM apps.XLA_DISTRIBUTION_LINKS XDL,

apps.RCV_RECEIVING_SUB_LEDGER RRSL,

apps.RCV_TRANSACTIONS RT,

apps.PO_DISTRIBUTIONS_ALL PDA,

apps.PO_REQ_DISTRIBUTIONS_ALL PRDA,

apps.PO_REQUISITION_LINES_ALL PRLA,

apps.PO_REQUISITION_HEADERS_ALL PRHA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = xal.aE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

RRSL.RCV_SUB_LEDGER_ID

AND RRSL.RCV_TRANSACTION_ID =

RT.TRANSACTION_ID

AND RT.PO_DISTRIBUTION_ID =

PDA.PO_DISTRIBUTION_ID

AND PDA.REQ_DISTRIBUTION_ID =

PRDA.DISTRIBUTION_ID

AND PRDA.REQUISITION_LINE_ID =

PRLA.REQUISITION_LINE_ID

AND PRHA.REQUISITION_HEADER_ID =

PRLA.REQUISITION_HEADER_ID

AND ROWNUM = 1),

'WIP',

(SELECT DISTINCT PRLA.ITEM_DESCRIPTION

FROM WIP_TRANSACTIONS WI,

WIP_TRANSACTION_ACCOUNTS WTA,

XLA_DISTRIBUTION_LINKS XDL,

RCV_TRANSACTIONS RT,

po_distributions_all PDA,

po_req_distributions_all PRDA,

po_REQUISITION_lines_all PRLA,

PO_REQUISITION_HEADERS_ALL PRHA

WHERE 1 = 1

AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

WTA.WIP_SUB_LEDGER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND WI.RCV_TRANSACTION_ID = RT.TRANSACTION_ID

AND RT.PO_DISTRIBUTION_ID =

PDA.PO_DISTRIBUTION_ID

AND PRDA.DISTRIBUTION_ID =

PDA.REQ_DISTRIBUTION_ID

AND PRDA.REQUISITION_LINE_ID =

PRLA.REQUISITION_LINE_ID

AND PRLA.REQUISITION_HEADER_ID =

PRHA.REQUISITION_HEADER_ID

AND ROWNUM = 1),

'Inventory',

(SELECT DISTINCT PHA.COMMENTS

FROM XLA_DISTRIBUTION_LINKS XDL,

PO_HEADERS_ALL PHA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'MTL_TRANSACTION_ACCOUNTS'

AND PHA.po_header_id =

XDL.APPLIED_TO_SOURCE_ID_NUM_1

AND ROWNUM = 1),

'Purchase Invoices',

(SELECT DISTINCT PHA.COMMENTS

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_DISTRIBUTIONS_ALL AID,

AP_INVOICES_ALL AI,

PO_HEADERS_ALL PHA,

PO_DISTRIBUTIONS_ALL PDA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND AI.INVOICE_ID = AID.INVOICE_ID

AND AID.INVOICE_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_INV_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND AID.PO_DISTRIBUTION_ID =

PDA.PO_DISTRIBUTION_ID

AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID

AND ROWNUM = 1)

)

PO_DESCRIPTION,

GJH.NAME JE_NAME,

GJB.NAME BATCH_NAME,

DECODE (

GJH.JE_CATEGORY,

'Purchase Invoices',

(SELECT DISTINCT APS.VENDOR_ID

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_DISTRIBUTIONS_ALL AID,

AP_INVOICES_ALL AI,

ap_suppliers APS

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND AI.INVOICE_ID = AID.INVOICE_ID

AND AID.INVOICE_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_INV_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND APS.VENDOR_ID = AI.VENDOR_ID

AND ROWNUM = 1),

'Reconciled Payments',

(SELECT DISTINCT APS.VENDOR_ID

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_CHECKS_ALL ACA,

ap_suppliers APS

WHERE ACA.CHECK_ID = XDL.APPLIED_TO_SOURCE_ID_NUM_1

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_PMT_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND APS.VENDOR_ID = ACA.VENDOR_ID),

'Payments',

(SELECT DISTINCT APS.VENDOR_ID

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_PAYMENTS_ALL AIPA,

AP_CHECKS_ALL ACA,

ap_suppliers APS

WHERE AIPA.INVOICE_ID =

XDL.APPLIED_TO_SOURCE_ID_NUM_1

AND ACA.CHECK_ID = AIPA.CHECK_ID

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_PMT_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND APS.VENDOR_ID = ACA.VENDOR_ID

AND ROWNUM = 1),

'Receiving',

(SELECT DISTINCT APS.VENDOR_ID

FROM XLA_DISTRIBUTION_LINKS XDL,

RCV_RECEIVING_SUB_LEDGER RRSL,

RCV_TRANSACTIONS RT,

RCV_SHIPMENT_HEADERS RSH,

ap_suppliers APS

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'RCV_RECEIVING_SUB_LEDGER'

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

RRSL.RCV_SUB_LEDGER_ID

AND RRSL.RCV_TRANSACTION_ID =

RT.TRANSACTION_ID

AND RSH.SHIPMENT_HEADER_ID =

RT.SHIPMENT_HEADER_ID

AND APS.VENDOR_ID = RSH.VENDOR_ID),

'WIP',

(SELECT DISTINCT APS.VENDOR_ID

FROM PO_LINES_ALL PLA,

WIP_TRANSACTIONS WI,

WIP_TRANSACTION_ACCOUNTS WTA,

XLA_DISTRIBUTION_LINKS XDL,

PO_HEADERS_ALL PHA,

ap_suppliers APS

WHERE 1 = 1

AND PLA.PO_LINE_ID = WI.PO_LINE_ID

AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PLA.PO_HEADER_ID = WI.PO_HEADER_ID

AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

WTA.WIP_SUB_LEDGER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND APS.VENDOR_ID = PHA.VENDOR_ID)

)

supplier_ID,

DECODE (

GJH.JE_CATEGORY,

'Purchase Invoices',

(SELECT DISTINCT AID.DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_DISTRIBUTIONS_ALL AID,

AP_INVOICES_ALL AI

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND AI.INVOICE_ID = AID.INVOICE_ID

AND AID.INVOICE_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_INV_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND ROWNUM = 1),

'Payments',

(SELECT DISTINCT AIA.DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_PAYMENTS_ALL AIPA,

AP_CHECKS_ALL ACA,

AP_INVOICES_ALL AIA

WHERE AIPA.INVOICE_ID =

XDL.APPLIED_TO_SOURCE_ID_NUM_1

AND ACA.CHECK_ID = AIPA.CHECK_ID

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_PMT_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND AIPA.INVOICE_ID = AIA.INVOICE_ID

AND ROWNUM = 1),

'Receipts',

(SELECT DISTINCT ACR.COMMENTS

FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH,

AR_CASH_RECEIPTS_ALL ACR

WHERE 1 = 1 AND ACRH.EVENT_ID = XAH.EVENT_ID

AND ACR.CASH_RECEIPT_ID =

ACRH.CASH_RECEIPT_ID),

'Sales Invoices',

(SELECT DISTINCT RCL.DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL,

RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,

RA_CUSTOMER_TRX_ALL RCTA,

RA_CUSTOMER_TRX_LINES_ALL RCL

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'RA_CUST_TRX_LINE_GL_DIST_ALL'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND RCTLG.CUST_TRX_LINE_GL_DIST_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND RCTLG.CUSTOMER_TRX_ID =

RCTA.CUSTOMER_TRX_ID

AND RCTA.CUSTOMER_TRX_ID =

RCL.CUSTOMER_TRX_ID

AND ROWNUM = 1),

'Debit Memos',

(SELECT DISTINCT RCL.DESCRIPTION

FROM RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,

RA_CUSTOMER_TRX_ALL RCTA,

RA_CUSTOMER_TRX_LINES_ALL RCL

WHERE RCTLG.EVENT_ID = XAH.EVENT_ID

AND RCTLG.CODE_COMBINATION_ID =

XAL.CODE_COMBINATION_ID

AND RCTLG.CUSTOMER_TRX_ID =

RCTA.CUSTOMER_TRX_ID

AND RCTA.CUSTOMER_TRX_ID =

RCL.CUSTOMER_TRX_ID

AND ROWNUM = 1),

'Credit Memos',

(SELECT DISTINCT RCL.DESCRIPTION

FROM RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,

RA_CUSTOMER_TRX_ALL RCTA,

RA_CUSTOMER_TRX_LINES_ALL RCL

WHERE RCTLG.EVENT_ID = XAH.EVENT_ID

AND RCTLG.CODE_COMBINATION_ID =

XAL.CODE_COMBINATION_ID

AND RCTLG.CUSTOMER_TRX_ID =

RCTA.CUSTOMER_TRX_ID

AND RCTA.CUSTOMER_TRX_ID =

RCL.CUSTOMER_TRX_ID

AND ROWNUM = 1),

'Addition',

(SELECT DISTINCT FTL.DESCRIPTION

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1),

'Adjustment',

(SELECT DISTINCT FTL.DESCRIPTION

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1),

'Retirement',

(SELECT DISTINCT FTL.DESCRIPTION

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1),

'Depreciation',

(SELECT DISTINCT FTL.DESCRIPTION

FROM FA_ADDITIONS_B FAB, FA_ADDITIONS_TL FTL

WHERE 1 = 1

AND FAB.ASSET_ID = FTL.ASSET_ID

AND FAB.ASSET_ID = XTE.SOURCE_ID_INT_1),

'Bank Transfers',

(SELECT DISTINCT CC.DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL, CE_CASHFLOWS CC

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE = 'CE_TRANS'

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

CC.CASHFLOW_ID),

'Receiving',

(SELECT DISTINCT RSL.ITEM_DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL,

RCV_RECEIVING_SUB_LEDGER RRSL,

RCV_TRANSACTIONS RT,

RCV_SHIPMENT_HEADERS RSH,

rcv_shipment_lines RSL

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'RCV_RECEIVING_SUB_LEDGER'

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

RRSL.RCV_SUB_LEDGER_ID

AND RRSL.RCV_TRANSACTION_ID =

RT.TRANSACTION_ID

AND RSH.SHIPMENT_HEADER_ID =

RT.SHIPMENT_HEADER_ID

AND RSL.shipment_line_id =

RT.SHIPMENT_LINE_ID),

'Payroll',

GJH.JE_CATEGORY || '-' || GJH.PERIOD_NAME,

'Inventory',

(SELECT DISTINCT MSI.DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL,

MTL_TRANSACTION_ACCOUNTS MTA,

MTL_SYSTEM_ITEMS_B MSI,

mtl_material_transactions mmt

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'MTL_TRANSACTION_ACCOUNTS'

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

MTA.INV_SUB_LEDGER_ID

AND mmt.transaction_id = mta.transaction_id

AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID

AND MSI.INVENTORY_ITEM_ID =

MMT.INVENTORY_ITEM_ID),

'Purchases',

(SELECT DISTINCT POL.ITEM_DESCRIPTION

FROM PO_HEADERS_ALL POH,

PO_LINES_ALL POL,

PO_LINE_LOCATIONS_ALL POLL,

PO_DISTRIBUTIONS_ALL POD,

XLA_DISTRIBUTION_LINKS XDL

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'PO_DISTRIBUTIONS_ALL'

AND POD.PO_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND POD.LINE_LOCATION_ID =

POLL.LINE_LOCATION_ID

AND POLL.PO_LINE_ID = POL.PO_LINE_ID

AND POL.PO_HEADER_ID = POH.PO_HEADER_ID

AND ROWNUM = 1),

'Reconciled Payments',

(SELECT DISTINCT AIA.DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_CHECKS_ALL ACA,

AP_INVOICE_PAYMENTS_ALL AIPA,

AP_INVOICES_ALL AIA

WHERE ACA.CHECK_ID = XDL.APPLIED_TO_SOURCE_ID_NUM_1

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_PMT_DIST'

AND AIPA.CHECK_ID = ACA.CHECK_ID

AND AIPA.INVOICE_ID = AIA.INVOICE_ID

AND XDL.EVENT_ID = XAH.EVENT_ID

AND ROWNUM = 1),

'Misc Receipts',

(SELECT DISTINCT ACR.COMMENTS

FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH,

AR_CASH_RECEIPTS_ALL ACR

WHERE 1 = 1 AND ACRH.EVENT_ID = XAH.EVENT_ID

AND ACR.CASH_RECEIPT_ID =

ACRH.CASH_RECEIPT_ID),

'Requisitions',

(SELECT DISTINCT PRLA.ITEM_DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL,

PO_REQ_DISTRIBUTIONS_ALL PRDA,

PO_REQUISITION_LINES_ALL PRLA,

PO_REQUISITION_HEADERS_ALL PRHA

WHERE XDL.SOURCE_DISTRIBUTION_TYPE =

'PO_REQ_DISTRIBUTIONS_ALL'

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

PRDA.DISTRIBUTION_ID

AND PRDA.REQUISITION_LINE_ID =

PRLA.REQUISITION_LINE_ID

AND PRLA.REQUISITION_HEADER_ID =

PRHA.REQUISITION_HEADER_ID),

'Release',

(SELECT DISTINCT PLA.ITEM_DESCRIPTION

FROM XLA_DISTRIBUTION_LINKS XDL,

PO_DISTRIBUTIONS_ALL PRDA,

PO_HEADERS_ALL PRHA,

PO_LINES_ALL PLA

WHERE XDL.SOURCE_DISTRIBUTION_TYPE =

'PO_REQ_DISTRIBUTIONS_ALL'

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

PRDA.REQ_DISTRIBUTION_ID

AND PRDA.PO_HEADER_ID = PRHA.PO_HEADER_ID

AND PRHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND ROWNUM = 1),

'WIP',

(SELECT DISTINCT WE.DESCRIPTION

FROM WIP_ENTITIES WE,

WIP_TRANSACTIONS WI,

WIP_TRANSACTION_ACCOUNTS WTA,

XLA_DISTRIBUTION_LINKS XDL

WHERE 1 = 1

AND WI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID

AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

WTA.WIP_SUB_LEDGER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID)

)

REF_DESCRIPTION,

DECODE (

GJH.JE_CATEGORY,

'Receiving',

(SELECT DISTINCT PPA.PROJECT_ID

FROM apps.XLA_DISTRIBUTION_LINKS XDL,

apps.RCV_RECEIVING_SUB_LEDGER RRSL,

apps.RCV_TRANSACTIONS RT,

apps.PO_DISTRIBUTIONS_ALL PDA,

PA_PROJECTS_ALL PPA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = xal.aE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

RRSL.RCV_SUB_LEDGER_ID

AND RRSL.RCV_TRANSACTION_ID =

RT.TRANSACTION_ID

AND RT.PO_DISTRIBUTION_ID =

PDA.PO_DISTRIBUTION_ID

AND PDA.PROJECT_ID = PPA.PROJECT_ID

AND ROWNUM = 1),

'Purchase Invoices',

(SELECT DISTINCT PPA.PROJECT_ID

FROM XLA_DISTRIBUTION_LINKS XDL,

AP_INVOICE_DISTRIBUTIONS_ALL AID,

AP_INVOICES_ALL AI,

PA_PROJECTS_ALL PPA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND AI.INVOICE_ID = AID.INVOICE_ID

AND AID.INVOICE_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'AP_INV_DIST'

AND XDL.EVENT_ID = XAH.EVENT_ID

AND AID.PROJECT_ID = PPA.PROJECT_ID

AND ROWNUM = 1),

'Purchases',

(SELECT DISTINCT PPA.PROJECT_ID

FROM PO_HEADERS_ALL POH,

PO_LINES_ALL POL,

PO_LINE_LOCATIONS_ALL POLL,

PO_DISTRIBUTIONS_ALL POD,

XLA_DISTRIBUTION_LINKS XDL,

PA_PROJECTS_ALL PPA

WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_TYPE =

'PO_DISTRIBUTIONS_ALL'

AND POD.PO_DISTRIBUTION_ID =

XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND POD.LINE_LOCATION_ID =

POLL.LINE_LOCATION_ID

AND POLL.PO_LINE_ID = POL.PO_LINE_ID

AND POL.PO_HEADER_ID = POH.PO_HEADER_ID

AND PPA.PROJECT_ID = POD.PROJECT_ID

AND ROWNUM = 1),

'Requisitions',

(SELECT DISTINCT PPA.PROJECT_ID

FROM XLA_DISTRIBUTION_LINKS XDL,

PO_REQ_DISTRIBUTIONS_ALL PRDA,

PO_REQUISITION_LINES_ALL PRLA,

PO_REQUISITION_HEADERS_ALL PRHA,

PA_PROJECTS_ALL PPA

WHERE XDL.SOURCE_DISTRIBUTION_TYPE =

'PO_REQ_DISTRIBUTIONS_ALL'

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

PRDA.DISTRIBUTION_ID

AND PRDA.REQUISITION_LINE_ID =

PRLA.REQUISITION_LINE_ID

AND PRLA.REQUISITION_HEADER_ID =

PRHA.REQUISITION_HEADER_ID

AND PRDA.PROJECT_ID = PPA.PROJECT_ID),

'WIP',

(SELECT DISTINCT PPA.PROJECT_ID

FROM WIP_ENTITIES WE,

WIP_TRANSACTIONS WI,

WIP_TRANSACTION_ACCOUNTS WTA,

XLA_DISTRIBUTION_LINKS XDL,

PA_PROJECTS_ALL PPA,

WIP_DISCRETE_JOBS WDJ

WHERE 1 = 1

AND WI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID

AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID

AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =

WTA.WIP_SUB_LEDGER_ID

AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND WDJ.PROJECT_ID = PPA.PROJECT_ID

AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID),

'Addition',

(SELECT DISTINCT PPA.PROJECT_ID

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL,

FA_ASSET_INVOICES FAI,

PA_PROJECTS_ALL PPA

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1

AND FAI.ASSET_ID = FAB.ASSET_ID

AND FAI.PROJECT_ID IS NOT NULL

AND FAI.PROJECT_ID = PPA.PROJECT_ID

AND ROWNUM = 1),

'Adjustment',

(SELECT DISTINCT PPA.PROJECT_ID

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL,

FA_ASSET_INVOICES FAI,

PA_PROJECTS_ALL PPA

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1

AND FAI.ASSET_ID = FAB.ASSET_ID

AND FAI.PROJECT_ID IS NOT NULL

AND FAI.PROJECT_ID = PPA.PROJECT_ID

AND ROWNUM = 1),

'Retirement',

(SELECT DISTINCT PPA.PROJECT_ID

FROM FA_TRANSACTION_HEADERS FTH,

FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL,

FA_ASSET_INVOICES FAI,

PA_PROJECTS_ALL PPA

WHERE 1 = 1

AND FTH.ASSET_ID = FAB.ASSET_ID

AND FTH.ASSET_ID = FTL.ASSET_ID

AND FTH.TRANSACTION_HEADER_ID =

XTE.SOURCE_ID_INT_1

AND FAI.ASSET_ID = FAB.ASSET_ID

AND FAI.PROJECT_ID IS NOT NULL

AND FAI.PROJECT_ID = PPA.PROJECT_ID

AND ROWNUM = 1),

'Depreciation',

(SELECT DISTINCT PPA.PROJECT_ID

FROM FA_ADDITIONS_B FAB,

FA_ADDITIONS_TL FTL,

FA_ASSET_INVOICES FAI,

PA_PROJECTS_ALL PPA

WHERE 1 = 1

AND FAB.ASSET_ID = FTL.ASSET_ID

AND FAB.ASSET_ID = XTE.SOURCE_ID_INT_1

AND FAI.ASSET_ID = FAB.ASSET_ID

AND FAI.PROJECT_ID IS NOT NULL

AND FAI.PROJECT_ID = PPA.PROJECT_ID

AND ROWNUM = 1)

)

PROJECT_ID,

XTE.ENTITY_ID,

XTE.SOURCE_ID_INT_1,

XAL.AE_HEADER_ID,

GJH.JE_CATEGORY,

XAL.AE_LINE_NUM,

GJL.JE_HEADER_ID

FROM GL_IMPORT_REFERENCES GIR,

XLA_AE_LINES XAL,

XLA_AE_HEADERS XAH,

XLA_EVENTS XE,

XLA_TRANSACTION_ENTITIES XTE,

GL_JE_HEADERS GJH,

GL_JE_LINES GJL,

GL_LEDGERS GL1,

GL_JE_SOURCES GJS,

GL_CODE_COMBINATIONS_KFV GCC,

GL_JE_CATEGORIES GJC,

gl_period_statuses per,

gl_je_batches gjb

WHERE 1 = 1

AND gjh.je_batch_id = gjb.je_batch_id

AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID

AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE

AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XAH.APPLICATION_ID = XAL.APPLICATION_ID

AND XE.EVENT_ID(+) = XAH.EVENT_ID

AND XE.APPLICATION_ID(+) = XAH.APPLICATION_ID

AND XTE.APPLICATION_ID(+) = XAH.APPLICATION_ID

AND XTE.ENTITY_ID(+) = XAH.ENTITY_ID

AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

AND GL1.LEDGER_ID = GJH.LEDGER_ID

AND GJS.JE_SOURCE_NAME = GJH.JE_SOURCE

AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME

AND GJC.LANGUAGE = 'US'

AND GJH.STATUS = 'P'

AND GJH.ACTUAL_FLAG = 'A'

AND (NVL (NVL (XAL.ACCOUNTED_CR, XAL.ACCOUNTED_DR), 0)) <>

AND gjh.period_name = per.period_name

AND per.ledger_id = gjh.ledger_id

AND per.application_id = 101

AND (TRUNC (TO_DATE (per.start_date)) >=

DECODE (

SUBSTR (:p_period_name_from, 1, 3),

'Adj',

(SELECT start_date

FROM gl_period_statuses

WHERE APPLICATION_ID = 101

AND ledger_id = per.ledger_id

AND PERIOD_NAME = :p_period_name_from),

TRUNC (TO_DATE (:p_period_name_from, 'Mon-yy'))

))

AND (TRUNC (per.end_date) <=

DECODE (

SUBSTR (:p_period_name_to, 1, 3),

'Adj',

(SELECT end_date

FROM gl_period_statuses

WHERE APPLICATION_ID = 101

AND ledger_id = per.ledger_id

AND PERIOD_NAME = :p_period_name_to),

TRUNC(LAST_DAY (

TO_DATE (:p_period_name_to, 'Mon-yy')

))

AND gcc.segment3 = '5521005'

GROUP BY GCC.SEGMENT3,

GCC.CHART_OF_ACCOUNTS_ID,

GJH.CURRENCY_CODE,

GJH.DEFAULT_EFFECTIVE_DATE,

GJC.USER_JE_CATEGORY_NAME,

GJH.JE_CATEGORY,

XTE.ENTITY_ID,

XTE.SOURCE_ID_INT_1,

GIR.JE_LINE_NUM,

GJH.PERIOD_NAME,

XAH.DOC_SEQUENCE_ID,

XAH.DOC_SEQUENCE_VALUE,

GJH.NAME,

gjb.NAME,

XAL.CODE_COMBINATION_ID,

GCC.SEGMENT2,

XAL.AE_LINE_NUM,

XAH.EVENT_ID,

XAL.DISPLAYED_LINE_NUMBER,

GCC.SEGMENT4,

USER_JE_SOURCE_NAME,

GL1.NAME,

GJH.DOC_SEQUENCE_VALUE,

gjh.je_header_id,

XAL.AE_HEADER_ID,

GJL.JE_HEADER_ID,

gjh.je_source,

gjl.description,

GL1.LEDGER_ID,

GCC.CODE_COMBINATION_ID)

UNION ALL

SELECT LEDGER_ID,

CODE_COMBINATION_ID,

JE_SOURCE,

PO_NUMBER,

JE_NAME,

BATCH_NAME,

PO_DESCRIPTION,

functional_amount,

transaction_currency,

functional_currency,

transaction_amount,

transaction_gl_date,

user_je_category_name,

supplier,

NVL (REF_DESCRIPTION, description) REF_DESCRIPTION,

DECODE (DOC_SEQUENCE_VALUE, NULL, description, DOC_SEQUENCE_VALUE)

transaction_reference,

NULL PROJECT_NUM

FROM ( SELECT (REGEXP_REPLACE (

REGEXP_REPLACE (

gjl.description,

'[^' || CHR (32) || '-' || CHR (126) || ']'

' '

))

description,

USER_JE_SOURCE_NAME "JE_SOURCE",

GL.LEDGER_ID,

GCC.CODE_COMBINATION_ID,

gcc.segment3 ac_code,

gcc.chart_of_accounts_id,

gjh.DOC_SEQUENCE_VALUE,

(SELECT DECODE (COUNT (1),

MAX (FFH.PARENT_FLEX_VALUE))

FROM FND_FLEX_VALUES_VL FFV,

FND_FLEX_VALUE_SETS FFS,

FND_FLEX_VALUE_HIERARCHIES FFH

WHERE FLEX_VALUE = GCC.SEGMENT3

AND FFS.FLEX_VALUE_SET_NAME =

'XXOLNG_GL_Accounts'

AND FFS.FLEX_VALUE_SET_ID =

FFV.FLEX_VALUE_SET_ID

AND FFV.SUMMARY_FLAG = 'N'

AND FFS.FLEX_VALUE_SET_ID =

FFH.FLEX_VALUE_SET_ID

AND GCC.SEGMENT3 BETWEEN FFH.CHILD_FLEX_VALUE_LOW

AND FFH.CHILD_FLEX_VALUE_HIGH)

Parent_Account,

apps.gl_flexfields_pkg.get_description_sql (

gcc.chart_of_accounts_id,

gcc.segment3

)

ac_name,

TRIM(TO_CHAR (

(SUM(NVL (gjl.accounted_dr, 0)

- NVL (gjl.accounted_cr, 0))),

'9999999999999999999999.00'

))

functional_amount,

gjh.currency_code transaction_currency,

'USD' functional_currency,

TRIM(TO_CHAR (

SUM(NVL (gjl.entered_dr, 0)

- NVL (gjl.entered_cr, 0)),

'9999999999999999999999.00'

))

transaction_amount,

TO_CHAR (gjh.default_effective_date, 'DD-MON-YYYY')

transaction_gl_date,

gjc.user_je_category_name,

gjh.period_name,

gcc.segment2 department_code,

apps.gl_flexfields_pkg.get_description_sql (

gcc.chart_of_accounts_id,

gcc.segment2

)

department_name,

gcc.segment4 loc_code,

apps.gl_flexfields_pkg.get_description_sql (

gcc.chart_of_accounts_id,

gcc.segment4

)

loc_description,

NULL transaction_reference,

NULL PO_NUMBER,

GJH.NAME JE_NAME,

gjb.NAME BATCH_NAME,

NULL PO_DESCRIPTION,

NULL supplier,

DECODE (gjh.je_category,

'Payroll',

GJH.JE_CATEGORY || '-' || GJH.PERIOD_NAME)

REF_DESCRIPTION

FROM gl_je_headers gjh,

gl_je_lines gjl,

gl_code_combinations_kfv gcc,

gl_ledgers gl,

gl_period_statuses per,

gl_je_categories gjc,

gl_je_sources gjs,

gl_je_batches gjb

WHERE 1 = 1

AND gjh.je_header_id = gjl.je_header_id

AND gjh.je_batch_id = gjb.je_batch_id

AND gl.ledger_id = gjh.ledger_id

AND gjh.status = 'P'

AND gjh.actual_flag = 'A'

AND gjh.je_category = gjc.je_category_name

AND gjs.JE_SOURCE_NAME = gjh.JE_SOURCE

AND gjc.LANGUAGE = 'US'

AND gjl.code_combination_id = gcc.code_combination_id

AND gjh.period_name = per.period_name

AND per.ledger_id = gjh.ledger_id

AND per.application_id = 101

AND (TRUNC (TO_DATE (per.start_date)) >=

DECODE (

SUBSTR (:p_period_name_from, 1, 3),

'Adj',

(SELECT start_date

FROM gl_period_statuses

WHERE APPLICATION_ID = 101

AND ledger_id = per.ledger_id

AND PERIOD_NAME = :p_period_name_from),

TRUNC (TO_DATE (:p_period_name_from, 'Mon-yy'))

))

AND (TRUNC (per.end_date) <=

DECODE (

SUBSTR (:p_period_name_to, 1, 3),

'Adj',

(SELECT end_date

FROM gl_period_statuses

WHERE APPLICATION_ID = 101

AND ledger_id = per.ledger_id

AND PERIOD_NAME = :p_period_name_to),

TRUNC(LAST_DAY (

TO_DATE (:p_period_name_to, 'Mon-yy')

))

AND gcc.segment3 = '5521005'

AND application_id = 101

AND NOT EXISTS

(SELECT 1

FROM GL_IMPORT_REFERENCES GIR1,

XLA_AE_LINES XAL

WHERE GIR1.JE_HEADER_ID = GJH.JE_HEADER_ID

AND GIR1.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID)

GROUP BY gcc.segment3,

gcc.chart_of_accounts_id,

gjh.currency_code,

gjh.default_effective_date,

gjc.user_je_category_name,

gjh.je_category,

gjh.period_name,

gcc.segment2,

gcc.segment4,

gjh.je_header_id,

gjh.je_source,

gl.NAME,

USER_JE_SOURCE_NAME,

GCC.CODE_COMBINATION_ID,

gjl.description,

gcc.chart_of_accounts_id,

gjh.DOC_SEQUENCE_VALUE,

GJH.NAME,

gjb.NAME,

GCC.CODE_COMBINATION_ID,

GL.LEDGER_ID)