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 (+)

/