SELECT item_type, item_key
FROM wf_items
WHERE user_key = '<journal batch name>';
SELECT item_type, item_key
FROM wf_items
WHERE user_key = '<journal batch name>';
DECLARE
-- Declare file handler
file_handle UTL_FILE.FILE_TYPE;
-- Declare variables for data to be written
v_line VARCHAR2(100);
v_filename VARCHAR2(50) := 'handlingunits'||TO_CHAR(SYSDATE,'MMDDRRHH24MISS')||'.csv'; -- Name of the file
v_dir VARCHAR2(50) := 'XX_CAO_OUTBOUND'; -- Directory path (ensure it's accessible by Oracle)
BEGIN
-- Open the file for writing. If the file does not exist, it will be created.
file_handle := UTL_FILE.FOPEN(v_dir, v_filename, 'w'); -- 'w' mode means write
-- Example data to write to the file
FOR xxtlm_rec IN (SELECT DISTINCT ';;;'||item||';'||vendor_lot||';1;1;1;' line
FROM xxtlm.xxtlm_rcv_transactions xrt
WHERE xrt.organization_id = (SELECT organization_id FROM mtl_parameters WHERE organization_code = 'TIF')
AND xrt.status_id = 2
AND transaction_type = 'RECEIVE'
UNION
SELECT '99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999'
FROM dual
ORDER BY 1 DESC)
LOOP
-- Write the data to the file
UTL_FILE.PUT_LINE(file_handle, xxtlm_rec.line);
END LOOP;
-- Close the file after writing
UTL_FILE.FCLOSE(file_handle);
-- Success message
DBMS_OUTPUT.PUT_LINE('Data written successfully to ' || v_dir || '/' || v_filename);
EXCEPTION
WHEN OTHERS THEN
-- Handle any exceptions
DBMS_OUTPUT.PUT_LINE('Exception: '||SQLERRM);
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.FCLOSE(file_handle);
END IF;
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
SELECT
'Requisition' type,
prha.document_status approval_status,
to_char(prha.creation_date, 'DD-MM-YYYY') po_date,
prha.requisition_number requisition_number,
replace(replace(prha.description,
CHR(13),
''),
CHR(10),
'') pr_po_desc,
(
SELECT
SUM((nvl(unit_price, amount) * nvl(quantity, 1)))
FROM
por_requisition_lines_all
WHERE
requisition_header_id = prha.requisition_header_id
) pr_po_amount,
(
SELECT
prla.currency_code
FROM
por_requisition_lines_all prla
WHERE
prla.requisition_header_id = prha.requisition_header_id
AND ROWNUM = 1
) currency,
prha.requisition_header_id,
NULL supplier_name,
wf.assigneesdisplayname pending_with_whom,
to_char(wf.assigneddate, 'DD-MM-YYYY') submit_for_appr_date,
email.*,
wf.identificationkey,
wf.PROCESSNAME,
wf.STATE
FROM
po_action_history pah,
por_requisition_headers_all prha,
fa_fusion_soainfra.wftask wf,
-- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,
hr_operating_units d,
(
SELECT
panf.full_name,
fu.username,
pea.email_address
FROM
per_users fu,
per_all_people_f papf,
per_person_names_f panf,
per_email_addresses pea
WHERE
fu.person_id = papf.person_id
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
AND panf.person_id = papf.person_id
AND sysdate BETWEEN panf.effective_start_date AND panf.effective_end_date
AND panf.name_type = 'GLOBAL'
AND pea.person_id = papf.person_id
AND sysdate BETWEEN nvl(pea.date_from, sysdate - 1) AND nvl(pea.date_to, sysdate + 1)
WHERE
pah.object_id = prha.requisition_header_id
AND prha.req_bu_id = d.organization_id
AND pah.object_type_code = 'REQ'
AND prha.document_status IN ( 'PENDING APPROVAL' )
AND wf.compositeinstanceid = prha.approval_instance_id
AND wf.assignees IS NOT NULL
AND wf.tasknumber = (
SELECT
MAX(tasknumber)
FROM
fa_fusion_soainfra.wftask wf1
WHERE
wf.correlationid = wf1.correlationid -- and wf1.state = 'ASSIGNED'
AND wf1.compositeinstanceid = prha.approval_instance_id
)
-- AND WASSS_V.taskid(+) = wf.taskid
AND email.username (+) = upper(substr(wf.assignees,
1,
instr(wf.assignees, ',') - 1)) ----WASSS_V.ASSIGNEE
AND pah.sequence_num = (
SELECT
MAX(pah1.sequence_num)
FROM
po_action_history pah1
WHERE
pah1.object_id = pah.object_id -----rEQ---
AND pah1.object_type_code = 'REQ'
AND prha.document_status IN ( 'PENDING APPROVAL' ) -- AND pah1.action_code = nvl(:p_status,pah1.action_code)
)
---Parameters
-- AND prha.requisition_header_id = nvl(:por_number, prha.requisition_header_id)
-- AND decode(:p_bu, NULL, 1, d.organization_id) = nvl(:p_bu, 1)
-- AND NVL (:PO_Number,1) = 1
UNION ALL
SELECT
'Purchase Order',
pv.change_order_status approval_status,
to_char(ph.creation_date, 'DD-MM-YYYY') po_date,
--------------
ph.segment1 po_number,
replace(replace(ph.comments,
CHR(13),
''),
CHR(10),
'') pr_po_desc,
(
SELECT
SUM(nvl(pl1.unit_price, pl1.amount) * decode(pl1.purchase_basis, 'SERVICES', 1, pl1.quantity))
FROM
po_lines_all pl1
WHERE
pl1.po_header_id = ph.po_header_id
) pr_po_amount,
ph.currency_code currency,
ph.po_header_id,
pv.vendor_name supplier_name,
wf.assigneesdisplayname pending_with_whom,
to_char(wf.assigneddate, 'DD-MM-YYYY') submit_for_appr_date,
email.*,
wf.identificationkey,
wf.PROCESSNAME,
wf.STATE
FROM
po_action_history poh,
fa_fusion_soainfra.wftask wf,
-- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,
po_headers_all ph,
po_versions pv,
poz_suppliers_v pv,
hr_operating_units d,
(
SELECT
panf.full_name,
fu.username,
pea.email_address
FROM
per_users fu,
per_all_people_f papf,
per_person_names_f panf,
per_email_addresses pea
WHERE
fu.person_id = papf.person_id
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
AND panf.person_id = papf.person_id
AND sysdate BETWEEN panf.effective_start_date AND panf.effective_end_date
AND panf.name_type = 'GLOBAL'
AND pea.person_id = papf.person_id
AND sysdate BETWEEN nvl(pea.date_from, sysdate - 1) AND nvl(pea.date_to, sysdate + 1)
WHERE
poh.correlation_id = wf.correlationid
AND poh.object_id = ph.po_header_id
AND ph.po_header_id = pv.po_header_id
AND wf.compositeinstanceid = pv.approval_instance_id
--AND wf.state = 'ASSIGNED'
AND assignees IS NOT NULL
-- AND ph.segment1 = 'ISG0000048'
AND poh.object_type_code = 'PO'
-- AND poh.ACTION_CODE = 'SUBMIT'
-- AND ph.DOCUMENT_STATUS IN ('PENDING APPROVAL')
-- ('WITHDRAWN', 'REJECTED', 'PENDING APPROVAL')
AND pv.change_order_status = 'PENDING APPROVAL'
AND ph.vendor_id = pv.vendor_id
AND ph.prc_bu_id = d.organization_id
AND wf.tasknumber = (
SELECT
MAX(tasknumber)
FROM
fa_fusion_soainfra.wftask wf1
WHERE
wf.correlationid = wf1.correlationid --
AND wf1.state = 'ASSIGNED'
AND wf1.compositeinstanceid = pv.approval_instance_id
)
-- AND WASSS_V.taskid(+) = wf.taskid
AND email.username (+) = upper(substr(wf.assignees,
1,
instr(wf.assignees, ',') - 1))
AND poh.sequence_num = (
SELECT
max ( pah1.sequence_num ) FROM po_action_history pah1 WHERE pah1.object_id = poh.object_id -----Po_header_id
AND pah1.object_type_code = 'PO' -- AND pah1.action_code = nvl(:p_status,pah1.action_code)
)
---Parameters
-- AND ph.po_header_id = nvl(:po_number, ph.po_header_id)
-- AND decode(:p_bu, NULL, 1, ph.prc_bu_id) = nvl(:p_bu, 1)
-- AND NVL (:POR_Number,1) =1