This appendix provides code for example purposes. The examples in this chapter are not part of Oracle's code; they have not been tested or certified by Oracle. Customers and implementors must use their discretion before implementing.
Note: The code below is for example purposes only. It is not part of Oracle's code, and has not been tested or certified by Oracle. Customers and implementors must use their discretion before implementing.
PACKAGE XXCQA_APPROVAL_WF_PKG AUTHID CURRENT_USER as
PROCEDURE find_approver (itemtype IN VARCHAR2
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2);
END xxcqa_approval_wf_pkg;
PACKAGE BODY XXCQA_APPROVAL_WF_PKG as
PROCEDURE find_approver (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2) IS
1_recipient VARCHAR2(1000);
BEGIN
1_recipient := wf_engine.getitemattrtext(
itemtype => itemkey,
item key => itemkey,
aname => 'RECIPIENT');
-- IF the notification is currently read by USER1, i.e. current approver
-- then make USER2 the next approver to receive the notification
IF (1_recipient = 'USER1') THEN
wf_engine.setitemattritext (
item type => itemtype,
item key => itemkey,
aname => 'RECIPIENT',
avalue => 'USER2';
result := 'COMPLETE:Y'; -- for Find Approver step to go back to Notification step
ELSE
result := 'COMPLETE:N'; -- No further approval required
END IF;
EXCEPTION
WHEN OTHERS THEN
wf_core.context ('qa_results_wf_pkg', 'find_approver',
itemtype, itemkey, to_char (actid), funcmode);
raise;
END find_approver;
END xxcqa_approval_wf_pkg;
This report provides a master detail data for a nonconformance. It lists the nonconformance header information, and the corresponding nonconformance lines and dispositions for each nonconformance line. In addition, if the disposition was a rework job, the operations and resources for the rework job are displayed on the report.
Note: The code below is for example purposes only. It is not part of Oracle's code, and has not been tested or certified by Oracle. Customers and implementors must use their discretion before implementing.
SET HEADING ON
SET VERIFY OFF
SET PAGESIZE 10000
SET SERVEROUTPUT ON
REM
REM The first query is a join between nonconformance master and detail.
REM It flattens the master and the detail into one result set.
REM And then the result set is fomratted into two blocks, a
REM "header block" with common information, and a detail
REM block with detail descriptions.
REM
REM
REM The following block of COLUMN statements declares what
REM data elements should appear in the header block.
REM (NOPRINT suppresses their value in the detail)
REM
COLUMN today NOPRINT NEW_VALUE today
COLUMN ncm_no NOPRINT NEW_VALUE ncm_no
COLUMN part_no NOPRINT NEW_VALUE part_no
COLUMN part_desc NOPRINT NEW_VALUE part_desc
COLUMN revision NOPRINT NEW_VALUE revision
COLUMN work_order NOPRINT NEW_VALUE work_order
COULMN ncm_qty NOPRINT NEW VALUE ncm_qty
COLUMN ncm_source NOPRINT NEW_VALUE ncm_source
COLUMN ncm_severity NOPRINT NEW_VALUE ncm_severity
COLUMN ncm_status NOPRINT NEW_VALUE ncm_status
COLUMN from_op_seq NOPRINT NEW_VALUE from_op_seq
REM
REM The following block fo COLUMN statements declares the
REM look and feel format of the detail elements.
REM
COLUMN ncm_line_num TRUNCATE HEADING "NCM Line"
COLUMN identidy FORMAT A20 WORD_WRAPPED HEADING "identidy"
COLUMN note_type FORMAT A9 TRUNCATE HEADING "Note Type"
COLUMN notes FORMAT A43 WORD_WRAPPED HEADING "Notes"
COLUMN requirement FORMAT A20 TRUNCATE HEADING "Requirement"
COLUMN deviation FORMAT A10 TRUNCATE HEADING "Deviation"
COLUMN disp_num FORMAT A7 TRUNCATE HEADING "Disp#"
COLUMN disp_line_num TRUNCATE HEADING "Disp Line"
COLUMN disposition FORMAT A12 WORD_WRAPPED HEADING "Disposition"
COLUMN disposition_source FORMAT A10 TRUNCATE HEADING "Source"
COLUMN disp_desc FORMAT A40 WORD_WRAPPED HEADING "Description"
COLUMN disp_status FORMAT A10 TRUNCATE HEADING "Status"
COLUMN disp_owner FORMAT A10 TRUNCATE HEADING "Owner"
COLUMN date_opened FORMAT A10 TRUNCATE HEADING "Date Opened"
COLUMN date_closed FORMAT A10 TRUNCATE HEADING "Date Closed"
COLUMN disp_action FORMAT A20 TRUNCATE HEADING "Action"
COLUMN disp_line_desc FORMAT A50 WORD_WRAPPED HEADING "Description"
REM
REM The following is the look and feel of the header block.
REM
TTITLE CENTER 'MATERIAL REVIEW REPORT' skip 1 -
CENTER today skip 3 -
LEFT 'NCM Number :' ncm_no skip 1 -
LEFT 'Work Order :' work_order skip 1 -
LEFT 'Part Number :' part_no skip 1 -
LEFT 'Revision :' revision skip 1 -
LEFT 'Description :' part_desc skip 1 -
LEFT 'Qty :' ncm_qty skip 1 -
LEFT 'Source :' ncm_source skip 1 -
LEFT 'Severity :' ncm_severity skip 1 -
LEFT 'Status :' ncm_status skip 1 -
LEFT 'OP Seq# :' from_op_seq skip 3 -
SELECT sysdate today,
ncm_mst.nonconformance_number ncm_no,
ncm_mst.item part_no,
substr(msi.description, 1, 75) part_desc,
ncm_mst.revision,
ncm_mst.nonconformance_source ncm_source,
ncm_mst.nonconformance_severity ncm_severity,
ncm_mst.nonconformance_status ncm_status,
ncm_mst.job work_order,
ncm_mst.from_op_seq_number from_op_seq,
ncm_dtl.nonconform_line_num ncm_line_num,
ncm_dtl.identidy identidy,
ncm_dtl.note_type note_type,
ncm_dtl.notes notes,
ncm_dtl.short_description requirement,
ncm_dtl.deviation deviation
FROM q_&&3._ncm_master_v ncm_mst,
q_&3._ncm_dtl_v ncm_dtl,
mtl_system_items_kfv msi
WHERE ncm_mst.nonconformance_number = '&&1' AND
ncm_mst.nonconformance_number = ncm_dtl.nonconformance_number (+) AND
ncm_mst.item_id = msi.inventory_item_id (+) AND
ncm_mst.organization_id = msi.organization_id (+)
ORDER BY ncm_line_num;
TTITLE OFF
REM
REM We now query the dispostions for each ncm line
REM
REM BREAK ON ncm_line_num
REM SELECT disp_hdr.source_ref_line_id ncm_line_num,
REM disp_hdr.disposition_number disp_num,
REM disp_hdr.disposition_source,
REM disp_hdr.disposition,
REM disp_hdr.disposition_desc disp_desc,
REM disp_hdr.disposition_status disp_status,
REM disp_hdr.date_opened,
REM disp_hdr.date_closed,
REM FROM q_&3._ncm_disp_hdr_v disp_hdr
REM WHERE
REM disp_hdr.source_reference_id = '&1'
REM ORDER BY ncm_line_num, disp_num;
REM
REM We now query the disposition details for each disposition
REM
BREAK ON ncm_line_num on disp_num
SELECT
disp_dtl.source_ref_line_id ncm_line_num,
disp_dtl.disposition_number disp_num,
disp_dtl.disposition disposition,
disp_dtl.disposition_action disp_action,
disp_dtl.disposition_line_desc disp_line_desc
disp_dtl.disposition_status disp_status
FROM
q_&3._ncm_disp_dtl_v disp_dtl
WHERE
disp_dtl.source_reference_id = '&1'
ORDER BY ncm_line_num, disp_num;
REM
REM The following is a refinement of the above to also print
REM out resource usage for each operation of each Rework Job
REM disposition.
REM
COLUMN disp_num NOPRINT NEW_VALUE disp_num
COLUMN disp_line_num NOPRINT NEW_VALUE disp_line_num
COLUMN disp_action NOPRINT NEW_VALUE disp_action
COLUMN disp_line_desc NOPRINT NEW_VALUE disp_line_desc
COLUMN new_rework_job NOPRINT NEW_VALUE new_rework_job
COLUMN op_seq FORMAT 9999 HEADING "Op"
COLUMN dept_code HEADING "Department"
COLUMN job_desc FORMAT A50 WORD_WRAPPED HEADING Description
COLUMN assigned_units FORMAT 999 HEADING "AssignedUnits"
COLUMN scheduled_units FORMAT A9 HEADING "ScheduledUnits"
COLUMN scheduled_flag FORMAT A3 HEADING Sch
COLUMN resources FORMAT A10 HEADING Resources
TTITLE skip 5 CENTER 'MATERIAL REVIEW REPORT' skip 1 -
CENTER 'Operation Resource Usage' skip 1 -
CENTER today skip 3 -
LEFT 'NCM Number :' ncm_no skip 1 -
LEFT 'Part Number :' part_no skip 1 -
LEFT 'Revision :' revision skip 1 -
LEFT 'Description :' part_desc skip 2 -
LEFT 'Disp :' disp_num skip 1 -
LEFT 'Disp Line :' disp_line_num skip 1 -
LEFT 'Disp Action :' disp_action skip 1 -
LEFT 'Description :' disp_line_desc skip 1 -
LEFT 'Rework Job :' new_rework_job skip 3
BREAK ON disp_num
SELECT --+ leading (q_&3.ncm_disp_wip_v.qr)
dis_dtl.disposition_number disp_num,
disp_dtl.disposition_line_num disp_line_num,
disp_dtl.disposition_action disp_action,
disp_dtl.disposition_line_desc disp_line_desc,
disp_wip.new_rework_job,
wor.operation_seq_num op_seq,
wor.department_code dept_code,
wor.resource_code resources,
wor.assigned_units,
wor.scheduled_units,
decode(wor.scheduled_flag, 1, 'Y', 'N') scheduled_flag,
wor.description job_desc
FROM
q_&3._ncm_disp_dtl_v disp_dtl,
q_&3._ncm_disp_wip_v disp_wip,
wip_entities we,
wip_operation_resources_v wor
WHERE
disp_wip.disposition_number = disp_dtl.disposition_number AND
(disp_dtl.disposition_line_num IS NULL OR
disp_wip.disposition_line_num = disp_dtl.disposition_line_num) AND
disp_wip.new_rework_job = we.wip_entity_name AND
we.wip_entity_id = wor.wip_entity_id AND
we.organization_id = disp_wip.organization_id AND
disp_dtl.source_reference_id = '&1'
ORDER BY disp_num, disp_line_num;
COLUMN ncm_status FORMAT A10 HEADING Status
COLUMN owner FORMAT A20 HEADING Owner
COLUMN detail_desc FORMAT A50 WORD_WRAPPED HEADING Description
COLUMN last_update_date HEADING 'Date'
TTITLE skip 5 CENTER 'MATERIAL REVIEW REPORT' skip 1 -
CENTER 'Disposition History' skip 3 -
LEFT 'NCM Number :' ncm_no skip 1 -
LEFT 'Part Number :' part_no skip 1 -
LEFT 'Revision :' revision skip 1 -
LEFT 'Description :' part_desc skip 3
COLUMN title FORMAT A80 HEADING 'Details'
SELECT ' ' as title FROM DUAL WHERE &&2 = 1;
DECLARE
1_prev q_&3._ncm_disp_dtl_hist_v%ROWTYPE;
first BOOLEAN := true;
BEGIN
IF &2 = 1 THEN
FOR r IN (SELECT *
FROM q_&3._ncm_disp_dtl_hist_v
WHERE source_reference_id = &1
ORDER BY source_ref_line_id, disposition_number, creation_date)
LOOP
IF first THEN
dbms_output.put_line('Disposition ' ll r.disposition_number ll ' created:
'llto_char(r.creation_date, 'DD-MON-YYYY HH24:MI:SS') ll ' by '
ll r.created_by);
dbms_output.put_line('.');
1_prev :=r;
first := false;
ELSE
IF (1_prev.source_ref_line_id <> r.source_ref_line_id) OR
(1_prev.disposition_number <> r.disposition_number)THEN
dbms_output.put_line('Disposition ' ll r.disposition_number ll ' created:
'llto_char(r.creation_date, 'DD-MON-YYYY HH24:MI:SS') ll ' by '
11 r.created_by);
dbms_output.put_line('.');
ELSE
dbms_output.put_line(Record updated: 'llto_char(r.creation_date, 'DD-MON_YYYY HH24:MI:SS') ll ' by '
11 r.created_by);
IF r.disposition_status <> 1_prev.disposition_status THEN
dbms_output.put_line('Disposition status updated from: 'll
1_prev.disposition_status ll ' to: 'll
r.disposition_status);
END IF;
IF r.disposition <> 1_prev.disposition THEN
dbms_output.put_line('Description updated from: 'll
1_prev.disposition ll' to: 'll
r.disposition);
END IF;
--
--
dbms_output.put_line('.');
END IF;
1_prev :=r;
END IF;
END LOOP;
END IF;
END;