Oracle Concurrent Request Error Script

SELECT a.request_id "Req Id" ,
  a.phase_code,
  a.status_code ,
  actual_start_date ,
  actual_completion_date ,
  c.concurrent_program_name
  || ': '
  || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,
  APPLSYS.fnd_concurrent_processes b,
  applsys.fnd_concurrent_queues q ,
  APPLSYS.fnd_concurrent_programs c ,
  APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager   = b.concurrent_process_id
AND a.concurrent_program_id   = c.concurrent_program_id
AND a.program_application_id  = c.application_id
AND a.status_code             = 'E'
AND a.phase_code              = 'C'
AND b.queue_application_id    = q.application_id
AND b.concurrent_queue_id     = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE              = 'US'
ORDER BY 5 DESC;

Concurrent Program Queries

SELECT request_id,
  DECODE(phase_code,'C','Completed',phase_code)phase_code,
  DECODE(status_code, 'D', 'Cancelled' , 'E', 'Error', 'G', 'Warning', 'H', 'On Hold', 'T', 'Terminating', 'M', 'No Manager', 'X', 'Terminated', 'C', 'Normal', status_code)status_code,
  TO_CHAR(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date,
  TO_CHAR(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
  program,
  user_concurrent_program_name,
  completion_text,
  requestor,
  request_date
FROM fnd_conc_req_summary_v
ORDER BY request_date DESC;

Query to Find Purchase Order in Oracle Apps R12

SELECT
  O.NAME "OPERATING_UNIT_NAME",
  PH.SEGMENT1 "PO_REV_NUM",
  PH.PO_HEADER_ID,
  PH.TYPE_LOOKUP_CODE "TYPE",
  PH.CURRENCY_CODE,
  PH.AUTHORIZATION_STATUS "STATUS",
  PV.VENDOR_SITE_CODE,
  V.VENDOR_NAME "SUPPLIER_NAME",
  LOC1.LOCATION_CODE "SHIP_TO_LOC",
  LOC2.LOCATION_CODE "BILL_TO_LOC",
  (PL1.QUANTITY*PL1.UNIT_PRICE) "TOTAL",
--LINES INFORMATION
  PL1.LINE_NUM,
  PL1.PURCHASE_BASIS "TYPE",
  I.SEGMENT1 "ITEM",
  PL1.ITEM_REVISION,
  C.SEGMENT1||C.SEGMENT1 "CATEGORY",
  PL1.ITEM_DESCRIPTION,
  PL1.QUANTITY,
  PL.UNIT_MEAS_LOOKUP_CODE,
  PL1.UNIT_PRICE,
  PL.NEED_BY_DATE,
  PL.SHIPMENT_TYPE,
  PL.SHIPMENT_NUM,
  M.ORGANIZATION_CODE "SHP_ORG_CODE",
  LOC1.LOCATION_CODE "SHIPMENT_LOC",
  PL.UNIT_MEAS_LOOKUP_CODE "SHP_UOM",
  PL.QUANTITY "SHP_QUANTITY",
  (PL.QUANTITY*PL.PRICE_OVERRIDE) "SHP_AMOUNT",
  D.DISTRIBUTION_NUM,
  D.DESTINATION_TYPE_CODE,
  GL.CONCATENATED_SEGMENTS,
  D.QUANTITY_ORDERED "DIS_QUANTITY",
  P.FULL_NAME "REQUESTER NAME",
  I.INVENTORY_ITEM_ID,
  C.CATEGORY_ID
FROM
  --PO_HEADERS
  HR_ORGANIZATION_UNITS O,
  po_headers_all PH,
  PO_VENDORS V,
  PO_VENDOR_SITES_ALL PV,
  HR_LOCATIONS_ALL_TL LOC1,
  HR_LOCATIONS_ALL_TL LOC2,
  --PO_LINES
  po_line_locations_all PL,
  PO_LINES_ALL PL1,
  MTL_SYSTEM_ITEMS_B I,
  MTL_CATEGORIES_B C,
  MTL_PARAMETERS M,
  PO_DISTRIBUTIONS_ALL D,
  GL_CODE_COMBINATIONS_KFV GL,
  PER_ALL_PEOPLE_F P
WHERE PH.VENDOR_SITE_ID=PV.VENDOR_SITE_ID
  AND PH.SHIP_TO_LOCATION_ID=PV.SHIP_TO_LOCATION_ID
  AND PH.VENDOR_ID=V.VENDOR_ID
  AND LOC1.LOCATION_ID=PH.SHIP_TO_LOCATION_ID
  AND LOC2.LOCATION_ID=PV.BILL_TO_LOCATION_ID
  AND PH.PO_HEADER_ID=PL1.PO_HEADER_ID
    --LINES
  AND PL1.ITEM_ID=I.INVENTORY_ITEM_ID
  AND PL.PO_HEADER_ID=PL1.PO_HEADER_ID
  AND PL.PO_LINE_ID=PL1.PO_LINE_ID
  AND PL1.CATEGORY_ID=C.CATEGORY_ID
  --ORG_CODE
  AND M.ORGANIZATION_ID=PL.SHIP_TO_ORGANIZATION_ID
  --AND O.ORGANIZATION_ID=M.ORGANIZATION_ID
  --AND C.ORGANIZATION_ID=M.ORGANIZATION_ID
  AND O.ORGANIZATION_ID=PH.ORG_ID
  --DISTRIBUTION
  AND D.PO_HEADER_ID=PL.PO_HEADER_ID
  AND D.PO_LINE_ID=PL.PO_LINE_ID
  AND D.LINE_LOCATION_ID=PL.LINE_LOCATION_ID
  AND GL.CODE_COMBINATION_ID=D.CODE_COMBINATION_ID
  AND D.DELIVER_TO_PERSON_ID=P.PERSON_ID;

Order Management Sales Order Query

SELECT
oha.header_id,bill_ca.cust_account_id,
oha.order_number,
oha.ordered_date,
oha.cust_po_number customer_po,
oha.org_id,
ott.name order_type,
ola.line_number,
msi.segment1 item_code,
msi. description item_desc,
ola.ordered_quantity,
ola.order_quantity_uom,
oha.transactional_curr_code trx_curr,
ola.unit_selling_price,
(ola.ordered_quantity*ola.unit_selling_price) line_amt,
ola.shipping_quantity,
ola.shipping_quantity_uom,
(nvl(ola.shipping_quantity,0)*ola.unit_selling_price) shipped_amt,
ola.schedule_ship_date,
--oha.flow_status_code order_status,
ol.meaning order_status,
bill_p.party_name,
bill_su.LOCATION invoice_to_location,
bill_loc.address1 invoice_to_address1,
bill_loc.address2 invoice_to_address2,
bill_loc.address3 invoice_to_address3,
bill_loc.address4 invoice_to_address4,
DECODE (bill_loc.city,
NULL, NULL,
bill_loc.city || ', '
)
|| DECODE (bill_loc.state,
NULL, bill_loc.province || ', ',
bill_loc.state || ', '
)
|| DECODE (bill_loc.postal_code,
NULL, NULL,
bill_loc.postal_code || ', '
)
|| DECODE (bill_loc.country, NULL, NULL, bill_loc.country) invoice_to_address5,
ship_loc.city ship_city,
ship_loc.country ship_country_code,
ft.territory_short_name ship_country,
(select name
from ra_salesreps_all
where salesrep_id = oha.salesrep_id
and org_id = oha.org_id) sales_person,
--wd.date_scheduled,
oha.conversion_type_code,
bill_ca.account_number,
oha.global_attribute2

FROM oe_order_headers_all oha,
oe_order_lines_all ola,
oe_transaction_types_tl ott,
mtl_system_items_b msi,
hz_cust_site_uses_all bill_su,
hz_party_sites bill_ps,
hz_locations bill_loc,
hz_cust_acct_sites_all bill_cas,
hz_cust_accounts bill_ca,
hz_parties bill_p,
hz_cust_site_uses_all ship_su,
hz_party_sites ship_ps,
hz_locations ship_loc,
hz_cust_acct_sites_all ship_cas,
--    wsh_deliverables_v wd
fnd_territories_tl ft,
oe_lookups ol

WHERE
oha.org_id = nvl(:p_org_id,oha.org_id)
and trunc(oha.ordered_date) between nvl(:p_order_date_from,trunc(oha.ordered_date))
and nvl(:p_order_date_to,trunc(oha.ordered_date))
and bill_ca.cust_account_id between nvl(:p_cust_acc_id_from,trunc(bill_ca.cust_account_id))
and nvl(:p_cust_acc_id_to,trunc(bill_ca.cust_account_id))
and trunc(NVL(ola.schedule_ship_date,SYSDATE)) between nvl(:p_delvry_date_from,trunc(NVL(ola.schedule_ship_date,SYSDATE)))
and nvl(:p_delvry_date_to,trunc(NVL(ola.schedule_ship_date,SYSDATE)))
--and oha.flow_status_code between nvl(:p_order_status_from,oha.flow_status_code)
--and nvl(:p_order_status_to,oha.flow_status_code)
and ol.meaning between nvl(:p_order_status_from,ol.meaning)
and nvl(:p_order_status_to,ol.meaning)
and msi.inventory_item_id between nvl(:p_prod_desc_from,msi.inventory_item_id)
and nvl(:p_prod_desc_to,msi.inventory_item_id)
/*and msi.description between nvl(:p_prod_desc_from,msi.description)
and nvl(:p_prod_desc_to,msi.description) */
and ship_loc.country between nvl(:p_ship_country_from,ship_loc.country)
and nvl(:p_ship_country_to,ship_loc.country)
and oha.salesrep_id between nvl(:p_salesrep_id_from,oha.salesrep_id)
and nvl(:p_salesrep_id_to,oha.salesrep_id)
and oha.header_id = ola.header_id
and oha.ORDER_TYPE_ID = ott.transaction_type_id
and ott.language = userenv('LANG')
and ola.inventory_item_id = msi.inventory_item_id
and ola.ship_from_org_id = msi.organization_id
and oha.invoice_to_org_id = bill_su.site_use_id(+)
and bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
and bill_cas.party_site_id = bill_ps.party_site_id(+)
and bill_loc.location_id(+) = bill_ps.location_id
and bill_cas.cust_account_id = bill_ca.cust_account_id
and bill_ca.party_id = bill_p.party_id
and oha.ship_to_org_id = ship_su.site_use_id(+)
and ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
and ship_cas.party_site_id = ship_ps.party_site_id(+)
and ship_loc.location_id(+) = ship_ps.location_id
--   and oha.header_id = wd.source_header_id(+)
--   and ola.line_id = wd.source_line_id(+)
and ship_loc.country = ft.territory_code
and ft.language = userenv('LANG')
and ol.lookup_type = 'FLOW_STATUS'
and ol.lookup_code = oha.flow_status_code
and oha.order_number = '1821062' ;

Account Receivable TO General Ledger Join Query ( Order to Cash)

SELECT   ooha.order_number,
         ooha.org_id,
         hca.account_name,
         hp.party_name "Customer Name",
         hcasab.orig_system_reference BILL_TO_ORIG_REF,
         hpsb.status BILL_TO_STATUS,
            'ADDRESS1 - '
         || bill_loc.address1
         || ','
         || CHR (10)
         || 'ADDRESS2 - '
         || bill_loc.address2
         || ','
         || CHR (10)
         || 'ADDRESS3 - '
         || bill_loc.address3
         || ','
         || CHR (10)
         || 'CITY     - '
         || bill_loc.city
         || ','
         || CHR (10)
         || 'POSTAL CD- '
         || bill_loc.postal_code
         || ','
         || CHR (10)
         || 'COUNTRY  - '
         || bill_loc.country
            BILL_TO_ADDRESS,
         hcasas.orig_system_reference SHIP_TO_ORIG_REF,
         hpss.status SHIP_TO_STATUS,
            'ADDRESS1 - '
         || ship_loc.address1
         || ','
         || CHR (10)
         || 'ADDRESS2 - '
         || ship_loc.address2
         || ','
         || CHR (10)
         || 'ADDRESS3 - '
         || ship_loc.address3
         || ','
         || CHR (10)
         || 'CITY     - '
         || ship_loc.city
         || ','
         || CHR (10)
         || 'POSTAL CD- '
         || ship_loc.postal_code
         || ','
         || CHR (10)
         || 'COUNTRY  - '
         || ship_loc.country
            SHIP_TO_ADDRESS,
         oola.inventory_item_id,
         oola.ordered_item,
         msib.description item_description,
         wnd.name delivery_number,
         rct.trx_number "AR Invoice Number",
         acr.receipt_number "AR Receipt Number",
         gjh.ledger_id,
         gjh.name
  FROM  -- Enter the Sales Order:
         oe_order_headers_all ooha,
         oe_order_lines_all oola,
         -- Enter the Sales Order: --
         -- AR Customer Detail
         hz_parties hp,
         hz_cust_accounts hca,
         hz_party_sites hpss,
         hz_party_sites hpsb,
         hz_locations bill_loc,
         hz_locations ship_loc,
         hz_cust_acct_sites_all hcasab,
         hz_cust_acct_sites_all hcasas,
         hz_cust_site_uses_all hzsuab,
         hz_cust_site_uses_all hzsuas,
         -- AR Customer Detail: --
         mtl_system_items_b msib,
         -- Book the Sales Order:
         wsh_delivery_details wdd,
         wsh_new_deliveries wnd,
         wsh_delivery_assignments wda,
         -- Book the Sales Order: --
         -- Create ARInvoice:
         ra_customer_trx_all rct,
         ra_customer_trx_lines_all rctl,
         ra_cust_trx_line_gl_dist_all rctld,
         -- Create AR Invoice: --
         -- Create AR Receipt
         ar_cash_receipts_all acr,
         -- Create AR Receipt --
         -- subledger accounting
         xla.xla_transaction_entities xte,
         xla_events xe,
         xla_ae_headers xah,
         xla_ae_lines xal,
         xla_distribution_links xdl,
         -- subledger accounting : --
         -- GL Journal Import:
         gl_import_references gir,
         gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl
 -- GL Journal Import: --
 WHERE      1 = 1
         AND ooha.order_number = :SalesOrderNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
         AND ooha.org_id = 81
         AND ooha.header_id = oola.header_id
         AND hca.cust_account_id = ooha.sold_to_org_id
         AND hp.party_id = hca.party_id
         AND hpss.party_id = hca.party_id
         AND hpsb.party_id = hca.party_id
         AND bill_loc.location_id = hpss.location_id
         AND ship_loc.location_id = hpsb.location_id
         AND hcasas.cust_account_id = hca.cust_account_id
         AND hcasab.cust_account_id = hca.cust_account_id
         AND hcasas.party_site_id = hpss.party_site_id
         AND hcasab.party_site_id = hpsb.party_site_id
         AND hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
         AND hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
         AND hzsuas.site_use_id = ooha.ship_to_org_id
         AND hzsuab.site_use_id = ooha.invoice_to_org_id
         AND wda.delivery_id = wnd.delivery_id(+)
         AND wdd.delivery_detail_id = wda.delivery_detail_id
         AND wdd.source_header_id = ooha.header_id
         AND wdd.source_line_id = oola.line_id
         AND wdd.organization_id = msib.organization_id(+)
         AND wdd.inventory_item_id = msib.inventory_item_id(+)
         AND rct.interface_header_attribute1 = TO_CHAR (ooha.order_number)
         AND rct.org_id = ooha.org_id
         AND rctl.customer_trx_id = rct.customer_trx_id
         AND rctl.sales_order = TO_CHAR (ooha.order_number)
         AND rctld.customer_trx_id = rct.customer_trx_id
         AND rctld.customer_trx_line_id = rctl.customer_trx_line_id
         AND acr.receipt_number = '05037'
         AND acr.pay_from_customer = rct.sold_to_customer_id
         AND acr.org_id = ooha.org_id
         AND acr.customer_site_use_id = rct.bill_to_site_use_id
         AND xte.transaction_number = acr.receipt_number
         AND xte.entity_code = 'RECEIPTS'
         AND xe.entity_id = xte.entity_id
         AND xah.event_id = xe.event_id
         AND xal.ae_header_id = xah.ae_header_id
         AND xal.accounting_class_code = 'CASH'
         AND xdl.ae_header_id = xah.ae_header_id
         AND xdl.ae_line_num = xal.ae_line_num
         --and xdl.source_distribution_id_num_1
         AND gir.reference_5 = xte.entity_id                      -- Entity Id
         AND gir.reference_6 = TO_CHAR (xe.event_id)                --Event Id
         AND gir.reference_7 = TO_CHAR (xah.ae_header_id)      -- AE Header Id
         AND gir.gl_sl_link_id = xal.gl_sl_link_id
         -- AND gir.created_by = XXXXXX
         AND gjb.je_batch_id = gir.je_batch_id
         AND gjh.je_batch_id = gjb.je_batch_id
         AND gjh.je_header_id = gir.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gir.je_line_num;