Wednesday, December 2, 2020

Oracle Fusion: Distributed Order Orchestration (Sales Order) Lines Query



 Below is the query to get Distributed Order Orchestration (Sales Order) line informations along with its EFF:

SELECT dla.header_id
     , dla.line_id
     , dla.display_line_number
     , dla.source_line_id
     , dla.source_line_number
     , dla.source_order_number
     , dla.source_order_system
     , dla.ordered_qty
     , dla.canceled_qty
     , dla.ordered_uom
     , dla.source_org_id
     , dla.org_id
     , dla.extended_amount
     , dla.line_type_code
     , dla.open_flag
     , dla.canceled_flag
     , dla.shipped_qty
     , dla.inventory_item_id
     , dla.inventory_organization_id
     , dla.unit_list_price
     , dla.unit_selling_price
     , dfla.fulfill_line_id
     , dfla.status_code
     , dfla.priced_on
     , dfla.request_ship_date
     , dfla.schedule_ship_date
     , dfla.packing_instructions
     , dfla.earliest_acceptable_ship_date
     , dfla.bill_to_customer_id
     , dfla.bill_to_site_use_id
     , dfleb.eff_line_id
     , dfleb.context_code
     , dfleb.attribute_char1
     , dfleb.attribute_char2
     , dfleb.attribute_char3
     , dfleb.attribute_char4
     , dfleb.attribute_char5
     , dfleb.attribute_char6
     , dfleb.attribute_char7
     , dfleb.attribute_char8
     , dfleb.attribute_char9
     , dfleb.attribute_char10
     , dfleb.attribute_char11
     , dfleb.attribute_char12
     , dfleb.attribute_char13
     , dfleb.attribute_char14
     , dfleb.attribute_char15
     , dfleb.attribute_char16
     , dfleb.attribute_char17
     , dfleb.attribute_char18
     , dfleb.attribute_char19
     , dfleb.attribute_char20
     , dla.created_by
     , TO_CHAR (FROM_TZ (dla.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') creation_date
     , dla.last_updated_by
     , TO_CHAR (FROM_TZ (dla.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') last_update_date
  FROM doo_lines_all dla, doo_fulfill_lines_all dfla, doo_fulfill_lines_eff_b dfleb
 WHERE dla.line_id = dfla.line_id
   AND dfla.fulfill_line_id = dfleb.fulfill_line_id(+)

Oracle Fusion: Distributed Order Orchestration (Sales Order) Headers Query



 Below is the query to get Distributed Order Orchestration (Sales Order) header informations along with its EFF:

SELECT dha.header_id
     , dha.order_number
     , dha.customer_po_number
     , dha.source_order_system
     , dha.source_order_number
     , dha.source_order_id
     , dha.sold_to_party_id
     , dha.transactional_currency_code
     , TO_CHAR (dha.ordered_date, 'RRRR-MM-DD HH24:MI:SS') ordered_date
     , dha.org_id bu_id
     , dha.order_type_code
     , dha.legal_entity_id
     , dha.freeze_tax_flag
     , dha.freeze_price_flag
     , dha.freeze_shipping_charge_flag
     , TO_CHAR (dha.priced_on, 'RRRR-MM-DD HH24:MI:SS') priced_on
     , dha.submitted_flag
     , dha.status_code
     , dha.change_version_number
     , dheb.context_code
     , dheb.attribute_char1
     , dheb.attribute_char2
     , dheb.attribute_char3
     , dheb.attribute_char4
     , dheb.attribute_char5
     , dheb.attribute_char6
     , dheb.attribute_char7
     , dheb.attribute_char8
     , dheb.attribute_char9
     , dheb.attribute_char10
     , dheb.attribute_char11
     , dheb.attribute_char12
     , dheb.attribute_char13
     , dheb.attribute_char14
     , dheb.attribute_char15
     , dha.created_by
     , TO_CHAR (FROM_TZ (dha.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') creation_date
     , dha.last_updated_by
     , TO_CHAR (FROM_TZ (dha.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') last_update_date
  FROM doo_headers_all dha, doo_headers_eff_b dheb
 WHERE dha.header_id = dheb.header_id(+)

Thursday, November 26, 2020

Oracle Fusion: Query to Get Item Cost per Transactions (Layer Cost)



Below is the query to get item transaction cost in oracle fusion which can be seen in Review Cost Accounting Distributions form.


SELECT imt.transaction_id
     , imt.transaction_interface_id
     , imt.source_code
     , imt.source_line_id
     , cit.external_system_reference
     , cit.subinventory_code
     , cit.locator_id
     , TO_CHAR (FROM_TZ (ct.transaction_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') transaction_date
     , ct.cost_transaction_type
     , TO_CHAR (FROM_TZ (ct.cost_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') cost_date
     , ct.use_item_cost_flag
     , TO_CHAR (FROM_TZ (clc.eff_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') eff_date
     , clc.layer_cost_id
     , clc.cost_source
     , clc.cost_reference
     , clc.quantity
     , clc.uom_code
     , clc.unit_cost
     , clc.currency_code
     , clc.posted_flag
     , clc.created_by
     , TO_CHAR (FROM_TZ (clc.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') creation_date
     , clc.last_updated_by
     , TO_CHAR (FROM_TZ (clc.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') last_update_date
  FROM cst_layer_costs clc
     , cst_transactions ct
     , cst_inv_transactions cit
     , inv_material_txns imt
 WHERE cit.external_system_ref_id = imt.transaction_id
   AND cit.EXTERNAL_SYSTEM_REFERENCE = 'FUSION'
   AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id
   AND clc.transaction_id = ct.transaction_id
--   AND imt.transaction_id = 1257380

As for average cost, you can query it from CST_PERPAVG_COST table.

Wednesday, October 14, 2020

Oracle Fusion Cloud: Query to Get Assigned Roles of a User



Below is query to get roles assigned to user(s). You can define username by using p_user_name parameter while running the query, or leave it empty to get all user's roles.

  SELECT pu.username
       , NAME.full_name
       , prdv.role_id
       , prdv.role_name
       , TO_CHAR (TRUNC (pur.start_date), 'MM/DD/YYYY') role_start_date
       , ppnf.effective_end_date
    FROM per_user_roles pur
       , per_users pu
       , per_roles_dn_vl prdv
       , per_person_names_f ppnf
   WHERE pur.user_id = pu.user_id
     AND pu.person_id = NAME.person_id(+)
     AND pur.role_id = prdv.role_id
     AND NVL (pu.suspended, 'N') = 'N'
     AND NAME.name_type = 'GLOBAL'
     AND TRUNC (SYSDATE) BETWEEN TRUNC (ppnf.effective_start_date) AND TRUNC (ppnf.effective_end_date)
     AND pu.username = nvl(:p_user_name, pu.username)
ORDER BY pu.username
       , prdv.role_name

Monday, May 23, 2016

Forms Builder 10g - Run Forms Locally in Windows 10




After some googling, I'm finally able to run forms locally using JRE 7 in Windows 10.

Here's how to do it:
 
First, you need to download the following files:

• Java 7 Update 79: http://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase7-521261.html#jre-7u79-oth-JPR

and

• Modified formsweb.cfg: http://luhunk.xtgem.com/ora/formsweb.cfg

Then, follow this step:
  1. Install Java 7 Update 79 downloaded from the link above
  2. Copy and replace the modified formsweb.cfg to Oracle Developer 10g installation folder, by default the location is: C:\DevSuiteHome_1\forms\server (don't forget to backup the original formsweb.cfg!)
  3. Now you can run the froms locally using JRE, instead of JInitiator :) 
Feel free to ask in the comment below if you have any problem :)


Wednesday, July 30, 2014

Pertempuran Konvoy Sukabumi - Cianjur (Bag. 2)





Sebagai tindak lanjut dari keberhasilan Komandemen TKR Jawa Barat dan Kantor Penghubung TKR Jakarta, pada minggu ketiga bulan Desember 1945, Pemerintah Republik Indonesia mengadakan Konferensi Besar di Yogyakarta. Presiden dan Wakil Presiden, serta pucuk Pimpinan TKR, memberikan penjelasan dan pengarahan kepada Panglima Komandemen dan Komandan Divisi se-Pulau Jawa, tentang tindak-lanjut kerjasama Misi Internasional dengan pihak AFNEI (Sekutu), dalam penanganan pemulangan tentara Jepang dan evakuasi APWI secara menyeluruh.

Berdasarkan hasil Konferensi Besar di Yogyakarta, pihak Pemerintah Republik Indonesia menunjuk Letnan Jenderal Oerip Soemohardjo, diserahi tugas untuk mempersiapkan Komando Operasi Misi Internasional itu. Kemudian pada tanggal 24 Desember 1945 dan pada tanggal 14 Januari 1946, diadakan perundingan, antara Jenderal Mayor Sudibjo dengan pucuk pimpinan Markas Besar AFNEI (Sekutu) di Jakarta.

Wednesday, July 23, 2014

Pertempuran Konvoy Sukabumi - Cianjur (Bag. 1)



Pertempuran Konvoy SUkabumi - Cianjur 1945-1946

Peran Inggris dalam kancah percaturan politik dunia, dari sejak Atlantic Charter, Konferensi Yalta hingga Konferensi Postdam, merupakan salah satu pelopor blok Sekutu, bahkan telah menempatkan diri sebagai kelompok Tiga Besar. Setelah dapat mengalahkan Nazi Jerman, Fasis Italia dan Kekaisaran Jepang, Sekutu (termasuk peran utama Inggris di dalamnya), menyatakan sebagai Pemenang Perang Dunia II.

Tentara Inggris yang mendarat di Indonesia pada tanggal 30 September 1945, berperan sebagai AFNEI (Allied Forces Netherlands East Indies – Pasukan Sekutu Hindia Belanda), mengemban Misi Internasional Sekutu dalam hal:
  1. Pelucutan senjata dan pemulangan tentara Jepang;
  2. Pengiriman perbekalan dan pemulangan APWI (Allied Prisoners of War and Interneers - Tawanan Perang dan Interniran Sekutu).