[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** SELECT HEADER **
****************************/
CURSOR sch_hdr_c IS
SELECT
CSH.SCHEDULE_ID SCHEDULE_ID,
CSH.SCHEDULE_TYPE SCHEDULE_TYPE,
CSH.BATCH_ID BATCH_ID --Bug 2064311
FROM
ECE_TP_DETAILS ETD,
PO_VENDOR_SITES PVS,
CHV_SCHEDULE_HEADERS CSH
WHERE
CSH.SCHEDULE_STATUS = 'CONFIRMED'
AND EXISTS (SELECT 1 FROM CHV_ITEM_ORDERS CIO
WHERE CIO.SCHEDULE_ID = CSH.SCHEDULE_ID)
AND ETD.EDI_FLAG = 'Y' -- EDI
AND ETD.DOCUMENT_ID = P_TRANSACTION_TYPE --ship SSSO,plan SPSO
AND P_TRANSACTION_TYPE = DECODE(SCHEDULE_TYPE, 'SHIP_SCHEDULE',
'SSSO', 'SPSO')
AND ((CSH.SCHEDULE_ID = P_SCHEDULE_ID
AND P_SCHEDULE_ID <> 0)
OR (P_SCHEDULE_ID = 0
AND NVL(CSH.COMMUNICATION_CODE,'NONE') IN ('BOTH','EDI')))
AND CSH.BATCH_ID = decode(P_BATCH_ID,0,CSH.BATCH_ID,P_BATCH_ID) --Bug 2064311
AND CSH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PVS.TP_HEADER_ID = ETD.TP_HEADER_ID
AND ETD.DOCUMENT_ID = DECODE(SCHEDULE_TYPE, 'SHIP_SCHEDULE',
'SSSO', 'SPSO')
FOR UPDATE;
** SELECT ITEM **
**************************/
DECLARE
x_item_detail_sequence NUMBER :=0;
SELECT
CSI.SCHEDULE_ID SCHEDULE_ID,
CSI.SCHEDULE_ITEM_ID SCHEDULE_ITEM_ID
FROM
CHV_SCHEDULE_ITEMS CSI
WHERE
CSI.SCHEDULE_ID = REC_HDR.SCHEDULE_ID
AND EXISTS (SELECT 1 FROM CHV_ITEM_ORDERS CIO
WHERE CIO.SCHEDULE_ID = CSI.SCHEDULE_ID);
Select count(*)
Into x_item_order
From chv_item_orders
Where schedule_id = rec_hdr.schedule_id;
** select the last sequence number assigned to **
** the detail record of the same schedule item id. **
*********************************************************/
xProgress := 'SPSO2B-10-1030';
Select count(schedule_id)
Into x_item_detail
From chv_item_orders
Where schedule_id = rec_hdr.schedule_id
And schedule_item_id = rec_item.schedule_item_id;
SELECT max(schedule_item_detail_sequence)
INTO x_item_detail_sequence
FROM ece_spso_item_det
WHERE schedule_id = rec_item.schedule_id
AND schedule_item_id= rec_item.schedule_item_id;
END; -- select max sequence block
** SELECT AND INSERT ITEM DETAIL **
**************************************/
DECLARE
TYPE tbl_hz_type IS TABLE OF VARCHAR2(25)
INDEX BY BINARY_INTEGER;
BEGIN -- begin select detail block
/****************
** Description **
****************/
-- Get BUCET_DESCRIPTION from CHV_HORIZONTAL_SCHEDULES
xProgress := 'SPSO2B-10-1040';
SELECT *
INTO rec_hz
FROM chv_horizontal_schedules
WHERE schedule_item_id = rec_item.schedule_item_id
AND schedule_id = rec_item.schedule_id
AND row_type = 'BUCKET_DESCRIPTOR';
BEGIN -- START DATE select block
xProgress := 'SPSO2B-10-1070';
SELECT *
INTO rec_hz
FROM chv_horizontal_schedules
WHERE schedule_item_id = rec_item.schedule_item_id
AND schedule_id = rec_item.schedule_id
AND row_type = 'BUCKET_START_DATE';
END; -- START DATE select block
BEGIN -- END DATE select block
xProgress := 'SPSO2B-10-1100';
SELECT *
INTO rec_hz
FROM chv_horizontal_schedules
WHERE schedule_item_id = rec_item.schedule_item_id
AND schedule_id = rec_item.schedule_id
AND row_type = 'BUCKET_END_DATE';
END; -- END DATE select block
SELECT *
INTO rec_hz
FROM chv_horizontal_schedules
WHERE schedule_item_id = rec_item.schedule_item_id
AND schedule_id = rec_item.schedule_id
AND row_type = 'RELEASE_QUANTITY';
END; -- RELEASE QUANTITY select block
BEGIN -- FORECAST QUANTITY select block
xProgress := 'SPSO2B-10-1160';
SELECT *
INTO rec_hz
FROM chv_horizontal_schedules
WHERE schedule_item_id = rec_item.schedule_item_id
AND schedule_id = rec_item.schedule_id
AND row_type = 'FORECAST_QUANTITY';
END; -- FORECAST QUANTITY select block
BEGIN -- TOTAL QUANTITY select block
xProgress := 'SPSO2B-10-1190';
SELECT *
INTO rec_hz
FROM chv_horizontal_schedules
WHERE schedule_item_id = rec_item.schedule_item_id
AND schedule_id = rec_item.schedule_id
AND row_type = 'TOTAL_QUANTITY';
END; -- TOTAL QUANTITY select block
END; -- end select detail block
** insert PAST DUE bucketed requirments detail **
**************************************************/
/* BEGIN -- PAST DUE insert block
-- incerment detail record sequence counter
x_item_detail_sequence := NVL(x_item_detail_sequence,0) + 1;
INSERT INTO ECE_SPSO_ITEM_DET
(
COMMUNICATION_METHOD ,
TRANSACTION_TYPE ,
RUN_ID ,
SCHEDULE_ITEM_DETAIL_SEQUENCE ,
SCHEDULE_ID ,
SCHEDULE_ITEM_ID ,
DETAIL_CATEGORY , -- bucketed requirments
DETAIL_DESCRIPTOR , -- Past Due
STARTING_DATE ,
ENDING_DATE ,
FORECAST_QUANTITY ,
RELEASE_QUANTITY ,
TOTAL_QUANTITY ,
TRANSACTION_RECORD_ID
)
VALUES
(
p_run_id,
x_item_detail_sequence,
rec_hz.schedule_id,
rec_hz.schedule_item_id,
'REQUIREMENT',
tbl_desc(1),
TO_DATE(NVL(tbl_start(1),'1901/01/01'), 'YYYY/MM/DD'),
TO_DATE(tbl_end(1), 'YYYY/MM/DD'),
NVL(TO_NUMBER(tbl_for(1)),0),
NVL(TO_NUMBER(tbl_rel(1)),0),
NVL(TO_NUMBER(tbl_tot(1)),0),
ece_spso_item_det_s.nextval
);
SELECT
ece_spso_item_det_s.currval
INTO
l_transaction_number
FROM
dual;
END; -- end select detail block
** insert ALL OTHER bucketed requirments detail **
***************************************************/
BEGIN -- ALL OTHER buckets insert block
-- loop until reach NULL columns.
<>
xProgress := 'SPSO2B-10-1220';
INSERT INTO ECE_SPSO_ITEM_DET
(
/* COMMUNICATION_METHOD ,
TRANSACTION_TYPE ,
*/
RUN_ID ,
SCHEDULE_ITEM_DETAIL_SEQUENCE ,
SCHEDULE_ID ,
SCHEDULE_ITEM_ID ,
DETAIL_CATEGORY , -- Bucketed Requirments
DETAIL_DESCRIPTOR , -- All other buckets
STARTING_DATE ,
ENDING_DATE ,
FORECAST_QUANTITY ,
RELEASE_QUANTITY ,
TOTAL_QUANTITY ,
TRANSACTION_RECORD_ID
)
VALUES
(
p_run_id,
x_item_detail_sequence,
rec_hz.schedule_id,
rec_hz.schedule_item_id,
'REQUIREMENT',
tbl_desc(i),
TO_DATE(tbl_start(i), 'YYYY/MM/DD'),
TO_DATE(tbl_end(i), 'YYYY/MM/DD'),
NVL(TO_NUMBER(tbl_for(i)),0),
NVL(TO_NUMBER(tbl_rel(i)),0),
NVL(TO_NUMBER(tbl_tot(i)),0),
ece_spso_item_det_s.nextval
);
SELECT
ECE_SPSO_ITEM_DET_S.currval
INTO
l_transaction_number
FROM
dual;
SELECT POLL.QUANTITY ITEM_DET_SHIP_QUANTITY,
MUM.UOM_CODE ITEM_DET_UOM_CODE,
HRL.LOCATION_CODE ITEM_DET_ST_LOC_CODE,
HRL.ECE_TP_LOCATION_CODE ITEM_DET_ST_LOC_CODE_EXT,
HRL.ADDRESS_LINE_1 ITEM_DET_ST_ADDR_1,
HRL.ADDRESS_LINE_2 ITEM_DET_ST_ADDR_2,
HRL.ADDRESS_LINE_3 ITEM_DET_ST_ADDR_3,
HRL.TOWN_OR_CITY ITEM_DET_ST_CITY,
HRL.POSTAL_CODE ITEM_DET_ST_POSTAL_CODE,
HRL.COUNTRY ITEM_DET_ST_COUNTRY,
HRL.REGION_1 ITEM_DET_ST_COUNTY,
HRL.REGION_2 ITEM_DET_ST_STATE,
HRL.REGION_3 ITEM_DET_ST_REGION_3,
HRL.TELEPHONE_NUMBER_1 ITEM_DET_ST_PHONE,
POH.SEGMENT1||'-'||to_char(POR.RELEASE_NUM) DOCUMENT_RELEASE_NUMBER,
POL.LINE_NUM DOCUMENT_LINE_NUMBER,
POLL.LINE_LOCATION_ID LINE_LOCATION_ID
FROM CHV_ITEM_ORDERS CIO, PO_LINE_LOCATIONS POLL,
HR_LOCATIONS HRL, MTL_UNITS_OF_MEASURE MUM,
PO_HEADERS POH, PO_LINES POL, PO_RELEASES POR
WHERE CIO.SCHEDULE_ID = rec_item.schedule_id
AND CIO.SCHEDULE_ITEM_ID = rec_item.schedule_item_id
AND CIO.DOCUMENT_HEADER_ID = POLL.PO_HEADER_ID
AND CIO.DOCUMENT_LINE_ID = POLL.PO_LINE_ID
AND CIO.DOCUMENT_SHIPMENT_ID = POLL.LINE_LOCATION_ID
AND CIO.PURCHASING_UNIT_OF_MEASURE = MUM.UNIT_OF_MEASURE(+)
AND HRL.LOCATION_ID(+) = POLL.SHIP_TO_LOCATION_ID
AND TRUNC(NVL(POLL.PROMISED_DATE,POLL.NEED_BY_DATE)) between
TO_DATE(NVL(tbl_start(i),'1901/01/01'),'YYYY/MM/DD')
AND TO_DATE(tbl_end(i), 'YYYY/MM/DD')
AND POLL.PO_HEADER_ID = POH.PO_HEADER_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID
AND POR.PO_HEADER_ID = POH.PO_HEADER_ID;
SELECT max(schedule_ship_id)
INTO x_schedule_ship_id
FROM ece_spso_ship_det
WHERE schedule_id = rec_item.schedule_id
AND schedule_item_id= rec_item.schedule_item_id
AND schedule_item_detail_sequence = x_item_detail_sequence;
INSERT INTO ECE_SPSO_SHIP_DET
(
RUN_ID,
SCHEDULE_SHIP_ID,
SCHEDULE_ID ,
SCHEDULE_ITEM_ID ,
SCHEDULE_ITEM_DETAIL_SEQUENCE,
ITEM_DET_SHIP_QUANTITY,
ITEM_DET_UOM_CODE,
ITEM_DET_ST_LOC_CODE,
ITEM_DET_ST_LOC_CODE_EXT,
ITEM_DET_ST_ADDR_1,
ITEM_DET_ST_ADDR_2,
ITEM_DET_ST_ADDR_3,
ITEM_DET_ST_CITY,
ITEM_DET_ST_POSTAL_CODE,
ITEM_DET_ST_COUNTRY,
ITEM_DET_ST_REGION_1,
ITEM_DET_ST_REGION_2,
ITEM_DET_ST_REGION_3,
ITEM_DET_ST_PHONE,
TRANSACTION_RECORD_ID,
DOCUMENT_RELEASE_NUMBER,
DOCUMENT_LINE_NUMBER,
LINE_LOCATION_ID
)
VALUES
( p_run_id,
x_schedule_ship_id,
rec_item.schedule_id,
rec_item.schedule_item_id,
x_item_detail_sequence,
rec_ship_det.ITEM_DET_SHIP_QUANTITY,
rec_ship_det.ITEM_DET_UOM_CODE,
rec_ship_det.ITEM_DET_ST_LOC_CODE,
rec_ship_det.ITEM_DET_ST_LOC_CODE_EXT,
rec_ship_det.ITEM_DET_ST_ADDR_1,
rec_ship_det.ITEM_DET_ST_ADDR_2,
rec_ship_det.ITEM_DET_ST_ADDR_3,
rec_ship_det.ITEM_DET_ST_CITY,
rec_ship_det.ITEM_DET_ST_POSTAL_CODE,
rec_ship_det.ITEM_DET_ST_COUNTRY,
rec_ship_det.ITEM_DET_ST_COUNTY,
rec_ship_det.ITEM_DET_ST_STATE,
rec_ship_det.ITEM_DET_ST_REGION_3,
rec_ship_det.ITEM_DET_ST_PHONE,
ece_spso_ship_det_s.nextval,
rec_ship_det.DOCUMENT_RELEASE_NUMBER,
rec_ship_det.DOCUMENT_LINE_NUMBER,
rec_ship_det.LINE_LOCATION_ID
);
SELECT
ECE_SPSO_SHIP_DET_S.currval
INTO
l_transaction_number
FROM
dual;