33/39
Corrective Action Sample Code
Sample Code for Custom SQL*Plus Report
SET HEADING ON
SET VERIFY OFF
SET PAGESIZE 10000
REM
REM The first query is a join between CAR request and review.
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 car_no NOPRINT NEW_VALUE car_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 car_qty NOPRINT NEW VALUE car_qty
COLUMN car_source NOPRINT NEW_VALUE car_source
COLUMN car_severity NOPRINT NEW_VALUE car_severity
COLUMN car_status NOPRINT NEW_VALUE car_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 note_type FORMAT A15 TRUNCATE HEADING "Review Type"
COLUMN notes FORMAT A30 WORD_WRAPPED HEADING "Action"
COLUMN due_date FORMAT A10 TRUNCATE HEADING "Due Date"
COLUMN actual_resolution_date FORMAT A11 TRUNCATE HEADING "Actual Date"
REM
REM The following is the look and feel of the header block.
REM
TTITLE CENTER 'CAR REPORT' skip 1 -
CENTER today skip 3 -
LEFT 'CAR Number :' car_no skip 1 -
LEFT 'Part Number :' part_no skip 1 -
LEFT 'Description :' part_desc skip 1 -
LEFT 'Source :' car_source skip 1 -
LEFT 'Severity :' car_severity skip 1 -
LEFT 'Status :' car_status skip 2 -
SELECT sysdate today,
car_req.corrective_action_num car_no,
car_req.item part_no,
substr(msi.description, 1, 75) part_desc,
car_req.revision,
car_req.request_source car_source,
car_req.request_severity car_severity,
car_req.request_status car_status,
car_rvw.expected_rsolution_date due_date,
car_rvw.actual_rsolution_date actual_resolution_date,
car_rvw.action_type note_type,
car_rvw.action_description notes,
FROM q_&&2_request_v car_req,
q_&2._review_v car_rvw,
mtl_system_items_kfv msi
WHERE car_req.corrective_action_num = '&&1' AND
car_req.corrective_action_num = car_rvw.corrective_action_num (+) AND
car_req.item_id = msi.inventory_item_id (+) AND
car_req.organization_id = msi.organization_id (+)
/