Get Transaction detail for a Account which have different sources like (Inventory,Purchasing,Manual,Spreadsheet,,Recurring,Payables,Receivables) in general ledgers

CREATE OR REPLACE VIEW ACCOUNT_INQUIRY_V (

  transaction_date,

  po,

  item_code,

  item_description,

  item_category,

  supplier,

  qty,

  uom,

  unit_price,

  charge_account,

  rt_no,

  source,

  journal_entry,

  period_name,

  line_number

) AS

----------------- INVENTORY -----------------------------------

SELECT TO_CHAR(mmt.transaction_date,'dd-mon-yyyy')                                            Transaction_Date

     , pha.segment1                                                                           PO

     , To_char(msib.segment1)||'-'||To_char(msib.segment2)||'-'||To_char(msib.segment3)                                  Item_Code

     , msib.description                                                                       Item_Description

     , Decode(mcb.SEGMENT1,mcb.segment2,mcb.segment3,NULL,'NULL', mcb.segment1||'.'||mcb.segment2||'.'||mcb.segment3) Item_Category

     , pv.vendor_name                                                                         Supplier

     , mmt.primary_quantity                                                                   QTY

     , mmt.transaction_uom                                                                    UOM

     , gjl.accounted_dr                                                                       Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

     , rsh.receipt_num                                       RT_No

     , gjh.je_source                                         Source

     , gjh.name                                              Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')                     Period_Name

     , gjl.je_line_num                                        line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , mtl_material_transactions mmt

     , mtl_system_items_b msib

     , gl_code_combinations gcc

     , rcv_transactions rt

     , rcv_shipment_headers rsh

     , po_headers_all pha

     , po_vendors pv

     , rcv_shipment_lines rsl

     , mtl_categories_b  mcb

WHERE  gps.set_of_books_id     = gjl.set_of_books_id

  AND  gjl.period_name         = gps.period_name

  AND  gjl.code_combination_id = gcc.code_combination_id

  AND  gjh.je_header_id        = gjl.je_header_id

  AND  gjb.je_batch_id         = gjh.je_batch_id

  AND  mmt.transaction_id      = TO_NUMBER(TRIM(gjl.reference_3))

  AND  mmt.inventory_item_id   = msib.inventory_item_id

  AND  mmt.organization_id     = msib.organization_id

  AND  mmt.rcv_transaction_id  = rt.transaction_id(+)

  AND  rt.shipment_line_id     = rsl.shipment_line_id (+)

  AND  rsl.CATEGORY_ID         = mcb.CATEGORY_ID(+)

  AND  rt.shipment_header_id   = rsh.shipment_header_id (+)

  AND  rt.po_header_id         = pha.po_header_id (+)

  AND  pha.vendor_id           = pv.vendor_id (+)

  AND gps.period_name NOT LIKE '%ADJ%'

  AND  gps.application_id      = '&APPLICATION_ID'

--  AND  gjl.code_combination_id ='&CODE_COMBINATION_ID' /OPTIONAL/

--  AND  gps.period_name                  = '&PERIOD_NAME' /*period should be in (MON-YY)format */

  AND  gjh.je_source           = 'Inventory'

--  AND  gjl.je_LINE_NUM= &LINE_NUM  /*OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE*/

UNION

----------------- PURCHASING-----------------------------------

SELECT TO_CHAR(rt.transaction_date ,'DD-MON-YY') Transaction_Date

     , pha.segment1                              PO

     , To_Char(rsl.ITEM_ID)                      Item_Code

     , rsl.item_description                      Item_Description

     , Decode(mcb.SEGMENT1,mcb.segment2,mcb.segment3,NULL,'NULL', mcb.segment1||'.'||mcb.segment2||'.'||mcb.segment3) Item_Category

     , pv.vendor_name                            Supplier

     , rt.quantity                               Qty

     , rt.unit_of_measure                        UOM

     , gjl.accounted_dr                          Unit_Price

     , gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'|| gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7  Charge_Account

     , rsh.receipt_num                            RT_No

     , gjh.je_source                              Source

     , gjh.name                                   Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     ,gjl.je_line_num                            line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , gl_code_combinations gcc

     , po_headers_all pha

     , rcv_transactions rt

     , po_vendors pv

     , rcv_shipment_headers rsh

     , rcv_shipment_lines rsl

     ,mtl_categories_b  mcb

WHERE  gps.set_of_books_id              = gjl.set_of_books_id

  AND  gjb.je_batch_id                  = gjh.je_batch_id

  AND  gjh.je_header_id                 = gjl.je_header_id

  AND  gjl.period_name                  = gps.period_name

  AND  gjl.code_combination_id          = gcc.code_combination_id

  AND  TO_NUMBER(TRIM(gjl.reference_2)) = pha.po_header_id

  AND  TO_NUMBER(TRIM(gjl.reference_5)) = rt.transaction_id

  AND  rt.shipment_header_id            = rsh.shipment_header_id

  AND  pha.vendor_id                    = pv.vendor_id

  AND  rt.shipment_line_id              = rsl.shipment_line_id

  AND  rsl.CATEGORY_ID                   = mcb.CATEGORY_ID

  AND  gps.period_name NOT LIKE '%ADJ%'

  AND  gps.application_id      = '&APPLICATION_ID'

--  AND  gjl.code_combination_id ='&CODE_COMBINATION_ID' /OPTIONAL/

--  AND  gps.period_name                  = '&PERIOD_NAME' /*period should be in (MON-YY)format */

  AND  gjh.je_source           = 'Purchasing'

--  AND  gjl.je_LINE_NUM= &LINE_NUM  /*OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE*/

UNION

----------------- MANUAL -----------------------------------

SELECT NULL                 Transaction_Date

     , NULL                 PO

     , NULL                 Item_Code

     , NULL                 Item_Description

     , NULL                 Item_Category

     , NULL                 Supplier

     , NULL                 Qty

     , NULL                 UOM

     , NULL                 Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

     , NULL                 RT_No

     , gjh.je_source        Source

     , gjh.name             Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     ,gjl.je_line_num                            line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , gl_code_combinations gcc

WHERE  gjb.je_batch_id         = gjh.je_batch_id

  AND  gjh.je_header_id        = gjl.je_header_id

  AND  gjl.period_name         = gps.period_name

  AND  gps.set_of_books_id     = gjl.set_of_books_id

  AND  gjl.code_combination_id = gcc.code_combination_id

 AND  gps.period_name NOT LIKE '&PERIOD'  /*period should be in format MON-YY*/

  AND  gps.application_id      = '&APPLICATION_ID'

--  AND  gjl.code_combination_id ='&CODE_COMBINATION_ID' /OPTIONAL/

  AND  gjh.je_source           = 'Manual'

--  AND  gjl.je_LINE_NUM= &LINE_NUM  /* OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE */

UNION

----------------- SPREADSHEET -----------------------------------

SELECT NULL                 Transaction_Date

     , NULL                 PO

     , NULL                 Item_Code

     , NULL                 Item_Description

     , NULL                 Item_Category

     , NULL                 Supplier

     , NULL                 Qty

     , NULL                 UOM

     , NULL                 Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

     , NULL                 RT_No

     , gjh.je_source        Source

     , gjh.name             Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     ,gjl.je_line_num                            line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , gl_code_combinations gcc

WHERE  gjb.je_batch_id         = gjh.je_batch_id

  AND  gjh.je_header_id        = gjl.je_header_id

  AND  gjl.period_name         = gps.period_name

  AND  gps.set_of_books_id     = gjl.set_of_books_id

  AND  gjl.code_combination_id = gcc.code_combination_id

  AND  gps.period_name NOT LIKE '%ADJ%'

  AND  gps.application_id      = &APPLICATION_ID

--  AND  gjl.code_combination_id = '&CODE_COMBINATION_ID' /OPTIONAL/

--  AND  gps.period_name         = '&PERIOD_NAME'

  AND  gjh.je_source           = 'Spreadsheet'

UNION

----------------- RECURRING -----------------------------------

SELECT NULL                 Transaction_Date

     , NULL                 PO

     , NULL                 Item_Code

     , NULL                 Item_Description

     , NULL                 Item_Category

     , NULL                 Supplier

     , NULL                 Qty

     , NULL                 UOM

     , NULL                 Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

     , NULL                 RT_No

     , gjh.je_source        Source

     , gjh.name             Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     ,gjl.je_line_num                            line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , gl_code_combinations gcc

WHERE  gjb.je_batch_id         = gjh.je_batch_id

  AND  gjh.je_header_id        = gjl.je_header_id

  AND  gjl.period_name         = gps.period_name

  AND  gps.set_of_books_id     = gjl.set_of_books_id

  AND  gjl.code_combination_id = gcc.code_combination_id

  AND  gps.period_name NOT LIKE '%ADJ%'

  AND  gps.application_id      = &APPLICATION_ID

--  AND  gjl.code_combination_id = '&CODE_COMBINATION_ID' /* OPTIONAL */

--  AND  gps.period_name         = '&PERIOD_NAME' /*period should be in (MON-YY)format */

  AND  gjh.je_source           = 'Recurring'

--  AND  gjl.je_LINE_NUM=&LINE_NUM  /*OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE*/

UNION

----------------- PAYABLES -----------------------------------

SELECT  TO_CHAR(rt.transaction_date ,'DD-MON-YY') Transaction_Date

, pha.segment1 PO

, To_Char(rsl.ITEM_ID) Item_Code

, rsl.item_description Item_Description

, Decode(mcb.SEGMENT1,mcb.segment2,mcb.segment3,NULL,'NULL', mcb.segment1||'.'||mcb.segment2||'.'||mcb.segment3) Item_Category

, pv.vendor_name Supplier

, rt.quantity Qty

, rt.unit_of_measure UOM

, rt.po_unit_price Unit_Price

, gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'|| gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7 Charge_Account

, rsh.receipt_num

, gjh.je_source Source

, gjh.name Journal_Entry

, to_date(gps.PERIOD_NAME,'MON-YY') Period_Name

, gjl.je_line_num line_number

FROM gl_period_statuses GPS

, gl_je_lines GJL

, gl_je_headers GJH

, gl_je_batches GJB

, gl_code_combinations GCC

, ap_invoices_all AIA

, AP_INVOICE_DISTRIBUTIONS_ALL AIDA

, PO_VENDORS PV

, PO_DISTRIBUTIONS_ALL PDA

, PO_HEADERS_ALL PHA

, RCV_TRANSACTIONS RT

, RCV_SHIPMENT_LINES RSL

, RCV_SHIPMENT_HEADERS RSH

, mtl_categories_b mcb

WHERE gjb.je_batch_id = gjh.je_batch_id

AND gjh.je_header_id = gjl.je_header_id

AND gjl.period_name = gps.period_name

AND gps.set_of_books_id = gjl.set_of_books_id

AND gjl.code_combination_id = gcc.code_combination_id

--AND aia.invoice_id=aida.invoice_id

AND TO_NUMBER(TRIM(gjl.reference_2))= aia.invoice_id

AND TO_NUMBER(TRIM(gjl.reference_2))= aida.invoice_id

AND TO_NUMBER(TRIM(gjl.reference_3))= aida.distribution_line_number

AND aia.vendor_id = pv.vendor_id

AND aida.po_distribution_id = pda.po_distribution_id

AND aida.RCV_TRANSACTION_ID = rt.TRANSACTION_ID

AND pha.po_header_id=rt.po_header_id

AND rt.shipment_line_id = rsl.shipment_line_id

AND rsl.category_id = mcb.category_id

AND rt.shipment_header_id = rsh.shipment_header_id

AND gps.application_id ='&APPLICATION_ID'

--AND GJL.code_combination_id = '&CODE_COMBINATION_ID' /OPTIONAL/

--AND GPS.period_name = '&PERIOD_NAME' /*period should be in (MON-YY)format */

AND GJH.je_source = 'Payables'

UNION

----------------- RECEIVABLES -----------------------------------

SELECT (SELECT TO_CHAR(max(rcta.trx_date) ,'DD-MON-YY') 

          FROM ra_customer_trx_all rcta

         WHERE rcta.trx_number = GJL.reference_4) Transaction_Date

     , NULL                 PO

     , NULL                 Item_Code

     , NULL                 Item_Description

     , NULL                 Item_Category

     , NULL                 Supplier

     , NULL                 Qty

     , NULL                 UOM

     , NULL                 Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

    -- , rcta.trx_number

     , GJL.reference_4 trx_number

     , gjh.je_source        Source

     , gjh.name             Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     , gjl.je_line_num                            line_number

FROM gl_period_statuses GPS

    ,gl_je_lines GJL

    ,gl_je_headers GJH

    ,gl_je_batches GJB

    ,gl_code_combinations GCC

  --  ,ra_customer_trx_all rcta

WHERE  GJB.je_batch_id      = GJH.je_batch_id

AND GJH.je_header_id        = GJL.je_header_id

AND gps.set_of_books_id     = gjl.set_of_books_id

AND GJL.period_name         = GPS.period_name

AND GJL.code_combination_id = GCC.code_combination_id

--AND TO_NUMBER(TRIM(GJL.reference_2)) = rcta.CUSTOMER_TRX_ID(+)

AND gps.period_name NOT LIKE '%ADJ%'

AND gps.application_id      = '&APPLICATION_ID'

AND GJH.je_source           = 'Receivables'

AND LENGTH(TRIM(TRANSLATE (GJL.reference_2, '0123456789',' '))) IS NULL

--AND GJL.code_combination_id = '&CODE_COMBINATION_ID' /OPTIONAL/

--AND GPS.period_name         = '&PERIOD_NAME' /*period should be in (MON-YY)format */

--AND gjl.je_line_num  = '&LINE_NUM'  /*OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE*/;

Query to test the View

SELECT * FRoM ACCOUNT_INQUIRY_V WHERE CHARGE_ACCOUNT='&ACCOUNT_NUMBER'

AND period_name LIKE '&PERIOD_NAME' /*period should be in (MON-YY)format */

AND source = '&SOURCE'