[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT RES.resource_code
FROM WIP_TRANSACTIONS WTXN, BOM_RESOURCES RES
WHERE WTXN.wip_entity_id = p_wip_entity_id
AND RES.resource_id = WTXN.resource_id
AND RES.billable_item_id IS NULL;
SELECT RES.billable_item_id INVENTORY_ITEM_ID,
SUM( NVL( WTXN.primary_quantity, 0 )) QUANTITY,
MSIW.concatenated_segments WIP_ITEM_NAME,
MSID.inventory_item_id DEPOT_ITEM_ID,
MSID.material_billable_flag BILLING_TYPE
FROM WIP_TRANSACTIONS WTXN,
BOM_RESOURCES RES,
MTL_SYSTEM_ITEMS_KFV MSIW,-- For WIP organization
-- MTL_SYSTEM_ITEMS_KFV MSID -- For Depot/Service organization
MTL_SYSTEM_ITEMS_B MSID -- For Depot/Service organization
WHERE WTXN.wip_entity_id = p_wip_entity_id
AND RES.resource_id = WTXN.resource_id
AND MSIW.inventory_item_id = RES.billable_item_id
AND MSIW.organization_id = p_wip_organization
AND MSID.inventory_item_id(+) = RES.billable_item_id
AND MSID.organization_id(+) = p_depot_organization
AND MSID.material_billable_flag IS NULL -- Billing type not defined
GROUP BY RES.billable_item_id, MSIW.concatenated_segments,
MSID.inventory_item_id, MSID.material_billable_flag;
SELECT RES.billable_item_id INVENTORY_ITEM_ID,
SUM( NVL( WTXN.primary_quantity, 0 )) QUANTITY,
MSID.concatenated_segments DEPOT_ITEM_NAME
FROM WIP_TRANSACTIONS WTXN,
BOM_RESOURCES RES,
MTL_SYSTEM_ITEMS_KFV MSID -- For Depot/Service organization
WHERE WTXN.wip_entity_id = p_wip_entity_id
AND RES.resource_id = WTXN.resource_id
AND MSID.inventory_item_id = RES.billable_item_id
AND MSID.organization_id = p_depot_organization
AND MSID.material_billable_flag IS NOT NULL
AND NOT EXISTS
(SELECT 'x'
FROM CS_TXN_BILLING_TYPES TBT,
CSD_REPAIR_TYPES_SAR SAR
WHERE TBT.billing_type = MSID.material_billable_flag
AND SAR.txn_billing_type_id = TBT.txn_billing_type_id
AND SAR.repair_type_id = p_repair_type_id
AND TRUNC(NVL(TBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
)
GROUP BY RES.billable_item_id, MSID.concatenated_segments;
SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
MSIW.concatenated_segments WIP_ITEM_NAME,
SUM( DECODE( MMT.transaction_type_id
, G_MTL_TXN_TYPE_COMP_ISSUE, ABS( mmt.primary_quantity )
, G_MTL_TXN_TYPE_COMP_RETURN,( -1 * ABS( mmt.primary_quantity )))) Quantity,
MSID.inventory_item_id DEPOT_ITEM_ID,
MSID.material_billable_flag BILLING_TYPE
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_KFV MSIW, -- For WIP organization
MTL_SYSTEM_ITEMS_B MSID -- For Depot/Service organization
-- MTL_SYSTEM_ITEMS_KFV MSID -- For Depot/Service organization
WHERE MMT.transaction_source_id = p_wip_entity_id
AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
G_MTL_TXN_TYPE_COMP_RETURN )
AND MMT.inventory_item_id <> p_inventory_item_id
AND MSIW.inventory_item_id = MMT.inventory_item_id
AND MSIW.organization_id = p_wip_organization
AND MSID.inventory_item_id(+) = MMT.inventory_item_id
AND MSID.organization_id(+) = p_depot_organization
AND MSID.material_billable_flag IS NULL -- Billing type not defined
GROUP BY mmt.inventory_item_id,
MSIW.concatenated_segments,
MSID.inventory_item_id,
MSID.material_billable_flag;
SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
MSID.concatenated_segments DEPOT_ITEM_NAME,
SUM( DECODE( MMT.transaction_type_id
, G_MTL_TXN_TYPE_COMP_ISSUE, ABS( mmt.primary_quantity )
, G_MTL_TXN_TYPE_COMP_RETURN,( -1 * ABS( mmt.primary_quantity )))) Quantity
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_KFV MSID -- For Depot/Service organization
WHERE MMT.transaction_source_id = p_wip_entity_id
AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
G_MTL_TXN_TYPE_COMP_RETURN )
AND MMT.inventory_item_id <> p_inventory_item_id
AND MSID.inventory_item_id = MMT.inventory_item_id
AND MSID.organization_id = p_depot_organization
AND MSID.material_billable_flag IS NOT NULL
AND NOT EXISTS
(SELECT 'x'
FROM CS_TXN_BILLING_TYPES TBT,
CSD_REPAIR_TYPES_SAR SAR
WHERE TBT.billing_type = MSID.material_billable_flag
AND SAR.txn_billing_type_id = TBT.txn_billing_type_id
AND SAR.repair_type_id = p_repair_type_id
AND TRUNC(NVL(TBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
)
GROUP BY mmt.inventory_item_id,
MSID.concatenated_segments;
SELECT CEST.estimate_detail_id,
JTB.task_id actual_source_id -- swai: bug 6042488
FROM CS_ESTIMATE_DETAILS CEST,
CSF_DEBRIEF_LINES CDBL,
CSF_DEBRIEF_HEADERS CDBH, -- swai: bug 6042488
JTF_TASKS_B JTB, -- swai: bug 6042488
JTF_TASK_ASSIGNMENTS JTA -- swai: bug 6042488
WHERE CEST.original_source_code = 'DR'
AND CEST.original_source_id = p_repair_line_id
AND CEST.source_code = 'SD'
AND CDBL.debrief_line_id = CEST.source_id
AND CDBH.debrief_header_id = CDBL.debrief_header_id
/* swai: added for bug fix 5949309 */
AND JTB.source_object_id = CEST.original_source_id
AND JTB.source_object_type_code = 'DR'
AND nvl (JTB.deleted_flag, 'N') <> 'Y'
AND CDBH.task_assignment_id = jta.task_assignment_id
AND JTA.task_id = jtb.task_id
AND JTA.assignee_role = 'ASSIGNEE'
/* end swai fix 5949309 */
AND NOT EXISTS
(
SELECT 'EXISTS'
FROM CSD_REPAIR_ACTUAL_LINES ACTL
WHERE ACTL.repair_actual_id = nvl(p_repair_actual_id,ACTL.repair_actual_id)
AND ACTL.estimate_detail_id = CEST.estimate_detail_id
AND ACTL.actual_source_code = G_ACTUAL_SOURCE_CODE_TASK
AND ACTL.actual_source_id = JTB.task_id -- swai: bug 6042488
);
SELECT count(distinct CEST.currency_code)
FROM CS_ESTIMATE_DETAILS CEST,
CSD_REPAIRS RO
WHERE RO.repair_line_id = p_repair_line_id
AND CEST.original_source_code = 'DR'
AND CEST.original_source_id = RO.repair_line_id
AND CEST.source_code = 'SD'
AND RO.currency_code <> CEST.currency_code ;
select order_line_id from
csd_repair_actual_lines_v
where repair_actual_line_id = p_repair_actual_line_id;
p_source_entity_id2 => NULL, -- Since we want to delete all messages.
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_curr_actual_line_rec.LAST_UPDATED_BY
l_curr_actual_line_rec.LAST_UPDATE_DATE
l_curr_actual_line_rec.LAST_UPDATE_LOGIN
*/
/*
-- In 11.5.10 we don't do Actual costing
l_curr_actual_line_rec.ITEM_COST := task_charge_line_rec.item_cost;
'Calling CSD_REPAIR_ACTUAL_LINES_PVT.update_repair_actual_lines');
CSD_REPAIR_ACTUAL_LINES_PVT.update_repair_actual_lines (
p_api_version => 1.0,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
px_csd_actual_lines_rec => l_curr_actual_line_rec,
px_charges_rec => l_charge_line_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'Returned from CSD_REPAIR_ACTUAL_LINES_PVT.update_repair_actual_lines');
SELECT XREF.wip_entity_id, XREF.organization_id WIP_Organization_Id,
XREF.inventory_item_id, XREF.JOB_NAME
FROM CSD_ACTUALS_FROM_WIP_V XREF
WHERE XREF.repair_line_id = p_repair_line_id;
SELECT XREF.wip_entity_id,
WENT.WIP_ENTITY_NAME JOB_NAME
FROM CSD_REPAIR_JOB_XREF XREF,
WIP_ENTITIES WENT
WHERE XREF.repair_line_id = p_repair_line_id
AND WENT.wip_entity_id = XREF.wip_entity_id
AND EXISTS
(SELECT 'x'
FROM CSD_REPAIR_JOB_XREF RJOB
WHERE RJOB.wip_entity_id = XREF.wip_entity_id
HAVING COUNT(*) > 1
);
SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
MSI.primary_uom_code UOM,
-- swai: bug fix 4458737 (FP of 4425939) remove CEIL
-- CEIL(SUM( DECODE( MMT.transaction_type_id
SUM( DECODE( MMT.transaction_type_id
, G_MTL_TXN_TYPE_COMP_ISSUE, ABS( mmt.primary_quantity )
, G_MTL_TXN_TYPE_COMP_RETURN,
( -1 * ABS( mmt.primary_quantity )))) QUANTITY,
MSI.concatenated_segments ITEM_NAME,
MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
G_ACTUAL_SOURCE_CODE_WIP ACTUAL_SOURCE_CODE,
l_wip_entity_id ACTUAL_SOURCE_ID
FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_SYSTEM_ITEMS_KFV MSI,
CSD_REPAIR_TYPES_SAR RTYP, CS_TXN_BILLING_TYPES TXBT
WHERE MMT.transaction_source_id = l_wip_entity_id
AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
G_MTL_TXN_TYPE_COMP_RETURN )
AND MMT.inventory_item_id <> l_inventory_item_id
AND MSI.inventory_item_id = MMT.inventory_item_id
-- AND MSI.organization_id = cs_std.get_item_valdn_orgzn_id
AND MSI.organization_id = p_organization_id
AND MSI.material_billable_flag IS NOT NULL
AND TXBT.billing_type = MSI.material_billable_flag
AND RTYP.repair_type_id = p_repair_type_id
AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
GROUP BY MMT.inventory_item_id, MSI.primary_uom_code,
MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
l_wip_entity_id
ORDER BY MMT.inventory_item_id;
SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
MSI.primary_uom_code UOM,
SUM( ABS( mmt.primary_quantity )) QUANTITY,
MSI.concatenated_segments ITEM_NAME,
MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
G_ACTUAL_SOURCE_CODE_WIP ACTUAL_SOURCE_CODE,
l_wip_entity_id ACTUAL_SOURCE_ID
FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_SYSTEM_ITEMS_KFV MSI,
CSD_REPAIR_TYPES_SAR RTYP, CS_TXN_BILLING_TYPES TXBT
WHERE MMT.transaction_source_id = l_wip_entity_id
AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
G_MTL_TXN_TYPE_COMP_RETURN )
AND MMT.inventory_item_id <> l_inventory_item_id
AND MSI.inventory_item_id = MMT.inventory_item_id
-- AND MSI.organization_id = cs_std.get_item_valdn_orgzn_id
AND MSI.organization_id = p_organization_id
AND MSI.material_billable_flag IS NOT NULL
AND TXBT.billing_type = MSI.material_billable_flag
AND RTYP.repair_type_id = p_repair_type_id
AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
AND MMT.transaction_type_id = G_MTL_TXN_TYPE_COMP_ISSUE
GROUP BY MMT.inventory_item_id, MSI.primary_uom_code,
MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
l_wip_entity_id
ORDER BY MMT.inventory_item_id;
SELECT RES.billable_item_id INVENTORY_ITEM_ID,
WTXN.primary_uom UOM,
-- swai: bug fix 4458737 (FP of 4425939) remove CEIL
-- CEIL(SUM( NVL( WTXN.primary_quantity, 0 ))) QUANTITY,
SUM( NVL( WTXN.primary_quantity, 0 )) QUANTITY,
MSI.concatenated_segments ITEM_NAME,
MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
G_ACTUAL_SOURCE_CODE_WIP ACTUAL_SOURCE_CODE,
l_wip_entity_id ACTUAL_SOURCE_ID,
RES.resource_id RESOURCE_ID -- Added for ER 3607765, vkjain.
FROM WIP_TRANSACTIONS WTXN, BOM_RESOURCES RES,
MTL_SYSTEM_ITEMS_KFV MSI, CSD_REPAIR_TYPES_SAR RTYP,
CS_TXN_BILLING_TYPES TXBT
WHERE WTXN.wip_entity_id = l_wip_entity_id
AND WTXN.transaction_type IN( 1, 2, 3 )
AND RES.resource_id = WTXN.resource_id
AND MSI.inventory_item_id = RES.billable_item_id
-- and MSI.organization_id = cs_std.get_item_valdn_orgzn_id
AND MSI.organization_id = p_organization_id
AND MSI.material_billable_flag IS NOT NULL
AND TXBT.billing_type = MSI.material_billable_flag
AND RTYP.repair_type_id = p_repair_type_id
AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
GROUP BY RES.billable_item_id, WTXN.primary_uom,
MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
l_wip_entity_id, RES.resource_id
ORDER BY RES.billable_item_id;
select default_po_num
into l_default_po_number
from csd_repairs
where repair_line_id = p_repair_line_id;
SELECT ESTL.repair_estimate_line_id,
ESTL.estimate_detail_id,
-- ESTL.item_cost, In 11.5.10 we don't do Actual costing.
ESTL.justification_notes,
ESTL.resource_id,
ESTL.context,
ESTL.attribute1,
ESTL.attribute2,
ESTL.attribute3,
ESTL.attribute4,
ESTL.attribute5,
ESTL.attribute6,
ESTL.attribute7,
ESTL.attribute8,
ESTL.attribute9,
ESTL.attribute10,
ESTL.attribute11,
ESTL.attribute12,
ESTL.attribute13,
ESTL.attribute14,
ESTL.attribute15,
ESTL.override_charge_flag
FROM CSD_REPAIR_ESTIMATE ESTH,
CSD_REPAIRS CR, -- swai: bug 4618500 (FP of 4580845)
CSD_REPAIR_ESTIMATE_LINES ESTL
-- swai: bug 4618500 (FP of 4580845)
-- Join with table CSD_REPAIRS added
-- We should not import the line from estimate to Actuals until the lines are accepted (i.e. approved)
-- if the flag Estimate Approval Required flag is checked. (This would make the behavior consistent with 1159)
-- Modified the query to achieve following:
-- (1)If Estimate Approval Required flag is checked and status of the estimate is accepted then only
-- import estimate lines to Actuals.
-- (2)If Estimate Approval Required flag unchecked then do not restrict lines from import.
WHERE CR.repair_line_id = p_repair_line_id
AND ( ( nvl(CR.approval_required_flag,'N') ='Y' and nvl(CR.approval_status,'X')= 'A' )
OR
( nvl(CR.approval_required_flag,'N') ='N' )
)
AND ESTH.repair_line_id = CR.repair_line_id
-- end swai: bug 4618500 (FP of 4580845)
AND ESTL.repair_estimate_id = ESTH.repair_estimate_id
AND NOT EXISTS
(
SELECT 'EXISTS'
FROM CSD_REPAIR_ACTUAL_LINES ACTL
WHERE ACTL.actual_source_code = G_ACTUAL_SOURCE_CODE_ESTIMATE
AND ACTL.actual_source_id = ESTL.repair_estimate_line_id
)
order by ESTL.estimate_detail_id ; /* nnadig,fix for bug#8219894 */
p_source_entity_id2 => NULL, -- Since we want to delete all messages.
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
--deleted the code here in between
-- end swai: bug 7119695
--bug#13627518 --bug#14014723
END IF; -- IF NOT l_skip_curr_row
select distinct
hp.party_site_id
from hz_party_sites_v hp,
hz_parties hz,
hz_cust_acct_sites_all hca,
hz_cust_site_uses_all hcsu
where hcsu.site_use_code = p_site_use_type
and hp.status = 'A'
and hcsu.status = 'A'
and hp.party_id = hz.party_id
and hp.party_id = p_party_id
and hca.party_site_id = hp.party_site_id
and hca.cust_account_id = p_account_id
and hcsu.cust_acct_site_id = hca.cust_acct_site_id
and hca.org_id = p_org_id
and hcsu.primary_flag = 'Y'
and rownum = 1;
select act.bill_to_account_id,
act.bill_to_party_id,
act.bill_to_party_site_id,
csd.incident_id
from csd_repair_actuals act,
csd_repairs csd
where csd.repair_line_id = p_repair_line_id
and act.repair_line_id = csd.repair_line_id;
select cs.account_id,
cs.bill_to_party_id,
cs.bill_to_site_id,
cs.ship_to_site_id
from csd_repairs csd,
cs_incidents_all_b cs
where csd.repair_line_id = p_repair_line_id
and csd.incident_id = cs.incident_id;