[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT text
FROM ece_output
WHERE run_id = p_run_id
ORDER BY line_id;
SELECT ece_output_runs_s.NEXTVAL
INTO p_run_id
FROM sys.dual;
DELETE
FROM ece_output
WHERE run_id = p_run_id;
SELECT text
FROM ece_output
WHERE run_id = p_run_id
ORDER BY line_id;
SELECT ece_output_runs_s.NEXTVAL
INTO p_run_id
FROM sys.dual;
DELETE
FROM ece_output
WHERE run_id = p_run_id;
cHeader_select VARCHAR2(32000);
cItem_select VARCHAR2(32000);
cItem_D_select VARCHAR2(32000);
ece_extract_utils_pub.select_clause ( cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
l_header_tbl,
cHeader_select,
cHeader_from,
cHeader_where );
ece_extract_utils_pub.select_clause ( cTransaction_Type,
cCommunication_Method,
cItem_Interface,
l_item_tbl,
cItem_select,
cItem_from,
cItem_where );
cHeader_select := cHeader_select ||
cHeader_from ||
cHeader_where;
ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
cItem_select := cItem_select ||
cItem_from ||
cItem_where;
ec_debug.pl ( 3, 'cItem_select: ',cItem_select );
cHeader_select,
dbms_sql.native );
cHeader_select );
cItem_select,
dbms_sql.native );
cItem_select );
cHeader_select,
ece_extract_utils_PUB.G_MaxColWidth );
cItem_select,
ece_extract_utils_PUB.G_MaxColWidth );
select count(*) into
x_schedule_order from
chv_item_orders where
schedule_id = l_header_tbl(n_schedule_id_pos).value;
SELECT ece_spso_headers_s.nextval
INTO l_header_fkey
FROM sys.dual;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id => iRun_id,
cTransaction_Type => cTransaction_Type,
cCommunication_Method => cCommunication_Method,
cInterface_Table => cHeader_Interface,
p_source_tbl => l_header_tbl,
p_foreign_key => l_header_fkey );
select item_id into g_item_id from chv_schedule_items where
schedule_item_id = l_item_tbl(n_item_id_pos).value;
SELECT
paa.asl_id,
paa.enable_authorizations_flag,
paa.scheduler_id,
ppf.first_name,
ppf.last_name,
ppf.work_telephone,
paa.attribute_category,
paa.attribute1,
paa.attribute2,
paa.attribute3,
paa.attribute4,
paa.attribute5,
paa.attribute6,
paa.attribute7,
paa.attribute8,
paa.attribute9,
paa.attribute10,
paa.attribute11,
paa.attribute12,
paa.attribute13,
paa.attribute14,
paa.attribute15
INTO
x_asl_id,
x_enable_authorizations_flag,
x_scheduler_id,
x_scheduler_first_name,
x_scheduler_last_name,
x_scheduler_work_telephone,
x_asl_attribute_category,
x_asl_attribute1,
x_asl_attribute2,
x_asl_attribute3,
x_asl_attribute4,
x_asl_attribute5,
x_asl_attribute6,
x_asl_attribute7,
x_asl_attribute8,
x_asl_attribute9,
x_asl_attribute10,
x_asl_attribute11,
x_asl_attribute12,
x_asl_attribute13,
x_asl_attribute14,
x_asl_attribute15
FROM
po_asl_attributes paa,
per_all_people_f ppf
WHERE
paa.vendor_id = l_header_tbl(n_vendor_id_pos).value
AND paa.vendor_site_id = l_header_tbl(n_vendor_site_id_pos).value
AND paa.item_id = g_item_id --Bug 1705597
AND paa.using_organization_id = chv_inq_sv.get_asl_org(
l_header_tbl(n_organization_id_pos).value,
l_header_tbl(n_vendor_id_pos).value,
l_header_tbl(n_vendor_site_id_pos).value,
g_item_id) -- Bug 1705597
AND scheduler_id = ppf.person_id(+)
AND ppf.effective_start_date (+) >= trunc(SYSDATE)
AND ppf.effective_end_date (+) <= trunc(SYSDATE)
AND decode(hr_security.view_all,'Y','TRUE',hr_security.show_record('PER_ALL_PEOPLE_F',
ppf.person_id (+),
ppf.person_type_id (+),
ppf.employee_number (+),
ppf.applicant_number (+) ))= 'TRUE';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'ASL_ID',
'TABLE_NAME',
'PO_ASL_ATTRIBUTES' );
SELECT primary_vendor_item
INTO x_supplier_product_num
FROM po_approved_supplier_list
WHERE asl_id = x_asl_id;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'PRIMARY_VENDOR_ITEM',
'TABLE_NAME',
'PO_APPROVED_SUPPLIER_LIST' );
SELECT last_name,
first_name,
work_telephone
INTO x_planner_last_name,
x_planner_first_name,
x_planner_work_telephone
FROM mtl_system_items msi,
mtl_planners mpl,
per_all_people_f ppf
WHERE msi.organization_id = l_header_tbl(n_organization_id_pos).value
AND msi.inventory_item_id = g_item_id -- Bug 1705597
AND mpl.organization_id = l_header_tbl(n_organization_id_pos).value
AND msi.planner_code = mpl.planner_code(+)
AND mpl.employee_id = ppf.person_id(+)
AND ppf.effective_start_date (+) >= trunc(SYSDATE)
AND ppf.effective_end_date (+) <= trunc(SYSDATE)
AND decode(hr_security.view_all,'Y','TRUE',hr_security.show_record('PER_ALL_PEOPLE_F',
ppf.person_id (+),
ppf.person_type_id (+),
ppf.employee_number (+),
ppf.applicant_number (+) ))= 'TRUE';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'LAST_NAME',
'TABLE_NAME',
'MTL_SYSTEM_ITEMS' );
SELECT ece_spso_items_s.nextval
INTO l_item_fkey
FROM sys.dual;
Select count(schedule_id)
Into x_item_detail
From chv_item_orders
Where schedule_id = l_header_tbl(n_schedule_id_pos).value
And schedule_item_id = l_item_tbl(n_item_id_pos).value;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id => iRun_id,
cTransaction_Type => cTransaction_Type,
cCommunication_Method => cCommunication_Method,
cInterface_Table => cItem_Interface,
p_source_tbl => l_item_tbl,
p_foreign_key => l_item_fkey );
UPDATE ece_spso_items
SET supplier_product_number = x_supplier_product_num,
item_scheduler_last_name = x_scheduler_last_name,
item_scheduler_first_name = x_scheduler_first_name,
item_scheduler_work_telephone = x_scheduler_work_telephone,
item_planner_last_name = x_planner_last_name,
item_planner_first_name = x_planner_first_name,
item_planner_work_telephone = x_planner_work_telephone,
asl_attribute_category = x_asl_attribute_category,
asl_attribute1 = x_asl_attribute1,
asl_attribute2 = x_asl_attribute2,
asl_attribute3 = x_asl_attribute3,
asl_attribute4 = x_asl_attribute4,
asl_attribute5 = x_asl_attribute5,
asl_attribute6 = x_asl_attribute6,
asl_attribute7 = x_asl_attribute7,
asl_attribute8 = x_asl_attribute8,
asl_attribute9 = x_asl_attribute9,
asl_attribute10 = x_asl_attribute10,
asl_attribute11 = x_asl_attribute11,
asl_attribute12 = x_asl_attribute12,
asl_attribute13 = x_asl_attribute13,
asl_attribute14 = x_asl_attribute14,
asl_attribute15 = x_asl_attribute15,
ship_to_org_enable_cum_flag =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_enable_cum_flag_pos).value),
ship_to_org_cum_start_date =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,to_date(l_item_tbl(n_cum_period_pos).value,'YYYYMMDD HH24MISS')),
ship_to_org_name =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_name_pos).value),
ship_to_org_code =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_item_st_org_pos).value),
ship_to_org_address_line_1 =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_add_1_pos).value),
ship_to_org_address_line_2 =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_add_2_pos).value),
ship_to_org_address_line_3 =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_add_3_pos).value),
ship_to_org_city =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_city_pos).value),
ship_to_org_region_1 =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_county_pos).value),
ship_to_org_region_2 =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_state_pos).value),
ship_to_org_country =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_country_pos).value),
ship_to_org_postal_code =
DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
'N', NULL,l_item_tbl(n_st_postal_pos).value)
WHERE
transaction_record_id = l_item_fkey;
'ECE_NO_ROW_UPDATED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'SUPPLIER_PRODUCT_NUMBER',
'TABLE_NAME',
'ECE_SPSO_ITEMS' );
** SELECT HEADER **
****************************/
CURSOR sch_hdr_c IS
SELECT
csh.schedule_id SCHEDULE_ID,
CSH.BATCH_ID BATCH_ID, --Bug 2064311
csh.organization_id ORGANIZATION_ID,
csh.vendor_id VENDOR_ID,
csh.vendor_site_id VENDOR_SITE_ID,
csh.schedule_type SCHEDULE_TYPE,
csh.schedule_horizon_start FORECAST_HORIZON_START_DATE,
csh.edi_count EDI_COUNT,
ccp.cum_period_start_date SHIP_TO_ORG_CUM_START,
etd.document_id TRANSACTION_TYPE
FROM
chv_cum_periods ccp,
ece_tp_details etd,
po_vendor_sites pvs,
chv_schedule_headers csh,
chv_org_options coo
WHERE
csh.schedule_status = 'CONFIRMED'
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 CSH.BATCH_ID = decode(P_BATCH_ID,0,CSH.BATCH_ID,P_BATCH_ID) -- Bug 2064311
AND NVL(csh.communication_code,'NONE') IN ('BOTH','EDI')
AND csh.vendor_site_id = pvs.vendor_site_id
AND pvs.tp_header_id = etd.tp_header_id
AND csh.organization_id = ccp.organization_id(+)
AND csh.organization_id = coo.organization_id(+)
AND (
( coo.enable_cum_flag = 'N' )
or
( ( coo.enable_cum_flag = 'Y')
AND
(
(
ccp.cum_period_end_date IS NULL
AND csh.schedule_horizon_start >= ccp.cum_period_start_date
)
OR
( csh.schedule_horizon_start BETWEEN ccp.cum_period_start_date
AND ccp.cum_period_end_date
)
)
)
)
AND EXISTS (SELECT 1 FROM CHV_ITEM_ORDERS CIO
WHERE CIO.SCHEDULE_ID = CSH.SCHEDULE_ID)
ORDER BY
csh.schedule_id
FOR UPDATE;
** SELECT ITEM **
**************************/
DECLARE
x_transaction_date DATE;
SELECT
csi.schedule_id SCHEDULE_ID,
csi.schedule_item_id SCHEDULE_ITEM_ID,
csi.item_id ITEM_ID,
csi.starting_auth_quantity STARTING_AUTH_QUANTITY,
csi.starting_cum_quantity STARTING_CUM_QUANTITY,
coo.enable_cum_flag SHIP_TO_ORG_ENABLE_CUM_FLAG,
ccp.cum_period_start_date SHIP_TO_ORG_CUM_PERIOD_START,
csi.last_receipt_transaction_id LAST_RECEIPT_TRANSACTION_ID,
csi.purchasing_unit_of_measure PURCHASING_UNIT_OF_MEASURE
FROM
chv_schedule_headers csh,
chv_schedule_items csi,
chv_org_options coo,
chv_cum_periods ccp,
mtl_item_flexfields mif,
mtl_parameters mtp
WHERE
csi.schedule_id = rec_hdr.schedule_id
AND csi.schedule_id = csh.schedule_id
AND csi.organization_id = coo.organization_id
AND csi.organization_id = mtp.organization_id
AND csi.item_id = mif.item_id
AND csi.organization_id = mif.organization_id
AND csi.organization_id = ccp.organization_id(+)
AND (
(COO.ENABLE_CUM_FLAG = 'N')
OR
(
( COO.ENABLE_CUM_FLAG = 'Y')
AND
(
(
CCP.CUM_PERIOD_END_DATE IS NULL and csh.schedule_horizon_start >=
ccp.cum_period_start_date
)
OR (
CSH.SCHEDULE_HORIZON_START BETWEEN CCP.CUM_PERIOD_START_DATE
AND CCP.CUM_PERIOD_END_DATE
)
)
)
)
AND EXISTS (SELECT 1 FROM CHV_ITEM_ORDERS CIO
WHERE CIO.SCHEDULE_ITEM_ID = CSI.SCHEDULE_ITEM_ID)
ORDER BY
csi.schedule_id,
csi.schedule_item_id,
mif.item_id,
mtp.organization_code;
** select the last sequence number assigned to **
** the detail record of the same schedule item id. **
*********************************************************/
BEGIN
xProgress := 'SPSOB-30-1020';
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;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'MAX(SCHEDULE_ITEM_DETAIL_SEQUENCE)',
'TABLE_NAME',
'ECE_SPSO_ITEM_DET' );
SELECT transaction_record_id
INTO x_transaction_record_id
FROM ece_spso_items
WHERE schedule_id = rec_item.schedule_id
AND schedule_item_id = rec_item.schedule_item_id
AND run_id = p_run_id;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'TRANSACTION_RECORD_ID',
'TABLE_NAME',
'ECE_SPSO_ITEMS' );
** SELECT ENABLE_AUTHORIZATION_FLAG **
** FROM APPROVED SUPPLIER LIST TABLE **
** FOR THE SPECIFIED VENODR, SITE, ITEM AND **
** ORGANIZATION. **
*************************************************/
BEGIN -- ASL block
xProgress := 'SPSOB-30-1040';
SELECT
enable_authorizations_flag
INTO
x_enable_authorizations_flag
FROM po_asl_attributes paa
WHERE vendor_id = rec_hdr.vendor_id
AND vendor_site_id = rec_hdr.vendor_site_id
AND item_id = rec_item.item_id
AND using_organization_id = chv_inq_sv.get_asl_org(
rec_hdr.organization_id,
rec_hdr.vendor_id,
rec_hdr.vendor_site_id,
rec_item.item_id);
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'ENABLE_AUTHORIZATIONS_FLAG',
'TABLE_NAME',
'PO_ASL_ATTRIBUTES' );
** SELECT AND INSERT ITEM DETAIL **
**************************************/
DECLARE
x_start_date DATE;
SELECT authorization_code AUTHORIZATION_CODE,
cutoff_date CUTOFF_DATE,
schedule_quantity SCHEDULE_QUANTITY
FROM chv_authorizations
WHERE reference_id = rec_item.schedule_item_id
AND reference_type = 'SCHEDULE_ITEMS';
** insert prior authorization detail **
***************************************/
xProgress := 'SPSOB-30-1050';
ece_spso_trans1.update_chv_schedule_headers ( rec_hdr.transaction_type,
rec_hdr.schedule_id,
rec_hdr.batch_id, --Bug 2064311
rec_hdr.edi_count );
Select count(*)
Into x_item_order
From chv_item_orders
Where schedule_id = rec_hdr.schedule_id;
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;
INSERT INTO ece_spso_item_det
(
run_id,
schedule_item_detail_sequence,
schedule_id,
schedule_item_id,
detail_category,
detail_descriptor,
starting_date,
forecast_quantity,
release_quantity,
total_quantity,
transaction_record_id
)
VALUES
(
p_run_id,
x_item_detail_sequence,
rec_item.schedule_id,
rec_item.schedule_item_id,
'AUTHORIZATION',
'PRIOR',
rec_hdr.forecast_horizon_start_date,
0,
0,
NVL(rec_item.starting_auth_quantity,0),
ece_spso_item_det_s.nextval
);
select
ece_spso_item_det_s.currval
into
l_transaction_number
from
dual;
** insert current authorization detail **
****************************************/
-- authorization start date is the cum start date.
-- cum_flag is enabled since cum quantity is included
-- in authorization quantity.
xProgress := 'SPSOB-30-1100';
INSERT INTO ece_spso_item_det
(
run_id,
schedule_item_detail_sequence,
schedule_id,
schedule_item_id,
detail_category,
detail_descriptor,
starting_date,
ending_date,
forecast_quantity,
release_quantity,
total_quantity,
transaction_record_id
)
VALUES
(
p_run_id,
x_item_detail_sequence,
rec_item.schedule_id,
rec_item.schedule_item_id,
'AUTHORIZATION',
rec_detail.authorization_code,
x_start_date,
rec_detail.cutoff_date,
0,
0,
NVL(rec_detail.schedule_quantity,0),
ece_spso_item_det_s.nextval
);
select
ece_spso_item_det_s.currval
into
l_transaction_number
from
dual;
** insert last receipt detail **
********************************/
xProgress := 'SPSOB-30-1130';
UPDATE ece_spso_items
SET last_receipt_shipment_code = x_shipment_num,
last_receipt_date = x_transaction_date,
last_receipt_quantity = x_last_quantity
WHERE transaction_record_id = x_transaction_record_id;
INSERT INTO ece_spso_item_det
(
run_id,
schedule_item_detail_sequence,
schedule_id,
schedule_item_id,
detail_category,
detail_descriptor,
starting_date,
forecast_quantity,
release_quantity,
total_quantity,
document_type,
document_number,
transaction_record_id
)
VALUES
(
p_run_id,
x_item_detail_sequence,
rec_item.schedule_id,
rec_item.schedule_item_id,
'RECEIPT',
'LAST',
x_transaction_date,
0,
0,
NVL(x_last_quantity,0),
'SHIPMENT',
x_shipment_num,
ece_spso_item_det_s.nextval
);
select
ece_spso_item_det_s.currval
into
l_transaction_number
from
dual;
** insert CUM receipt detail **
********************************/
xProgress := 'SPSOB-30-1170';
INSERT INTO ece_spso_item_det
(
run_id,
schedule_item_detail_sequence,
schedule_id,
schedule_item_id,
detail_category,
detail_descriptor,
starting_date,
ending_date,
forecast_quantity,
release_quantity,
total_quantity,
transaction_record_id
)
VALUES
(
p_run_id,
x_item_detail_sequence,
rec_item.schedule_id,
rec_item.schedule_item_id,
'RECEIPT',
'CUMULATIVE',
rec_item.ship_to_org_cum_period_start,
rec_hdr.forecast_horizon_start_date,
0,
0,
NVL(rec_item.starting_cum_quantity,0),
ece_spso_item_det_s.nextval
);
select
ece_spso_item_det_s.currval
into
l_transaction_number
from
dual;
UPDATE ece_spso_items
SET last_receipt_cum_qty = NVL(rec_item.starting_cum_quantity,0)
WHERE transaction_record_id = x_transaction_record_id;
** procedure UPDATE_CHV_SCHEDULE_HEADERS **
** This procedure will update the records in CHV_SCHEDULE_HEADERS table**
** which have been extracted for EDI transmission. The communication **
** code will be set according to their inital value. If the record is **
** flaged for BOTH print and edi, after performing EDI transaction it **
** will be reset to print. If the initial vaues is EDI then after **
** completion of transaction the code will be set to NONE. **
*************************************************************************/
PROCEDURE Update_CHV_Schedule_Headers ( p_transaction_type IN VARCHAR2,
p_schedule_id IN INTEGER := 0,
p_batch_id IN NUMBER,
p_edi_count IN NUMBER := 0 )
IS
xProgress VARCHAR2(30) := NULL;
ec_debug.push ( 'ECE_SPSO_TRANS1.UPDATE_CHV_SCHEDULE_HEADERS' );
UPDATE chv_schedule_headers
SET communication_code = DECODE ( communication_code,
'BOTH', 'PRINT',
'EDI', 'NONE',
'NONE', 'NONE',
'PRINT', 'PRINT',
NULL ),
last_update_date = SYSDATE,
last_updated_by = -1,
last_edi_date = SYSDATE,
edi_count = NVL(p_edi_count,0) + 1
WHERE ((schedule_id = p_schedule_id AND
p_schedule_id <> 0) OR
(p_schedule_id = 0 AND
NVL(communication_code, 'NONE') IN ('BOTH','EDI')))
AND p_transaction_type = DECODE ( schedule_type,
'SHIP_SCHEDULE', 'SSSO',
'SPSO' )
AND batch_id = decode(p_batch_id,0,batch_id,p_batch_id); -- Bug 2064311
ec_debug.pop ( 'ECE_SPSO_TRANS1.UPDATE_CHV_SCHEDULE_HEADERS' );
END Update_CHV_Schedule_Headers;
** 4. Delete data from Interface Tables. **
** To use this procedure must have access to the procedures in **
** ECE_FLATFILE package. **
** HISTORY: **
** Apr 3, 1995 wlang Created. **
** **
** May 15, 1996 mbabaloy **
*************************************************************************/
PROCEDURE Put_Data_To_Output_Table ( p_communication_method IN VARCHAR2,
p_transaction_type IN VARCHAR2, -- plan SPSO, ship SSSO
p_output_width IN INTEGER,
p_run_id IN INTEGER,
p_header_interface IN VARCHAR2 := 'ECE_SPSO_HEADERS',
p_item_interface IN VARCHAR2 := 'ECE_SPSO_ITEMS',
p_item_d_interface IN VARCHAR2 := 'ECE_SPSO_ITEM_DET',
p_ship_d_interface IN VARCHAR2 := 'ECE_SPSO_SHIP_DET')
IS
xProgress VARCHAR2(30);
x_header_select VARCHAR2(32000);
x_item_select VARCHAR2(32000);
x_item_d_select VARCHAR2(32000);
x_ship_d_select VARCHAR2(32000);
x_header_delete1 VARCHAR2(32000);
x_item_delete1 VARCHAR2(32000);
x_item_d_delete1 VARCHAR2(32000);
x_ship_d_delete1 VARCHAR2(32000);
x_header_delete2 VARCHAR2(32000);
x_item_delete2 VARCHAR2(32000);
x_item_d_delete2 VARCHAR2(32000);
x_ship_d_delete2 VARCHAR2(32000);
c_header_select VARCHAR2(100);
ece_flatfile_pvt.select_clause ( p_transaction_type,
p_communication_method,
p_header_interface,
x_header_x_interface,
l_header_tbl,
c_header_common_key_name,
x_header_select,
x_header_from,
x_header_where );
ece_flatfile_pvt.select_clause ( p_transaction_type,
p_communication_method,
p_item_interface,
x_item_x_interface,
l_item_tbl,
c_item_common_key_name,
x_item_select,
x_item_from ,
x_item_where );
ece_flatfile_pvt.select_clause ( p_transaction_type,
p_communication_method,
p_item_d_interface,
x_item_d_x_interface,
l_item_d_tbl,
c_item_d_common_key_name,
x_item_d_select,
x_item_d_from ,
x_item_d_where );
ece_flatfile_pvt.select_clause ( p_transaction_type,
p_communication_method,
p_ship_d_interface,
x_ship_d_x_interface,
l_ship_d_tbl,
c_ship_d_common_key_name,
x_ship_d_select,
x_ship_d_from ,
x_ship_d_where );
REM Select Data1, Data2, Data3...........
REM From v_header_Interface A, v_item_Interface B,
REM v_item_details_Interface C,
REM v_header_Interface_X D, v_item_Interface_X E,
REM v_item_details_Interface_X F
REM Where A.Transaction_Record_ID = D.Transaction_Record_ID (+)
REM and B.Transaction_Record_ID = E.Transaction_Record_ID (+)
REM and C.Transaction_Record_ID = F.Transaction_Record_ID (+)
REM $$$$$ (Customization should be added here) $$$$$$
REM and A.Communication_Method = 'EDI'
REM and A.xxx = B.xxx ........
REM and B.yyy = C.yyy .......
REM -------------------------------------------------------------------------*/
/* --------------------------------------------------------------------------
:schedule_id is a place holder for foreign key value.
A PL/SQL table (list of values) will be used to store data.
Procedure ece_flatfile_pvt.Find_pos will be used to locate the specific
data value in the PL/SQL table.
dbms_sql (Native Oracle db functions that come with every Oracle Apps)
dbms_sql.bind_variable will be used to assign data value to :schedule_id
Let's use the above example:
1. Execute dynamic SQL 1 for headers (A) data
Get value of A.xxx (foreign key to B)
2. bind value A.xxx to variable B.xxx
3. Execute dynamic SQL 2 for lines (B) data
Get value of B.yyy (foreign key to C)
4. bind value B.yyy to variable C.yyy
5. Execute dynamic SQL 3 for line_details (C) data
--------------------------------------------------------------------------*/
xProgress := 'SPSOB-50-1030';
x_header_select := x_header_select ||
',' ||
p_header_interface ||
'.ROWID,' ||
x_header_x_interface ||
'.ROWID,' ||
p_header_interface ||
'.SCHEDULE_ID' ;
ec_debug.pl ( 3, 'x_header_select: ',x_header_select );
x_item_select := x_item_select ||
',' ||
p_item_interface ||
'.ROWID,' ||
x_item_x_interface ||
'.ROWID,' ||
p_item_interface ||
'.SCHEDULE_ITEM_ID' ;
ec_debug.pl ( 3, 'x_item_select: ',x_item_select );
x_item_d_select := x_item_d_select ||
',' ||
p_item_d_interface ||
'.ROWID,' ||
x_item_d_x_interface ||
'.ROWID, ' ||
p_item_d_interface ||
'.SCHEDULE_ITEM_DETAIL_SEQUENCE';
ec_debug.pl ( 3, 'x_item_d_select: ',x_item_d_select );
x_ship_d_select := x_ship_d_select ||
',' ||
p_ship_d_interface ||
'.ROWID,' ||
x_ship_d_x_interface ||
'.ROWID';
ec_debug.pl ( 3, 'x_ship_d_select: ',x_ship_d_select );
x_header_select := x_header_select ||
x_header_from ||
x_header_where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'x_header_select: ',x_header_select);
x_item_select := x_item_select || x_item_from || x_item_where;
ec_debug.pl ( 3, 'x_item_select: ',x_item_select);
x_item_d_select := x_item_d_select ||
x_item_d_from ||
x_item_d_where ;
ec_debug.pl ( 3, 'x_item_d_select: ',x_item_d_select );
x_ship_d_select := x_ship_d_select ||
x_ship_d_from ||
x_ship_d_where ;
ec_debug.pl ( 3, 'x_ship_d_select: ',x_ship_d_select );
x_header_delete1 := 'DELETE FROM ' ||
p_header_interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'x_header_delete1: ',x_header_delete1 );
x_item_delete1 := 'DELETE FROM ' ||
p_item_interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'x_item_delete1: ',x_item_delete1 );
x_item_d_delete1 := 'DELETE FROM ' ||
p_item_d_interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'x_item_d_delete1: ',x_item_d_delete1 );
x_ship_d_delete1 := 'DELETE FROM ' ||
p_ship_d_interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'x_ship_d_delete1: ',x_ship_d_delete1 );
x_header_delete2 := 'DELETE FROM ' ||
x_header_x_interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'x_header_delete2: ',x_header_delete2 );
x_item_delete2 := 'DELETE FROM ' ||
x_item_x_interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'x_item_delete2: ',x_item_delete2 );
x_item_d_delete2 := 'DELETE FROM ' ||
x_item_d_x_interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'x_item_d_delete2: ',x_item_d_delete2 );
x_ship_d_delete2 := 'DELETE FROM ' ||
x_ship_d_x_interface ||
' WHERE ROWID = :col_rowid';
x_header_select,
dbms_sql.native );
x_header_select );
x_item_select,
dbms_sql.native );
x_item_select );
x_item_d_select,
dbms_sql.native );
x_item_d_select );
x_ship_d_select,
dbms_sql.native );
x_ship_d_select );
x_header_delete1,
dbms_sql.native );
x_header_delete1 );
x_item_delete1,
dbms_sql.native );
x_item_delete1 );
x_item_d_delete1,
dbms_sql.native );
x_item_d_delete1 );
x_ship_d_delete1,
dbms_sql.native );
x_ship_d_delete1 );
x_header_delete2,
dbms_sql.native );
x_header_delete2 );
x_item_delete2,
dbms_sql.native );
x_item_delete2 );
x_item_d_delete2,
dbms_sql.native );
x_item_d_delete2 );
x_ship_d_delete2,
dbms_sql.native );
x_ship_d_delete2 );
x_header_select,
ece_flatfile_pvt.G_MaxColWidth );
x_item_select,
ece_flatfile_pvt.G_MaxColWidth );
x_item_d_select,
ece_flatfile_pvt.G_MaxColWidth );
x_ship_d_select,
ece_flatfile_pvt.G_MaxColWidth);