Query to get Concurrent Program Data Template details

 SELECT  fcpt.user_concurrent_program_name

      , fcp.concurrent_program_name
      , fet.user_executable_name executable_name
      , fe.executable_name executable_short_name
      , fet.description
      , fe.execution_file_name
      , fcp.enabled_flag
      , xtt.template_name
      , xddv.data_source_name
      , xddv.data_source_code
      , xl.file_name
      , xl.lob_code
      , xl.language
      , xl.territory
      , xl.creation_date
      , xl.last_update_date

FROM fnd_concurrent_programs_tl fcpt

      , fnd_concurrent_programs fcp
      , xdo_templates_b xtb
      , xdo_templates_tl xtt
      , xdo_ds_definitions_vl xddv
      , xdo_lobs xl
      , apps.fnd_executables fe
      , apps.fnd_executables_tl fet

WHERE fcpt.concurrent_program_id = fcp.concurrent_program_id

  AND xtb.data_source_code = fcp.concurrent_program_name
  AND xtb.template_code = xtt.template_code
  AND xtb.data_source_code = xddv.data_source_code
  AND xl.lob_code = xtb.template_code
  AND xl.xdo_file_type = xtb.template_type_code
  AND fet.application_id = fe.application_id
  AND fet.executable_id = fe.executable_id
  AND fcp.application_id = fe.application_id
  AND fcp.executable_id = fe.executable_id
  AND fcpt.language = 'US'
  AND xtt.language = 'US'
  AND fet.language = 'US'

GROUP BY fcpt.user_concurrent_program_name

      , fcp.concurrent_program_name
      , fet.user_executable_name
      , fe.executable_name
      , fet.description
      , fe.execution_file_name
      , fcp.enabled_flag
      , xtt.template_name
      , xddv.data_source_name
      , xddv.data_source_code
      , xl.file_name
      , xl.lob_code
      , xl.language
      , xl.territory
      , xl.creation_date
      , xl.last_update_date;

Query to get Bill To and Ship To address details of AR Invoice

SELECT (SELECT name FROM v$database) instance,

  ct.trx_number, -- ct.interface_header_attribute1, ct.interface_header_attribute3,
  ct.old_trx_number, hou.name ou_name,
  ct.recurred_from_trx_number,
  ct.trx_date,
  ct.batch_id,
  ct.batch_source_id,
  ct.reason_code,
  ct.term_id,
  ct.complete_flag,
  ct.invoice_currency_code,
  ct.purchase_order,
  rac_bill_party.party_name bill_party_name,
  rac_bill.account_number bill_account_number,
  su_bill.LOCATION bill_location,
  raa_bill_loc.address1    bill_address1,
  raa_bill_loc.address2    bill_address2,
  raa_bill_loc.address3    bill_address3,
  raa_bill_loc.city        bill_city,
  raa_bill_loc.county      bill_county,
  raa_bill_loc.state       bill_state,
  raa_bill_loc.province    bill_province,
  raa_bill_loc.postal_code bill_postal_code,
  rac_ship_party.party_name ship_party_name,
  rac_ship.account_number ship_account_number,
  su_ship.LOCATION bill_location,
  raa_ship_loc.address1    ship_address1,
  raa_ship_loc.address2    ship_address2,
  raa_ship_loc.address3    ship_address3,
  raa_ship_loc.city        ship_city,
  raa_ship_loc.county      ship_county,
  raa_ship_loc.state       ship_state,
  raa_ship_loc.province    ship_province,
  raa_ship_loc.postal_code ship_postal_code,
  bs.NAME batch_source_name,
  rab.NAME batch_name,
  ctt.NAME trx_type_name,
  ctt.TYPE trx_type

FROM ra_customer_trx_all ct,

  hz_cust_accounts_all rac_bill,
  hz_parties rac_bill_party,
  hz_cust_accounts_all rac_ship,
  hz_parties rac_ship_party,
  hz_cust_accounts_all rac_paying,
  hz_parties rac_paying_party,
  hz_cust_site_uses_all su_bill,
  hz_cust_site_uses_all su_ship,
  hz_cust_acct_sites_all raa_bill,
  hz_party_sites raa_bill_ps,
  hz_locations raa_bill_loc,
  hz_cust_acct_sites_all raa_ship,
  hz_party_sites raa_ship_ps,
  hz_locations raa_ship_loc,
  hz_relationships raco_ship_rel,
  hz_parties raco_bill_party,
  hz_relationships raco_bill_rel,
  ra_batch_sources_all bs,
  ra_batches_all rab,
  ra_cust_trx_types_all ctt,
  hr_operating_units hou

WHERE 1   =   1

AND ct.bill_to_customer_id              = rac_bill.cust_account_id
AND rac_bill.party_id                   = rac_bill_party.party_id
AND ct.ship_to_customer_id              = rac_ship.cust_account_id(+)
AND rac_ship.party_id                   = rac_ship_party.party_id(+)
AND ct.paying_customer_id               = rac_paying.cust_account_id(+)
AND rac_paying.party_id                 = rac_paying_party.party_id(+)
AND ct.bill_to_site_use_id              = su_bill.site_use_id
AND ct.ship_to_site_use_id              = su_ship.site_use_id(+)
AND su_bill.cust_acct_site_id           = raa_bill.cust_acct_site_id
AND ct.org_id                           = hou.organization_id
AND raa_bill.party_site_id              = raa_bill_ps.party_site_id
AND raa_bill_loc.location_id            = raa_bill_ps.location_id
AND su_ship.cust_acct_site_id           = raa_ship.cust_acct_site_id(+)
AND raa_ship.party_site_id              = raa_ship_ps.party_site_id(+)
AND raa_ship_loc.location_id(+)         = raa_ship_ps.location_id
AND raco_bill_rel.subject_table_name(+) = 'HZ_PARTIES'
AND raco_bill_rel.object_table_name(+)  = 'HZ_PARTIES'
AND raco_bill_rel.directional_flag(+)   = 'F'
AND raco_bill_rel.subject_id            = raco_bill_party.party_id(+)
AND raco_ship_rel.subject_table_name(+) = 'HZ_PARTIES'
AND raco_ship_rel.object_table_name(+)  = 'HZ_PARTIES'
AND raco_ship_rel.directional_flag(+)   = 'F'
AND ct.batch_source_id                  = bs.batch_source_id
AND ct.batch_id                         = rab.batch_id(+)
AND ct.cust_trx_type_id                 = ctt.cust_trx_type_id
AND ctt.org_id                          = ct.org_id
AND bs.org_id                           = ct.org_id
--AND ct.trx_number IN ('8612110138', '8612110083', '8612180120', '8612180140')
AND rac_bill_party.party_name = 'HD HYUNDAI INFRACORE'
GROUP BY ct.trx_number,
  ct.old_trx_number, hou.name,
  ct.recurred_from_trx_number,
  ct.trx_date,
  ct.batch_id,
  ct.batch_source_id,
  ct.reason_code,
  ct.term_id,
  ct.complete_flag,
  ct.invoice_currency_code,
  ct.purchase_order,
  rac_bill_party.party_name ,
  rac_bill.account_number ,
  su_bill.LOCATION ,
  raa_bill_loc.address1    ,
  raa_bill_loc.address2    ,
  raa_bill_loc.address3    ,
  raa_bill_loc.city        ,
  raa_bill_loc.county      ,
  raa_bill_loc.state       ,
  raa_bill_loc.province    ,
  raa_bill_loc.postal_code ,
  rac_ship_party.party_name ,
  rac_ship.account_number ,
  su_ship.LOCATION ,
  raa_ship_loc.address1    ,
  raa_ship_loc.address2    ,
  raa_ship_loc.address3    ,
  raa_ship_loc.city        ,
  raa_ship_loc.county      ,
  raa_ship_loc.state       ,
  raa_ship_loc.province    ,
  raa_ship_loc.postal_code ,
  bs.NAME,
  rab.NAME,
  ctt.NAME,
  ctt.TYPE;

Query to check Standard Purchase Order linked with Blanket Agreement

 SELECT pha.segment1 po_number

, pha.type_lookup_code
, pha.enabled_flag
, fu.user_name standard_po_created_by
, pha.vendor_id
, pha.vendor_site_id
, pha.authorization_status
, pla.line_num
, pla.item_id
, pla.unit_price
, pla.quantity
, pla.from_header_id
, pla.from_line_id
, pha1.segment1 BPA_NUM
, pha1.global_agreement_flag
, pha1.type_lookup_code
, fu1.user_name bpa_created_by
, pha1.vendor_id
, pha1.vendor_site_id
, pha1.authorization_status
, pla1.item_id
, pla1.unit_price
, pla1.from_header_id
, pla1.from_line_id

FROM po_headers_all pha

, po_lines_all pla
, po_headers_all pha1
, po_lines_all pla1
, fnd_user fu
, fnd_user fu1

WHERE pha.po_header_id   = pla.po_header_id

  AND pla.from_header_id = pha1.po_header_id
  AND pla.from_line_id     = pla1.po_line_id
  AND pha.created_by       = fu.user_id
  AND pha1.created_by     = fu1.user_id
  --
  AND pha.segment1 IN ('5800999942', '5800999943')
  AND pla.line_num = 10;

---> Check at PO line level.. Columns - from header id and from line id, also the Global agreement flag should by Y for Blanket Agreement.

Query to check Sales Order HOLD reasons

 SELECT ooha.order_number

, ooha.ordered_date
, ooha.flow_status_code
, ooha.credit_card_approval_code
, ohd.name "Hold Name"
, ohs.released_flag
, ohr.release_reason_code
, ohr.creation_date "Hold Release Date"
, ooha.payment_type_code 

FROM apps.oe_order_headers_all ooha

, apps.oe_order_holds_all ohld
, apps.oe_hold_sources_all ohs
, apps.oe_hold_definitions ohd
, apps.oe_hold_releases ohr 

WHERE 1 = 1

AND ooha.order_category_code = 'ORDER' 
AND ohld.header_id(+)       = ooha.header_id 
AND ohs.hold_source_id(+)   = ohld.hold_source_id 
AND ohd.hold_id(+)          = ohs.hold_id 
AND ohr.hold_release_id(+)  = ohs.hold_release_id 
-- AND ooha.order_number = 'ORDERNUMBER'
ORDER BY ooha.order_number

Query to get Customer details of Sales Order

 

SELECT  ooha.order_number

     ,  ooha.ordered_date

     ,  ooha.flow_status_code

     ,  hp.party_name "Customer Name"
     ,  hca.account_name
     ,  hca.org_id
     ,  hpb.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 Code: '
        || bill_loc.postal_code
        || ','
        || CHR (10)
        || 'Country: '
        || bill_loc.country
           bill_to_address
     ,  hcasas.orig_system_reference ship_to_orig_ref
     ,  hps.status ship_to_status
     ,     'Address1: '
        || ship_loc.address1
        || ','
        || CHR (10)
        || 'Address1: '
        || ship_loc.address2
        || ','
        || CHR (10)
        || 'Address1: '
        || ship_loc.address3
        || ','
        || CHR (10)
        || 'City: '
        || ship_loc.city
        || ','
        || CHR (10)
        || 'Postal Code: '
        || ship_loc.postal_code
        || ','
        || CHR (10)
        || 'Country: '
        || ship_loc.country
           ship_to_address

  FROM  apps.oe_order_headers_all ooha

     ,  apps.hz_parties hp
     ,  apps.hz_cust_accounts hca
     ,  apps.hz_cust_acct_sites_all hcasab
     ,  apps.hz_cust_acct_sites_all hcasas
     ,  apps.hz_cust_site_uses_all hzsuab
     ,  apps.hz_cust_site_uses_all hzsuas
     ,  apps.hz_party_sites hps
     ,  apps.hz_party_sites hpb
     ,  apps.hz_locations bill_loc
     ,  apps.hz_locations ship_loc

 WHERE 

   AND hp.party_id                        = hca.party_id

   AND hca.cust_account_id          = ooha.sold_to_org_id
   AND hcasab.cust_account_id    = hca.cust_account_id
   AND hcasas.cust_account_id     = hca.cust_account_id
   AND hpb.location_id                 = bill_loc.location_id
   AND hps.location_id                 = ship_loc.location_id
   AND hcasab.party_site_id         = hpb.party_site_id
   AND hcasas.party_site_id         = hps.party_site_id
   AND hcasab.cust_acct_site_id  = hzsuab.cust_acct_site_id
   AND hcasas.cust_acct_site_id  = hzsuas.cust_acct_site_id
   AND ooha.ship_to_org_id        = hzsuas.site_use_id
   AND ooha.invoice_to_org_id   = hzsuab.site_use_id
   AND ooha.order_number          = '&Order_Number';

Add Concurrent Program to Request Group from backend

DECLARE

  lc_program_short_name  VARCHAR2 (200);
  lc_program_application VARCHAR2 (200);
  lc_request_group       VARCHAR2 (200);
  lc_group_application   VARCHAR2 (200);
  lc_check               VARCHAR2 (2);

BEGIN

  lc_program_short_name  := 'XXPO123REPORT';
  lc_program_application := 'XXPO Custom';
  lc_request_group       := 'Custom Purchasing';
  lc_group_application   := 'XXPO Custom';

  -- Calling API to assign concurrent program to a reqest group

   apps.fnd_program.add_to_group (program_short_name  => lc_program_short_name,
                                  program_application => lc_program_application,
                                  request_group       => lc_request_group,
                                  group_application   => lc_group_application                            
                                 );  

  COMMIT;

  BEGIN

    --To check whether a concurrent program has added to Request Group or not

     SELECT 'Y'
       INTO l_check
       FROM fnd_request_groups frg,
      fnd_request_group_units frgu,
      fnd_concurrent_programs fcp
      WHERE frg.request_group_id    = frgu.request_group_id
    AND frg.application_id          = frgu.application_id
    AND frgu.request_unit_id        = fcp.concurrent_program_id
    AND frgu.unit_application_id    = fcp.application_id
    AND fcp.concurrent_program_name = 'XXPO123REPORT';
    dbms_output.put_line ('Adding Concurrent Program to Request Group Succeeded');

  EXCEPTION

  WHEN no_data_found THEN

    dbms_output.put_line ('Adding Concurrent Program to Request Group Failed');

  END;

END;

/