CMR_ALL_TRANSACTIONS_V

Details

• Schema: FUSION

• Object owner: CMR

• Object type: VIEW

Columns

Name

TRANSACTION_ID SOURCE_TABLE PROFIT_CENTER_BUSINESS_UNIT_ID BILL_TO_BUSINESS_UNIT_ID INVENTORY_ORG_ID ITEM_ORGANIZATION_ID SHIP_FROM_INV_ORG_ID SOURCE_DOCUMENT_CODE SOURCE_DOCUMENT_NUMBER DOCUMENT_TYPE_CODE TRANSACTION_NUMBER TRANSACTION_LINE_NUMBER RECEIPT_NUMBER RECEIPT_LINE_NUMBER SHIPMENT_NUMBER SHIPMENT_LINE_NUMBER INVOICE_NUMBER INVOICE_LINE_NUMBER TRANSACTION_DATE TRANSACTION_TYPE POSTED_FLAG ERROR_CODE DESTINATION_TYPE_CODE INVENTORY_ITEM_ID SOURCE_DOC_QTY SOURCE_DOC_UOM_CODE TRANSACTION_QTY TRANSACTION_UOM_CODE PRIMARY_QTY PRIMARY_UOM_CODE SECONDARY_QTY SECONDARY_UOM_CODE TRANSACTION_AMT RCV_CONVERSION_DATE RCV_CONVERSION_RATE CONSIGNED_FLAG LCM_PROCESSED_FLAG TAX_PROCESSED_FLAG BUDGETARY_CONTROL_FLAG ENCUMBRANCE_ACCOUNTING_FLAG MANUAL_RECEIPT_REQD_FLAG CMR_PO_DISTRIBUTION_ID CMR_RCV_TRANSACTION_ID CMR_PO_LINE_LOCATION_ID PARENT_TRANSACTION_ID CMR_ROOT_RECEIVE_TXN_ID CMR_ROOT_DELIVER_TXN_ID PO_DISTRIBUTION_ID PO_LINE_LOCATION_ID TRADE_EVENT_ID INV_SHIPPING_TRANSACTION_ID RCV_TRANSACTION_ID MATCH_OPTION AP_INVOICE_DIST_ID CMR_AP_INVOICE_DIST_ID CMR_AP_INVOICE_LINE_ID CONSUMPTION_ADVICE_HEADER_ID CONSUMPTION_ADVICE_LINE_ID CORRECTED_INVOICE_DIST_ID DISTRIBUTION_MATCH_TYPE INVOICE_TYPE INVOICE_DISTRIBUTION_TYPE FINAL_MATCH_FLAG INVOICE_SOURCE INVOICE_CURRENCY_CODE RA_INVOICE_TRANSACTION_FLAG INVOICE_BASE_AMOUNT INVOICE_LINE_TYPE LCM_ENABLED_FLAG LCM_ASSOCIATION_PROCESSED_FLAG SELF_ASSESSED_FLAG INCLUSIVE_FLAG TAX_POINT_BASIS ACCRUAL_CLR_RULE_NAME ACCRUAL_CLR_REASON_DESC ACCRUAL_RECEIVED_QTY ACCRUAL_INVOICE_QTY CMR_ACCRUAL_AMT AP_ACCRUAL_AMT LE_TIMEZONE_CODE CONVERT_TO_LETZ FISCAL_DOC_HEADER_ID FISCAL_DOC_LINE_ID FISCAL_DOC_SCHEDULE_ID FISCAL_DOC_ACCESS_KNUM REF_FISCAL_DOC_HEADER_ID REF_FISCAL_DOC_LINE_ID REF_FISCAL_DOC_SCHEDULE_ID REF_FISCAL_DOC_ACCESS_KNUM CMR_RCV_TRANSACTION_ID_DERIVED CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN

Query

SQL_Statement

SELECT ct.transaction_id, 'CMR_TRANSACTIONS' source_table, ct.profit_center_business_unit_id, ct.bill_to_business_unit_id, ct.ship_to_organization_id inventory_org_id, /* Difference between Inventory and Shipping Inventory Org - Both are same where the goods are delivered */ ct.ship_to_organization_id item_organization_id, ct.ship_from_inv_org_id, ct.source_document_code, decode(ct.source_document_code, 'PO', cpod.po_number, 'INVENTORY', ct.shipment_number, 'TRANSFER ORDER', ctol.transfer_order_number, NULL) source_document_number, /* bug 30556974 */ decode(ct.source_document_code, 'PO', 'Purchase Order', 'TRANSFER ORDER', 'Transfer Order', 'RMA', 'RMA', 'INVENTORY', 'Inter Organization Transfer', ct.source_document_code) document_type_code, /* 'Receipts' */ ct.receipt_number transaction_number, ct.receipt_line_number transaction_line_number, nvl(ct.receipt_number,( SELECT crt.receipt_number FROM cmr_rcv_transactions crt WHERE crt.cmr_po_line_location_id = ct.cmr_po_line_location_id AND ROWNUM = 1 )) receipt_number, /* bug 30556974 */ ct.receipt_line_number, ct.shipment_number, ct.shipment_line_number, NULL invoice_number, NULL invoice_line_number, ct.transaction_date, ct.transaction_type, decode(cpod.accrue_on_receipt_flag, 'N', 'X', ct.posted_flag) AS posted_flag, ct.error_code, ct.destination_type_code destination_type_code, ct.inventory_item_id, ct.source_doc_qty, ct.source_doc_uom_code, ct.transaction_qty, ct.transaction_uom_code, ct.primary_qty, ct.primary_uom_code, ct.secondary_transaction_qty secondary_qty, ct.secondary_uom_code, ct.transaction_amt, ct.currency_conversion_date rcv_conversion_date, ct.currency_conversion_rate rcv_conversion_rate, ct.consigned_flag, ct.lcm_processed_flag, ct.tax_processed_flag, ct.budgetary_control_flag, ct.encumbrance_accounting_flag, ct.manual_receipt_reqd_flag, ct.cmr_po_distribution_id, ct.cmr_rcv_transaction_id, ct.cmr_po_line_location_id, ct.parent_transaction_id, ct.cmr_root_receive_txn_id, ct.cmr_root_deliver_txn_id, ct.po_distribution_id, ct.po_line_location_id, ct.trade_event_id, ct.inv_shipping_transaction_id, ct.rcv_transaction_id, cpod.match_option, NULL ap_invoice_dist_id, NULL cmr_ap_invoice_dist_id, NULL cmr_ap_invoice_line_id, NULL consumption_advice_header_id, NULL consumption_advice_line_id, NULL corrected_invoice_dist_id, NULL distribution_match_type, NULL invoice_type, NULL invoice_distribution_type, NULL final_match_flag, NULL invoice_source, NULL invoice_currency_code, NULL ra_invoice_transaction_flag, NULL invoice_base_amount, NULL invoice_line_type, NULL lcm_enabled_flag, NULL lcm_association_processed_flag, NULL self_assessed_flag, NULL inclusive_flag, NULL tax_point_basis, NULL accrual_clr_rule_name, NULL accrual_clr_reason_desc, NULL accrual_received_qty, NULL accrual_invoice_qty, NULL cmr_accrual_amt, NULL ap_accrual_amt, ct.le_timezone_code, 1 convert_to_letz, ct.fiscal_doc_header_id, ct.fiscal_doc_line_id, ct.fiscal_doc_schedule_id, ct.fiscal_doc_access_knum, ct.ref_fiscal_doc_header_id, ct.ref_fiscal_doc_line_id, ct.ref_fiscal_doc_schedule_id, ct.ref_fiscal_doc_access_knum, decode(cpod.match_option, 'R', ct.cmr_rcv_transaction_id, - 1) cmr_rcv_transaction_id_derived, ct.created_by, ct.creation_date, ct.last_updated_by, ct.last_update_date, ct.last_update_login FROM cmr_transactions ct, cmr_purchase_order_dtls cpod, cst_transfer_order_lines ctol WHERE ct.cmr_po_distribution_id = cpod.cmr_po_distribution_id (+) AND nvl(cpod.active_flag, 'Y') = 'Y' AND ct.external_system_reference = ctol.external_system_reference (+) AND ct.transfer_order_line_id = ctol.external_system_ref_id (+) UNION ALL SELECT cte.trade_event_id transaction_id, 'CST_TRADE_EVENTS' source_table, cte.business_unit_id profit_center_business_unit_id, cte.bill_to_bu_id bill_to_business_unit_id, cte.inventory_org_id inventory_org_id, ( SELECT master_organization_id FROM inv_org_parameters op WHERE op.organization_id = cte.inventory_org_id ) item_organization_id, cte.prior_inventory_org_id ship_from_inv_org_id, nvl(cte.txn_source_ref_doc_type, cte.txn_source_doc_type) source_document_code, nvl(cte.txn_source_ref_doc_number, cte.txn_source_doc_number) source_document_number, decode(cte.txn_source_doc_type, 'PO', 'Purchase Order', 'OO', 'Sales Order', 'IOT', 'InterOrganization Transfer', 'SO', 'Sales Order', 'SHIP_REF', 'Shipment Reference', 'TO', 'Transfer Order', cte.txn_source_doc_type) document_type_code, /*'Trade Events' */ cte.txn_source_doc_number transaction_number, NULL transaction_line_number, decode(cte.ownership_change_doc_type, 'RCV', cte.ownership_change_doc_number) receipt_number, NULL receipt_line_number, decode(cte.ownership_change_event_type, 'ASN_FROM_SUPPLIER', cte.ownership_change_doc_number, decode(cte.ownership_change_doc_type, 'SHIP_REF', cte.ownership_change_doc_number)) shipment_number, /* populating shipment number for valid cases */ NULL shipment_line_number, decode(cte.ownership_change_event_type, 'AP_INVOICE_MATCH', cte.ownership_change_doc_number) invoice_number, NULL invoice_line_number, cte.transaction_date, CASE WHEN CTE.OWNERSHIP_CHANGE_EVENT_TYPE in ('TRANSFER_TO_CONSIGNED','TRANSFER_TO_OWNED') THEN 'Consigned Receipt Consumption' ELSE (CASE TRANSACTION_TYPE WHEN 'RECEIPT_ACCRUAL' THEN CASE WHEN CTE.consigned_flag = 'Y' THEN 'Consigned Trade Receipt Accrual' ELSE 'Trade Receipt Accrual' END WHEN 'RETURN_ACCRUAL' THEN CASE WHEN CTE.consigned_flag = 'Y' THEN 'Consigned Trade Return Accrual' ELSE 'Trade Return Accrual' END WHEN 'INTRANSIT_RETURN' THEN 'Trade In-Transit Return' WHEN 'INTRANSIT_ISSUE' THEN 'Trade In-Transit Issue' WHEN 'INTRANSIT_RECEIPT'THEN 'Trade In-Transit Receipt' WHEN 'INTRANSIT_RETURN_RECEIPT' THEN 'Trade In-Transit Return Receipt' ELSE NULL END) END AS transaction_type, cte.process_by_ra_flag posted_flag, cte.error_code, NULL destination_type_code, cte.inventory_item_id, cte.source_doc_quantity source_doc_qty, cte.source_doc_uom_code source_doc_uom_code, cte.transaction_qty, cte.transaction_uom_code, cte.primary_qty, cte.primary_uom_code, cte.secondary_transaction_qty secondary_qty, cte.secondary_uom_code, NULL transaction_amt, NULL rcv_conversion_date, NULL rcv_conversion_rate, cte.consigned_flag, NULL lcm_processed_flag, cte.tax_calculated_flag tax_processed_flag, NULL budgetary_control_flag, NULL encumbrance_accounting_flag, NULL manual_receipt_reqd_flag, NULL cmr_po_distribution_id, NULL cmr_rcv_transaction_id, NULL cmr_po_line_location_id, NULL parent_transaction_id, NULL cmr_root_receive_txn_id, NULL cmr_root_deliver_txn_id, NULL po_distribution_id, cte.po_line_location_id, cte.trade_event_id, cte.inv_transaction_id inv_shipping_transaction_id, cte.rcv_transaction_id, NULL match_option, NULL ap_invoice_dist_id, NULL cmr_ap_invoice_dist_id, NULL cmr_ap_invoice_line_id, NULL consumption_advice_header_id, NULL consumption_advice_line_id, NULL corrected_invoice_dist_id, NULL distribution_match_type, NULL invoice_type, NULL invoice_distribution_type, NULL final_match_flag, NULL invoice_source, NULL invoice_currency_code, NULL ra_invoice_transaction_flag, NULL invoice_base_amount, NULL invoice_line_type, NULL lcm_enabled_flag, NULL lcm_association_processed_flag, NULL self_assessed_flag, NULL inclusive_flag, NULL tax_point_basis, NULL accrual_clr_rule_name, NULL accrual_clr_reason_desc, NULL accrual_received_qty, NULL accrual_invoice_qty, NULL cmr_accrual_amt, NULL ap_accrual_amt, NULL le_timezone_code, 1 convert_to_letz, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, - 1 cmr_rcv_transaction_id_derived, cte.created_by, cte.creation_date, cte.last_updated_by, cte.last_update_date, cte.last_update_login FROM cst_trade_events cte WHERE cte.transaction_type IN ( 'RECEIPT_ACCRUAL', 'RETURN_ACCRUAL' ) OR ( cte.transaction_type IN ( 'INTRANSIT_RETURN', 'INTRANSIT_ISSUE', 'INTRANSIT_RECEIPT', 'INTRANSIT_RETURN_RECEIPT' ) AND cte.inventory_item_id IS NULL ) UNION ALL SELECT caid.cmr_ap_invoice_dist_id transaction_id, 'CMR_AP_INVOICE_DTLS' source_table, nvl(cpod.trade_org_profit_center_bu_id, cpod.deliver_to_business_unit_id) profit_center_business_unit_id, caid.invoicing_business_unit_id bill_to_business_unit_id, cpod.deliver_to_inventory_org_id inventory_org_id, cpod.deliver_to_inventory_org_id item_organization_id, NULL ship_from_inv_org_id, 'Purchase Order' source_document_code, cpod.po_number source_document_number, 'Invoices' document_type_code, caid.invoice_number transaction_number, to_number(caid.invoice_line_number) transaction_line_number, NULL receipt_number, NULL receipt_line_number, NULL shipment_number, NULL shipment_line_number, caid.invoice_number, caid.invoice_line_number, caid.accounting_date transaction_date, 'Invoice Price Adjustment' transaction_type, decode(cpod.accrue_on_receipt_flag, 'N', 'X', decode(caid.processed_by_ra_flag, 'D', 'Y', caid.processed_by_ra_flag)) posted_flag, /* Y and D means Invoice Variances are processed */ NULL error_code, cpod.destination_type_code destination_type_code, cpod.inventory_item_id inventory_item_id, caid.invoice_qty_in_po_uom source_doc_qty, cpod.uom_code source_doc_uom_code, caid.invoice_qty transaction_qty, caid.unit_of_measure transaction_uom_code, caid.invoice_qty_in_primary_uom primary_qty, NULL primary_uom_code, NULL secondary_qty, NULL secondary_uom_code, caid.invoice_amt transaction_amt, caid.currency_conversion_date rcv_conversion_date, caid.currency_conversion_rate rcv_conversion_rate, NULL consigned_flag, NULL lcm_processed_flag, NULL tax_processed_flag, NULL budgetary_control_flag, NULL encumbrance_accounting_flag, NULL manual_receipt_reqd_flag, caid.cmr_po_distribution_id cmr_po_distribution_id, caid.cmr_rcv_transaction_id cmr_rcv_transaction_id, cpod.cmr_po_line_location_id cmr_po_line_location_id, NULL parent_transaction_id, NULL cmr_root_receive_txn_id, NULL cmr_root_deliver_txn_id, caid.po_distribution_id po_distribution_id, cpod.po_line_location_id po_line_location_id, NULL trade_event_id, NULL inv_shipping_transaction_id, caid.rcv_transaction_id rcv_transaction_id, cpod.match_option, caid.external_system_ref_id ap_invoice_dist_id, caid.cmr_ap_invoice_dist_id, caid.cmr_ap_invoice_line_id, caid.consumption_advice_header_id, caid.consumption_advice_line_id, caid.corrected_invoice_dist_id, caid.distribution_match_type, caid.invoice_type, caid.invoice_distribution_type, caid.final_match_flag, caid.invoice_source, caid.currency_code invoice_currency_code, decode(cpod.accrue_on_receipt_flag, 'Y', decode(caid.invoice_distribution_type, 'ERV', 'Y', 'TRV', 'Y', 'TIPV', 'Y', 'IPV', 'Y', 'TERV', 'Y', 'NONREC_TAX', decode(caid.self_assessed_flag, 'Y', 'Y', 'N'), 'N'), 'X') ra_invoice_transaction_flag, caid.invoice_base_amount invoice_base_amount, caid.invoice_line_type, caid.lcm_enabled_flag, caid.lcm_association_processed_flag, caid.self_assessed_flag, caid.inclusive_flag, caid.tax_point_basis, NULL accrual_clr_rule_name, NULL accrual_clr_reason_desc, NULL accrual_received_qty, NULL accrual_invoice_qty, NULL cmr_accrual_amt, NULL ap_accrual_amt, caid.le_timezone_code, 0 convert_to_letz, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, decode(cpod.match_option, 'R', caid.cmr_rcv_transaction_id, - 1) cmr_rcv_transaction_id_derived, caid.created_by, caid.creation_date, caid.last_updated_by, caid.last_update_date, caid.last_update_login FROM cmr_ap_invoice_dtls caid, cmr_purchase_order_dtls cpod WHERE caid.cmr_po_distribution_id = cpod.cmr_po_distribution_id AND cpod.active_flag = 'Y' UNION ALL SELECT caca.accrual_clr_id transaction_id, 'CMR_ACCRUAL_CLR_AMTS' source_table, caca.sold_to_business_unit_id profit_center_business_unit_id, caca.bill_to_business_unit_id bill_to_business_unit_id, caca.inventory_organization_id inventory_org_id, caca.inventory_organization_id item_organization_id, NULL ship_from_inv_org_id, NULL source_document_code, cpod.po_number source_document_number, 'Purchase Order' document_type_code, NULL transaction_number, NULL transaction_line_number, NULL receipt_number, NULL receipt_line_number, NULL shipment_number, NULL shipment_line_number, NULL invoice_number, NULL invoice_line_number, caca.latest_txn_date transaction_date, caca.transaction_type_code transaction_type, 'Y' posted_flag, NULL error_code, caca.destination_type_code destination_type_code, caca.inventory_item_id inventory_item_id, NULL source_doc_qty, NULL source_doc_uom_code, NULL transaction_qty, NULL transaction_uom_code, NULL primary_qty, NULL primary_uom_code, NULL secondary_qty, NULL secondary_uom_code, caca.accrual_clr_amt transaction_amt, caca.currency_conversion_date conversion_date, caca.currency_conversion_rate conversion_rate, NULL consigned_flag, NULL lcm_processed_flag, NULL tax_processed_flag, NULL budgetary_control_flag, NULL encumbrance_accounting_flag, NULL manual_receipt_reqd_flag, caca.cmr_po_distribution_id cmr_po_distribution_id, NULL cmr_rcv_transaction_id, caca.cmr_po_line_location_id cmr_po_line_location_id, NULL parent_transaction_id, NULL cmr_root_receive_txn_id, NULL cmr_root_deliver_txn_id, to_number(cpod.external_system_ref_id) po_distribution_id, cpod.po_line_location_id po_line_location_id, NULL trade_event_id, NULL inv_shipping_transaction_id, NULL rcv_transaction_id, cpod.match_option, NULL ap_invoice_dist_id, NULL cmr_ap_invoice_dist_id, NULL cmr_ap_invoice_line_id, NULL consumption_advice_header_id, NULL consumption_advice_line_id, NULL corrected_invoice_dist_id, NULL distribution_match_type, NULL invoice_type, NULL invoice_distribution_type, NULL final_match_flag, NULL invoice_source, NULL invoice_currency_code, NULL ra_invoice_transaction_flag, NULL invoice_base_amount, NULL invoice_line_type, NULL lcm_enabled_flag, NULL lcm_association_processed_flag, NULL self_assessed_flag, NULL inclusive_flag, NULL tax_point_basis, caca.accrual_clr_rule_name, caca.accrual_clr_reason_desc, caca.received_qty accrual_received_qty, caca.invoice_qty accrual_invoice_qty, - cmr_accrual_amt, caca.ap_accrual_amt, caca.le_timezone_code, 0 convert_to_letz, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, - 1 cmr_rcv_transaction_id_derived, caca.created_by, caca.creation_date, caca.last_updated_by, caca.last_update_date, caca.last_update_login FROM cmr_accrual_clr_amts caca, cmr_purchase_order_dtls cpod WHERE caca.cmr_po_distribution_id = cpod.cmr_po_distribution_id AND cpod.active_flag = 'Y' UNION ALL SELECT cre.accounting_event_id transaction_id, 'CMR_RCV_EVENTS' source_table, cre.business_unit_id profit_center_business_unit_id, cre.bill_to_business_unit_id bill_to_business_unit_id, cre.inventory_org_id inventory_org_id, cre.inventory_org_id item_organization_id, NULL ship_from_inv_org_id, cre.event_source source_document_code, cre.source_doc_number source_document_number, 'Purchase Order' document_type_code, NULL transaction_number, NULL transaction_line_number, NULL receipt_number, NULL receipt_line_number, NULL shipment_number, NULL shipment_line_number, NULL invoice_number, NULL invoice_line_number, cre.transaction_date transaction_date, cre.transaction_type_code, 'Y' posted_flag, cre.error_code error_code, cre.destination_type_code destination_type_code, cre.inventory_item_id inventory_item_id, cre.source_doc_qty source_doc_qty, cre.source_doc_uom_code source_doc_uom_code, cre.transaction_qty transaction_qty, cre.transaction_uom_code transaction_uom_code, cre.primary_qty primary_qty, cre.primary_uom_code primary_uom_code, cre.secondary_transaction_qty secondary_qty, cre.secondary_uom_code, cre.transaction_amt transaction_amt, cre.exchange_date rcv_conversion_date, cre.exchange_rate rcv_conversion_rate, cre.consigned_flag consigned_flag, NULL lcm_processed_flag, NULL tax_processed_flag, cre.budgetary_control_flag budgetary_control_flag, cre.encumbrance_accounting_flag encumbrance_accounting_flag, NULL manual_receipt_reqd_flag, cre.cmr_po_distribution_id cmr_po_distribution_id, cre.cmr_rcv_transaction_id cmr_rcv_transaction_id, cre.cmr_po_line_location_id cmr_po_line_location_id, NULL parent_transaction_id, NULL cmr_root_receive_txn_id, NULL cmr_root_deliver_txn_id, to_number(cpod.external_system_ref_id) po_distribution_id, cpod.po_line_location_id po_line_location_id, cre.trade_event_id trade_event_id, cre.inv_shipping_transaction_id inv_shipping_transaction_id, NULL rcv_transaction_id, cpod.match_option, NULL ap_invoice_dist_id, NULL cmr_ap_invoice_dist_id, NULL cmr_ap_invoice_line_id, NULL consumption_advice_header_id, NULL consumption_advice_line_id, NULL corrected_invoice_dist_id, NULL distribution_match_type, NULL invoice_type, NULL invoice_distribution_type, NULL final_match_flag, NULL invoice_source, NULL invoice_currency_code, NULL ra_invoice_transaction_flag, NULL invoice_base_amount, NULL invoice_line_type, NULL lcm_enabled_flag, NULL lcm_association_processed_flag, NULL self_assessed_flag, NULL inclusive_flag, NULL tax_point_basis, NULL accrual_clr_rule_name, NULL accrual_clr_reason_desc, NULL accrual_received_qty, NULL accrual_invoice_qty, NULL cmr_accrual_amt, NULL ap_accrual_amt, cre.le_timezone_code, 1 convert_to_letz, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, decode(cpod.match_option, 'R', cre.cmr_rcv_transaction_id, - 1) cmr_rcv_transaction_id_derived, cre.created_by, cre.creation_date, cre.last_updated_by, cre.last_update_date, cre.last_update_login FROM cmr_rcv_events cre, cmr_purchase_order_dtls cpod WHERE event_class_code = 'PERIOD_END_ACCRUAL' AND cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id AND cpod.active_flag = 'Y' UNION ALL SELECT nvl(cre.accounting_event_id, - cpod.cmr_po_distribution_id) transaction_id, 'CMR_RCV_EVENTS' source_table, decode(cpod.sfo_primary_trade_relation_id, NULL, cpod.deliver_to_business_unit_id, cpod.trade_org_profit_center_bu_id) profit_center_business_unit_id, cpod.bill_to_business_unit_id bill_to_business_unit_id, decode(cpod.sfo_primary_trade_relation_id, NULL, cpod.deliver_to_inventory_org_id, cpod.trade_organization_id) inventory_org_id, cpod.deliver_to_inventory_org_id item_organization_id, NULL ship_from_inv_org_id, 'Purchase Order' source_document_code, cpod.po_number source_document_number, cpod.event_type document_type_code, cpod.po_number transaction_number, to_number(cpod.line_number) transaction_line_number, NULL receipt_number, NULL receipt_line_number, NULL shipment_number, NULL shipment_line_number, NULL invoice_number, NULL invoice_line_number, cpod.event_date transaction_date, 'Retroactive Price Adjustment' transaction_type, /* change it later to transaction type code, being done for OAL . Changed to correct txn type */ decode(cpod.processed_by_ra_flag, 'D', 'Y', cpod.processed_by_ra_flag) posted_flag, NULL error_code, cpod.destination_type_code destination_type_code, cpod.inventory_item_id inventory_item_id, cpod.quantity_ordered source_doc_qty, cpod.uom_code source_doc_uom_code, cpod.quantity_ordered transaction_qty, cpod.uom_code transaction_uom_code, NULL primary_qty, NULL primary_uom_code, NULL secondary_qty, NULL secondary_uom_code, cpod.amount_ordered transaction_amt, cpod.currency_conversion_date rcv_conversion_date, cpod.currency_conversion_rate rcv_conversion_rate, cpod.consigned_flag consigned_flag, NULL lcm_processed_flag, NULL tax_processed_flag, NULL budgetary_control_flag, NULL encumbrance_accounting_flag, NULL manual_receipt_reqd_flag, cpod.cmr_po_distribution_id cmr_po_distribution_id, NULL cmr_rcv_transaction_id, cpod.cmr_po_line_location_id cmr_po_line_location_id, NULL parent_transaction_id, NULL cmr_root_receive_txn_id, NULL cmr_root_deliver_txn_id, to_number(cpod.external_system_ref_id) po_distribution_id, cpod.po_line_location_id po_line_location_id, NULL trade_event_id, NULL inv_shipping_transaction_id, NULL rcv_transaction_id, cpod.match_option, NULL ap_invoice_dist_id, NULL cmr_ap_invoice_dist_id, NULL cmr_ap_invoice_line_id, NULL consumption_advice_header_id, NULL consumption_advice_line_id, NULL corrected_invoice_dist_id, NULL distribution_match_type, NULL invoice_type, NULL invoice_distribution_type, NULL final_match_flag, NULL invoice_source, NULL invoice_currency_code, NULL ra_invoice_transaction_flag, NULL invoice_base_amount, NULL invoice_line_type, NULL lcm_enabled_flag, NULL lcm_association_processed_flag, NULL self_assessed_flag, NULL inclusive_flag, NULL tax_point_basis, NULL accrual_clr_rule_name, NULL accrual_clr_reason_desc, NULL accrual_received_qty, NULL accrual_invoice_qty, NULL cmr_accrual_amt, NULL ap_accrual_amt, cpod.le_timezone_code, 1 convert_to_letz, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, decode(cpod.match_option, 'R', cre.cmr_rcv_transaction_id, - 1) cmr_rcv_transaction_id_derived, cre.created_by, cre.creation_date, cre.last_updated_by, cre.last_update_date, cre.last_update_login FROM cmr_rcv_events cre, cmr_purchase_order_dtls cpod WHERE cpod.cmr_po_distribution_id = cre.cmr_po_distribution_id (+) AND cpod.event_date = cre.event_date (+) AND /*Bug# 32829156 Changed TRANSACTION_DATE to EVENT_DATE from CRE as the transaction date was truncated for certain txns ad added below table name condition to restrict data only to retro price changes*/ cre.event_txn_table_name (+) = 'CMR_RCV_EVENTS' AND cpod.purchase_basis = 'GOODS' AND cpod.event_type = 'Retroactive Price Update' AND nvl(cpod.consigned_flag, 'N') = 'N' AND cpod.accrue_on_receipt_flag = 'Y' /* only accrue at receipt Price changes are processed */