[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct cr.repair_line_id repair_line_id
FROM csd_repairs cr
, csd_repair_estimate cre
WHERE cr.repair_line_id = cre.repair_line_id
AND EXISTS ( SELECT 'x'
FROM csd_repair_estimate_lines crel, cs_estimate_details ced
WHERE cre.repair_estimate_id = crel.repair_estimate_id
AND crel.estimate_detail_id = ced.estimate_detail_id
AND ced.order_line_id is not null)
AND NOT EXISTS ( SELECT 'x'
FROM csd_repair_actuals cra
WHERE cr.repair_line_id = cra.repair_line_id)
AND cr.repair_line_id >= p_start_rep_line_id
AND cr.repair_line_id <= p_end_rep_line_id;
APPS.CSD_REPAIR_ACTUALS_PKG.INSERT_ROW( px_REPAIR_ACTUAL_ID => l_repair_actual_id
,p_OBJECT_VERSION_NUMBER => 1
,p_REPAIR_LINE_ID => act_hdr_arr(j)
,P_CREATED_BY => fnd_global.user_id
,P_CREATION_DATE => sysdate
,P_LAST_UPDATED_BY => fnd_global.user_id
,P_LAST_UPDATE_DATE => sysdate
,P_LAST_UPDATE_LOGIN => fnd_global.login_id
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null);
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_ACTUALS',
'CSD_REPAIR_ACTUALS',
act_hdr_arr(j),
sysdate,
v_error_text,
'11.5.10');
raise_application_error( -20000, 'Error while migrating ACTUALS Headers data: Error while inserting into CSD_REPAIR_ACTUALS. '|| v_error_text);
/* Update all the 1159 not interfaced to OM charge lines in */
/* cs_estimate_details table to charge_line_type = ESTIMATE */
/* from charge_line_type = ACTUAL */
/*-------------------------------------------------------------------------------*/
PROCEDURE csd_charge_estimate_lines_mig3(p_slab_number IN NUMBER DEFAULT 1)
IS
TYPE EST_LINES_REC_ARRAY_TYPE IS VARRAY(1000) OF NUMBER;
SELECT ced.estimate_detail_id
FROM cs_estimate_details ced
, csd_repair_estimate_lines cr
WHERE cr.estimate_detail_id = ced.estimate_detail_id
AND ced.charge_line_type = 'ACTUAL'
AND ced.order_line_id is null
AND ced.original_source_code = 'DR'
AND ced.source_code = 'DR'
AND NOT EXISTS ( SELECT 'x'
FROM csd_repair_actual_lines cral
WHERE cral.estimate_detail_id = cr.estimate_detail_id)
AND ced.estimate_detail_id >= p_start_est_det_id
AND ced.estimate_detail_id <= p_end_est_det_id;
SELECT ced.estimate_detail_id
FROM cs_estimate_details ced
, csd_repairs cr
, csd_repair_estimate cre
, csd_repair_estimate_lines crel
WHERE cr.repair_line_id = cre.repair_line_id
AND cre.repair_estimate_id = crel.repair_estimate_id
AND crel.estimate_detail_id = ced.estimate_detail_id
AND ced.charge_line_type = 'ACTUAL'
AND ced.order_line_id is null
AND ced.original_source_code = 'DR'
AND ced.source_code = 'DR'
AND NOT EXISTS ( SELECT 'x'
FROM csd_repair_actual_lines cral
WHERE cral.estimate_detail_id = crel.estimate_detail_id)
AND cr.repair_line_id >= p_start_rep_line_id
AND cr.repair_line_id <= p_end_rep_line_id;
UPDATE CS_ESTIMATE_DETAILS
SET CHARGE_LINE_TYPE = 'ESTIMATE'
WHERE ESTIMATE_DETAIL_ID = est_lines_arr(j);
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_ESTIMATE_LINES',
'CS_ESTIMATE_DETAILS',
est_lines_arr(j),
sysdate,
v_error_text,
'11.5.10');
/* Update all records in 1159 CSD_REPAIR_ESTIMATE_LINES table for*/
/* New Cols Added : EST_LINE_SOURCE_TYPE_CODE = 'MANUAL' */
/* , EST_LINE_SOURCE_ID1 = NULL */
/* , EST_LINE_SOURCE_ID2 = NULL */
/* , RO_SERVICE_CODE_ID = NULL */
/* */
/*-------------------------------------------------------------------------------*/
PROCEDURE csd_repair_estimate_lines_mig3(p_slab_number IN NUMBER DEFAULT 1)
IS
TYPE REP_EST_LINES_REC_ARRAY_TYPE IS VARRAY(1000) OF NUMBER;
SELECT cr.repair_estimate_line_id
FROM csd_repair_estimate_lines cr
WHERE cr.est_line_source_type_code is null
AND cr.repair_estimate_line_id >= p_start_rep_est_lin_id
AND cr.repair_estimate_line_id <= p_end_rep_est_lin_id;
SELECT crel.repair_estimate_line_id
FROM csd_repairs cr
, csd_repair_estimate cre
, csd_repair_estimate_lines crel
WHERE cr.repair_line_id = cre.repair_line_id
AND cre.repair_estimate_id = crel.repair_estimate_id
AND crel.est_line_source_type_code is null
AND cr.repair_line_id >= p_start_rep_line_id
AND cr.repair_line_id <= p_end_rep_line_id;
UPDATE CSD_REPAIR_ESTIMATE_LINES
SET EST_LINE_SOURCE_TYPE_CODE = 'MANUAL'
, EST_LINE_SOURCE_ID1 = NULL
, EST_LINE_SOURCE_ID2 = NULL
, RO_SERVICE_CODE_ID = NULL
WHERE REPAIR_ESTIMATE_LINE_ID = rep_est_lines_arr(j);
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_ESTIMATE_LINES',
'CSD_REPAIR_ESTIMATE_LINES',
rep_est_lines_arr(j),
sysdate,
v_error_text,
'11.5.10');
/* Update all records in 1159 CSD_REPAIR_ESTIMATE_LINES table for*/
/* purpose : Step 2A and 2B of 11510 Actuals Data Migration Steps */
/* creates new Estimate charge line */
/* 1. linking the new Estimate charge line to Depot Estimate line*/
/* 2. create a new Depot Actual line and */
/* 3. link the Actual line created to old Estimate charge line */
/* */
/*-------------------------------------------------------------------------------*/
PROCEDURE csd_acttoest_charge_line_mig3(p_slab_number IN NUMBER DEFAULT 1)
IS
-- TYPE EST_DET_REC_ARRAY_TYPE IS VARRAY(1000) OF CS.CS_ESTIMATE_DETAILS%ROWTYPE;
-- gilam: bug 3362408/3362418 - commented out all the columns that are not used in insert_row
-- as some of the columns have been dropped in 11.5.10 by Charges
-- gilam: define each column individually
TYPE NumTabTypeI IS TABLE OF NUMBER
INDEX by Binary_Integer;
v_LAST_UPDATE_BY NumTabTypeII ;
v_LAST_UPDATE_LOGIN NumTabTypeII ;
v_LAST_UPDATE_DATE DateTabType ;
-- gilam: bug 3362408/3362418 - commented out all the columns that are not used in insert_row
-- as some of the columns have been dropped in 11.5.10 by Charges
-- gilam: bug 3410383 - changed query to use repair line id instead
/*
CURSOR get_charge_est_details(p_start_est_det_id number, p_end_est_det_id number)
IS
SELECT ced.estimate_detail_id,
ced.last_update_date,
ced.last_updated_by,
ced.creation_date,
ced.created_by,
ced.last_update_login,
--ced.estimate_id,
ced.line_number,
ced.inventory_item_id,
ced.serial_number,
ced.quantity_required,
ced.unit_of_measure_code,
ced.selling_price,
ced.after_warranty_cost,
ced.pricing_context,
ced.pricing_attribute1,
ced.pricing_attribute2,
ced.pricing_attribute3,
ced.pricing_attribute4,
ced.pricing_attribute5,
ced.pricing_attribute6,
ced.pricing_attribute7,
ced.pricing_attribute8,
ced.pricing_attribute9,
ced.pricing_attribute10,
ced.pricing_attribute11,
ced.pricing_attribute12,
ced.pricing_attribute13,
ced.pricing_attribute14,
ced.pricing_attribute15,
ced.attribute1,
ced.attribute2,
ced.attribute3,
ced.attribute4,
ced.attribute5,
ced.attribute6,
ced.attribute7,
ced.attribute8,
ced.attribute9,
ced.attribute10,
ced.attribute11,
ced.attribute12,
ced.attribute13,
ced.attribute14,
ced.attribute15,
ced.context,
--ced.organization_id,
--ced.diagnosis_id,
--ced.estimate_business_group_id,
ced.transaction_type_id,
ced.customer_product_id,
ced.order_header_id,
--ced.original_system_reference,
--ced.original_system_line_reference,
ced.installed_cp_return_by_date,
ced.new_cp_return_by_date,
ced.interface_to_oe_flag,
ced.rollup_flag,
--ced.add_to_order,
--ced.system_id,
--ced.rma_header_id,
--ced.rma_number,
--ced.rma_line_id,
--ced.rma_line_number,
--ced.technician_id,
--ced.txn_start_time,
--ced.txn_end_time,
ced.coverage_bill_rate_id,
ced.coverage_billing_type_id,
--ced.time_zone_id,
ced.txn_billing_type_id,
ced.business_process_id,
ced.incident_id,
ced.original_source_id,
ced.original_source_code,
ced.source_id,
ced.source_code,
ced.contract_id,
ced.coverage_id,
ced.coverage_txn_group_id,
ced.invoice_to_org_id,
ced.ship_to_org_id,
ced.purchase_order_num,
ced.line_type_id,
ced.line_category_code,
ced.currency_code,
ced.conversion_rate,
ced.conversion_type_code,
ced.conversion_rate_date,
ced.return_reason_code,
ced.order_line_id,
ced.price_list_header_id,
--ced.func_curr_aft_warr_cost,
--ced.orig_system_reference,
--ced.orig_system_line_reference,
ced.add_to_order_flag,
--ced.exception_coverage_used,
--ced.tax_code,
--ced.est_tax_amount,
ced.object_version_number,
ced.pricing_attribute16,
ced.pricing_attribute17,
ced.pricing_attribute18,
ced.pricing_attribute19,
ced.pricing_attribute20,
ced.pricing_attribute21,
ced.pricing_attribute22,
ced.pricing_attribute23,
ced.pricing_attribute24,
ced.pricing_attribute25,
ced.pricing_attribute26,
ced.pricing_attribute27,
ced.pricing_attribute28,
ced.pricing_attribute29,
ced.pricing_attribute30,
ced.pricing_attribute31,
ced.pricing_attribute32,
ced.pricing_attribute33,
ced.pricing_attribute34,
ced.pricing_attribute35,
ced.pricing_attribute36,
ced.pricing_attribute37,
ced.pricing_attribute38,
ced.pricing_attribute39,
ced.pricing_attribute40,
ced.pricing_attribute41,
ced.pricing_attribute42,
ced.pricing_attribute43,
ced.pricing_attribute44,
ced.pricing_attribute45,
ced.pricing_attribute46,
ced.pricing_attribute47,
ced.pricing_attribute48,
ced.pricing_attribute49,
ced.pricing_attribute50,
ced.pricing_attribute51,
ced.pricing_attribute52,
ced.pricing_attribute53,
ced.pricing_attribute54,
ced.pricing_attribute55,
ced.pricing_attribute56,
ced.pricing_attribute57,
ced.pricing_attribute58,
ced.pricing_attribute59,
ced.pricing_attribute61,
ced.pricing_attribute62,
ced.pricing_attribute63,
ced.pricing_attribute64,
ced.pricing_attribute65,
ced.pricing_attribute66,
ced.pricing_attribute67,
ced.pricing_attribute68,
ced.pricing_attribute69,
ced.pricing_attribute70,
ced.pricing_attribute71,
ced.pricing_attribute72,
ced.pricing_attribute73,
ced.pricing_attribute74,
ced.pricing_attribute75,
ced.pricing_attribute76,
ced.pricing_attribute77,
ced.pricing_attribute78,
ced.pricing_attribute79,
ced.pricing_attribute80,
ced.pricing_attribute81,
ced.pricing_attribute82,
ced.pricing_attribute83,
ced.pricing_attribute84,
ced.pricing_attribute85,
ced.pricing_attribute86,
ced.pricing_attribute87,
ced.pricing_attribute88,
ced.pricing_attribute89,
ced.pricing_attribute90,
ced.pricing_attribute91,
ced.pricing_attribute92,
ced.pricing_attribute93,
ced.pricing_attribute94,
ced.pricing_attribute95,
ced.pricing_attribute96,
ced.pricing_attribute97,
ced.pricing_attribute98,
ced.pricing_attribute99,
ced.pricing_attribute100,
ced.pricing_attribute60,
--ced.security_group_id,
--ced.upgraded_status_flag,
--ced.orig_system_reference_id,
ced.no_charge_flag,
ced.org_id,
ced.item_revision,
--ced.trans_inv_organization_id,
--ced.trans_subinventory,
--ced.activity_date,
--ced.activity_start_time,
--ced.activity_end_time,
ced.generated_by_bca_engine_flag,
ced.transaction_inventory_org,
ced.transaction_sub_inventory,
ced.activity_start_date_time,
ced.activity_end_date_time,
ced.charge_line_type,
ced.ship_to_contact_id,
ced.bill_to_contact_id,
ced.ship_to_account_id,
ced.invoice_to_account_id,
ced.list_price,
ced.contract_discount_amount,
ced.bill_to_party_id,
ced.ship_to_party_id,
ced.submit_restriction_message,
ced.submit_error_message,
ced.line_submitted,
ced.submit_from_system
FROM cs_estimate_details ced
, csd_repair_estimate_lines cr
WHERE cr.estimate_detail_id = ced.estimate_detail_id
AND ced.charge_line_type = 'ACTUAL'
AND ced.order_line_id is not null
AND ced.original_source_code = 'DR'
AND ced.source_code = 'DR'
AND NOT EXISTS ( SELECT 'x'
FROM csd_repair_actual_lines cral
WHERE cral.estimate_detail_id = cr.estimate_detail_id)
AND ced.estimate_detail_id >= p_start_est_det_id
AND ced.estimate_detail_id <= p_end_est_det_id;
SELECT ced.estimate_detail_id,
ced.last_update_date,
ced.last_updated_by,
ced.creation_date,
ced.created_by,
ced.last_update_login,
--ced.estimate_id,
ced.line_number,
ced.inventory_item_id,
ced.serial_number,
ced.quantity_required,
ced.unit_of_measure_code,
ced.selling_price,
ced.after_warranty_cost,
ced.pricing_context,
ced.pricing_attribute1,
ced.pricing_attribute2,
ced.pricing_attribute3,
ced.pricing_attribute4,
ced.pricing_attribute5,
ced.pricing_attribute6,
ced.pricing_attribute7,
ced.pricing_attribute8,
ced.pricing_attribute9,
ced.pricing_attribute10,
ced.pricing_attribute11,
ced.pricing_attribute12,
ced.pricing_attribute13,
ced.pricing_attribute14,
ced.pricing_attribute15,
ced.attribute1,
ced.attribute2,
ced.attribute3,
ced.attribute4,
ced.attribute5,
ced.attribute6,
ced.attribute7,
ced.attribute8,
ced.attribute9,
ced.attribute10,
ced.attribute11,
ced.attribute12,
ced.attribute13,
ced.attribute14,
ced.attribute15,
ced.context,
--ced.organization_id,
--ced.diagnosis_id,
--ced.estimate_business_group_id,
ced.transaction_type_id,
ced.customer_product_id,
ced.order_header_id,
--ced.original_system_reference,
--ced.original_system_line_reference,
ced.installed_cp_return_by_date,
ced.new_cp_return_by_date,
ced.interface_to_oe_flag,
ced.rollup_flag,
--ced.add_to_order,
--ced.system_id,
--ced.rma_header_id,
--ced.rma_number,
--ced.rma_line_id,
--ced.rma_line_number,
--ced.technician_id,
--ced.txn_start_time,
--ced.txn_end_time,
ced.coverage_bill_rate_id,
ced.coverage_billing_type_id,
--ced.time_zone_id,
ced.txn_billing_type_id,
ced.business_process_id,
ced.incident_id,
ced.original_source_id,
ced.original_source_code,
ced.source_id,
ced.source_code,
ced.contract_id,
ced.coverage_id,
ced.coverage_txn_group_id,
ced.invoice_to_org_id,
ced.ship_to_org_id,
ced.purchase_order_num,
ced.line_type_id,
ced.line_category_code,
ced.currency_code,
ced.conversion_rate,
ced.conversion_type_code,
ced.conversion_rate_date,
ced.return_reason_code,
ced.order_line_id,
ced.price_list_header_id,
--ced.func_curr_aft_warr_cost,
--ced.orig_system_reference,
--ced.orig_system_line_reference,
ced.add_to_order_flag,
--ced.exception_coverage_used,
--ced.tax_code,
--ced.est_tax_amount,
ced.object_version_number,
ced.pricing_attribute16,
ced.pricing_attribute17,
ced.pricing_attribute18,
ced.pricing_attribute19,
ced.pricing_attribute20,
ced.pricing_attribute21,
ced.pricing_attribute22,
ced.pricing_attribute23,
ced.pricing_attribute24,
ced.pricing_attribute25,
ced.pricing_attribute26,
ced.pricing_attribute27,
ced.pricing_attribute28,
ced.pricing_attribute29,
ced.pricing_attribute30,
ced.pricing_attribute31,
ced.pricing_attribute32,
ced.pricing_attribute33,
ced.pricing_attribute34,
ced.pricing_attribute35,
ced.pricing_attribute36,
ced.pricing_attribute37,
ced.pricing_attribute38,
ced.pricing_attribute39,
ced.pricing_attribute40,
ced.pricing_attribute41,
ced.pricing_attribute42,
ced.pricing_attribute43,
ced.pricing_attribute44,
ced.pricing_attribute45,
ced.pricing_attribute46,
ced.pricing_attribute47,
ced.pricing_attribute48,
ced.pricing_attribute49,
ced.pricing_attribute50,
ced.pricing_attribute51,
ced.pricing_attribute52,
ced.pricing_attribute53,
ced.pricing_attribute54,
ced.pricing_attribute55,
ced.pricing_attribute56,
ced.pricing_attribute57,
ced.pricing_attribute58,
ced.pricing_attribute59,
ced.pricing_attribute61,
ced.pricing_attribute62,
ced.pricing_attribute63,
ced.pricing_attribute64,
ced.pricing_attribute65,
ced.pricing_attribute66,
ced.pricing_attribute67,
ced.pricing_attribute68,
ced.pricing_attribute69,
ced.pricing_attribute70,
ced.pricing_attribute71,
ced.pricing_attribute72,
ced.pricing_attribute73,
ced.pricing_attribute74,
ced.pricing_attribute75,
ced.pricing_attribute76,
ced.pricing_attribute77,
ced.pricing_attribute78,
ced.pricing_attribute79,
ced.pricing_attribute80,
ced.pricing_attribute81,
ced.pricing_attribute82,
ced.pricing_attribute83,
ced.pricing_attribute84,
ced.pricing_attribute85,
ced.pricing_attribute86,
ced.pricing_attribute87,
ced.pricing_attribute88,
ced.pricing_attribute89,
ced.pricing_attribute90,
ced.pricing_attribute91,
ced.pricing_attribute92,
ced.pricing_attribute93,
ced.pricing_attribute94,
ced.pricing_attribute95,
ced.pricing_attribute96,
ced.pricing_attribute97,
ced.pricing_attribute98,
ced.pricing_attribute99,
ced.pricing_attribute100,
ced.pricing_attribute60,
--ced.security_group_id,
--ced.upgraded_status_flag,
--ced.orig_system_reference_id,
ced.no_charge_flag,
ced.org_id,
ced.item_revision,
--ced.trans_inv_organization_id,
--ced.trans_subinventory,
--ced.activity_date,
--ced.activity_start_time,
--ced.activity_end_time,
ced.generated_by_bca_engine_flag,
ced.transaction_inventory_org,
ced.transaction_sub_inventory,
ced.activity_start_date_time,
ced.activity_end_date_time,
ced.charge_line_type,
ced.ship_to_contact_id,
ced.bill_to_contact_id,
ced.ship_to_account_id,
ced.invoice_to_account_id,
ced.list_price,
ced.contract_discount_amount,
ced.bill_to_party_id,
ced.ship_to_party_id,
ced.submit_restriction_message,
ced.submit_error_message,
ced.line_submitted,
ced.submit_from_system
--sangigup 4610625
,ced.contract_line_id
,ced.rate_type_code
--sangigup
FROM cs_estimate_details ced
, csd_repairs cr
, csd_repair_estimate cre
, csd_repair_estimate_lines crel
WHERE cr.repair_line_id = cre.repair_line_id
AND cre.repair_estimate_id = crel.repair_estimate_id
AND crel.estimate_detail_id = ced.estimate_detail_id
AND ced.charge_line_type = 'ACTUAL'
AND ced.order_line_id is not null
AND ced.original_source_code = 'DR'
AND ced.source_code = 'DR'
AND NOT EXISTS ( SELECT 'x'
FROM csd_repair_actual_lines cral
WHERE cral.estimate_detail_id = crel.estimate_detail_id)
AND cr.repair_line_id >= p_start_rep_line_id
AND cr.repair_line_id <= p_end_rep_line_id;
-- gilam: bug 3362408/3362418 - commented out all the columns that are not used in insert_row
-- as some of the columns have been dropped in 11.5.10 by Charges
-- gilam: changed FETCH stmt to fetch into individual tables
FETCH get_charge_est_details BULK COLLECT
INTO
v_ESTIMATE_DETAIL_ID ,
v_LAST_UPDATE_DATE ,
v_LAST_UPDATE_BY ,
v_CREATION_DATE ,
v_CREATED_BY ,
v_LAST_UPDATE_LOGIN ,
--v_ESTIMATE_ID ,
v_LINE_NUMBER ,
v_INVENTORY_ITEM_ID ,
v_SERIAL_NUMBER ,
v_QUANTITY_REQUIRED ,
v_UNIT_OF_MEASURE_CODE ,
v_SELLING_PRICE ,
v_AFTER_WARRANTY_COST ,
v_PRICING_CONTEXT ,
v_PRICING_ATTRIBUTE1 ,
v_PRICING_ATTRIBUTE2 ,
v_PRICING_ATTRIBUTE3 ,
v_PRICING_ATTRIBUTE4 ,
v_PRICING_ATTRIBUTE5 ,
v_PRICING_ATTRIBUTE6 ,
v_PRICING_ATTRIBUTE7 ,
v_PRICING_ATTRIBUTE8 ,
v_PRICING_ATTRIBUTE9 ,
v_PRICING_ATTRIBUTE10 ,
v_PRICING_ATTRIBUTE11 ,
v_PRICING_ATTRIBUTE12 ,
v_PRICING_ATTRIBUTE13 ,
v_PRICING_ATTRIBUTE14 ,
v_PRICING_ATTRIBUTE15 ,
v_ATTRIBUTE1 ,
v_ATTRIBUTE2 ,
v_ATTRIBUTE3 ,
v_ATTRIBUTE4 ,
v_ATTRIBUTE5 ,
v_ATTRIBUTE6 ,
v_ATTRIBUTE7 ,
v_ATTRIBUTE8 ,
v_ATTRIBUTE9 ,
v_ATTRIBUTE10 ,
v_ATTRIBUTE11 ,
v_ATTRIBUTE12 ,
v_ATTRIBUTE13 ,
v_ATTRIBUTE14 ,
v_ATTRIBUTE15 ,
v_CONTEXT ,
--v_ORGANIZATION_ID ,
--v_DIAGNOSIS_ID ,
--v_ESTIMATE_BUSINESS_GROUP_ID ,
v_TRANSACTION_TYPE_ID ,
v_CUSTOMER_PRODUCT_ID ,
v_ORDER_HEADER_ID ,
--v_ORIGINAL_SYSTEM_REFERENCE ,
--v_ORIGINAL_SYS_LINE_REFERENCE ,
v_INSTALLED_CP_RETURN_BY_DATE ,
v_NEW_CP_RETURN_BY_DATE ,
v_INTERFACE_TO_OE_FLAG ,
v_ROLLUP_FLAG ,
--v_ADD_TO_ORDER ,
--v_SYSTEM_ID ,
--v_RMA_HEADER_ID ,
--v_RMA_NUMBER ,
--v_RMA_LINE_ID ,
--v_RMA_LINE_NUMBER ,
--v_TECHNICIAN_ID ,
--v_TXN_START_TIME ,
--v_TXN_END_TIME ,
v_COVERAGE_BILL_RATE_ID ,
v_COVERAGE_BILLING_TYPE_ID ,
--v_TIME_ZONE_ID ,
v_TXN_BILLING_TYPE_ID ,
v_BUSINESS_PROCESS_ID ,
v_INCIDENT_ID ,
v_ORIGINAL_SOURCE_ID ,
v_ORIGINAL_SOURCE_CODE ,
v_SOURCE_ID ,
v_SOURCE_CODE ,
v_CONTRACT_ID ,
v_COVERAGE_ID ,
v_COVERAGE_TXN_GROUP_ID ,
v_INVOICE_TO_ORG_ID ,
v_SHIP_TO_ORG_ID ,
v_PURCHASE_ORDER_NUM ,
v_LINE_TYPE_ID ,
v_LINE_CATEGORY_CODE ,
v_CURRENCY_CODE ,
v_CONVERSION_RATE ,
v_CONVERSION_TYPE_CODE ,
v_CONVERSION_RATE_DATE ,
v_RETURN_REASON_CODE ,
v_ORDER_LINE_ID ,
v_PRICE_LIST_HEADER_ID ,
--v_FUNC_CURR_AFT_WARR_COST ,
--v_ORIG_SYSTEM_REFERENCE ,
--v_ORIG_SYSTEM_LINE_REFERENCE ,
v_ADD_TO_ORDER_FLAG ,
--v_EXCEPTION_COVERAGE_USED ,
--v_TAX_CODE ,
--v_EST_TAX_AMOUNT ,
v_OBJECT_VERSION_NUMBER ,
v_PRICING_ATTRIBUTE16 ,
v_PRICING_ATTRIBUTE17 ,
v_PRICING_ATTRIBUTE18 ,
v_PRICING_ATTRIBUTE19 ,
v_PRICING_ATTRIBUTE20 ,
v_PRICING_ATTRIBUTE21 ,
v_PRICING_ATTRIBUTE22 ,
v_PRICING_ATTRIBUTE23 ,
v_PRICING_ATTRIBUTE24 ,
v_PRICING_ATTRIBUTE25 ,
v_PRICING_ATTRIBUTE26 ,
v_PRICING_ATTRIBUTE27 ,
v_PRICING_ATTRIBUTE28 ,
v_PRICING_ATTRIBUTE29 ,
v_PRICING_ATTRIBUTE30 ,
v_PRICING_ATTRIBUTE31 ,
v_PRICING_ATTRIBUTE32 ,
v_PRICING_ATTRIBUTE33 ,
v_PRICING_ATTRIBUTE34 ,
v_PRICING_ATTRIBUTE35 ,
v_PRICING_ATTRIBUTE36 ,
v_PRICING_ATTRIBUTE37 ,
v_PRICING_ATTRIBUTE38 ,
v_PRICING_ATTRIBUTE39 ,
v_PRICING_ATTRIBUTE40 ,
v_PRICING_ATTRIBUTE41 ,
v_PRICING_ATTRIBUTE42 ,
v_PRICING_ATTRIBUTE43 ,
v_PRICING_ATTRIBUTE44 ,
v_PRICING_ATTRIBUTE45 ,
v_PRICING_ATTRIBUTE46 ,
v_PRICING_ATTRIBUTE47 ,
v_PRICING_ATTRIBUTE48 ,
v_PRICING_ATTRIBUTE49 ,
v_PRICING_ATTRIBUTE50 ,
v_PRICING_ATTRIBUTE51 ,
v_PRICING_ATTRIBUTE52 ,
v_PRICING_ATTRIBUTE53 ,
v_PRICING_ATTRIBUTE54 ,
v_PRICING_ATTRIBUTE55 ,
v_PRICING_ATTRIBUTE56 ,
v_PRICING_ATTRIBUTE57 ,
v_PRICING_ATTRIBUTE58 ,
v_PRICING_ATTRIBUTE59 ,
v_PRICING_ATTRIBUTE61 ,
v_PRICING_ATTRIBUTE62 ,
v_PRICING_ATTRIBUTE63 ,
v_PRICING_ATTRIBUTE64 ,
v_PRICING_ATTRIBUTE65 ,
v_PRICING_ATTRIBUTE66 ,
v_PRICING_ATTRIBUTE67 ,
v_PRICING_ATTRIBUTE68 ,
v_PRICING_ATTRIBUTE69 ,
v_PRICING_ATTRIBUTE70 ,
v_PRICING_ATTRIBUTE71 ,
v_PRICING_ATTRIBUTE72 ,
v_PRICING_ATTRIBUTE73 ,
v_PRICING_ATTRIBUTE74 ,
v_PRICING_ATTRIBUTE75 ,
v_PRICING_ATTRIBUTE76 ,
v_PRICING_ATTRIBUTE77 ,
v_PRICING_ATTRIBUTE78 ,
v_PRICING_ATTRIBUTE79 ,
v_PRICING_ATTRIBUTE80 ,
v_PRICING_ATTRIBUTE81 ,
v_PRICING_ATTRIBUTE82 ,
v_PRICING_ATTRIBUTE83 ,
v_PRICING_ATTRIBUTE84 ,
v_PRICING_ATTRIBUTE85 ,
v_PRICING_ATTRIBUTE86 ,
v_PRICING_ATTRIBUTE87 ,
v_PRICING_ATTRIBUTE88 ,
v_PRICING_ATTRIBUTE89 ,
v_PRICING_ATTRIBUTE90 ,
v_PRICING_ATTRIBUTE91 ,
v_PRICING_ATTRIBUTE92 ,
v_PRICING_ATTRIBUTE93 ,
v_PRICING_ATTRIBUTE94 ,
v_PRICING_ATTRIBUTE95 ,
v_PRICING_ATTRIBUTE96 ,
v_PRICING_ATTRIBUTE97 ,
v_PRICING_ATTRIBUTE98 ,
v_PRICING_ATTRIBUTE99 ,
v_PRICING_ATTRIBUTE100 ,
v_PRICING_ATTRIBUTE60 ,
--v_SECURITY_GROUP_ID ,
--v_UPGRADED_STATUS_FLAG ,
--v_ORIG_SYSTEM_REFERENCE_ID ,
v_NO_CHARGE_FLAG ,
v_ORG_ID ,
v_ITEM_REVISION ,
--v_TRANS_INV_ORGANIZATION_ID ,
--v_TRANS_SUBINVENTORY ,
--v_ACTIVITY_DATE ,
--v_ACTIVITY_START_TIME ,
--v_ACTIVITY_END_TIME ,
v_GENERATED_BY_BCA_ENGINE_FLAG ,
v_TRANSACTION_INVENTORY_ORG ,
v_TRANSACTION_SUB_INVENTORY ,
v_ACTIVITY_START_DATE_TIME ,
v_ACTIVITY_END_DATE_TIME ,
v_CHARGE_LINE_TYPE ,
v_SHIP_TO_CONTACT_ID ,
v_BILL_TO_CONTACT_ID ,
v_SHIP_TO_ACCOUNT_ID ,
v_INVOICE_TO_ACCOUNT_ID ,
v_LIST_PRICE ,
v_CONTRACT_DISCOUNT_AMOUNT ,
v_BILL_TO_PARTY_ID ,
v_SHIP_TO_PARTY_ID ,
v_SUBMIT_RESTRICTION_MESSAGE ,
v_SUBMIT_ERROR_MESSAGE ,
v_LINE_SUBMITTED ,
v_SUBMIT_FROM_SYSTEM
--sangigup 4610625
, v_contract_line_id
, v_rate_type_Code
--sangigup
LIMIT MAX_BUFFER_SIZE;
SELECT max(line_number) + 1
INTO l_line_num
FROM CS_ESTIMATE_DETAILS
WHERE incident_id = v_INCIDENT_ID(j);
SELECT cs_estimate_details_s.nextval
INTO l_new_est_detail_id
FROM SYS.DUAL;
SELECT repair_actual_id
INTO l_actual_id
FROM CSD_REPAIR_ACTUALS
WHERE repair_line_id = v_ORIGINAL_SOURCE_ID(j);
SELECT repair_estimate_line_id
INTO l_rep_est_line_id
FROM CSD_REPAIR_ESTIMATE_LINES
WHERE estimate_detail_id = l_old_est_detail_id;
CS_ESTIMATE_DETAILS_PKG.Insert_Row(
p_org_id => v_ORG_ID(j),
p_incident_id => v_INCIDENT_ID(j),
p_original_source_id => v_ORIGINAL_SOURCE_ID(j),
p_original_source_code => v_ORIGINAL_SOURCE_CODE(j),
p_source_id => v_SOURCE_ID(j),
p_source_code => v_SOURCE_CODE(j),
p_contract_id => v_CONTRACT_ID(j),
p_coverage_id => v_COVERAGE_ID(j),
p_coverage_txn_group_id => v_COVERAGE_TXN_GROUP_ID(j),
p_CURRENCY_CODE => v_CURRENCY_CODE(j),
p_CONVERSION_RATE => v_CONVERSION_RATE(j),
p_CONVERSION_TYPE_CODE => v_CONVERSION_TYPE_CODE(j),
p_CONVERSION_RATE_DATE => v_CONVERSION_RATE_DATE(j),
p_invoice_to_org_id => v_INVOICE_TO_ORG_ID(j),
p_ship_to_org_id => v_SHIP_TO_ORG_ID(j),
p_purchase_order_num => v_PURCHASE_ORDER_NUM(j),
p_order_line_id => NULL, -- changed
p_line_type_id => v_LINE_TYPE_ID(j),
p_LINE_CATEGORY_CODE => v_LINE_CATEGORY_CODE(j),
p_price_list_header_id => v_PRICE_LIST_HEADER_ID(j), -- changed
p_line_number => v_LINE_NUMBER(j),
p_inventory_item_id => v_INVENTORY_ITEM_ID(j),
p_item_revision => v_ITEM_REVISION(j),
p_SERIAL_NUMBER => v_SERIAL_NUMBER(j),
p_quantity_required => v_QUANTITY_REQUIRED(j),
p_unit_of_measure_code => v_UNIT_OF_MEASURE_CODE(j),
p_selling_price => v_SELLING_PRICE(j),
p_after_warranty_cost => v_AFTER_WARRANTY_COST(j),
p_business_process_id => v_BUSINESS_PROCESS_ID(j),
p_transaction_type_id => v_TRANSACTION_TYPE_ID(j),
p_customer_product_id => v_CUSTOMER_PRODUCT_ID(j),
p_order_header_id => NULL, -- changed
p_installed_cp_return_by_date => v_INSTALLED_CP_RETURN_BY_DATE(j),
p_new_cp_return_by_date => v_NEW_CP_RETURN_BY_DATE(j),
p_interface_to_oe_flag => 'N', -- changed
p_rollup_flag => v_ROLLUP_FLAG(j),
p_no_charge_flag => v_NO_CHARGE_FLAG(j),
p_add_to_order_flag => 'N', -- changed
p_return_reason_code => v_RETURN_REASON_CODE(j),
p_generated_by_bca_engine_flag => NULL, -- changed
p_transaction_inventory_org => v_TRANSACTION_INVENTORY_ORG(j),
p_transaction_sub_inventory => v_TRANSACTION_SUB_INVENTORY(j),
p_charge_line_type => 'ESTIMATE', -- changed
p_ship_to_account_id => v_SHIP_TO_ACCOUNT_ID(j),
p_invoice_to_account_id => v_INVOICE_TO_ACCOUNT_ID(j), -- changed
p_ship_to_contact_id => v_SHIP_TO_CONTACT_ID(j),
p_bill_to_contact_id => v_BILL_TO_CONTACT_ID(j),
p_list_price => v_LIST_PRICE(j),
p_activity_start_date_time => v_ACTIVITY_START_DATE_TIME(j),
p_activity_end_date_time => v_ACTIVITY_END_DATE_TIME(j),
p_contract_discount_amount => v_CONTRACT_DISCOUNT_AMOUNT(j),
p_bill_to_party_id => v_BILL_TO_PARTY_ID(j),
p_ship_to_party_id => v_SHIP_TO_PARTY_ID(j),
p_pricing_context => v_PRICING_CONTEXT(j),
p_pricing_attribute1 => v_PRICING_ATTRIBUTE1(j),
p_pricing_attribute2 => v_PRICING_ATTRIBUTE2(j),
p_pricing_attribute3 => v_PRICING_ATTRIBUTE3(j),
p_pricing_attribute4 => v_PRICING_ATTRIBUTE4(j),
p_pricing_attribute5 => v_PRICING_ATTRIBUTE5(j),
p_pricing_attribute6 => v_PRICING_ATTRIBUTE6(j),
p_pricing_attribute7 => v_PRICING_ATTRIBUTE7(j),
p_pricing_attribute8 => v_PRICING_ATTRIBUTE8(j),
p_pricing_attribute9 => v_PRICING_ATTRIBUTE9(j),
p_pricing_attribute10 => v_PRICING_ATTRIBUTE10(j),
p_pricing_attribute11 => v_PRICING_ATTRIBUTE11(j),
p_pricing_attribute12 => v_PRICING_ATTRIBUTE12(j),
p_pricing_attribute13 => v_PRICING_ATTRIBUTE13(j),
p_pricing_attribute14 => v_PRICING_ATTRIBUTE14(j),
p_pricing_attribute15 => v_PRICING_ATTRIBUTE15(j),
p_pricing_attribute16 => v_PRICING_ATTRIBUTE16(j),
p_pricing_attribute17 => v_PRICING_ATTRIBUTE17(j),
p_pricing_attribute18 => v_PRICING_ATTRIBUTE18(j),
p_pricing_attribute19 => v_PRICING_ATTRIBUTE19(j),
p_pricing_attribute20 => v_PRICING_ATTRIBUTE20(j),
p_pricing_attribute21 => v_PRICING_ATTRIBUTE21(j),
p_pricing_attribute22 => v_PRICING_ATTRIBUTE22(j),
p_pricing_attribute23 => v_PRICING_ATTRIBUTE23(j),
p_pricing_attribute24 => v_PRICING_ATTRIBUTE24(j),
p_pricing_attribute25 => v_PRICING_ATTRIBUTE25(j),
p_pricing_attribute26 => v_PRICING_ATTRIBUTE26(j),
p_pricing_attribute27 => v_PRICING_ATTRIBUTE27(j),
p_pricing_attribute28 => v_PRICING_ATTRIBUTE28(j),
p_pricing_attribute29 => v_PRICING_ATTRIBUTE29(j),
p_pricing_attribute30 => v_PRICING_ATTRIBUTE30(j),
p_pricing_attribute31 => v_PRICING_ATTRIBUTE31(j),
p_pricing_attribute32 => v_PRICING_ATTRIBUTE32(j),
p_pricing_attribute33 => v_PRICING_ATTRIBUTE33(j),
p_pricing_attribute34 => v_PRICING_ATTRIBUTE34(j),
p_pricing_attribute35 => v_PRICING_ATTRIBUTE35(j),
p_pricing_attribute36 => v_PRICING_ATTRIBUTE36(j),
p_pricing_attribute37 => v_PRICING_ATTRIBUTE37(j),
p_pricing_attribute38 => v_PRICING_ATTRIBUTE38(j),
p_pricing_attribute39 => v_PRICING_ATTRIBUTE39(j),
p_pricing_attribute40 => v_PRICING_ATTRIBUTE40(j),
p_pricing_attribute41 => v_PRICING_ATTRIBUTE41(j),
p_pricing_attribute42 => v_PRICING_ATTRIBUTE42(j),
p_pricing_attribute43 => v_PRICING_ATTRIBUTE43(j),
p_pricing_attribute44 => v_PRICING_ATTRIBUTE44(j),
p_pricing_attribute45 => v_PRICING_ATTRIBUTE45(j),
p_pricing_attribute46 => v_PRICING_ATTRIBUTE46(j),
p_pricing_attribute47 => v_PRICING_ATTRIBUTE47(j),
p_pricing_attribute48 => v_PRICING_ATTRIBUTE48(j),
p_pricing_attribute49 => v_PRICING_ATTRIBUTE49(j),
p_pricing_attribute50 => v_PRICING_ATTRIBUTE50(j),
p_pricing_attribute51 => v_PRICING_ATTRIBUTE51(j),
p_pricing_attribute52 => v_PRICING_ATTRIBUTE52(j),
p_pricing_attribute53 => v_PRICING_ATTRIBUTE53(j),
p_pricing_attribute54 => v_PRICING_ATTRIBUTE54(j),
p_pricing_attribute55 => v_PRICING_ATTRIBUTE55(j),
p_pricing_attribute56 => v_PRICING_ATTRIBUTE56(j),
p_pricing_attribute57 => v_PRICING_ATTRIBUTE57(j),
p_pricing_attribute58 => v_PRICING_ATTRIBUTE58(j),
p_pricing_attribute59 => v_PRICING_ATTRIBUTE59(j),
p_pricing_attribute60 => v_PRICING_ATTRIBUTE60(j),
p_pricing_attribute61 => v_PRICING_ATTRIBUTE61(j),
p_pricing_attribute62 => v_PRICING_ATTRIBUTE62(j),
p_pricing_attribute63 => v_PRICING_ATTRIBUTE63(j),
p_pricing_attribute64 => v_PRICING_ATTRIBUTE64(j),
p_pricing_attribute65 => v_PRICING_ATTRIBUTE65(j),
p_pricing_attribute66 => v_PRICING_ATTRIBUTE66(j),
p_pricing_attribute67 => v_PRICING_ATTRIBUTE67(j),
p_pricing_attribute68 => v_PRICING_ATTRIBUTE68(j),
p_pricing_attribute69 => v_PRICING_ATTRIBUTE69(j),
p_pricing_attribute70 => v_PRICING_ATTRIBUTE70(j),
p_pricing_attribute71 => v_PRICING_ATTRIBUTE71(j),
p_pricing_attribute72 => v_PRICING_ATTRIBUTE72(j),
p_pricing_attribute73 => v_PRICING_ATTRIBUTE73(j),
p_pricing_attribute74 => v_PRICING_ATTRIBUTE74(j),
p_pricing_attribute75 => v_PRICING_ATTRIBUTE75(j),
p_pricing_attribute76 => v_PRICING_ATTRIBUTE76(j),
p_pricing_attribute77 => v_PRICING_ATTRIBUTE77(j),
p_pricing_attribute78 => v_PRICING_ATTRIBUTE78(j),
p_pricing_attribute79 => v_PRICING_ATTRIBUTE79(j),
p_pricing_attribute80 => v_PRICING_ATTRIBUTE80(j),
p_pricing_attribute81 => v_PRICING_ATTRIBUTE81(j),
p_pricing_attribute82 => v_PRICING_ATTRIBUTE82(j),
p_pricing_attribute83 => v_PRICING_ATTRIBUTE83(j),
p_pricing_attribute84 => v_PRICING_ATTRIBUTE84(j),
p_pricing_attribute85 => v_PRICING_ATTRIBUTE85(j),
p_pricing_attribute86 => v_PRICING_ATTRIBUTE86(j),
p_pricing_attribute87 => v_PRICING_ATTRIBUTE87(j),
p_pricing_attribute88 => v_PRICING_ATTRIBUTE88(j),
p_pricing_attribute89 => v_PRICING_ATTRIBUTE89(j),
p_pricing_attribute90 => v_PRICING_ATTRIBUTE90(j),
p_pricing_attribute91 => v_PRICING_ATTRIBUTE91(j),
p_pricing_attribute92 => v_PRICING_ATTRIBUTE92(j),
p_pricing_attribute93 => v_PRICING_ATTRIBUTE93(j),
p_pricing_attribute94 => v_PRICING_ATTRIBUTE94(j),
p_pricing_attribute95 => v_PRICING_ATTRIBUTE95(j),
p_pricing_attribute96 => v_PRICING_ATTRIBUTE96(j),
p_pricing_attribute97 => v_PRICING_ATTRIBUTE97(j),
p_pricing_attribute98 => v_PRICING_ATTRIBUTE98(j),
p_pricing_attribute99 => v_PRICING_ATTRIBUTE99(j),
p_pricing_attribute100 => v_PRICING_ATTRIBUTE100(j),
p_attribute1 => v_ATTRIBUTE1(j),
p_attribute2 => v_ATTRIBUTE2(j),
p_attribute3 => v_ATTRIBUTE3(j),
p_attribute4 => v_ATTRIBUTE4(j),
p_attribute5 => v_ATTRIBUTE5(j),
p_attribute6 => v_ATTRIBUTE6(j),
p_attribute7 => v_ATTRIBUTE7(j),
p_attribute8 => v_ATTRIBUTE8(j),
p_attribute9 => v_ATTRIBUTE9(j),
p_attribute10 => v_ATTRIBUTE10(j),
p_attribute11 => v_ATTRIBUTE11(j),
p_attribute12 => v_ATTRIBUTE12(j),
p_attribute13 => v_ATTRIBUTE13(j),
p_attribute14 => v_ATTRIBUTE14(j),
p_attribute15 => v_ATTRIBUTE15(j),
p_context => v_CONTEXT(j),
p_coverage_bill_rate_id => v_COVERAGE_BILL_RATE_ID(j),
p_coverage_billing_type_id => null,
p_txn_billing_type_id => v_TXN_BILLING_TYPE_ID(j),
p_submit_restriction_message => v_SUBMIT_RESTRICTION_MESSAGE(j),
p_submit_error_message => v_SUBMIT_ERROR_MESSAGE(j),
p_submit_from_system => v_SUBMIT_FROM_SYSTEM(j),
p_line_submitted => null,
--sangigup 4610625
p_contract_line_id => null,
p_rate_type_Code => null,
--sangigup
p_last_update_date => sysdate,
--p_last_update_login => p_user_id,
p_last_update_login => null, -- changed to null for create
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate,
p_created_by => FND_GLOBAL.USER_ID,
p_estimate_detail_id => l_new_est_detail_id,
x_object_version_number => x_object_version_number
);
|| 'CS_ESTIMATE_DETAILS_PKG.Insert_Row Failed - For Estimate Detail Id:'
|| v_ESTIMATE_DETAIL_ID(j);
CSD_REPAIR_ACTUAL_LINES_PKG.Insert_Row(
px_REPAIR_ACTUAL_LINE_ID => x_actual_line_id
,p_OBJECT_VERSION_NUMBER => 1
,p_ESTIMATE_DETAIL_ID => l_old_est_detail_id
,p_REPAIR_ACTUAL_ID => l_actual_id
,p_REPAIR_LINE_ID => v_ORIGINAL_SOURCE_ID(j)
,p_CREATED_BY => FND_GLOBAL.USER_ID
,p_CREATION_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
,p_ITEM_COST => null
,p_JUSTIFICATION_NOTES => null
,p_RESOURCE_ID => null
,p_OVERRIDE_CHARGE_FLAG => null
,p_ACTUAL_SOURCE_CODE => 'ESTIMATE'
,p_ACTUAL_SOURCE_ID => l_rep_est_line_id
,p_WARRANTY_CLAIM_FLAG => null
,p_WARRANTY_NUMBER => null
,p_WARRANTY_STATUS_CODE => null
,p_REPLACED_ITEM_ID => null
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null
,p_LOCATOR_ID => null
,p_LOC_SEGMENT1 => null
,p_LOC_SEGMENT2 => null
,p_LOC_SEGMENT3 => null
,p_LOC_SEGMENT4 => null
,p_LOC_SEGMENT5 => null
,p_LOC_SEGMENT6 => null
,p_LOC_SEGMENT7 => null
,p_LOC_SEGMENT8 => null
,p_LOC_SEGMENT9 => null
,p_LOC_SEGMENT10 => null
,p_LOC_SEGMENT11 => null
,p_LOC_SEGMENT12 => null
,p_LOC_SEGMENT13 => null
,p_LOC_SEGMENT14 => null
,p_LOC_SEGMENT15 => null
,p_LOC_SEGMENT16 => null
,p_LOC_SEGMENT17 => null
,p_LOC_SEGMENT18 => null
,p_LOC_SEGMENT19 => null
,p_LOC_SEGMENT20 => null);
|| 'CSD_REPAIR_ACTUAL_LINES_PKG.Insert_Row Failed - For Estimate Detail Id:'
|| l_old_est_detail_id
|| ' Loop for Estimate Detail Id: '
|| v_ESTIMATE_DETAIL_ID(j);
UPDATE CSD_REPAIR_ESTIMATE_LINES
SET ESTIMATE_DETAIL_ID = l_new_est_detail_id
WHERE ESTIMATE_DETAIL_ID = l_old_est_detail_id;
|| 'UPDATE CSD_REPAIR_ESTIMATE_LINES Failed - To update Estimate Detail Id to :'
|| l_new_est_detail_id
|| ' from Estimate Detail Id: '
|| l_old_est_detail_id;
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_ESTIMATE_LINES',
'CS_ESTIMATE_DETAILS',
v_ESTIMATE_DETAIL_ID(j),
sysdate,
v_error_text,
'11.5.10');
SELECT max(line_number) + 1
INTO l_line_num
FROM CS_ESTIMATE_DETAILS
WHERE incident_id = est_det_arr(j).incident_id;
SELECT cs_estimate_details_s.nextval
INTO l_new_est_detail_id
FROM SYS.DUAL;
SELECT repair_actual_id
INTO l_actual_id
FROM CSD_REPAIR_ACTUALS
WHERE repair_line_id = est_det_arr(j).original_source_id;
SELECT repair_estimate_line_id
INTO l_rep_est_line_id
FROM CSD_REPAIR_ESTIMATE_LINES
WHERE estimate_detail_id = l_old_est_detail_id;
CS_ESTIMATE_DETAILS_PKG.Insert_Row(
p_org_id => est_det_arr(j).org_id,
p_incident_id => est_det_arr(j).incident_id,
p_original_source_id => est_det_arr(j).original_source_id,
p_original_source_code => est_det_arr(j).original_source_code,
p_source_id => est_det_arr(j).source_id,
p_source_code => est_det_arr(j).source_code,
p_contract_id => est_det_arr(j).contract_id,
p_coverage_id => est_det_arr(j).coverage_id,
p_coverage_txn_group_id => est_det_arr(j).coverage_txn_group_id,
p_CURRENCY_CODE => est_det_arr(j).currency_code,
p_CONVERSION_RATE => est_det_arr(j).conversion_rate,
p_CONVERSION_TYPE_CODE => est_det_arr(j).conversion_type_code,
p_CONVERSION_RATE_DATE => est_det_arr(j).conversion_rate_date,
p_invoice_to_org_id => est_det_arr(j).invoice_to_org_id,
p_ship_to_org_id => est_det_arr(j).ship_to_org_id,
p_purchase_order_num => est_det_arr(j).purchase_order_num,
p_order_line_id => NULL, -- changed
p_line_type_id => est_det_arr(j).line_type_id,
p_LINE_CATEGORY_CODE => est_det_arr(j).LINE_CATEGORY_CODE,
p_price_list_header_id => est_det_arr(j).price_list_header_id, -- changed
p_line_number => l_line_num,
p_inventory_item_id => est_det_arr(j).inventory_item_id,
p_item_revision => est_det_arr(j).item_revision,
p_SERIAL_NUMBER => est_det_arr(j).SERIAL_NUMBER,
p_quantity_required => est_det_arr(j).quantity_required,
p_unit_of_measure_code => est_det_arr(j).unit_of_measure_code,
p_selling_price => est_det_arr(j).selling_price,
p_after_warranty_cost => est_det_arr(j).after_warranty_cost,
p_business_process_id => est_det_arr(j).business_process_id,
p_transaction_type_id => est_det_arr(j).transaction_type_id,
p_customer_product_id => est_det_arr(j).customer_product_id,
p_order_header_id => NULL, -- changed
p_installed_cp_return_by_date => est_det_arr(j).installed_cp_return_by_date,
p_new_cp_return_by_date => est_det_arr(j).new_cp_return_by_date,
p_interface_to_oe_flag => 'N', -- changed
p_rollup_flag => est_det_arr(j).rollup_flag,
p_no_charge_flag => est_det_arr(j).no_charge_flag,
p_add_to_order_flag => 'N', -- changed
p_return_reason_code => est_det_arr(j).return_reason_code,
p_generated_by_bca_engine_flag => NULL, -- changed
p_transaction_inventory_org => est_det_arr(j).transaction_inventory_org,
p_transaction_sub_inventory => est_det_arr(j).transaction_sub_inventory,
p_charge_line_type => 'ESTIMATE', -- changed
p_ship_to_account_id => est_det_arr(j).ship_to_account_id,
p_invoice_to_account_id => est_det_arr(j).bill_to_party_id, -- changed
p_ship_to_contact_id => est_det_arr(j).ship_to_contact_id,
p_bill_to_contact_id => est_det_arr(j).bill_to_contact_id,
p_list_price => est_det_arr(j).list_price,
p_activity_start_date_time => est_det_arr(j).activity_start_time,
p_activity_end_date_time => est_det_arr(j).activity_end_time,
p_contract_discount_amount => est_det_arr(j).contract_discount_amount,
p_bill_to_party_id => est_det_arr(j).bill_to_party_id,
p_ship_to_party_id => est_det_arr(j).ship_to_party_id,
p_pricing_context => est_det_arr(j).pricing_context,
p_pricing_attribute1 => est_det_arr(j).pricing_attribute1,
p_pricing_attribute2 => est_det_arr(j).pricing_attribute2,
p_pricing_attribute3 => est_det_arr(j).pricing_attribute3,
p_pricing_attribute4 => est_det_arr(j).pricing_attribute4,
p_pricing_attribute5 => est_det_arr(j).pricing_attribute5,
p_pricing_attribute6 => est_det_arr(j).pricing_attribute6,
p_pricing_attribute7 => est_det_arr(j).pricing_attribute7,
p_pricing_attribute8 => est_det_arr(j).pricing_attribute8,
p_pricing_attribute9 => est_det_arr(j).pricing_attribute9,
p_pricing_attribute10 => est_det_arr(j).pricing_attribute10,
p_pricing_attribute11 => est_det_arr(j).pricing_attribute11,
p_pricing_attribute12 => est_det_arr(j).pricing_attribute12,
p_pricing_attribute13 => est_det_arr(j).pricing_attribute13,
p_pricing_attribute14 => est_det_arr(j).pricing_attribute14,
p_pricing_attribute15 => est_det_arr(j).pricing_attribute15,
p_pricing_attribute16 => est_det_arr(j).pricing_attribute16,
p_pricing_attribute17 => est_det_arr(j).pricing_attribute17,
p_pricing_attribute18 => est_det_arr(j).pricing_attribute18,
p_pricing_attribute19 => est_det_arr(j).pricing_attribute19,
p_pricing_attribute20 => est_det_arr(j).pricing_attribute20,
p_pricing_attribute21 => est_det_arr(j).pricing_attribute21,
p_pricing_attribute22 => est_det_arr(j).pricing_attribute22,
p_pricing_attribute23 => est_det_arr(j).pricing_attribute23,
p_pricing_attribute24 => est_det_arr(j).pricing_attribute24,
p_pricing_attribute25 => est_det_arr(j).pricing_attribute25,
p_pricing_attribute26 => est_det_arr(j).pricing_attribute26,
p_pricing_attribute27 => est_det_arr(j).pricing_attribute27,
p_pricing_attribute28 => est_det_arr(j).pricing_attribute28,
p_pricing_attribute29 => est_det_arr(j).pricing_attribute29,
p_pricing_attribute30 => est_det_arr(j).pricing_attribute30,
p_pricing_attribute31 => est_det_arr(j).pricing_attribute31,
p_pricing_attribute32 => est_det_arr(j).pricing_attribute32,
p_pricing_attribute33 => est_det_arr(j).pricing_attribute33,
p_pricing_attribute34 => est_det_arr(j).pricing_attribute34,
p_pricing_attribute35 => est_det_arr(j).pricing_attribute35,
p_pricing_attribute36 => est_det_arr(j).pricing_attribute36,
p_pricing_attribute37 => est_det_arr(j).pricing_attribute37,
p_pricing_attribute38 => est_det_arr(j).pricing_attribute38,
p_pricing_attribute39 => est_det_arr(j).pricing_attribute39,
p_pricing_attribute40 => est_det_arr(j).pricing_attribute40,
p_pricing_attribute41 => est_det_arr(j).pricing_attribute41,
p_pricing_attribute42 => est_det_arr(j).pricing_attribute42,
p_pricing_attribute43 => est_det_arr(j).pricing_attribute43,
p_pricing_attribute44 => est_det_arr(j).pricing_attribute44,
p_pricing_attribute45 => est_det_arr(j).pricing_attribute45,
p_pricing_attribute46 => est_det_arr(j).pricing_attribute46,
p_pricing_attribute47 => est_det_arr(j).pricing_attribute47,
p_pricing_attribute48 => est_det_arr(j).pricing_attribute48,
p_pricing_attribute49 => est_det_arr(j).pricing_attribute49,
p_pricing_attribute50 => est_det_arr(j).pricing_attribute50,
p_pricing_attribute51 => est_det_arr(j).pricing_attribute51,
p_pricing_attribute52 => est_det_arr(j).pricing_attribute52,
p_pricing_attribute53 => est_det_arr(j).pricing_attribute53,
p_pricing_attribute54 => est_det_arr(j).pricing_attribute54,
p_pricing_attribute55 => est_det_arr(j).pricing_attribute55,
p_pricing_attribute56 => est_det_arr(j).pricing_attribute56,
p_pricing_attribute57 => est_det_arr(j).pricing_attribute57,
p_pricing_attribute58 => est_det_arr(j).pricing_attribute58,
p_pricing_attribute59 => est_det_arr(j).pricing_attribute59,
p_pricing_attribute60 => est_det_arr(j).pricing_attribute60,
p_pricing_attribute61 => est_det_arr(j).pricing_attribute61,
p_pricing_attribute62 => est_det_arr(j).pricing_attribute62,
p_pricing_attribute63 => est_det_arr(j).pricing_attribute63,
p_pricing_attribute64 => est_det_arr(j).pricing_attribute64,
p_pricing_attribute65 => est_det_arr(j).pricing_attribute65,
p_pricing_attribute66 => est_det_arr(j).pricing_attribute66,
p_pricing_attribute67 => est_det_arr(j).pricing_attribute67,
p_pricing_attribute68 => est_det_arr(j).pricing_attribute68,
p_pricing_attribute69 => est_det_arr(j).pricing_attribute69,
p_pricing_attribute70 => est_det_arr(j).pricing_attribute70,
p_pricing_attribute71 => est_det_arr(j).pricing_attribute71,
p_pricing_attribute72 => est_det_arr(j).pricing_attribute72,
p_pricing_attribute73 => est_det_arr(j).pricing_attribute73,
p_pricing_attribute74 => est_det_arr(j).pricing_attribute74,
p_pricing_attribute75 => est_det_arr(j).pricing_attribute75,
p_pricing_attribute76 => est_det_arr(j).pricing_attribute76,
p_pricing_attribute77 => est_det_arr(j).pricing_attribute77,
p_pricing_attribute78 => est_det_arr(j).pricing_attribute78,
p_pricing_attribute79 => est_det_arr(j).pricing_attribute79,
p_pricing_attribute80 => est_det_arr(j).pricing_attribute80,
p_pricing_attribute81 => est_det_arr(j).pricing_attribute81,
p_pricing_attribute82 => est_det_arr(j).pricing_attribute82,
p_pricing_attribute83 => est_det_arr(j).pricing_attribute83,
p_pricing_attribute84 => est_det_arr(j).pricing_attribute84,
p_pricing_attribute85 => est_det_arr(j).pricing_attribute85,
p_pricing_attribute86 => est_det_arr(j).pricing_attribute86,
p_pricing_attribute87 => est_det_arr(j).pricing_attribute87,
p_pricing_attribute88 => est_det_arr(j).pricing_attribute88,
p_pricing_attribute89 => est_det_arr(j).pricing_attribute89,
p_pricing_attribute90 => est_det_arr(j).pricing_attribute90,
p_pricing_attribute91 => est_det_arr(j).pricing_attribute91,
p_pricing_attribute92 => est_det_arr(j).pricing_attribute92,
p_pricing_attribute93 => est_det_arr(j).pricing_attribute93,
p_pricing_attribute94 => est_det_arr(j).pricing_attribute94,
p_pricing_attribute95 => est_det_arr(j).pricing_attribute95,
p_pricing_attribute96 => est_det_arr(j).pricing_attribute96,
p_pricing_attribute97 => est_det_arr(j).pricing_attribute97,
p_pricing_attribute98 => est_det_arr(j).pricing_attribute98,
p_pricing_attribute99 => est_det_arr(j).pricing_attribute99,
p_pricing_attribute100 => est_det_arr(j).pricing_attribute100,
p_attribute1 => est_det_arr(j).attribute1,
p_attribute2 => est_det_arr(j).attribute2,
p_attribute3 => est_det_arr(j).attribute3,
p_attribute4 => est_det_arr(j).attribute4,
p_attribute5 => est_det_arr(j).attribute5,
p_attribute6 => est_det_arr(j).attribute6,
p_attribute7 => est_det_arr(j).attribute7,
p_attribute8 => est_det_arr(j).attribute8,
p_attribute9 => est_det_arr(j).attribute9,
p_attribute10 => est_det_arr(j).attribute10,
p_attribute11 => est_det_arr(j).attribute11,
p_attribute12 => est_det_arr(j).attribute12,
p_attribute13 => est_det_arr(j).attribute13,
p_attribute14 => est_det_arr(j).attribute14,
p_attribute15 => est_det_arr(j).attribute15,
p_context => est_det_arr(j).context,
p_coverage_bill_rate_id => est_det_arr(j).coverage_bill_rate_id,
p_coverage_billing_type_id => null,
p_txn_billing_type_id => est_det_arr(j).txn_billing_type_id,
p_submit_restriction_message => est_det_arr(j).submit_restriction_message,
p_submit_error_message => est_det_arr(j).submit_error_message,
p_submit_from_system => est_det_arr(j).submit_from_system,
p_line_submitted => null,
p_last_update_date => sysdate,
--p_last_update_login => p_user_id,
p_last_update_login => null, -- changed to null for create
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate,
p_created_by => FND_GLOBAL.USER_ID,
p_estimate_detail_id => l_new_est_detail_id,
x_object_version_number => x_object_version_number );
|| 'CS_ESTIMATE_DETAILS_PKG.Insert_Row Failed - For Estimate Detail Id:'
|| est_det_arr(j).estimate_detail_id;
CSD_REPAIR_ACTUAL_LINES_PKG.Insert_Row(
px_REPAIR_ACTUAL_LINE_ID => x_actual_line_id
,p_OBJECT_VERSION_NUMBER => 1
,p_ESTIMATE_DETAIL_ID => l_old_est_detail_id
,p_REPAIR_ACTUAL_ID => l_actual_id
,p_REPAIR_LINE_ID => est_det_arr(j).original_source_id
,p_CREATED_BY => FND_GLOBAL.USER_ID
,p_CREATION_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
,p_ITEM_COST => null
,p_JUSTIFICATION_NOTES => null
,p_RESOURCE_ID => null
,p_OVERRIDE_CHARGE_FLAG => null
,p_ACTUAL_SOURCE_CODE => 'ESTIMATE'
,p_ACTUAL_SOURCE_ID => l_rep_est_line_id
,p_WARRANTY_CLAIM_FLAG => null
,p_WARRANTY_NUMBER => null
,p_WARRANTY_STATUS_CODE => null
,p_REPLACED_ITEM_ID => null
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null
,p_LOCATOR_ID => null
,p_LOC_SEGMENT1 => null
,p_LOC_SEGMENT2 => null
,p_LOC_SEGMENT3 => null
,p_LOC_SEGMENT4 => null
,p_LOC_SEGMENT5 => null
,p_LOC_SEGMENT6 => null
,p_LOC_SEGMENT7 => null
,p_LOC_SEGMENT8 => null
,p_LOC_SEGMENT9 => null
,p_LOC_SEGMENT10 => null
,p_LOC_SEGMENT11 => null
,p_LOC_SEGMENT12 => null
,p_LOC_SEGMENT13 => null
,p_LOC_SEGMENT14 => null
,p_LOC_SEGMENT15 => null
,p_LOC_SEGMENT16 => null
,p_LOC_SEGMENT17 => null
,p_LOC_SEGMENT18 => null
,p_LOC_SEGMENT19 => null
,p_LOC_SEGMENT20 => null);
|| 'CSD_REPAIR_ACTUAL_LINES_PKG.Insert_Row Failed - For Estimate Detail Id:'
|| l_old_est_detail_id
|| ' Loop for Estimate Detail Id: '
|| est_det_arr(j).estimate_detail_id;
UPDATE CSD_REPAIR_ESTIMATE_LINES
SET ESTIMATE_DETAIL_ID = l_new_est_detail_id
WHERE ESTIMATE_DETAIL_ID = l_old_est_detail_id;
|| 'UPDATE CSD_REPAIR_ESTIMATE_LINES Failed - To update Estimate Detail Id to :'
|| l_new_est_detail_id
|| ' from Estimate Detail Id: '
|| l_old_est_detail_id;
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_ESTIMATE_LINES',
'CS_ESTIMATE_DETAILS',
est_det_arr(j).estimate_detail_id,
sysdate,
v_error_text,
'11.5.10');
SELECT repair_job_xref_id, rowid
FROM csd_repair_job_xref
WHERE wip_entity_id = group_id AND repair_job_xref_id >= p_start
AND repair_job_xref_id <= p_end;
SELECT repair_job_xref_id, rowid
FROM csd_repair_job_xref
WHERE source_type_code IS NULL AND repair_job_xref_id >= p_start
AND repair_job_xref_id <= p_end;
UPDATE csd_repair_job_xref
SET wip_entity_id = NULL,
job_name = 'CSD' || group_id
WHERE rowid = rowid_mig(j);
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_JOB_XREF',
'CSD_REPAIR_JOB_XREF',
repair_job_xref_id_mig(j),
sysdate,
v_error_text,
'11.5.10');
UPDATE csd_repair_job_xref
SET source_type_code = 'MANUAL'
WHERE rowid = rowid_mig(j);
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_JOB_XREF',
'CSD_REPAIR_JOB_XREF',
repair_job_xref_id_mig(j),
sysdate,
v_error_text,
'11.5.10');
Update csd_repair_types_b set repair_type_ref = 'RR' where repair_type_ref = 'WR';
|| 'Not able to update Repair type from RR to WR';
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_TYPE_ERROR',
'CSD_REPAIR_TYPE_UPDATE',
null,
sysdate,
v_error_text,
'11.5.10');
Update csd_repair_types_b set repair_type_ref = 'ARR' where repair_type_ref = 'WRL';
|| 'Not able to update Repair type from ARR to WRL';
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_TYPE_ERROR',
'CSD_REPAIR_TYPE_UPDATE',
null,
sysdate,
v_error_text,
'11.5.10');
SELECT rel.repair_estimate_line_id estimate_line_id, rel.item_cost item_cost,
rel.original_cost original_cost, ced.currency_code, ced.creation_date ,
ced.unit_of_measure_code, ced.estimate_detail_id, rel.resource_id, rel.rowid
FROM csd_repair_estimate_lines rel, cs_estimate_details ced
WHERE rel.estimate_detail_id = ced.estimate_detail_id
and (
( rel.item_cost is not null and rel.original_cost is null)
OR ( rel.item_cost is null and rel.original_cost is not null)
)
and rel.repair_Estimate_line_id >= p_start
and rel.repair_Estimate_line_id <= p_end;
SELECT gl.currency_code
FROM gl_sets_of_books gl, hr_operating_units hr
WHERE hr.set_of_books_id = gl.set_of_books_id
AND hr.organization_id = p_org_id;
SELECT primary_uom_code
FROM mtl_system_items MSI, cs_estimate_details ced
WHERE CED.estimate_detail_id = p_estimate_detail_id
AND MSI.inventory_item_id = CED.inventory_item_id
AND MSI.organization_id = cs_std.get_item_valdn_orgzn_id;
SELECT BCAT.billing_category
FROM csd_repair_estimate_lines ESTL,
cs_estimate_details ESTD,
cs_txn_billing_types TXNT,
cs_billing_type_categories BCAT
WHERE ESTL.repair_estimate_line_id = p_estimate_detail_id
AND ESTD.estimate_detail_id = ESTL.estimate_detail_id
AND TXNT.txn_billing_type_id = ESTD.txn_billing_type_id
AND BCAT.billing_type = TXNT.billing_type;
SELECT CRC.resource_rate
FROM cst_resource_costs CRC
WHERE CRC.resource_id = p_bom_resource_id
AND CRC.organization_id = p_organization_id
AND CRC.cost_type_id = 1; -- standard/frozen cost
SELECT BR.unit_of_measure
FROM BOM_RESOURCES BR
WHERE BR.resource_id = p_bom_resource_id;
Update csd_repair_Estimate_lines
set original_cost = item_cost,
item_cost = null
where repair_estimate_line_id = repair_estimate_line_id_mig(j);
Update csd_repair_Estimate_lines
set original_cost = item_cost,
item_cost = null
where repair_estimate_line_id = repair_estimate_line_id_mig(j);
Update csd_repair_Estimate_lines
set original_cost = l_res_cost,
item_cost = l_res_cost
where repair_estimate_line_id = repair_estimate_line_id_mig(j);
--Update the variable l_orig_or_item_cost with l_res_cost so
--that if currency codes are different, we send the updated
--value to the GL API.
l_orig_or_item_cost := l_res_cost;
-- Update original_cost with the item_cost and item_cost
--with null and save this info in CSD_UPG_ERRORS table.
IF l_rate < 0 then
Update csd_repair_Estimate_lines
set original_cost = item_cost,
item_cost = null
where repair_estimate_line_id = repair_estimate_line_id_mig(j);
Update csd_repair_Estimate_lines
set original_cost = item_cost,
item_cost = x_conv_amount
where repair_estimate_line_id =
repair_estimate_line_id_mig(j);
Update csd_repair_Estimate_lines
set original_cost = item_cost
where repair_estimate_line_id =
repair_estimate_line_id_mig(j);
/* INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES('CSD_REPAIR_ESTIMATE_LINES',
'CSD_REPAIR_ESTIMATE_LINES',
repair_estimate_line_id_mig(j),
sysdate,
v_error_text,
'11.5.10');*/
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES('CSD_REPAIR_ESTIMATE_LINES',
'CSD_REPAIR_ESTIMATE_LINES',
repair_estimate_line_id_mig(j),
sysdate,
v_error_text,
'11.5.10');
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES('CSD_REPAIR_ESTIMATE_LINES',
'CSD_REPAIR_ESTIMATE_LINES',
p_estimate_line_id,
sysdate,
v_error_text,
'11.5.10');
/* pending wip jobs and run the update program.
/* */
/* */
/*------------------------------------------------------------------------*/
PROCEDURE csd_repair_history_mig3(p_slab_number IN NUMBER)
IS
-- Table type definitions
-- Shiv Ragunathan, 12/10/03, Changed VARRAY to TABLE, as prior to
-- oracle 9.0.1, rowid is not recognized as a supported datatype
-- for VARRAY, due to which we get PLS-00531 error at compile
-- time.( This was due to a PLSQL bug, fixed in versions 9.0.1
-- and beyond ). Changed VartabType as well to be consistent.
-- TYPE RowidTabType IS VARRAY(1000) OF ROWID;
SELECT HIST.rowid,
HROU.name,
WSH.subinventory
FROM CSD_REPAIR_HISTORY HIST,
WSH_DELIVERY_DETAILS WSH,
HR_ALL_ORGANIZATION_UNITS_VL HROU
WHERE HIST.EVENT_CODE = 'PS'
AND HIST.REPAIR_HISTORY_ID >= v_min
AND HIST.REPAIR_HISTORY_ID <= v_max
AND HIST.PARAMN1 IS NOT NULL
AND WSH.delivery_detail_id = HIST.paramn1
AND HROU.organization_id = WSH.organization_id;
UPDATE CSD_REPAIR_HISTORY HIST
SET HIST.PARAMC3 = ( SELECT hrou.name
FROM hr_all_organization_units_vl HROU,
RCV_TRANSACTIONS RCV
WHERE RCV.transaction_id = HIST.paramn1
AND HROU.organization_id = RCV.organization_id
)
WHERE HIST.EVENT_CODE = 'RR'
AND HIST.REPAIR_HISTORY_ID >= v_min
AND HIST.REPAIR_HISTORY_ID <= v_max
AND HIST.PARAMN1 IS NOT NULL;
UPDATE CSD_REPAIR_HISTORY HIST
SET HIST.PARAMC1 = ( SELECT rcv.subinventory
FROM RCV_TRANSACTIONS RCV
WHERE RCV.transaction_id = HIST.paramn1
)
WHERE HIST.EVENT_CODE = 'RSC'
AND HIST.REPAIR_HISTORY_ID >= v_min
AND HIST.REPAIR_HISTORY_ID <= v_max
AND HIST.PARAMN1 IS NOT NULL;
UPDATE CSD_REPAIR_HISTORY HIST
SET HIST.paramn6 =
nvl( to_number(decode(instr(hist.paramc5,'CSD',1),0,hist.paramc5, substr(hist.paramc5,4,length(hist.paramc5)) ) ),''),
--nvl(to_number(hist.paramc5),''),
hist.paramc5 = NULL
WHERE HIST.EVENT_CODE = 'JS'
AND HIST.REPAIR_HISTORY_ID >= v_min
AND HIST.REPAIR_HISTORY_ID <= v_max;
UPDATE CSD_REPAIR_HISTORY HIST
SET HIST.EVENT_CODE = 'JSU'
WHERE HIST.EVENT_CODE = 'JS'
AND HIST.REPAIR_HISTORY_ID >= v_min
AND HIST.REPAIR_HISTORY_ID <= v_max;
UPDATE CSD_REPAIR_HISTORY HIST
SET HIST.PARAMC7 = ( SELECT task_name
FROM JTF_TASKS_VL
WHERE task_id = HIST.paramn1
)
WHERE HIST.EVENT_CODE IN ('TC', 'TOC', 'TSC')
AND HIST.REPAIR_HISTORY_ID >= v_min
AND HIST.REPAIR_HISTORY_ID <= v_max
AND HIST.PARAMN1 IS NOT NULL;
UPDATE CSD_REPAIR_HISTORY HIST
SET HIST.EVENT_CODE = 'ESU'
WHERE HIST.EVENT_CODE IN ('A', 'R')
AND HIST.REPAIR_HISTORY_ID >= v_min
AND HIST.REPAIR_HISTORY_ID <= v_max;
UPDATE CSD_REPAIR_HISTORY
SET paramc3 = shipping_org_name_arr(i),
paramc4 = shipping_subinv_name_arr(i)
WHERE rowid = rowid_arr(i);
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_REPAIR_HISTORY',
'CSD_REPAIR_HISTORY',
NULL,
sysdate,
v_error_text,
'11.5.10');
/* updated in csd_product_txns table */
/*RELEASED action_type is 'SHIP' and order line qty is > 1 */
/* Only qty that is captured in csd_repair_history is */
/* updated in csd_product_txns table. */
/************************************************************************/
procedure CSD_PRODUCT_TRANS_MIG3(p_slab_Number Number) IS
TYPE NumTabType IS TABLE OF NUMBER
INDEX by Binary_Integer;
Select cpt.rowid,
cpt.product_transaction_Id ,
cpt.Action_TYpe,
cpt.Action_Code,
cpt.estimate_detail_id,
cpt.repair_line_id,
cpt.Prod_txn_Status,
oola.header_id ,
oola.line_id ,
(oola.ordered_quantity - Nvl(oola.cancelled_quantity,0) ) Line_quantity,
oola.shipped_quantity Transacted_Qty,
oola.inventory_item_id ,
oola.ship_to_org_id ,
oola.ship_from_org_id,
msi.serial_Number_control_code,
msi.comms_Nl_trackable_flag ,
msi.lot_control_code ,
msi.location_control_code ,
ced.quantity_required ,
ced.customer_product_id ,
ced.serial_number,
cpt.shipped_serial_number,
crt.repair_type_ref,
NULL, -- locator_id
NULL, -- source_instance_id
NULL, -- non_source_instance_id
NULL, -- quantity_received
NULL, -- Quantity_shipped
NULL, -- source_serial_number
NULL -- non_source_serial_number
From csd_product_transactions cpt ,
csd_repairs cr ,
csd_repair_types_b crt,
cs_estimate_details ced ,
oe_order_headers_all ooha,
oe_order_lines_all oola ,
mtl_system_items_b msi
Where cpt.product_transaction_id >= p_Start_Slab
and cpt.product_transaction_id <= p_End_Slab
and cpt.repair_line_id = cr.repair_line_id
and cr.repair_type_id = crt.repair_type_id
and cpt.estimate_detail_id = ced.estimate_detail_id
and cpt.quantity_received is NULL
and cpt.quantity_shipped is NULL
and cpt.source_instance_id is Null
and cpt.non_source_instance_id is NUll
and cpt.source_serial_number is Null
and cpt.non_source_serial_number is null
and cpt.locator_id is null
and ced.order_header_id = ooha.header_id
and ooha.header_id = oola.header_id
and ced.order_line_id = oola.line_id
and oola.inventory_item_id = msi.inventory_item_id
and oola.ship_from_org_id = msi.organization_id ;
Select rcvt.transaction_id, rcvt.quantity, rcvt.locator_id
From Rcv_Transactions Rcvt
Where rcvt.Transaction_Type = 'DELIVER'
and rcvt.oe_order_header_id = p_order_header_Id
and rcvt.oe_order_line_id in ( Select line_id from oe_order_lines_all
start with line_id = p_Order_Line_Id
connect by prior line_id = split_from_line_id ) ;
Select rcvt.transaction_id, rcvt.quantity, rcvt.locator_id
From Rcv_Transactions Rcvt
Where rcvt.Transaction_Type = 'DELIVER'
and rcvt.oe_order_header_id = p_order_header_Id
and rcvt.oe_order_line_id = p_Order_Line_Id ;
Select wdt.delivery_detail_id, wdt.shipped_quantity,wdt.locator_id ,
Nvl(wdt.serial_number, wsn.fm_serial_number) serial_number
From wsh_delivery_details wdt,
Wsh_Serial_Numbers wsn
Where wdt.Released_Status in ( 'C', 'I')
and wdt.source_header_id = p_order_header_Id
and wdt.source_line_id in ( Select line_id from oe_order_lines_all
start with line_id = p_Order_Line_Id
connect by prior line_id = split_from_line_id )
and wdt.delivery_detail_id = wsn.delivery_detail_id(+);
Select wdt.delivery_detail_id, wdt.shipped_quantity, wdt.locator_id ,
Nvl(wdt.serial_number, wsn.fm_serial_number) serial_number
From wsh_delivery_details wdt,
wsh_serial_numbers wsn
Where wdt.Released_Status in ( 'C', 'I')
and wdt.source_header_id = p_order_header_Id
and wdt.source_line_id = p_Order_Line_Id
and wdt.delivery_detail_id = wsn.delivery_detail_id(+);
SavePoint Update_prod_Txns ;
Select Quantity
Into l_Quantity
From csd_repair_history
Where event_code = 'RR'
and paramn1 = rcv_txn_rec.transaction_id
and repair_line_id = V_Repair_Line_Id(j);
-- might have deleted records from SO_RMA interface table after those records are
-- successfully received in inventory. In such cases there will be no record created
-- in rcv_transactions table.So Depot has changed the logic for finding
-- serial number for recevied items, it looks for serial number information in
-- rcv_transactions table but if the record does not exist it looks for the info in
-- csd_repairs table, if the record is not found there too then Depot will assign
-- blank values to source serial number column.
-- This strategy will help upgrade process not to stop because of missing records in
-- rcv_transactions table.
-- Item is serial controlled
l_procedure_Event := 'Delivery info for serial and received item';
Select rcvt.quantity, rcvt.locator_id , rst.serial_num
Into v_Quantity_Received(j), v_Locator_Id(j),v_Source_Serial_Number(j)
From Rcv_Transactions Rcvt ,
Rcv_Serial_Transactions rst
Where rcvt.Transaction_Type = 'DELIVER'
and rcvt.oe_order_header_id = v_order_header_id(j)
and rcvt.oe_order_line_id = v_order_line_id(j)
and rcvt.transaction_id = rst.transaction_id
and rownum = 1;
Select Serial_Number Into V_Source_Serial_Number(j)
From CSD_Repairs
Where Repair_line_Id = V_Repair_Line_ID(j);
select instance_id
Into v_Source_Instance_Id(j)
from csi_item_instances
where inventory_item_id = v_inventory_item_id(j)
and serial_number = v_Source_Serial_Number(j) ;
Select Quantity
Into l_Quantity
From csd_repair_history
Where event_code = 'PS'
and paramn1 = Delv_Detl_rec.Delivery_Detail_id
and repair_line_id = v_Repair_Line_Id(j);
select Serial_Number
Into v_Non_Source_Serial_Number(j)
from csi_item_instances
where inventory_item_id = v_inventory_item_id(j)
and instance_id = v_Customer_Product_Id(j)
and inv_master_organization_Id = cs_std.get_item_valdn_orgzn_id;
Select wdt.shipped_quantity, wdt.locator_id ,
Nvl(wdt.serial_number, wsn.fm_serial_number) Serial_number
Into v_Quantity_Shipped(j), v_Locator_Id(j) , v_Source_Serial_Number(j)
From wsh_delivery_details wdt,
wsh_serial_numbers wsn
Where wdt.Released_Status in ( 'C', 'I')
and wdt.source_header_id = v_order_header_id(j)
and wdt.source_line_id = v_order_line_id(j)
and wdt.delivery_detail_id = wsn.delivery_detail_id(+)
and rownum = 1;
select instance_id
Into v_Source_Instance_Id(j)
from csi_item_instances
where inventory_item_id = v_inventory_item_id(j)
and serial_number = v_Source_Serial_Number(j) ;
select Serial_Number
Into v_Non_Source_Serial_Number(j)
from csi_item_instances
where inventory_item_id = v_inventory_item_id(j)
and instance_id = v_Customer_Product_Id(j);
ROLLBACK TO Update_prod_Txns;
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_PRODUCT_TRANSACTIONS',
'CSD_PRODUCT_TRANSACTIONS',
v_Product_Trans_id(j),
sysdate,
l_error_text,
'11.5.10');
Update Csd_Product_Transactions
Set source_serial_number = v_Source_Serial_Number(i) ,
source_instance_id = v_source_instance_id(i) ,
non_source_serial_number = v_non_source_serial_number(i) ,
non_source_instance_id = v_non_source_instance_id(i) ,
locator_id = v_locator_id(i) ,
-- sub_inventory = v_sub_inventory(i), bug#3622825
-- Lot_Number = v_Lot_Number(i), bug#3622825
Quantity_Received = v_Quantity_Received(i) ,
Quantity_Shipped = v_Quantity_Shipped(i),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
Where rowid = v_rowid(i)
And ( v_Source_serial_number(i) is not null
Or v_Source_Instance_id(i) is Not Null
Or V_Non_Source_Serial_Number(i) is Not Null
or V_Non_Source_Instance_id(i) is Not Null
or V_Quantity_Shipped(i) is Not Null
or V_Quantity_Received(i) is Not Null
or V_Locator_Id(i) is Not Null );
ROLLBACK TO Update_prod_Txns;
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_PRODUCT_TRANSACTIONS',
'CSD_PRODUCT_TRANSACTIONS',
NULL,
sysdate,
l_error_text,
'11.5.10');
INSERT INTO CSD_UPG_ERRORS
(ORIG_SYSTEM_REFERENCE,
TARGET_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID,
UPGRADE_DATETIME,
ERROR_MESSAGE,
MIGRATION_PHASE)
VALUES ('CSD_PRODUCT_TRANSACTIONS',
'CSD_PRODUCT_TRANSACTIONS',
NULL,
sysdate,
l_error_text,
'11.5.10');